Oracle Potential Server Performance Impact

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?

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.

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).

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

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.

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.

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.

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

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

This video shows syntax for various backup options while discussing how the different basic backup types work. It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…