Wednesday, March 2, 2011

Real time SQL monitoring (requires DIAGNOSTIC+TUNING pack license)


SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> set pages 9999
SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ s.* from sh.sales s,SH.channels c where s.channel_id=c.channel_id and c.channel_desc='Partners'

Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : DONGHUA (150:2043)
SQL ID : 68r55a0ccwgzu
SQL Execution ID : 16777216
Execution Started : 03/02/2011 15:23:11
First Refresh Time : 03/02/2011 15:23:11
Last Refresh Time : 03/02/2011 15:23:35
Duration : 24s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@vmxavsvr.lab.dbaglobe.com (TNS V1-V3)
Fetch Calls : 17203

Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 1.42 | 0.54 | 0.29 | 0.60 | 17203 | 18807 | 51 | 13MB |
===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=1652006204)
=======================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=======================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 25 | +0 | 1 | 258K | | | | | |
| 1 | HASH JOIN | | 230K | 536 | 25 | +0 | 1 | 258K | | | 383K | 100.00 | Cpu (1) |
| 2 | TABLE ACCESS FULL | CHANNELS | 1 | 3 | 1 | +0 | 1 | 1 | | | | | |
| 3 | PARTITION RANGE ALL | | 919K | 529 | 25 | +0 | 1 | 919K | | | | | |
| 4 | TABLE ACCESS FULL | SALES | 919K | 529 | 25 | +0 | 28 | 919K | 51 | 13MB | | | |
=======================================================================================================================================================



Related Views: V$SQL_MONITOR, V$SQL_PLAN_MONITOR
Applies to Oracle DB 11gR1 onwards