Monitoring Progress of a SQL Execution Plan / sql query is really hanged or not
How to check the progress of a query is making from within the execution plan used. Using this we can find sql query is really hanged or not.
1. Find the active sessions
2. Monitoring Progress of a SQL Execution Plan
3. DBMS_SQLTUNE.REPORT_SQL_MONITOR
1. Find the active seesions
select sid, serial#, username, status, sql_id,to_char(LOGON_TIME, ‘DD-MON-YYYY HH24:MI:SS’) AS LOGON, event from v$session where username=’&username’;
SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE username=’&username’ and status not like ‘%DONE%’;
Output:
———–
SQL> select sid, serial#, username, status, sql_id, event from v$session where username=’SH’;
SID SERIAL# USERNAME STATUS SQL_ID EVENT
———- ———- —————————— ——– ————- ———-
24 23 SH ACTIVE 5mxdwvuf9j3vp direct path read
27 61 SH ACTIVE 5mxdwvuf9j3vp direct path read
SQL>
2. Monitoring Progress of a SQL Execution Plan
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%’ and sid=’&sid’ <—
ORDER BY 1,4;
Ouput:
SID SQL_ID STATUS LINE OPERATION ROWS
———- ————- ———- —– ————————- ———-
24 5mxdwvuf9j3vp EXECUTING 0 SELECT STATEMENT 0
1 SORT AGGREGATE 0
2 NESTED LOOPS 10
3 PARTITION RANGE ALL 23714 <—
4 TABLE ACCESS FULL 23714 <—
5 TABLE ACCESS FULL 10
27 5mxdwvuf9j3vp EXECUTING 0 SELECT STATEMENT 0
1 SORT AGGREGATE 0
2 NESTED LOOPS 10
3 PARTITION RANGE ALL 23781 <—
4 TABLE ACCESS FULL 23781 <—
5 TABLE ACCESS FULL 10
12 rows selected.
SQL> /
SID SQL_ID STATUS LINE OPERATION ROWS
———- ————- ———- —– ————————- ———-
24 5mxdwvuf9j3vp EXECUTING 0 SELECT STATEMENT 0
1 SORT AGGREGATE 0
2 NESTED LOOPS 10
3 PARTITION RANGE ALL 23737 <– see diff
4 TABLE ACCESS FULL 23737 <– see diff
5 TABLE ACCESS FULL 10
27 5mxdwvuf9j3vp EXECUTING 0 SELECT STATEMENT 0
1 SORT AGGREGATE 0
2 NESTED LOOPS 10
3 PARTITION RANGE ALL 23804 <— see diff
4 TABLE ACCESS FULL 23804 <— see diff
5 TABLE ACCESS FULL 10
12 rows selected.
SQL> /
SID SQL_ID STATUS LINE OPERATION ROWS
———- ————- ———- —– ————————- ———-
24 5mxdwvuf9j3vp EXECUTING 0 SELECT STATEMENT 0
1 SORT AGGREGATE 0
2 NESTED LOOPS 10
3 PARTITION RANGE ALL 23794 <– see diff
4 TABLE ACCESS FULL 23794 <– see diff
5 TABLE ACCESS FULL 10
27 5mxdwvuf9j3vp EXECUTING 0 SELECT STATEMENT 0
1 SORT AGGREGATE 0
2 NESTED LOOPS 10
3 PARTITION RANGE ALL 23861 <— see diff
4 TABLE ACCESS FULL 23861 <— see diff
5 TABLE ACCESS FULL 10
12 rows selected.
SQL>
We can see the difference. Hence the query is not hanged
3.REPORT_SQL_MONITOR in HTML (OR) TEXT format
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL report_sql_monitor.txt
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => ‘5mxdwvuf9j3vp’, <— SQLID
type => ‘TEXT’, <— HTML
report_level => ‘ALL’) AS report
FROM dual;
SPOOL OFF
Output from the above query shows whether status is executing or not
