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