SQL Monitor Report is your best friend

By Alex Zaballa

Hi all,

One of the quickest ways to help with the SQL tuning is by getting the SQL Monitor Report.

You have a lot of options to get this, like SQL Developer, OEM, command line, etc.

To get this using the command line, you can use:

set linesize 1000 pagesize 100 verify off trimout on trimspool on echo off head off feed off timing off termout off long 1000000 longchunksize 1000000
set serveroutput on size 1000000
spool sqlmon.html replace
clear scr
select dbms_sqltune.report_sql_monitor(
                sql_id => 'YOUR_SQL_ID',
                report_level=>'ALL',
                type => 'ACTIVE')
from dual;
spool off

One easy way to get the SQL_ID is searching the GV$SQL, something like:

select * from gv$sql where sql_text like 'SELECT bla bla bla%';

REMEMBER: By default, a SQL statement that either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O time in a single execution will be monitored (not true for Parallel queries, Parallel DMLs, and Parallel DDLs – it’s on by default).

But it’s possible to force monitoring for any SQL statement by adding the MONITOR hint to the statement.

SELECT /*+ MONITOR */ * from tab where id=1;

If you can’t change the code, you can use SQL PATCH or something like this:

 ALTER SYSTEM SET EVENTS 'sql_monitor [sql:MY_SQL_ID] force=true';

Sometimes, the SQL is “old” and it’s not in the GV$SQL_MONITOR anymore.

For these cases, you have some options to get the historical data (based on the AWR retention).

SELECT * FROM dba_hist_reports where key1 = 'YOUR_SQL_ID';

set linesize 1000 pagesize 100 verify off trimout on trimspool on echo off head off feed off timing off termout off long 1000000 longchunksize 1000000
set serveroutput on size 1000000
spool sqlmon.html replace
clear scr
SELECT DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL(RID => YOUR_REPORT_ID, TYPE => 'ACTIVE') FROM dual;
spool off

set linesize 1000 pagesize 100 verify off trimout on trimspool on echo off head off feed off timing off termout off long 1000000 longchunksize 1000000
set serveroutput on size 1000000
spool sqlmon.html replace
clear scr
select dbms_perf.report_sql(sql_id=>'YOUR_SQL_ID',is_realtime=>0,type=>'active',selected_start_time=>To_date('09/28/2022 21:00:00','MM/DD/YYYY HH24:MI:SS'),selected_end_time=>To_date('08/28/2022 04:00:00','MM/DD/YYYY HH24:MI:SS') ) from dual;
spool off

Probably, you will need to get the SQL_ID searching the DBA_HIST_SQLTEXT, something like:

select * from DBA_HIST_SQLTEXT where sql_text like 'SELECT bla bla bla%';

SQL Monitor has a limit on the number of lines in the execution plan, In this case, you can increase the parameter “_sqlmon_max_planlines“.

***To use the Real-Time SQL Monitoring feature you need an Oracle Tuning Pack license.

This post is a contribution to #JoelKallmanDay

Thanks

Alex

Fonte: Alex Zaballa