9.0.2.8 Serious performance issues
Greetings!
I'm a DBA primarily with background in DB2 and MSSQL but I am being asked to help support an Oracle issue.
Version:9.0.2.8
OS: Linux; Redhat
We have numerous jobs that typically take less then 2 minutes that are now, as of last week, taking 1-2 hours and it appears to be purely random. This behavior started last week and the more I dig into this situation it might be linked with taking on some new clients and the load caused by the increase.
We originally thought that it was an issue with statistics begin created for the tables. Fooling with tweaking the "Analyze table" scripts seems to cause one process to improve at the cost of another process.
Then we started to see commit issues on mass updates and we were seeing the error "Rollback segment too small".
I started to think these might all be symptoms of another issue like a lack of resources but I'm no expert with Oracle. So what can I look at in the v$ tables to figure this out?
Here is one thing that really stuck out to me in the v$latch table but it could be nothing:
Name: cache buffers chains
Gets: 99,338,117,727.00
Wait_Time: 20,420,444,970.00
Any advice is greatly appreciated! If you need more information please just ask!
-Art