[Oracle工程师手记] 利用 DBMS_SQLTUNE.report_sql_monitor 生成 SQL 语句的监控信息

可以通过给 SQL语句加 /*+ MONITOR */，强制收集 monitor 信息（正常情况下 5秒以上的 SQL语句会被自动收集），之后用 BMS_SQLTUNE.report_sql_monitor 得到执行时的执行计划等信息。甚至也包括执行时发生的 I/O 等信息。下面是一个小例子：

执行 SQL 语句：

grant dba to u1 identified by u1;

CONN u1/u1

create table tab001 as select * from dba_objects;


SELECT /*+ MONITOR */ object_name
FROM   tab001 t1
where mod(t1.object_id,2) =0;

查看这个SQL语句的 sql_id：

SQL> SELECT sql_id, status, sql_text
FROM   v$sql_monitor
WHERE  username = 'U1';

SQL_ID        STATUS
------------- -------------------
SQL_TEXT
------------------------------------------------------
7djhpnfv7d4wu DONE (ALL ROWS)
SELECT /*+ MONITOR */ object_name
FROM   tab001 t1
where mod(t1.object_id,2) =0

利用上面得到的 SQL_ID，获得执行计划等信息。

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       => '7djhpnfv7d4wu',
  type         => 'TEXT',
  report_level => 'ALL') AS report
FROM dual;


SQL Text
------------------------------
SELECT /*+ MONITOR */ object_name FROM tab001 t1 where mod(t1.object_id,2) =0

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  U1 (78:6405)
 SQL ID              :  7djhpnfv7d4wu
 SQL Execution ID    :  16777216
 Execution Started   :  04/10/2021 14:49:39
 First Refresh Time  :  04/10/2021 14:49:39
 Last Refresh Time   :  04/10/2021 14:49:45
 Duration            :  6s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@hst05200.tst.com (TNS V1-V3)
 Fetch Calls         :  2430

Global Stats
===========================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
===========================================================================
|    0.10 |    0.03 |     0.00 |     0.06 |  2430 |   3831 |   27 |  11MB |
===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=3707625324)
==========================================================================================================================================
| 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    |        |         |      |         7 |     +0 |     1 |    36430 |      |       |          |                 |
|  1 |   TABLE ACCESS FULL | TAB001 |     730 |  397 |         7 |     +0 |     1 |    36430 |   27 |  11MB |          |                 |
==========================================================================================================================================

SQL>