oracle之 v$sql_monitor 監視正在運作的SQL語句的統計資訊

11g中引入了新的動态性能視圖V$SQL_MONITOR，該視圖用以顯示Oracle監視的SQL語句資訊。SQL監視會對那些并行執行或者消耗5秒以上cpu時間或I/O時間的SQL語句自動啟動，同時在V$SQL_MONITOR視圖中産生一條記錄。當SQL語句正在執行，V$SQL_MONITOR視圖中的統計資訊将被實時重新整理，頻率為每秒1次。SQL語句執行完成後，監視資訊将不會被立即删除，Oracle會保證相關記錄儲存一分鐘(由參數_sqlmon_recycle_time所控制，預設為60s)，最終這些記錄都會被删除并被重用。這一新的SQL性能監視特性僅在CONTROL_MANAGEMENT_PACK_ACCESS為DIAGNOSTIC+TUNING和STATISTICS_LEVEL為ALL|TYPICAL時被啟用。

v$sql_monitor視圖包含目前正在運作的SQL語句，以及最近運作的SQL語句。

使用v$sql_monitor視圖中所監控的SQL語句時需在滿足以下條件：

1） 自動監控任何并行語句

2） 如果一個SQL語句消耗了超過5秒的CPU或I/O時間，則會自動監控

3） 監控任何在語句級啟用監控的SQL語句（使用monitor或no_monitor）

提示：結合v$sql_monitor視圖與v$sql_plan_monitor視圖可以進一步查詢SQL的執行計劃等資訊。聯合一些其他視圖，如v$active_session_history、v$session、v$session_longops、v$sql、v$sql_plan等，可以獲得關于SQL的更多資訊。

注意：為了進行SQL監控，初始化參數STATISTICS_LEVEL必須設定為TYPICAL或ALL，CONTROL_MANAGEMENT_PACK_ACCESS必須設定為DIAGNOSTIC+TUNING。

SQL> show parameter STATISTICS_LEVE

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL

SQL> show parameter CONTROL_MANAGEMENT_PACK_ACCESS

control_management_pack_access string DIAGNOSTIC+TUNING

1、 如檢視資料庫中消耗CPU資源排名前10位的查詢：

select * from (

select sql_id,username,to_char(sql_exec_start,'yyyy-mm-dd hh24:mi:ss') sql_exec_start,

sql_exec_id,sum(buffer_gets) buffer_gets,

sum(disk_reads) disk_reads,round(sum(cpu_time/1000000),1) cpu_secs

from v$sql_monitor

where username not in ('SYS','SYSTEM')

group by sql_id,username,sql_exec_start,sql_exec_id

order by 7 desc)

where rownum<=10;

SQL_ID USERNAME SQL_EXEC_START SQL_EXEC_ID BUFFER_GETS DISK_READS CPU_SECS

------------- ------------------------------ ------------------- ----------- ----------- ---------- ----------

8p8ann3c68vxc SCOTT 2016-11-24 09:50:20 16777216 0 0 37.1

2、監控指定的SQL語句：

2.1、 Hint提示監控指定的SQL語句

select /*+ monitor */e.empno,e.ename,e.sal,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno;

2.2、v$sql_monitor視圖中檢視被監控的SQL語句消耗的資源資訊：

sum(disk_reads)disk_reads,round(sum(cpu_time/1000000),1) cpu_secs

where sql_text like '%monitor%'

group by sql_id,username,sql_exec_start,sql_exec_id;

2.3、 然後可以使用DBMS_SQLTUNE包來檢視SQL語句生成的監控資訊（如執行計劃等）：

SQL> 

set long 10000000

set longchunksize 10000000

set linesize 200

select dbms_sqltune.report_sql_monitor from dual;

文章可以轉載，必須以連結形式标明出處。

本文轉自 張沖andy 部落格園部落格，原文連結：  http://www.cnblogs.com/andy6/p/7507195.html ，如需轉載請自行聯系原作者