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 … Continue reading SQL Query is really hanged or not →
12
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%';123456789101112131415161718192021222324252627282930
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;1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586
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 OFF123456789101112131415161718192021222324252627282930313233343536373839404142434445
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 : [email protected] (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) |
===========================================================================================================================================================Please to add comments
No comments yet. Be the first to comment!