SQL Query is really hanged or not

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


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;

-- 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