As a developer I rarely get access to the graphical consoles for Oracle. The DBA:s tend to keep them locked down. It is usually easier to get access to the built-in performance views. Over the years I have gathered a few scripts that help me diagnose performance issues. They are nothing fancy, I’m posting them partly for my own benefit as this makes them easy to find when I need them.
I have used the scripts for a long time, so I don’t remember their origin. A likely source is Tom Kyte’s excellent Effective Oracle by Design, a must read for anyone into Oracle performance tuning.
select wait_class, total_waits, round(100 * (total_waits / sum_waits),2) pct_waits, time_waited, round(100 * (time_waited / sum_time),2) pct_time from (select wait_class, total_waits, time_waited from v$system_wait_class where wait_class != 'idle'), (select sum(total_waits) sum_waits, sum(time_waited) sum_time from v$system_wait_class where wait_class != 'idle') order by 5 desc;
Top wait events
select h.event "wait event", sum(h.wait_time + h.time_waited) "total wait time" from v$active_session_history h, v$event_name e where h.sample_time between sysdate - 1/24 and sysdate and h.event_id = e.event_id and e.wait_class 'idle' group by h.event order by 2 desc;
select to_char(sn.begin_interval_time,'dd mon hh24:mi:ss') start_date, to_char(sn.end_interval_time,'dd mon hh24:mi:ss') end_date, newmem.value-oldmem.value fts from dba_hist_sysstat oldmem, dba_hist_sysstat newmem, dba_hist_snapshot sn where sn.snap_id = (select max(snap_id) from dba_hist_snapshot) and newmem.snap_id = sn.snap_id and oldmem.snap_id = sn.snap_id-1 and oldmem.stat_name = 'table scans (long tables)' and newmem.stat_name = 'table scans (long tables)';
select h.user_id, u.username, sql.sql_text, sum(h.wait_time + h.time_waited) "total wait time" from v$active_session_history h, v$sqlarea sql, dba_users u, v$event_name e where h.sample_time between sysdate - 1/24 and sysdate and h.sql_id = sql.sql_id and h.user_id = u.user_id and h.sql_id is not null and e.event_id = h.event_id and e.wait_class 'idle' group by h.user_id,sql.sql_text, u.username order by 4 desc;
This is usually enough to point me in the right direction. It is even easier with the graphical user interface, but SQL always works!