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.
Easy "water hack" burns 2 lbs OVERNIGHT
ReplyDeleteAt least 160 000 men and women are using a simple and secret "liquids hack" to lose 2 lbs every night in their sleep.
It's very easy and it works all the time.
This is how to do it yourself:
1) Take a clear glass and fill it half glass
2) Then follow this weight loss HACK
so you'll become 2 lbs thinner the next day!
Hi, This blog is very useful for oracle learners. Thanks for the valuable information.
ReplyDeleteOracle training in Chennai
Thanks for sharing such amazing content which is very helpful for us.
ReplyDeleteOracle DBA Training