Swapping space critical on DB servers

[Fenglin]

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]

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]

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]

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

[Michael Steele_2]

Note: You need to assign 0 to 10 points to every responder and close the thread when finished.

[Aneesh Mohan]

Hi,


IDLE_TIMEOUT
============

You have to set idle_time out in profile and also set the init-ora-parameter RESOURCE_LIMIT =TRUE to do this.



For killing a Snipped Session:-
-------------------------------

#!/bin/sh
tmpfile=/tmp/tmp.$$
sqlplus -s '/ as sysdba' << !
spool $tmpfile
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
quit
!
for x in `cat $tmpfile | grep "^[0123456789]"`
do
kill -9 $x
done
rm $tmpfile
date >> date.txt
#


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

I dont think so because of adding more data into your database you got a problem in physical memory.

You have to increase the SGA before changing in to shared mode since it uses UGA from SGA once it has converted.Get an advice from your dba and do the changes.

Aneesh

[Fenglin]

Hi Aneesh

Am I correct to say that there are 2 solutions to fix this issue?
1) To change DB config from dedicated to shared
2) To increase swap space on the server

If yes, which one would you advise?

Many thanks
Feng Lin

[Aneesh Mohan]

Hi ,

I may select the first one ,also tune SGA (shared_pool/large_pool) to appropriate values.

If you are using 10g or above , you can enable SGA_TARGET to a high value and let oracle takes the control on it.If it is 11g then Memory_target will take care of both (SGA/PGA)

Aneesh

[Fenglin]

Hi Aneesh

We are still using Oracle 9.2.0.8. Please advice.

Thanks
Feng Lin

[Aneesh Mohan]

It's best to review the concepts manual for the general architecture. Links to the appropraite area ffor 9i and 10g are

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c09procs.htm#19210
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1644

After you have an idea how this works, head over to the Administator's Guide at http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/manproc.htm#13880 to learn about the parameters and how to set them.


Aneesh