Oracle11g使用sql_monitor實時監控sql
Oracle 11g sql_Monitor的實時監控
在 Oracle Database 11g 中，v$session 檢視增加了一些新的欄位，這其中包括SQL_EXEC_START 和 SQL_EXEC_ID
這兩個欄位實際上代表了 Oracle 11g 的一個新特性：實時的 SQL 監視（Real Time SQL Monitoring）。
在 Oracle 11g 之前的版本，長時間執行的 SQL 可以透過監控 v$session_longops
當某個操作執行時間超過 6 秒，就會被記錄在 v$session_longops 中，通常可以監控到全表掃
描、全索引掃描、雜湊聯接、並行查詢等操作；而在 Oracle 11g 中，當 SQL 並行執行時，會
立即被實時監控到，或者當 SQL 單程式執行時，如果消耗超過 5 秒的 CPU 或 I/O 時間，它也
會被監控到。監控資料被記錄在 V$SQL_MONITOR 檢視中，當然也可以透過 Oracle 11g 新增
的 package DBMS_MONITOR 來主動對 SQL 執行監控部署。
idle> desc v$session;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
SADDR RAW(8)
SID NUMBER
SERIAL# NUMBER
AUDSID NUMBER
PADDR RAW(8)
USER# NUMBER
USERNAME VARCHAR2(30)
COMMAND NUMBER
OWNERID NUMBER
TADDR VARCHAR2(16)
LOCKWAIT VARCHAR2(16)
STATUS VARCHAR2(8)
SERVER VARCHAR2(9)
SCHEMA# NUMBER
SCHEMANAME VARCHAR2(30)
OSUSER VARCHAR2(30)
PROCESS VARCHAR2(24)
MACHINE VARCHAR2(64)
PORT NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TYPE VARCHAR2(10)
SQL_ADDRESS RAW(8)
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
PREV_SQL_ADDR RAW(8)
PREV_HASH_VALUE NUMBER
PREV_SQL_ID VARCHAR2(13)
PREV_CHILD_NUMBER NUMBER
PREV_EXEC_START DATE
PREV_EXEC_ID NUMBER
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
CLIENT_INFO VARCHAR2(64)
FIXED_TABLE_SEQUENCE NUMBER
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
TOP_LEVEL_CALL# NUMBER
LOGON_TIME DATE
LAST_CALL_ET NUMBER
PDML_ENABLED VARCHAR2(3)
FAILOVER_TYPE VARCHAR2(13)
FAILOVER_METHOD VARCHAR2(10)
FAILED_OVER VARCHAR2(3)
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
PDML_STATUS VARCHAR2(8)
PDDL_STATUS VARCHAR2(8)
PQ_STATUS VARCHAR2(8)
CURRENT_QUEUE_DURATION NUMBER
CLIENT_IDENTIFIER VARCHAR2(64)
BLOCKING_SESSION_STATUS VARCHAR2(11)
BLOCKING_INSTANCE NUMBER
BLOCKING_SESSION NUMBER
FINAL_BLOCKING_SESSION_STATUS VARCHAR2(11)
FINAL_BLOCKING_INSTANCE NUMBER
FINAL_BLOCKING_SESSION NUMBER
SEQ# NUMBER
EVENT# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(8)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(8)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(8)
WAIT_CLASS_ID NUMBER
WAIT_CLASS# NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
WAIT_TIME_MICRO NUMBER
TIME_REMAINING_MICRO NUMBER
TIME_SINCE_LAST_WAIT_MICRO NUMBER
SERVICE_NAME VARCHAR2(64)
SQL_TRACE VARCHAR2(8)
SQL_TRACE_WAITS VARCHAR2(5)
SQL_TRACE_BINDS VARCHAR2(5)
SQL_TRACE_PLAN_STATS VARCHAR2(10)
SESSION_EDITION_ID NUMBER
CREATOR_ADDR RAW(8)
CREATOR_SERIAL# NUMBER
ECID VARCHAR2(64)
idle> desc v$sql_monitor
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
KEY NUMBER
STATUS VARCHAR2(19)
USER# NUMBER
USERNAME VARCHAR2(30)
MODULE VARCHAR2(64)
ACTION VARCHAR2(64)
SERVICE_NAME VARCHAR2(64)
CLIENT_IDENTIFIER VARCHAR2(64)
CLIENT_INFO VARCHAR2(64)
PROGRAM VARCHAR2(48)
PLSQL_ENTRY_OBJECT_ID NUMBER
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
PLSQL_OBJECT_ID NUMBER
PLSQL_SUBPROGRAM_ID NUMBER
FIRST_REFRESH_TIME DATE
LAST_REFRESH_TIME DATE
REFRESH_COUNT NUMBER
SID NUMBER
PROCESS_NAME VARCHAR2(5)
SQL_ID VARCHAR2(13)
SQL_TEXT VARCHAR2(2000)
IS_FULL_SQLTEXT VARCHAR2(1)
SQL_EXEC_START DATE
SQL_EXEC_ID NUMBER
SQL_PLAN_HASH_VALUE NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
SQL_CHILD_ADDRESS RAW(8)
SESSION_SERIAL# NUMBER
PX_IS_CROSS_INSTANCE VARCHAR2(1)
PX_MAXDOP NUMBER
PX_MAXDOP_INSTANCES NUMBER
PX_SERVERS_REQUESTED NUMBER
PX_SERVERS_ALLOCATED NUMBER
PX_SERVER# NUMBER
PX_SERVER_GROUP NUMBER
PX_SERVER_SET NUMBER
PX_QCINST_ID NUMBER
PX_QCSID NUMBER
ERROR_NUMBER VARCHAR2(40)
ERROR_FACILITY VARCHAR2(4)
ERROR_MESSAGE VARCHAR2(256)
BINDS_XML CLOB
OTHER_XML CLOB
ELAPSED_TIME NUMBER
QUEUING_TIME NUMBER
CPU_TIME NUMBER
FETCHES NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
IO_INTERCONNECT_BYTES NUMBER
PHYSICAL_READ_REQUESTS NUMBER
PHYSICAL_READ_BYTES NUMBER
PHYSICAL_WRITE_REQUESTS NUMBER
PHYSICAL_WRITE_BYTES NUMBER
APPLICATION_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
RM_LAST_ACTION VARCHAR2(48)
RM_LAST_ACTION_REASON VARCHAR2(30)
RM_LAST_ACTION_TIME DATE
RM_CONSUMER_GROUP VARCHAR2(30)
V$SQL_MONITOR 收集的資訊每秒重新整理一次，接近實時，當 SQL 執行完畢，資訊並不會
立即從 v$sql_monitor 中刪除，至少會保留 1 分鐘，v$sql_plan_monitor 檢視中的執行計劃資訊
也是每秒更新一次，當 SQL 執行完結，它們同樣至少被保留 1 分鐘。
實時 SQL 監控需要 statistics_level 初始化引數設定為 TYPICAL 或 ALL：
idle> show parameter statistic
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
同 CONTROL_MANAGEMENT_PACK_ACCESS 引數必須是 DIAGNOSTIC+TUNING（這是預設設定）：
idle> show parameter control_managemen
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING
idle> SELECT statistics_name,session_status,system_status,activation_level,session_settable FROM v$statistics_level WHERE statistics_name = 'SQL Monitoring';
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
---------------------------------------------------------------- -------- -------- ------- ---
SQL Monitoring ENABLED ENABLED TYPICAL YES
，資料庫會啟動自動的實時 SQL 監控，Oracle 還提供 Hints 可以強制制定對
SQL 執行監控或者不允許執行監控，這兩個 Hints 是 monitor 與 no_monitor。
select /*+ monitor */ count(*) from scott.emp where sal > 5000;
select /*+ no_monitor */ count(*) from scott.emp where sal >5000;
檢視監控資訊
idle> set long 10000000
idle> set longchunksize 10000000
idle> set linesize 200
idle> select /*+ monitor */ count(*) from scott.emp where sal > 5000;
COUNT(*)
----------
0
idle> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor */ count(*) from scott.emp where sal > 5000
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (1:5)
SQL ID : 6jfz01hn2n1mj
SQL Execution ID : 16777216
Execution Started : 01/12/2014 10:05:04
First Refresh Time : 01/12/2014 10:05:04
Last Refresh Time : 01/12/2014 10:05:04
Duration : .046322s
Module/Action : (TNS V1-V3)/-
Service : SYS$USERS
Program : (TNS V1-V3)
Fetch Calls : 1
Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 0.05 | 0.00 | 0.01 | 0.03 | 1 | 6 | 2 | 49152 |
===========================================================================
SQL Plan Monitoring Details (Plan Hash Value=2083865914)
=========================================================================================================================================
| 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 | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | |
| 2 | TABLE ACCESS FULL | EMP | 1 | 3 | | | 1 | | 2 | 49152 | | |
=========================================================================================================================================
對於資料庫中已經捕獲的 SQL，透過其 SQL_ID，使用 DBMS_SQLTUNE 程式包中的
REPORT_SQL_MONITOR 函式，我們可以生成更為直觀的 SQL 報告輸出，輔助分析和診斷。
通常情況下，提供 SQL_ID 等少數引數，即可生成報告，TYPE 引數用於指定報告型別，
這裡可以指定生成：TEXT、HTML、XML、ACTIVE 模式的報告。ACTIVE 模式的報告最為
華麗直觀。
首先可以透過查詢 v$sql_monitor 獲得那些被監控收集過的 SQL 資訊：
idle> select sql_id from v$sql_monitor;
SQL_ID
-------------
6gvch1xu9ca3g
5zruc4v6y32f9
6jfz01hn2n1mj
53c2k4c43zcfx
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 => '&sqlid', type => 'TEXT')
AS report FROM dual;
Enter value for sqlid: 5zruc4v6y32f9
old 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT')
new 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '5zruc4v6y32f9', type => 'TEXT')
SQL Monitoring Report
SQL Text
------------------------------
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_subname VARCHAR2(30) := :job_subname; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE := :job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; chain_id VARCHAR2(14) := :chainid;
credential_owner varchar2(30) := :credown; credential_name varchar2(30) := :crednam; destination_owner varchar2(30) := :destown; destination_name varchar2(30) := :destnam; job_dest_id varchar2(14) := :jdestid; log_id number := :log_id; BEGIN DECLARE ename VARCHAR2(30); BEGIN ename := dbms_sqltune.execute_tuning_task( 'SYS_AUTO_SQL_TUNING_TASK'); END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : SYS (60:21)
SQL ID : 5zruc4v6y32f9
SQL Execution ID : 16777216
Execution Started : 01/12/2014 10:11:33
First Refresh Time : 01/12/2014 10:11:43
Last Refresh Time : 01/12/2014 10:11:44
Duration : 11s
Module/Action : DBMS_SCHEDULER/ORA$AT_SQ_SQL_SW_63
Service : SYS$USERS
Program : (J002)
Global Stats
===========================================================================================================
| Elapsed | Cpu | IO | Concurrency | PL/SQL | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
===========================================================================================================
| 11 | 0.82 | 1.83 | 0.00 | 1.52 | 8.15 | 22904 | 1973 | 30MB | 8 | 224KB |
===========================================================================================================
REPORT_SQL_MONITOR_LIST檢視在11gR2在v$sql_monitor中的總理效能
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_list(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 |
=================================================================================================================================================================================
| DONE | 5.0s | 53c2k4c43zcfx | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA$AT_OS_OPT_S | | 12s | 417 | MERGE /*+ dynamic_sampling(ST 4) |
| | | | | 10:11:44 | | | | | | dynamic_sampling_est_cdn(ST) */ INTO|
| | | | | | | | | | | STATS_TARGET$ ST USING (SELECT STALENESS, |
| | | | | | | | | | | OSIZE, OBJ#, TYPE#, AFLAGS, STATUS, SID, |
| | | | | | | | | | | SERIAL#, PART#, BO# FROM ( SELECT /*|
| | | | | | | | | | | no_expand... |
| DONE | 11s | 5zruc4v6y32f9 | 16777216 | 01/12/2014 | SYS | DBMS_SCHEDULER/ORA$AT_SQ_SQL_S | | 10s | 1981 | DECLARE job BINARY_INTEGER := :job; |
| | | | | 10:11:33 | | | | | | next_date TIMESTAMP WITH TIME ZONE :|
| | | | | | | | | | | :mydate; broken BOOLEAN := FALSE; job_name |
| | | | | | | | | | | VARCHAR2(30) := :job_name; job_subname |
| | | | | | | | | | | VARCHAR2(30) := :job_subname; job_owner... |
| DONE (ALL | 0.05s | 6jfz01hn2n1mj | 16777216 | 01/12/2014 | SYS | (/- | | 0.05s | 2 | select /*+ monitor */ count(*) from |
| ROWS) | | | | 10:05:04 | | | | | | scott.emp where sal > 5000 |
| DONE | 19s | 6gvch1xu9ca3g | 16777216 | 01/12/2014 | SYS | | | 19s | 1373 | DECLARE job BINARY_INTEGER := :job; |
| | | | | 09:52:27 | | | | | | next_date DATE := :mydate; broken BOOLEAN := |
| | | | | | | | | | | FALSE; BEGIN |
| | | | | | | | | | | EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); |
| | | | | | | | | | | :mydate := next_date; IF broken THEN :b := |
| | | | | | | | | | | 1; ELSE :b := 0; ... |
=================================================================================================================================================================================
(MOS ID 1380492.1)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2071821/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 程式實時監控
- 使用Flink SQL進行實時效能監控：AdTech廣告用例SQL
- JAMon監控SQL執行時間SQL
- 實時檔案監控
- iOS實時卡頓監控iOS
- 【SQL監控】SQL完全監控的指令碼SQL指令碼
- mytop 使用介紹 mysql實時監控工具MySql
- RunLoop實戰：實時卡頓監控OOP
- 監控Data Guard實時同步
- 實時監控網站安全網站
- 實時監控log檔案
- 實時監控系統，統一監控企業APIAPI
- sar效能監視命令-實時監控CPU
- 常用監控SQLSQL
- 如何使用nload實時監控網路頻寬
- oracle之 v$sql_monitor 監視正在執行的SQL語句的統計資訊OracleSQL
- Oracle慢SQL監控指令碼實現OracleSQL指令碼
- WGCLOUD實時視覺化監控 使用監測FTP和SFTP教程GCCloud視覺化FTP
- 在Ubuntu上使用Netdata設定實時效能監控Ubuntu
- Laravel 實時監聽列印 SQLLaravelSQL
- oracle 長sql監控OracleSQL
- Oracle常用監控SQLOracleSQL
- 監控長時間執行的查詢(監控資料庫效能的SQL ) -- 轉資料庫SQL
- webrtc-streamer實時播放監控Web
- 監控寶SQL Server效能監控的功能和配置SQLServer
- 使用Prometheus、Grafana監控Artifactory實踐PrometheusGrafana
- 使用Sar實現系統監控
- RestCloud監控平臺，專為微服務API打造的實時監控中心RESTCloud微服務API
- Oracle-常用監控SQLOracleSQL
- Oracle常用監控SQL(轉）OracleSQL
- 使用zabbix監控sql server的釋出訂閱SQLServer
- 監控使用高cpu的sql語句指令碼SQL指令碼
- TiDB監控實現--存活監控TiDB
- Oracle輕量級實時監控工具-oratopOracle
- IO實時監控命令iostat詳解iOS
- 網路實時監控工具：PeakHour for macMac
- Flume實時監控 單個追加檔案
- 實時成本監控系統淺談薦