Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle Potential Server Performance Impact

Posted on 2009-07-07
13
Medium Priority
?
403 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

0
Comment
Question by:gflmartins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 24792481
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.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24793644
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.
0
 

Author Comment

by:gflmartins
ID: 24793787
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?
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24793835
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.
0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 1000 total points
ID: 24793860
Also my rule of thumbs:

1) Dedicated DB server I always leave at least 25% free memory overhead, and that may vary depending on other factors. For machines with 4GB total RAM or less, I leave 1.5GB free for OS. Windows needs this much.

2) On shared DB server (with other apps) I never dedicate more than 50% RAM to Oracle, and again that changes with other apps as well.
0
 

Author Comment

by:gflmartins
ID: 24793865
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
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24793901
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.
0
 

Author Comment

by:gflmartins
ID: 24793948
That certainly sounds feasible - I will look into changing this now and see what the results are.  Thanks mrjoltcola :)
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 1000 total points
ID: 24794009
Is your physical standby open for read only operations?  If not, then you can reduce the SGA to a very small size (say 300M or even smaller).  The database is only in recovery mode an does not require a large SGA.
0
 

Author Comment

by:gflmartins
ID: 24794049
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
0
 
LVL 35

Expert Comment

by:johnsone
ID: 24794194
Then there is definitely no need for an SGA that large.  Cut it way down.
0
 

Author Comment

by:gflmartins
ID: 24794204
Good advice - thanks Johnsone
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24795483
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.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question