Capture specific high-load sqls from an Oracle Db workload using filters

Finding SQLs with a specific condition by manually inspecting dozens of AWR reports is an error prone and tedious task.

Especially in RAC environments, where there are multiple instances generating a lot of AWR data.

However, it is possible to easily extract the required information from AWR or the cursor cache using a query. 

The DBMS_SQLTUNE package

The DBMS_SQLTUNE db package has  two subprograms which are useful. 

They are the select_workload_repository  and the  select_cursor_cache subprograms. 

They can be used to quickly find, and capture the sqls of interest and their metrics.

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY procedure (for past workloads)

Parameter Name	Purpose
Begin_snap	Start snapshot id for capture (Capture will be for activity this snapshot’s end interval time)
End_snap	End snapshot id for capture (Capture will be for activity upto and including this snapshot’s end interval time)
Basic_filter	Filter the list of sqls to be captured based on elapsed time, session module, etc. When this parameter is provided adhoc pl/sql blocks execution will also be captured.
Example query: The sql below extracts the data of ONLY those sqls which match these conditions: • Starting from snapshot id 10 and until snapshot id 11  • Sqls with cpu time more than 250k microseconds  • Session module starting with ‘OGG’  • At least one execution within the snapshot period select sql_id, elapsed_time , buffer_gets, disk_reads, cpu_time, executions     from table(       dbms_sqltune.select_workload_repository(       begin_snap=>10,       end_snap=> 11,                basic_filter=>'executions > 0 and module like ' || '''OGG%''' || ' and cpu_time > 250000'       ))     order by elapsed_time desc     The screenshot below shows its output

 

DBMS_SQLTUNE.SELECT_CURSOR_CACHE procedure (for live workloads)

Parameter Name	Purpose
basic_filter	Its like a WHERE clause to filter the list of SQLS returned.  In the example belowit filters for cpu_time > 250k microseconds.
Example query:    SELECT  sql_id, parsing_schema_name, module,      round(elapsed_time/(executions * 1000000 ),2) elapsed_per_exec,     round(buffer_gets/executions,2) gets_per_exec,     cpu_time,  disk_reads, executions,     sql_text     FROM table(     dbms_sqltune.select_cursor_cache(     basic_filter =>'cpu_time > 250000'     ) )     The screenshot below shows its output