I'm running Oracle 10g on a V880 using Solaris 8 connected to a NetApps SAN and recently I've been getting cpu activity that normally is 2 -3 % now running from 14% ass a low up to 90% peak against oracle PIDs. No recent changes have been to Oracle or the OS. THere has been some firmware updates to the NetApps SAN but no errors can be found on the server or SAN. What can I check to see what's causing this high CPU activity? Oracle is also install on a SAN partition.
Thanks,
Most of the time when Oracle starts to work overtime the cause is some new requirement in an application - ie., a badly tuned cursor, a dropped index, use way beyond the original design, etc.
For example, somebody coded for reporting one billing cycle at a time, but the business decided that it needs to run 10 cycles 3 times a day. I would talk with the users first, then get the DBA's to verify the schema. It is impossible for there to have been no changes.
DBA's can also run a trace on a process to see what it is causing the slowdown.
performance tuning is often trial and error process, sometimes is due to badly configured system even tho the system seems to have a lot of disk memory and cpu. Sometimes changing raid configuration may greatly improve performance.
how to find other clues.
do a top or prstat -a to find the biggest oracle process (usually it's a LOCAL=YES)
/usr/proc/bin/ptree to see if it spawn any other process
truss -p <process id> to see what it's doing ie read() or write() or semop() etc ...
sar 3 20 (3 seconds 20 rep) might also give some indication if there is high (and constand wio) wio is an indication disk contention.
if there is no or low wio but idle time for cpu is zero (constantly 0 all the time) could mean insufficient cpu.
vmstat 3 333 (look at the pi/po and sr columns) if there is high 'po' (page out) is indication there demand for memory. So when there is insufficient memory apps gets paged out to swap and swap is usually from disk so leads to high disk io hence it may show up in sar.
Application takes memory from free list, SR is a scan rate of how often the kernel will scan for memory dirty pages to add to the freelist.
If there is high po then sr should be busy as busy as po.
if there is high po and low sr is an indication of improper or unoptimized system. Review your system configuration, review your disk raid, review your system semaphore shared memory settings etc ....
To slow down a good usable computer:
Install Oracle
By the way...I stopped "Enterprise Manager" ($ORACLE_HOME/bin/emctl stop dbconsole) and use of CPU came back to normal...you don't even see oracle in "top"...but it is running.
Well..."Enterprise Manager" is a really good tool, for example because is web based, but I guess it is not necesary to have it always running...just in case.
I hope this help you and I also hope this bring back to you your interest in using Oracle...I can't say that Oracle is the best choice, but I can say that it is better than many that I've used before.