1、sql monitor 捕捉sql的前提
(1)、sql monitor只能捕捉到并行执行的sql语句,或者单次执行sql语句消耗cpu、io 5s以上
(2)、 statistics_level级别必须是TYPICAL 或者ALL
SQL> show parameter STATISTICS_LEVEL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
(3)control_management_pack_access 必须是 DIAGNOSTIC+TUNING
SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
(4)要有 A Diagnostics and Tuning Pack License
(5) 可以通过查询视图V$SQL_MONITOR and V$SQL_PLAN_MONITOR 来获取信息 elapsed time, CPU time, number of reads and writes, I/O wait time, and various other wait times。
SQL> SELECT statistics_name,session_status,system_status,activation_level,session_settable FROM v$statistics_level WHERE statistics_name = 'SQL Monitoring';
- 当命令执行时,这些统计信息会在几乎实时的情况下刷新,通常是每秒一次。
- 命令执行完毕后,会在视图中V$SQL_MONITOR and V$SQL_PLAN_MONITOR 保留1分钟
- 该条目最终被删除,以便在监视新命令时可以回收它的空间。
STATISTICS_NAME SESSION_STATUS SYSTEM_STATUS ACTIVATION_LEVEL SESSION_SETTABLE
---------------------------------------------------------------- -------------- ------------- ---------------- ----------------
SQL Monitoring ENABLED ENABLED TYPICAL YES
(6) 可以通过函数DBMS_SQLTUNE.REPORT_SQL_MONITOR 来获取更多监控信息
- SQL_ID : sql 语句的 标识符,默认NULL ,表示当前session上次执行的sql语句监控
- REPORT_LEVEL : 'NONE', 'BASIC', 'TYPICAL' or 'ALL',默认是TYPICAL,在大多数情况下满足需要
- TYPE :'TEXT', 'HTML', 'XML' or 'ACTIVE' 输出格式
- SESSION_ID :SYS_CONTEXT('USERENV','SID')可以获取当前session的sid
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT') AS report FROM dual;
2、测试
(1) 通过hint提示
SQL 执行监控或者不允许执行监控,这两个 Hints 是 monitor 与 no_monitor
select /*+ monitor */ pk_account_feesrcmsg,nwaitfee
from AC_ACCOUNT_FEE
where ifeetype = 7504 and dr = 0
and ffeestatus <> 7302
and pk_acctcorp = '8617'
order by creationtime
select dbms_sqltune.report_sql_monitor from dual;
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '<sql_id>', type => 'TEXT') AS report FROM dual;
SQL Text
------------------------------
select /*+ monitor */ pk_account_feesrcmsg,nwaitfee from AC_ACCOUNT_FEE where ifeetype = 7504 and dr = 0 and ffeestatus <> 7302 and pk_acctcorp = '8617' order by creationtime
Global Information
------------------------------
Status : DONE (FIRST N ROWS)
Instance ID : 1
Session : BOSWLL (1183:34161)
SQL ID : 58s074r96cvut
SQL Execution ID : 16777216
Execution Started : 11/21/2017 08:51:21
First Refresh Time : 11/21/2017 08:51:21
Last Refresh Time : 11/21/2017 08:51:44
Duration : 23s
Module/Action : PL/SQL Developer/SQL Window - New
Service : bostest
Program : plsqldev.exe
Fetch Calls : 1
Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.03 | 0.03 | 0.00 | 1 | 1955 |
=================================================
SQL Plan Monitoring Details (Plan Hash Value=1541598135)
============================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
============================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 100 | | | |
| 1 | SORT ORDER BY | | 12423 | 533 | 1 | +0 | 1 | 100 | 2M | | |
| 2 | TABLE ACCESS FULL | AC_ACCOUNT_FEE| 12423 | 532 | 1 | +0 | 1 | 25970 | | | |
============================================================================================================================================================
(2)通过sql_id 获取正在执行,或者刚刚执行完毕的sql
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '6p35wmnbdwdk7', type => 'TEXT') from dual;
SQL Monitoring Report
SQL Text
------------------------------
DELETE FROM HDS_B WHERE STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) AND ROWNUM <= :3
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : HADES_FM (488:8265)
SQL ID : 6p35wmnbdwdk7
SQL Execution ID : 16784424
Execution Started : 11/20/2017 22:11:04
First Refresh Time : 11/20/2017 22:11:08
Last Refresh Time : 11/20/2017 22:11:18
Duration : 14s
Module/Action : JDBC Thin Client/-
Service : SYS$USERS
Program : JDBC Thin Client
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | NUMBER | 3 |
| :2 | 2 | NUMBER | 31 |
| :3 | 3 | NUMBER | 100 |
========================================================================================================================
Global Stats
========================================================
| Elapsed | Cpu | IO | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |
========================================================
| 14 | 1.28 | 13 | 77121 | 2446 | 601MB |
========================================================
SQL Plan Monitoring Details (Plan Hash Value=2111154680)
=========================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | |
=========================================================================================================================================================================================
| 0 | DELETE STATEMENT | | | | | | 1 | | | | | | |
| 1 | DELETE | HDS_B | | | | | 1 | | | | | | |
| 2 | COUNT STOPKEY | | | | | | 1 | | | | | | |
| -> 3 | TABLE ACCESS FULL | HDS_B | 5M | 102K | 14 | +1 | 1 | 0 | 1809 | 444MB | 100.00 | Cpu (1) | 22% |
| | | | | | | | | | | | | db file scattered read (13) | |
=========================================================================================================================================================================================
(3) 查看某个sql的整体性能
SELECT DBMS_SQLTUNE.report_sql_monitor_list(sql_id=>'6p35wmnbdwdk7',type =>'TEXT',report_level => 'ALL') AS report FROM dual;
SQL Monitoring List
=====================
===================================================================================================================================================================================
| Status | Duration | SQL Id | Exec Id | Start | User | Module/Action | Dop | DB Time | IOs | SQL Text |
===================================================================================================================================================================================
| EXECUTING | 5.0s | 6p35wmnbdwdk7 | 16784429 | 11/20/2017 22:18:19 | HADES_FM | JDBC Thin Clien/- | | 3.6s | 659 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| EXECUTING | 94s | 6p35wmnbdwdk7 | 16784428 | 11/20/2017 22:16:50 | HADES_FM | JDBC Thin Clien/- | | 93s | 19761 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 125s | 6p35wmnbdwdk7 | 16784427 | 11/20/2017 22:15:13 | HADES_FM | JDBC Thin Clien/- | | 127s | 22685 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 113s | 6p35wmnbdwdk7 | 16784426 | 11/20/2017 22:13:57 | HADES_FM | JDBC Thin Clien/- | | 115s | 24172 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 115s | 6p35wmnbdwdk7 | 16784425 | 11/20/2017 22:12:19 | HADES_FM | JDBC Thin Clien/- | | 116s | 26470 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 112s | 6p35wmnbdwdk7 | 16784424 | 11/20/2017 22:11:04 | HADES_FM | JDBC Thin Clien/- | | 113s | 25810 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 111s | 6p35wmnbdwdk7 | 16784423 | 11/20/2017 22:09:28 | HADES_FM | JDBC Thin Clien/- | | 112s | 26432 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 113s | 6p35wmnbdwdk7 | 16784422 | 11/20/2017 22:08:10 | HADES_FM | JDBC Thin Clien/- | | 113s | 24461 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 116s | 6p35wmnbdwdk7 | 16784421 | 11/20/2017 22:06:31 | HADES_FM | JDBC Thin Clien/- | | 117s | 26105 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 117s | 6p35wmnbdwdk7 | 16784420 | 11/20/2017 22:05:14 | HADES_FM | JDBC Thin Clien/- | | 117s | 25142 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 98s | 6p35wmnbdwdk7 | 16784419 | 11/20/2017 22:03:54 | HADES_FM | JDBC Thin Clien/- | | 99s | 24481 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 103s | 6p35wmnbdwdk7 | 16784418 | 11/20/2017 22:02:32 | HADES_FM | JDBC Thin Clien/- | | 103s | 21269 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 107s | 6p35wmnbdwdk7 | 16784417 | 11/20/2017 22:01:06 | HADES | JDBC Thin Clien/- | | 108s | 21513 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 106s | 6p35wmnbdwdk7 | 16784416 | 11/20/2017 21:59:45 | HADES | JDBC Thin Clien/- | | 106s | 20562 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 102s | 6p35wmnbdwdk7 | 16784415 | 11/20/2017 21:58:23 | HADES | JDBC Thin Clien/- | | 102s | 21397 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 115s | 6p35wmnbdwdk7 | 16784414 | 11/20/2017 21:56:51 | HADES | JDBC Thin Clien/- | | 115s | 21958 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 116s | 6p35wmnbdwdk7 | 16784413 | 11/20/2017 21:55:28 | HADES| JDBC Thin Clien/- | | 115s | 20208 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 106s | 6p35wmnbdwdk7 | 16784412 | 11/20/2017 21:54:04 | HADES| JDBC Thin Clien/- | | 106s | 21726 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 107s | 6p35wmnbdwdk7 | 16784411 | 11/20/2017 21:52:41 | HADES| JDBC Thin Clien/- | | 107s | 20730 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 106s | 6p35wmnbdwdk7 | 16784410 | 11/20/2017 21:51:18 | HADES | JDBC Thin Clien/- | | 106s | 21122 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 106s | 6p35wmnbdwdk7 | 16784409 | 11/20/2017 21:49:55 | HADES | JDBC Thin Clien/- | | 106s | 21262 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 105s | 6p35wmnbdwdk7 | 16784408 | 11/20/2017 21:48:34 | HADES| JDBC Thin Clien/- | | 106s | 21097 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 100s | 6p35wmnbdwdk7 | 16784407 | 11/20/2017 21:47:15 | HADES| JDBC Thin Clien/- | | 101s | 20927 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 107s | 6p35wmnbdwdk7 | 16784406 | 11/20/2017 21:45:46 | HADES | JDBC Thin Clien/- | | 109s | 21794 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 107s | 6p35wmnbdwdk7 | 16784405 | 11/20/2017 21:44:28 | HADES | JDBC Thin Clien/- | | 107s | 20380 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 97s | 6p35wmnbdwdk7 | 16784404 | 11/20/2017 21:43:09 | HADES | JDBC Thin Clien/- | | 98s | 21412 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 99s | 6p35wmnbdwdk7 | 16784403 | 11/20/2017 21:41:49 | HADES | JDBC Thin Clien/- | | 100s | 20940 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 98s | 6p35wmnbdwdk7 | 16784402 | 11/20/2017 21:40:30 | HADES | JDBC Thin Clien/- | | 98s | 21375 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 115s | 6p35wmnbdwdk7 | 16784401 | 11/20/2017 21:38:53 | HADES | JDBC Thin Clien/- | | 116s | 24074 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 116s | 6p35wmnbdwdk7 | 16784400 | 11/20/2017 21:37:35 | HADES | JDBC Thin Clien/- | | 116s | 24567 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 116s | 6p35wmnbdwdk7 | 16784399 | 11/20/2017 21:35:57 | HADES | JDBC Thin Clien/- | | 116s | 27771 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 115s | 6p35wmnbdwdk7 | 16784398 | 11/20/2017 21:34:40 | HADES | JDBC Thin Clien/- | | 117s | 24323 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) |
| | | | | | | | | | | AND ROWNUM <= :3 |
| DONE | 114s | 6p35wmnbdwdk7 | 16784397 | 11/20/2017 21:33:03 | HADES | JDBC Thin Clien/- | | 114s | 27551 | DELETE FROM HDS_FM_FRVER_WORKER_TASK_B WHERE |
| | | | | | | | | | | STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 )
(4) 查看整个系统的性能
SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;