sql monitor 实践
来源：互联网 发布：成都网络综合布线 编辑：程序博客网 时间：2024/06/08 05:38
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 LONG 1000000
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;
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 creationtimeGlobal 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 ReportSQL Text------------------------------DELETE FROM HDS_B WHERE STATUS = :1 AND CREATE_TIME < (SYSDATE - :2 ) AND ROWNUM <= :3Global 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;
阅读全文
0 0
- sql monitor 实践
- 【实践】Memory Monitor使用
- Monitor SQL Server Memory
- Oracle -- SQL monitor privilege
- SQL Server Monitor v0.5
- Informix DB Monitor SQL Text
- SQL Server CDC Admin & Monitor
- Oracle 11g sql*monitor
- monitor
- Monitor
- Monitor
- Monitor
- monitor
- Monitor
- Correlating SQL Server Profiler with Performance Monitor
- SQL实践
- 华丽丽的SQL报告：SQL Monitor Report
- Monitor sql connection from .Net SqlClient Data Provider
- python-正则表达式
- HDU-1394-Minimum Inversion Number树状数组
- ORA-01691: unable to extend lob segment 问题解决
- 普通程序员如何入门深度学习？
- centos环境下如何配置yum源
- sql monitor 实践
- Java将图片处理成背景透明的圆形图片
- Linux下vim的常用命令
- first_report_gu
- C++ 继承、虚继承、虚函数类内存分配
- tomcat+myeclipse+mysql环境搭建
- 深入理解数据结构之链表
- 【NOIP2017普及组正式赛】跳房子
- 初识Hibernate(一)之配置文件以及框架搭建
