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:
parsing_schema_name as schema,
elapsed_time_delta/1000/1000 as elapsed_sec,
iowait_delta/1000/1000 as iowait_sec,
clwait_delta/1000/1000 as clwait_sec,
to_char(c.end_interval_time,'dd.mm hh24:mi:ss') as snaptime,
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!