Oracle11g使用sql_monitor實時監控sql

kunlunzhiying發表於2016-03-29

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)

頂 0 踩

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28211342/viewspace-2071821/,如需轉載,請註明出處,否則將追究法律責任。

相關文章