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 sessions
2. Monitoring Progress of a SQL Execution Plan
3. DBMS_SQLTUNE.REPORT_SQL_MONITOR
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>
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
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.amitpawardba.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) |
===========================================================================================================================================================
I hope this article helped you. Your suggestions/feedback are most welcome.
