We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Oracle Potential Server Performance Impact

Medium Priority
417 Views
Last Modified: 2013-12-27
I have a Solaris 9 server which has three instances of Oracle, a Physical Standby and two development environments.  Following a power cut and UPS failure, my server began to run incredibly slowly following a reboot.  An output from "sar -g 5 5" and "prstat -s size -n 5" show that my server is paging and the top memory resources are Oracle.  The only instance up at the moment is the physical standby.  I am also running a restore from tape to the server.

Is this likely to be an issue with the DR instance? What would be the best way to resolve the issue? Recreate the physical standby?

Thanks,

Terry
michaelt@colt: /d10/oracle/proddata
$ sar -g 5 5
 
SunOS colt 5.9 Generic_122300-10 sun4u    07/07/2009
 
09:34:37  pgout/s ppgout/s pgfree/s pgscan/s %ufs_ipf
09:34:42   336.88  5265.48  5640.63 428815.75     0.00
09:34:47   430.27  6682.62  7222.07 411592.19     0.00
09:34:52   442.72  6929.51  7306.21 320783.28     0.00
09:34:57   427.18  6656.50  7055.53 301319.03     0.00
09:35:03   290.65  4474.66  4975.00 332370.56     0.00
 
Average    383.01  5960.94  6400.87 357967.91     0.00
michaelt@colt: /d10/oracle/proddata
$ prstat -s size -n 5
   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
 14997 oracle   3636M 3556M sleep   59    0   0:01:01 0.0% oracle/71
   889 oracle   3611M 3556M sleep   59    0   0:01:35 0.0% oracle/258
 15017 oracle   3611M 3556M sleep   59    0   0:00:12 0.0% oracle/11
 14963 oracle   3611M 3557M sleep   59    0   0:00:00 0.0% oracle/11
   918 oracle   3609M 3549M sleep   59    0   0:00:01 0.0% oracle/1
Total: 124 processes, 633 lwps, load averages: 4.14, 3.20, 2.15

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
Please check the network parameters and the NIC cards.
Are they working properly?
Are the parameters correct?

Also check the sessions on the servers:

SELECT count(*) FROM v$session;

The memory should be used up to the last byte - this is the normal behavior of Unix.
But paging is not good.
Top Expert 2009

Commented:
What is the overall system memory usage? It may be that you configured Oracle with too much memory and it did not take affect until the restart. Perhaps someone modified init settings with "alter system .. scope = spfile", so they did not affect until reboot.

Author

Commented:
well sga_max_size is set to 3673741824 whilst shminfo_shmmax is a massive 9663676416 as the /etc/system file was taken from a production system.  The server itself has only 4GB or RAM so I figure, it has over allocated shmmax thus forcing everything else to page into virtual memory (as is currenlty happening on an idle db with no users logged on or requests running).

do you aggree?
Top Expert 2009

Commented:
Yes! That is way too much memory your SGA_MAX_SIZE should _never_ be larger than your physical memory size. Oracle will just use the virtual memory with no regard to physical vs swap space

That is your problem.
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
SGA_MAX_SIZE is slightly smaller than virtual memory, it is shminfo_shmmax  which is ridiculously high.  Does this basically have the same outcome?

Thanks,

Terry
Top Expert 2009

Commented:
Even if SGA_MAX_SIZE is slightly smaller, this may not leave enough overhead for:

1) PGA
2) Oracle process overhead
3) Other OS  / kernel processes
4) Other applications

So I recommend decrease SGA to no more than 50% of total RAM or you will have the problems you see. For 4GB RAM I recommend a 2GB or less SGA.

Author

Commented:
That certainly sounds feasible - I will look into changing this now and see what the results are.  Thanks mrjoltcola :)
johnsoneSenior Oracle DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi,
No the physical standby is just mounted as standby database and not openned.  I will have two development instances on this server once I have sorted out the performance issues as well so any space will be graciously received.

Thanks,

Terry
johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
Then there is definitely no need for an SGA that large.  Cut it way down.

Author

Commented:
Good advice - thanks Johnsone
CERTIFIED EXPERT
Top Expert 2008

Commented:
SGA is too big. Take in account that every connection uses 2-5 MB RAM.
Will recommend smaller SGA, because due the latches big SGA works slower.
Also use automatic memory allocation that will dinamically change the size of the SGA caches.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.