sql monitoring實驗

通過set autotrace on語句，我們可以跟蹤一條語句的執行計劃和執行消耗的統計。這些資料是資料庫層面的，或者說偏重於資料庫方面的負載情況。通常來說，這些訊息在語句調優方面足夠了，但有時候我們想要看到該語句消耗了多少i/o時間、多少cpu時間、花費的總時間等資訊，這時可以藉助sql monitoring特性，官方文件中稱為generating the sql monitor report。

該特性監控的是實時語句，當一條語句是並行執行、或者執行一次所花費的cpu時間或i/o時間超過了5秒，就會自動儲存這些資訊。通過v$sql_monitor和v$sql_plan_monitor檢視可以檢視具體細節。

隨著一條滿足上述條件語句的執行，相關的統計資料每秒被重新整理到動態效能檢視v$sql_monitor中，資料每秒重新整理，語句結束後不會立即刪除，而是最少保留一分鐘。如果新的待監控語句進入系統，將覆蓋舊資料。

為了唯一確認一條待監控的語句，可以使用sql_id、sql_exec_start、sql_exec_id組成的混合鍵作為條件。

在v$sql_plan_monitor檢視中可以看到語句的監控資訊，也是每秒重新整理的。儲存策略與v$sql_monitor相同。

使用dbms_sqltune.report_sql_monitor函式可以將語句監控的結果輸出到檔案或者直接顯示出來。

（1）準備
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 22 06:51:14 2012

SQL> conn scott/scott
Connected.

SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

SQL> show parameter control_management_pack_access

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string DIAGNOSTIC+TUNING

（2）執行超過5秒的語句的監控
首先在sqldeveloper中執行語句：
select * from dba_tables t,dba_objects o order by t.iot_name,o.data_object_id

由於是兩個表的笛卡爾積連線，花費時間超過了五秒，可以被監控。

通過v$sql_monitor可以看到：

SQL> select t.KEY,t.MODULE,substr(t.SQL_TEXT,1,30) from v$sql_monitor t
where t.FIRST_REFRESH_TIME>sysdate-20/1440; 2

KEY MODULE SUBSTR(T.SQL_TEXT,1,30)
---------- ------------------------------------------------ ------------------------------
9.0194E+10 PL/SQL Developer select * from dba_tables t,dba

通過下列語句看到監控結果（由於語句未執行完成，因此有報錯）：

SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select * from dba_tables t,dba_objects o order by t.iot_name,o.data_object_id

Error: ORA-1013
------------------------------
ORA-01013: ？？？？？？？？？？？

Global Information
------------------------------
Status              : DONE (ERROR)
Instance ID         : 1
Session             : SCOTT (18:49)
SQL ID              : 9tx1sm6g512p8
SQL Execution ID    : 16777216
Execution Started   : 12/22/2012 12:35:16
First Refresh Time : 12/22/2012 12:35:20
Last Refresh Time   : 12/22/2012 12:35:45
Duration            : 29s
Module/Action       : PL/SQL Developer/SQL Window - Query data of table
Service             : SYS$USERS
Program             : plsqldev.exe
Fetch Calls         : 1

Global Stats
================================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
================================================================================
| 32 | 17 | 14 | 1 | 5209 | 563 | 26MB | 1148 | 242MB |
================================================================================

SQL Plan Monitoring Details (Plan Hash Value=2413660526)
================================================================================
| Id |              Operation              |     Name      | Rows   | Cost |
|    |                                     |               | (Estim) |      | Ac
================================================================================
| 0 | SELECT STATEMENT                    |               |         |      |
| 1 |   SORT ORDER BY                     |               |    205M | 149M |
|    |                                     |               |         |      |
| 2 |    MERGE JOIN CARTESIAN             |               |    205M | 771K |
| 3 |     HASH JOIN                       |               |    3023 | 995 |
| 4 |      FIXED TABLE FULL               | X$KSPPCV      |     100 |      |
| 5 |      MERGE JOIN CARTESIAN           |               |    3023 | 995 |
| 6 |       HASH JOIN RIGHT OUTER         |               |    3023 | 961 |
| 7 |        TABLE ACCESS FULL            | SEG$          |    5694 |   47 |
| 8 |        HASH JOIN RIGHT OUTER        |               |    2876 | 914 |
| 9 |         TABLE ACCESS FULL           | USER$         |      91 |    3 |
| 10 |         HASH JOIN RIGHT OUTER       |               |    2876 | 910 |
| 11 |          TABLE ACCESS FULL          | DEFERRED_STG$ |    2602 |    7 |
| 12 |          HASH JOIN OUTER            |               |    2876 | 903 |
| 13 |           HASH JOIN OUTER           |               |    2876 | 831 |
| 14 |            HASH JOIN                |               |    2876 | 629 |
| 15 |             TABLE ACCESS FULL       | USER$         |      91 |    3 |
| 16 |             HASH JOIN               |               |    2876 | 625 |
| 17 |              HASH JOIN              |               |    2876 | 379 |
| 18 |               TABLE ACCESS FULL     | TS$           |       8 |    5 |
| 19 |               TABLE ACCESS FULL     | TAB$          |    2876 | 373 |
| 20 |              TABLE ACCESS FULL      | OBJ$          |   73071 | 246 |
| 21 |            INDEX FAST FULL SCAN     | I_OBJ2        |   73071 | 201 |
| 22 |           INDEX FAST FULL SCAN      | I_OBJ1        |   73071 |   71 |
| 23 |       BUFFER SORT                   |               |       1 | 947 |
| 24 |        FIXED TABLE FULL             | X$KSPPI       |       1 |      |
| 25 |     BUFFER SORT                     |               |   67937 | 149M |
| 26 |      VIEW                           | DBA_OBJECTS   |   67937 | 255 |
| 27 |       UNION-ALL                     |               |         |      |
| 28 |        TABLE ACCESS BY INDEX ROWID | SUM$          |       1 |      |
| 29 |         INDEX UNIQUE SCAN           | I_SUM$_1      |       1 |      |
| 30 |        TABLE ACCESS BY INDEX ROWID | OBJ$          |       1 |    3 |
| 31 |         INDEX RANGE SCAN            | I_OBJ1        |       1 |    2 |
| 32 |        FILTER                       |               |         |      |
| 33 |         HASH JOIN                   |               |   73068 | 252 |
| 34 |          TABLE ACCESS FULL          | USER$         |      91 |    3 |
| 35 |          HASH JOIN                  |               |   73068 | 248 |
| 36 |           INDEX FULL SCAN           | I_USER2       |      91 |    1 |
| 37 |           TABLE ACCESS FULL         | OBJ$          |   73068 | 246 |
| 38 |         TABLE ACCESS BY INDEX ROWID | IND$          |       1 |    2 |
| 39 |          INDEX UNIQUE SCAN          | I_IND1        |       1 |    1 |
| 40 |         NESTED LOOPS                |               |       1 |    2 |
| 41 |          INDEX FULL SCAN            | I_USER2       |       1 |    1 |
| 42 |          INDEX RANGE SCAN           | I_OBJ4        |       1 |    1 |
| 43 |        NESTED LOOPS                 |               |       1 |    3 |
| 44 |         TABLE ACCESS FULL           | LINK$         |       1 |    2 |
| 45 |         TABLE ACCESS CLUSTER        | USER$         |       1 |    1 |
| 46 |          INDEX UNIQUE SCAN          | I_USER#       |       1 |      |
================================================================================

通過以上步驟方便地得到正在或剛剛完成的語句的相關統計資料，和10046事件相比，操作簡便一些，
不需要在資料庫和系統間切換。

監控資訊可以和如下檢視一起使用：
■V$ACTIVE_SESSION_HISTORY
■V$SESSION
■V$SESSION_LONGOPS
■V$SQL
■V$SQL_PLAN

（3）並行語句的監控

執行並行度為2的一條語句：

SQL> create table te parallel 2 as select * from user_tables;

Table created.

檢視v$sql_monitor：

SQL> select t.KEY,t.MODULE,substr(t.SQL_TEXT,1,30) from v$sql_monitor t
where t.FIRST_REFRESH_TIME>sysdate-20/1440; 2

KEY MODULE SUBSTR(T.SQL_TEXT,1,30)
---------- ------------------------------------------------ ------------------------------
2.3622E+11
2.3622E+11
2.8347E+11
2.8347E+11
3.6937E+11 SQL*Plus create table te parallel 2 as

可以通過sql_text、時間等資訊找到sql_id,sql_exec_start,sql_exec_id:

SQL> select sql_id,sql_exec_start,sql_exec_id,sql_text from v$sql_monitor where sql_text like 'create table te%';

SQL_ID SQL_EXEC_ SQL_EXEC_ID SQL_TEXT
------------- --------- ----------- -------------------------------------------------------
fphq85mq3j3fu 22-DEC-12 16777216 create table te parallel 2 as select * from user_tables

按照官方文件的說法，使用以下語句可以顯示結果：
variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => 'fphq85mq3j3fu',sql_exec_id => '16777216');
END;
/
print :my_rept;

但實驗中有些問題，結果為：
SQL> variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => 'fphq85mq3j3fu',sql_exec_id => '16777216');
END;
/
print :my_rept;
SQL> 2 3 4 SP2-0552: Bind variable "MY_REPT" not declared.
SQL>
variable my_rept CLOB;
BEGIN
:my_rept :=DBMS_SQLTUNE.REPORT_SQL_MONITOR();
END;
/
print :my_rept;SP2-0552: Bind variable "MY_REPT" not declared.
SQL> SQL> 2 3 4
PL/SQL procedure successfully completed.

SQL> print :my_rept
ERROR:
ORA-00911: invalid character

MY_REPT
--------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
create table te p

（4）除了並行語句和佔用時間長的語句外，還可以通過hint為特定語句進行實時監控。

SQL> select /*+monitor*/object_id,object_name from t where object_id='20';

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
20 ICOL$

SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+monitor*/object_id,object_name from t where object_id='20'

Global Information
------------------------------
Status              : DONE (ALL ROWS)
Instance ID         : 1
Session             : SCOTT (140:461)
SQL ID              : d82yd0gq3d1fx
SQL Execution ID    : 16777216
Execution Started   : 12/22/2012 06:10:38
First Refresh Time : 12/22/2012 06:10:38
Last Refresh Time   : 12/22/2012 06:10:38
Duration            : .186049s
Module/Action       : PL/SQL Developer/Command Window - New
Service             : SYS$USERS
Program             : plsqldev.exe
Fetch Calls         : 1

Global Stats
================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
================================================================
| 0.19 | 0.07 | 0.12 | 1 | 1034 | 37 | 8MB |
================================================================

SQL Plan Monitoring Details (Plan Hash Value=1601196873)
================================================================================
| Id |      Operation      | Name | Rows   | Cost |   Time    | Start | Execs
|    |                     |      | (Estim) |      | Active(s) | Active |
================================================================================
| 0 | SELECT STATEMENT    |      |         |      |         1 |     +0 |     1
| 1 |   TABLE ACCESS FULL | T    |       1 | 289 |         1 |     +0 |     1
================================================================================

如果不希望產生監控資訊，可以使用hint：no_monitor

sql 監控報告的資料是從如下檢視獲得的：
■GV$SQL_MONITOR
■GV$SQL_PLAN_MONITOR
■GV$SQL
■GV$SQL_PLAN
■GV$ACTIVE_SESSION_HISTORY
■GV$SESSION_LONGOPS

另外再說一下dbms_sqltune包，通過desc 命令，可以看到該包下有很多過程。可以利用REPORT_SQL_MONITOR、REPORT_SQL_MONITOR_XML等命令報告監控資料，還可以使用CREATE_SQLSET 、LOAD_SQLSET等儲存過程進行sql_tuning_set的設定。後續將繼續實驗。

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

sql monitoring實驗

相關文章