11g SQL Monitor
1，首先确认两个参数的值
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> SHOW PARAMETER control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
2，从v$sql_monitor查看sql_id
SQL> select sql_id ,sql_text from v$sql_monitor where sql_text like '%EMPLOYEE_TEST%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
5ujtyb95f83jy
select /*+ parallel(8) */ * from scott.EMPLOYEE_TEST
2v0bzu74hyj30
select /*+ parallel(8) */ * from scott.EMPLOYEE_TEST where rownum<500
3，生成sql monitor报告
如果运行dbms_sqltune.report_sql_monitor报错说sqlmonitor组件不存在，要首先执行oracle/db/rdbms/admin/execrept.sql这个脚本。
SQL Monitor报告有三种格式 TEXT,HTML, ACTIVE
TEXT标准版
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SQL> SPOOL report_sql_monitor.txt
SQL> SELECT dbms_sqltune.report_sql_monitor(sql_id => '2v0bzu74hyj30',report_level => 'ALL',type=>'TEXT') comm FROM dual;
SQL Monitoring Report
SQL Text
------------------------------
select /*+ parallel(8) */ * from scott.EMPLOYEE_TEST where rownum<500
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (132:1717)
SQL ID : 2v0bzu74hyj30
SQL Execution ID : 16777216
Execution Started : 09/22/2016 10:36:07
First Refresh Time : 09/22/2016 10:36:07
Last Refresh Time : 09/22/2016 10:36:07
Duration : .026675s
Module/Action : sqlplus@fff1 (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@fff1 (TNS V1-V3)
Fetch Calls : 35
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Application | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 0.15 | 0.04 | 0.08 | 0.00 | 0.03 | 35 | 137 | 16 | 12MB |
=========================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=8)
Instances : 2
=============================================================================================================================================
| Instance | Name | Type | Server# | Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Wait Events |
| | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | (sample #) |
=============================================================================================================================================
| 1 | PX Coordinator | QC | | 0.03 | 0.01 | | 0.00 | 0.01 | 13 | | . | |
| 2 | p004 | Set 1 | 1 | 0.02 | 0.00 | 0.01 | | | 13 | 2 | 2MB | |
| 2 | p005 | Set 1 | 2 | 0.01 | 0.00 | 0.00 | | | 21 | 1 | 304KB | |
| 2 | p006 | Set 1 | 3 | 0.02 | 0.00 | 0.01 | | | 30 | 3 | 2MB | |
| 2 | p007 | Set 1 | 4 | 0.02 | 0.00 | 0.01 | | | 13 | 2 | 2MB | |
| 1 | p004 | Set 1 | 5 | 0.02 | 0.00 | 0.01 | | 0.01 | 13 | 2 | 2MB | |
| 1 | p005 | Set 1 | 6 | 0.02 | 0.00 | 0.01 | | 0.01 | 8 | 2 | 2MB | |
| 1 | p006 | Set 1 | 7 | 0.02 | 0.00 | 0.01 | | 0.00 | 13 | 2 | 1MB | |
| 1 | p007 | Set 1 | 8 | 0.02 | 0.00 | 0.01 | | | 13 | 2 | 2MB | |
=============================================================================================================================================
Instance Drill-Down
===================================================================================================================================
| Instance | Process Names | Elapsed | Cpu | IO | Application | Other | Buffer | Read | Read | Wait Events |
| | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | |
===================================================================================================================================
| 1 | QC p004 p005 p006 p007 | 0.10 | 0.03 | 0.04 | 0.00 | 0.03 | 60 | 8 | 6MB | |
| 2 | p004 p005 p006 p007 | 0.06 | 0.02 | 0.04 | | | 77 | 8 | 6MB | |
===================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3705175653)
======================================================================================================================================================
| 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 | +0 | 1 | 499 | | | | |
| 1 | COUNT STOPKEY | | | | 1 | +0 | 1 | 499 | | | | |
| 2 | PX COORDINATOR | | | | 1 | +0 | 9 | 499 | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 4M | 964 | 2 | +0 | 8 | 3992 | | | | |
| 4 | COUNT STOPKEY | | | | 2 | +0 | 8 | 3992 | | | | |
| 5 | PX BLOCK ITERATOR | | 4M | 964 | 2 | +0 | 8 | 3992 | | | | |
| 6 | TABLE ACCESS FULL | EMPLOYEE_TEST | 4M | 964 | 2 | +0 | 9 | 3992 | 16 | 12MB | | |
======================================================================================================================================================
SQL> SPOOL OFF
ACTIVE尊享版
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SQL> SPOOL report_sql_monitor.htm
SELECT dbms_sqltune.report_sql_monitor(sql_id => '2v0bzu74hyj30',report_level => 'ALL',type=>'ACTIVE') comm FROM dual;
SQL> SPOOL OFF
4，参考：
https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1
http://www.oracle.com/technetwork/database/manageability/sqlmonitor-084401.html
http://blog.itpub.net/23718752/viewspace-1216422/
http://www.eygle.com/archives/2012/09/sqloracle_sql_monitor_report.html
Doc ID 1380492.1
