Data Mining AWR

This is a collection of scripts that I often using when mining the AWR for performance.  These especially come in handy when I’m working on a client site that I don’t have access to their EM12c console.  I always believe before you become reliant on a GUI for performance and troubleshooting you should have an understanding and be able to find the answers through scripts.  My end goal is to utilize the AWR for performance predictive analytic then I will know I have arrived.

1. To find a particular SQL and it’s SQL_ID without having to know the snap_id or the range of the snap_id:

select
sql_text,
parsing_schema_name as schema,
elapsed_time_delta/1000/1000 as elapsed_sec,
a.sorts_total,
sorts_delta,
executions_total,
disk_reads_total,
disk_reads_delta,
buffer_gets_total,
buffer_gets_delta,
rows_processed_total,
rows_processed_delta,
cpu_time_total,
cpu_time_delta,
iowait_delta/1000/1000 as iowait_sec,
clwait_delta/1000/1000 as clwait_sec,
cc_wait_total,
a.snap_id,
to_char(c.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
b.sql_id
from
dba_hist_sqlstat a,
dba_hist_sqltext b,
dba_hist_snapshot c
where
a.sql_id=b.sql_id and
a.snap_id=c.snap_id and
c.begin_interval_time>=sysdate-1 and -- slide the date as needed
upper(sql_text) like '%some text%' and -- text of my SQL if you know the sql_id
-- then don't use this but use the sql_id
sql_id = 'a1x1xh9y64bb9' -- if you know the specific sql_id
parsing_schema_name ='USER' -- replace with the user that parsed or alternatively change to a not in
order by elapsed_time_delta asc;

Now that I have the SQL_ID as well as the stats I can find the Execution plan within the AWR:

select * from(dbms_xplan.display_awr('sql_id');

Now that I have the stats and the execution plan, its a matter of analyzing the data to find a more efficient way of answering the business question. Remember not every query is maintained in the AWR.

This same information can be found from the cache provided it still exists.

Find the SQL_ID:
select * from v$sql where sql_text like '%text';

Find the exectution plan for the SQL_ID:
select* from table(dbms_xplan.display_cursor('sql_id','child_no');
— replace the values with the actual numbers from the output in the select * from sql.

More to follow!

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: