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.

migrated from stackoverflow.com Nov 9 '12 at 20:14

This question came from our site for professional and enthusiast programmers.


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.

  • I dont have access to AWR, but maintenance have mentioned "things look OK" and supplied us with Top SQLs. It has the SGA that AWR suggests. They cant seem to see anything in AWR that points to CPU congestion, IO congestions or others. Every slow question must be limited by a resource. How do I find it? :) Im working at optimizing individual queries, but with 50+ slow ones, it takes time. – Emil Nov 9 '12 at 14:43
  • @Emil - Does maintenance have access to AWR? Can they generate the report and share it with you? The top 5 wait events (and the more detailed wait event breakdown) on the AWR report will tell you what the database is waiting on. Whatever the bottleneck (or at least whatever the most common bottleneck) should be clear from those wait events. – Justin Cave Nov 9 '12 at 14:48
  • Thanks. I'll begin with asking for that then. No suggestions on SQLs i can execute to measure possible bottlenecks myself? – Emil Nov 9 '12 at 14:53
  • @emil there are things you can check but it would be a lot easier with AWR installed and ticking (ideally every prod DB should have AWR running all the time, as you should compare a healhly slow to a perceived "poor performance" slot to see what'g going on. you could do some quick checks in gv$sql to see anyhting with high IO to execution ratios. you could check your shared pool v$sqlarea to see if you have any statements repeated a lot (suggesting a bind variable problem) like tom kyte shows here asktom.oracle.com/pls/asktom/… – DazzaL Nov 9 '12 at 15:02
  • 2
    @emil, statspack is still available , so you could use that if you don't have the diagnostic pack. – DazzaL Nov 9 '12 at 15:17

Have you tried ASHMON? It's a free graphical tool which will display information from v$active_session_history. It will complain if you try to connect not as 'system' user but I've tried it before and as long as you've got access to v$active_session_history you should be fine.

  • Hi. ASHMON seems great. I still need help in understanding the wait events though. The biggest wait events seems to be "direct path read temp" and "direct path write temp". But on the "init" view, the "CPU" seems to be constantly up to the "max cpu" line and sometimes above it (with some "BCPU" now and then). Is "CPU" always time spent on CPU, or can it also be time spent waiting for CPU? How do I break that down? Does the sum of this mean that "direct path read/write temp" consumes lots of CPU so both are issues? – Emil Nov 12 '12 at 12:15

Your Answer

