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 : sqlplus@eagle (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@eagle (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
-----------------