Archive

Posts Tagged ‘Oracle performance tuning’

Oracle performance tuning scripts

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.

High-level view

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;

Table scans

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)';

Top SQL

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!

Advertisements
Categories: Database, Oracle