Monitoring Progress of a SQL Execution Plan

There are a couple of ways to get information to see where a query is executing in terms of the execution plan. First, by querying the V$SQL_PLAN_MONITOR view, you can get information for all queries that are in progress, as well as recent queries that are complete. If we are joining two tables to get employee and department information, our query would look like this:

SELECT ename, dname
FROM emppart JOIN dept USING (deptno);

To see information for the foregoing query while it is currently running, you can issue a query like
the one shown here (some rows have been removed for conciseness):


column operation format a25
column plan_line_id format 9999 heading 'LINE'
column plan_options format a10 heading 'OPTIONS'
column status format a10
column output_rows heading 'ROWS'
break on sid on sql_id on status

SELECT sid, sql_id, status, plan_line_id,
plan_operation || ' ' || plan_options operation, output_rows
FROM v$sql_plan_monitor
WHERE status not like '%DONE%'
ORDER BY 1,4;

Another method of seeing the progress of a query via the execution plan is by using the DBMS_SQLTUNE.REPORT_SQL_MONITOR function. If we use the same query against the EMPPART and DEPT tables used in the previous example, we can run the REPORT_SQL_MONITOR function to get a graphical look at the progress. See the following example of how to generate the file that would produce the HTML file that could be, in turn, used to view our progress. Figure 9-2 shows portions of the resulting report.

set pages 9999
set long 1000000

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=> '36bdwxutr5n75',type=>'HTML') FROM dual;

-------------------------------------------------------
Identifying Resource-Consuming SQL Statements That Have Executed in the Past

SELECT * FROM (
SELECT sql_id, sum(disk_reads_delta) disk_reads_delta,
sum(disk_reads_total) disk_reads_total,
sum(executions_delta) execs_delta,
sum(executions_total) execs_total
FROM dba_hist_sqlstat
GROUP BY sql_id
ORDER BY 2 desc)

WHERE rownum <= 5;

Since the actual text of the SQL isn’t stored in DBA_HIST_SQLSTAT, you can then look at the associated DBA_HIST_SQLTEXT view to get the SQL text for the query with the highest number of disk reads:

SELECT sql_text FROM dba_hist_sqltext

WHERE sql_id = '36bdwxutr5n75';
---------------------------------------------
SELECT id, operation || ' ' || options operation, object_name, cost, bytes
FROM dba_hist_sql_plan
WHERE sql_id = '0gzf8010xdasr'

ORDER BY 1;