The Power of ASH (Active Session History)

Oracle 10g, brings many new features through which one can easily tune the bad sqls or also can diagnose the database performance issues.

Using database metrics, active session history and time model views.

Following query fetchs top sqls spent more on cpu/wait/io. (Thanks to Kyle Hailey for this script):

select
ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#

SQL_ID CPU WAIT IO TOTAL
------------- ---------- ---------- ---------- ----------
bqts5m5y267ct 0 0 20 20
4gd6b1r53yt88 0 16 1 17
35rqnp0hn3p3j 0 13 0 13
3shtm7x3a54qu 0 0 8 8
0hf43mhpx086p 0 0 4 4

Use any of v$sql to get the sql_text for one of the above sql_id.

SELECT sql_text FROM v$sqlarea WHERE sql_id = 'bqts5m5y267ct';

dbms_xplan.display_awr can be used to extract the sql plan for this sql_id.

SELECT * FROM table(dbms_xplan.display_awr('bqts5m5y267ct');


The above scinario was done in Oracle 10g Re.2 on SunSolaris version 10.