Question: How do I use the
report_sql_monitor function? Also can you give an
example of using dbms_sqltune.report_sql_monior in
action?
Answer:
The report_sql_monitor function is used to
displays a report based upon the real time SQL monitoring
data being captured. It returns that report as a CLOB.
These are the report_sql_monitor parameters:
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
SQL_ID
|
VARCHAR2
|
IN
|
NULL
|
|
SESSION_ID
|
NUMBER
|
IN
|
NULL
|
|
SESSION_SERIAL
|
NUMBER
|
IN
|
NULL
|
|
SQL_EXEC_START
|
DATE
|
IN
|
NULL
|
|
SQL_EXEC_ID
|
NUMBER
|
IN
|
NULL
|
|
INST_ID
|
NUMBER
|
IN
|
-1
|
|
START_TIME_FILTER
|
DATE
|
IN
|
NULL
|
|
END_TIME_FILTER
|
DATE
|
IN
|
NULL
|
|
INSTANCE_ID_FILTER
|
NUMBER
|
IN
|
NULL
|
|
PARALLEL_FILTER
|
VARCHAR2
|
IN
|
NULL
|
|
EVENT_DETAIL
|
VARCHAR2
|
IN
|
'YES'
|
|
REPORT_LEVEL
|
VARCHAR2
|
IN
|
'TYPICAL'
|
|
TYPE
|
VARCHAR2
|
IN
|
'TEXT'
|
Report_sql_monitor Parameters
The report_sql_monitor
function begins by using the monitor hint in SQL to monitor
the SQL statement:
select /*+ monitor */
stuff
from
mytab . . .
Next, you need to gather the sql_id
of the statements:
col c1 heading 'SQL|ID' format a13
col c2 heading 'Cost' format 9,999,999
col c3 heading 'SQL Text' format a200
select
p.sql_id
c1,
p.cost
c2,
to_char(s.sql_text) c3
from
dba_hist_sql_plan p,
dba_hist_sqltext s
where
p.id = 0
and
p.sql_id = s.sql_id
and
p.cost is not null
order by
p.cost desc
;
The output of the above query might look like this, showing
the high cost SQL statements over time:
SQL
ID Cost SQL Text
------------- ----------
-------------------------------------------
847ahztscj4xw 358,456 select /*+ monitor
*/
s.begin_interval_time c1,
pl.sql_id c2,
pl.object_name c3,
. . .
Now that we have the SQL_ID we can use
the
report_sql_monitor function to display details
about the SQL statements:
set long 1000000
set longchunksize 1000000
set lines 1000
set pages 80
set trim on
set trimspool on
set echo off
set feedback off
spool /tmp/report_sql_monitor.htm
select
dbms_sqltune.report_sql_monitor(
sql_id
=> '847ahztscj4xw',
type
=> 'html',
report_level => 'all') as report
from
dual;
spool off
This will produce the SQL monitoring report,
including execution statistics and the execution plan for
the SQL statement that is being monitored. In many
cases it is easier to use the SQL*Plus
Autotrace command than to use the
report_sql_monitor function.
