DBA Hub

📋Steps in this guide1/4

SQL Query is really hanged or not - Bright DBA

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 →

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

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

Code/Command (click line numbers to comment):

1
2
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%';
2

Section 2

Output: ———– 2. Monitoring Progress of a SQL Execution Plan <---

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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;
3

Section 3

Ouput: We can see the difference. Hence the query is not hanged 3. REPORT_SQL_MONITOR in HTML (OR) TEXT format

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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
4

Section 4

Output ======= 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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) |
===========================================================================================================================================================

Comments (0)

Please to add comments

No comments yet. Be the first to comment!