Background:
I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in mind. The issue still feels too wide and general to only be caused by bad SQL. It's not really a huge database (the largest couple of tables contains like 8million rows, with less than half a k of data per row) and the server specifications are not that bad either (Ultrasparc, access(they say) to 96 hyperthreads @ 1.2ghz, 2gb sga). We've tried increasing the sga to near the size of the whole db (20gb), seeing only a few percentage is speedgains, which made us willing to rule out I/O issues. The maintenance dept cant see anything wrong HW or OS wise, but arn't too helpful either.
Question:
There are several possible bottlenecks in a DB like this. I list the ones I can think of below. Is there any perticular actions(preferably SQLs) I, with only "a few" DBA permissions, can perform to get measurable values that can help in deciding where my issues are?
- Disk I/O speed. How do I get a good measurement right now?
- CPU througput. How much data can 1.2ghz shovel?
- Number of CPUs, How many are accessible really?
- Memory / SGA size. Index swapping etc.
- Sorting area size
- Temp tablespace (for materializations etc)
Please suggest more categories if you think of something I miss.
Best Answer
first thing you should do is get AWR(10g on)/statspack(if your on an older version of the DB) running on your database so that it generates a snap every few minutes (few being say 15, 30 ..if you need more resolution, make the snaps more frequent). this will give you a system overview on IO, CPU etc and will show you the TOP SQL (in terms of IO, CPU, runtime, executions and the like). it will advise on SGA / buffer cache usage too.
From that point onwards you should start looking at the worst sql/areas and run tools like explain plans/tkprof/sql trace on sql statements and plsql profiler on any slow running pl/sql code.