HPE Community read-only access December 15, 2018
This is a maintenance upgrade. You will be able to read articles and posts, but not post or reply.
Hours:
Dec 15, 4:00 am to 10:00 am UTC
Dec 14, 10:00 pm CST to Dec 15, 4:00 am CST
Dec 14, 8:00 pm PST to Dec 15, 2:00 am PST
System Administration
cancel
Showing results for 
Search instead for 
Did you mean: 

Swapping space critical on DB servers

 
SOLVED
Go to solution
Fenglin
Regular Advisor

Swapping space critical on DB servers

hi

In what circumstances will we receive swapping space critical on DB servers? What does this alert mean?

Regards
Feng Lin
36 REPLIES
Johnson Punniyalingam
Honored Contributor

Re: Swapping space critical on DB servers

in term of "Performance issue" -> too much work load to DB Server,

or improper - Kernal parameter settings
Problems are common to all, but attitude makes the difference
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

How to know whether there are improper kernel settings?

In terms of performance, we added a few more jobs on the DB servers but they are schedules at different timings when we receive the alerts. That is why I need your help to throw me more light into this issue.

Regards
Feng Lin

Johnson Punniyalingam
Honored Contributor

Re: Swapping space critical on DB servers

for more information

http://www.dba-oracle.com/t_server_swap_space_allocation.htm
Problems are common to all, but attitude makes the difference
Johnson Punniyalingam
Honored Contributor
Solution

Re: Swapping space critical on DB servers

>>we added a few more jobs on the DB servers but they are schedules at different timings when we receive the alerts. That is why I need your help to throw me more light into this issue<<

Yes - I can shed some light on OS portion only

suggestion and recommendation related to oracle you cross check with oracle support,

how much is your memeory installed

dmesg |grep -i pshyical

swapinfo -mat

what OS version?

uname -a;model

what are your currnet parmeter settings

Based on above output hope you can also shed some light on better understanding of your problem
Problems are common to all, but attitude makes the difference
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

can too many queries on the DB servers cause swapping space critical? or just high cpu alerts ?

I ask this because after a recent project cutover, we are receiving these alerts which we do not receive before. Our DB servers are serving customers online as well.

The main point is do we need to add memory on our DB servers ?

Aneesh Mohan
Honored Contributor

Re: Swapping space critical on DB servers

Hi,

>>can too many queries on the DB servers cause swapping space critical? or just high cpu alerts ?

Yes , PGA memory is a swappable memory,if your DML operations required more PGA work area to complete ,I belive it can demand more swap memory.

How much is your swap memory utilisation ?

Aneesh
Johnson Punniyalingam
Honored Contributor

Re: Swapping space critical on DB servers

>>The main point is do we need to add memory on our DB servers ?<<

Performance - Not only based on the adding memory will not solve you issue,

As mentioned on above post if you could provide information asked , hope we can give some suggestion

reason for asking "performance" issue could be many reason it depends all factors ,

CPU,Memory,Swap,Kernal settings,disk, patches.. etc

I would suggestion if you escalte this issue to DBA admin, hopefully they could give clear picture - hence database inputs also important

Hope this helps

Johnson
Problems are common to all, but attitude makes the difference
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

I got the following info on the server

swapinfo -mat
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 12288 4534 7754 37% 0 - 1 /dev/vg00/lvol2
reserve - 7749 -7749
memory 12721 8340 4381 66%
total 25009 20623 4386 82% - 0 -
uname -a;model
HP-UX nspppdb1 B.11.11 U 9000/800 1236689390 unlimited-user license;9000/800/rp4440

There is no info when i do 'dmesg|grep -i physical

Please advice.
Aneesh Mohan
Honored Contributor

Re: Swapping space critical on DB servers

Hi,

You need more swap memory.

To get available physical memory:-

# echo phys_mem_pages/D | adb64 -k /stand/vmunix /dev/mem
phys_mem_pages:
phys_mem_pages: 524288 ---> multiply the value by 4096 to get in bytes


# bc
524288 * 4096 /1024/1024
2048 ------> 2GB physical memory


regards,
Aneesh
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

Hi Aneesh

How did you deduce that more swap memory is needed? and how to do it?

Thanks in advance
Feng Lin

Re: Swapping space critical on DB servers

>How did you deduce that more swap memory is needed?

Possibly by using the old rule of thumb of 2 X the size of RAM??

>total 25009 20623 4386 82%

You have 4 Gb free. If you aren't planning to start more processes, you may have enough.
"swapping space critical": This may mean you are low on swap.
Johnson Punniyalingam
Honored Contributor

Re: Swapping space critical on DB servers

As adviced by <> if you are not planning to start more process - you have enough swap space.

If You intend to start more process, you can increase you swap space

Are you familiar with "LVM" commands ..?
Problems are common to all, but attitude makes the difference
Johnson Punniyalingam
Honored Contributor

Re: Swapping space critical on DB servers

Using swapon command


Create the logical volume for your secondary swap:

# lvcreate -L 128 /dev/vgXX

NOTE: "-L 128" is the size of desired swap


Verify the logical volume:

# lvdisplay /dev/vgXX/lvolXX

Use SAM to declare your new secondary swap or use the command line:

# swapon /dev/vgXX/lvolXX

Verify it has been added in "/etc/fstab" when using SAM, or add it manually when using the command line.

Example:

/dev/vgXX/lvolXX . swap defaults 0 0
Problems are common to all, but attitude makes the difference
Aneesh Mohan
Honored Contributor

Re: Swapping space critical on DB servers

Hi,

In dedicated server mode ,the UGA is allocated in the PGA and single session can demand more virtual memory depends upon the transaction/statement and number of memory objects .If you executed heavy sorting queries or highly memory intensive queries you need to have more virtual memory .
As per swa info o/p.
Your system had used more than 4GB swap memory (page out) .It indicates your system might have experienced low on physical memory than the threshold and triggered paging out operation using LRU., presumably if we have sufficient physical memory we shouldn`t need to page out ever
There are cases where the paging out can be caused by file system buffers also quite a bit.
vmstat utility gives you â poâ and â srâ rate , if it is rising steadily during your normal operation you can decide to add more physical memory.
If your system is experiencing memory pressure then there are chances of paging out critical parts of the SGA is never good unless you use lock_sga instance parameter .

Regards,
Aneesh
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

Thanks to all for your replies.

We just did a data migration cutover. Before the data migration, there was no swap space critical alerts on the DB server. After the data migration, we start receiving the alerts. Obviously, we want to know what is causing the additional memory required.

I realise that we receive the alerts when there are 4 or more ORACLEISVCEPS processes running on the DB server. There were some changes to the schema ISVCEPS(now, it exists both the old and the new). We did some deployment changes as well.

Based on your DB expertise, are you able to help me identify where the root cause is?
Michael Steele_2
Honored Contributor

Re: Swapping space critical on DB servers

Please provide

a) Total physical memory size
b) oracle SGA size
c) ipcs -mob
d) vmstat 5 5
e) UNIX95=1 ps -ef -o vsz,pid,ppid,state,chan,comm
f) UNIX95=1 ps -ef -o pcpu,pid,ppid,state,chan,comm
Support Fatherhood - Stop Family Law
Michael Steele_2
Honored Contributor

Re: Swapping space critical on DB servers

g) swapinfo -tam
h) Append to - UNIX95=1 ps -ef -o .... | sort -rn | head 15
i) exact error messages and source
Support Fatherhood - Stop Family Law
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

Attached are the results.

Best regards
Feng Lin
Michael Steele_2
Honored Contributor

Re: Swapping space critical on DB servers

Hi

You've got 2 GB of Total Memory and an SGA 1.8 GB and 5.5 GB of shared memory, 110 MB of free memory 5.0 GB Active Memory?????

Please run this command and verify your Physical memory size

Glance, m for memory, look around at the bottom of page for actual size
-or-
grep Phy /var/adm/syslog/syslog.log
-or-
dmesg | grep Phy

One of this should verify Total Memory.
#######################
Note: For sure you are out of swap. 83% Total means its time to add some more.

Also, you paged out once. 83% swap, not unexpected or unusual

The UNIX95 commands are too big. Please rerun them with appended to the end

| sort -rn | hear -15

Note: 25 GB swap.

Dude, you got a lot more RAM than you think.

Support Fatherhood - Stop Family Law
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

Attached are the results.

Please let me know whether we need to add additional memory. How much more can we add on the server? and how to do it?

Thanks
Feng Lin
Aneesh Mohan
Honored Contributor

Re: Swapping space critical on DB servers

Before planning for a memory upgrade ,It is better to check few things from database part also.

a) Memory (session uga memory/current uga memory) use for each user session.

b) Check for IDLE/Snipped session and schedule a script to clear IDLE/Snipped sessions.

c) If you are using dedicated server mode ,you can change it to shared server mode for efficient utilising of session memory.

ANeesh
Fenglin
Regular Advisor

Re: Swapping space critical on DB servers

Will there be any side effects if we change to shared server mode? We are currently using dedicated mode.

Regarding the script to clear idle sessions, can give more clue on how to do it? An example of such a script would be useful.

After the data migration, what is causing the extra memory needed to do our usual workload on the server? Any idea?
Michael Steele_2
Honored Contributor

Re: Swapping space critical on DB servers

Hi

a) You have 16.0gb total Phyical memory
b) 25 GB of swap
c) To get down to 75% utilized you need
25GB/.75 = 33.33 Tot, or another 8.33 GB of additional swap.
D) To get down to 65% utilized you need
25GB/.65 = 38.43 Tot, or another 13.43 GB of additional swap
e) Oracle listener is using the most memory of any process

1167932 16555 1 R tnslsnr
689212 16779 1 S tnslsnr
135228 17235 1 S tnslsnr

f) Here are your greatest CPU hogs

23.41 22537 1 S oracleISVCEPS
21.09 20731 1 S oracleISVCEPS
19.59 4019 1 S oracleISVCEPS
15.78 2628 1 R oracleNSPAY
9.61 24922 1 S oracleISVCEPS

g) Follow this pocedure to add swap

- http://forums13.itrc.hp.com/service/forums/questionanswer.do?admit=&threadId=451429



Support Fatherhood - Stop Family Law
Michael Steele_2
Honored Contributor

Re: Swapping space critical on DB servers

Note: You need to assign 0 to 10 points to every responder and close the thread when finished.
Support Fatherhood - Stop Family Law