We are facing some performance problems in our oracle DB qerries and reports recently. We applied Solaris 10 recommended patchset on april 24th. Our monthly report generation acitivity ran 3 days i.e may 11,12,13 and application team complained of degraded performance. I can see that the cpu usage on the server is about 100% during the report generation , but everything else is fine. Free memory is 15gb , all mountpoints are under 80% and there is no other hardware issue as well.
Now app team is suspecting that last month patching could be the cause of the problem, because till last month they were performing this activity without having a performance degradtion.
I just need to be sure that solaris 10 recommended patches are not causing this issue. Any of you ever faced such an issue after patch installation ?
The server is V490 with 8 cpu / 32gb RAM. Any hints will be appreciated ?
You will have to either log a call to oracle support - be prepared with lots of logs, you will probably be asked to run explorer as well.
Or you can try searching by patch number on the OTN for known issues.
Has your DBA done a THROROUGH alert log check?
And, of course the DBA's have pushed no schema changes, there were zero production code changes.
If that fails startup sa (sar) sar monitoring to get a baseline. Keep 30 days worth of data. Then on month end start comparing daily sar output against what you get during month-end.
nothing has changed in kernel parameters in /etc/system
Hi jim,
our DBA has logged call with Oracle and also shared the patches we applied last month to get a response from them. Lets wait and see what oracle has to say on this.
After checking logs, DBA could only confirm that there is very high Logical I/O activity. There is no wait I/O on the system and DBA also confirmed physical I/O is negligible from his logs.
Could it be that the high logical I/O is caused by some application defect ? Also we have lot of memory available on the system which is not used by oracle. Shall i ask DBA to increase SGA to include the free memory ? Will it help reduce the logical I/O. Any comments will be appreciated.
So approximately, there was an 80 - 20 ratio between user% and system% of cpu utilisation. Same thing i confirmed from vmstat as well.
Thanks in advance for your suggestions based on above log.[/LEFT]
---------- Post updated at 01:21 PM ---------- Previous update was at 12:45 PM ----------
Hi All,
In addition to this, i just wanted to know whether any of you have ever faced a performance problem in oracle DB after solaris patching ? I find this highly improbable, Just let me know your response on this .
Have you captured all the logs before you went for patching.
Like Shared memory / cache settings in /etc/system
Also.. Try to understand the DB/ Application (how it works) ,
How much GB does your DB needs..
What are the scheduled cron jobs.. Are the LUNS allocated to zpools or disk having any issues..
if the patches installed were os patches, there is a possibility that some corruption to the db has occured especially if the db was active during the patch install ... however -- whether or not db was active during patching -- if possible, restore a pre-patch copy of the db into another directory and see if that works better ...