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的设置。后续将继续实验。