Monitoring Progress of a SQL Execution Plan / sql query is really hanged or not
How to see the progress 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 seesions
2. Monitoring Progress of a SQL Execution Plan
3. DBMS_SQLTUNE.REPORT_SQL_MONITOR
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; -- OR --- 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;
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
=======
SQL Monitoring Report SQL Text ------------------------------ SELECT ...... Global Information ------------------------------ Status : EXECUTING <---- Instance ID : 1 Session : SH (27:61) SQL ID : 5mxdwvuf9j3vp SQL Execution ID : 16777216 Execution Started : 10/26/2016 20:19:01 <--- First Refresh Time : 10/26/2016 20:19:05 Last Refresh Time : 10/26/2016 20:38:25 Duration : 1164s <--- Module/Action : SQL*Plus/- Service : SYS$USERS Program : sqlplus@rac2.rajasekhar.com (TNS V1-V3) Global Stats ====================================================================== | Elapsed | Cpu | IO | Concurrency | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | ====================================================================== | 1172 | 77 | 1095 | 0.00 | 15M | 537K | 114GB | ====================================================================== SQL Plan Monitoring Details (Plan Hash Value=2043253752) =========================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | =========================================================================================================================================================== | 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | 1 | SORT AGGREGATE | | 1 | | 811 | +220 | 1 | 0 | | | | | | -> 2 | NESTED LOOPS | | 5557 | 370M | 1167 | +4 | 1 | 4 | | | | | | -> 3 | PARTITION RANGE ALL | | 919K | 499 | 1167 | +4 | 1 | 10325 | | | | | | -> 4 | TABLE ACCESS FULL | SALES | 919K | 499 | 1167 | +4 | 5 | 10325 | 1 | 208KB | | | | -> 5 | TABLE ACCESS FULL | CUSTOMERS | 1 | 403 | 1170 | +1 | 10326 | 4 | 535K | 114GB | 100.00 | Cpu (35) | | | | | | | | | | | | | | direct path read (1128) | ===========================================================================================================================================================
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala