有时候在优化SQL语句时，需要得到详细sql的相关信息，这个时候可以借助dbms_sqltune工具实现，还有一种情况，有时候知道SQL_ID也能通过v$sqlarea、v$sql等视图查看到sql_text，但是sql_text的内容看到的不全，这个时候也可以借助dbms_sqltune工具来展示完整的SQL_TEXT。
Oracle Database 11g: Real-Time SQL Monitoring
Real-Time SQL Monitoring, introduced in Oracle Database 11g, provides a very effective way to identify run-time performance problems with resource intensive long-running and parallel SQL statements. Interactive Enterprise Manager screens display details of SQL execution using new, fine-grained SQL statistic that are tracked out-of-the-box with no performance penalty to production systems. Statistics at each step of the execution plan are tracked by key performance metrics, including elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times. This allows DBAs to analyze SQL execution more deeply than previously possible and decide on the most appropriate tuning strategies for monitored SQL statements.
The real-time SQL monitoring feature of Oracle Database enables you to monitor the performance of SQL statements while they are executing. By default, SQL monitoring automatically starts when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or I/O time in a single execution.
oracle 11g 增加了实时监控SQL性能的工具dbms_sqltune, 单次执行时间超过5s的语句会被记录到v$sql_monitor中，下面是关于v$sql_monitor视图的介绍
V$SQL_MONITORV$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.
实验过程
1、首先查询v$sql_monitor视图
SQL> select sid, sql_id, status, username,sql_text from v$sql_monitor where username = 'SCOTT';
SID SQL_ID STATUS USERNAME SQL_TEXT
---------- ------------- ------------------- --------------- ----------------------------------------
142 7rnnuxvqm98y2 DONE (ERROR) SCOTT declare
Elapsed: 00:00:00.00
SQL>
这个时候scott用户被监控的sql只有一个。
2、连接到scott用户，执行一个SQL，执行消耗时间小于5s
SQL> show user
USER is "SCOTT"
SQL> set timing on
SQL> select count(*) from t;
COUNT(*)
----------
14
Elapsed: 00:00:00.00
SQL> 3、再次查询v$sql_monitor视图确认
SQL> select sid, sql_id, status, username,sql_text from v$sql_monitor where username = 'SCOTT';
SID SQL_ID STATUS USERNAME SQL_TEXT
---------- ------------- ------------------- --------------- ----------------------------------------
142 7rnnuxvqm98y2 DONE (ERROR) SCOTT declare
Elapsed: 00:00:00.00
SQL>
SQL> SQL> show user
USER is "SCOTT"
SQL> select /*+ monitor */ count(*) from t;
COUNT(*)
----------
14
Elapsed: 00:00:00.00
SQL> SQL> select sid, sql_id, status, username,sql_text from v$sql_monitor where username = 'SCOTT';
SID SQL_ID STATUS USERNAME SQL_TEXT
---------- ------------- ------------------- --------------- ----------------------------------------
125 5zh82u75cjxzt DONE (ALL ROWS) SCOTT select /*+ monitor */ count(*) from t <======发现sql已经记录到了v$sql_monitor 视图里面
142 7rnnuxvqm98y2 DONE (ERROR) SCOTT declare
Elapsed: 00:00:00.01
SQL> 6、使用DBMS_SQLTUNE.report_sql_monitor打印出完整的监控信息
FUNCTION REPORT_SQL_MONITOR RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
SESSION_ID NUMBER IN DEFAULT
SESSION_SERIAL NUMBER IN DEFAULT
SQL_EXEC_START DATE IN DEFAULT
SQL_EXEC_ID NUMBER IN DEFAULT
INST_ID NUMBER IN DEFAULT
START_TIME_FILTER DATE IN DEFAULT
END_TIME_FILTER DATE IN DEFAULT
INSTANCE_ID_FILTER NUMBER IN DEFAULT
PARALLEL_FILTER VARCHAR2 IN DEFAULT
PLAN_LINE_FILTER NUMBER IN DEFAULT
EVENT_DETAIL VARCHAR2 IN DEFAULT
BUCKET_MAX_COUNT NUMBER IN DEFAULT
BUCKET_INTERVAL NUMBER IN DEFAULT
BASE_PATH VARCHAR2 IN DEFAULT
LAST_REFRESH_TIME DATE IN DEFAULT
REPORT_LEVEL VARCHAR2 IN DEFAULT
TYPE VARCHAR2 IN DEFAULT
SQL_PLAN_HASH_VALUE NUMBER IN DEFAULT
一般使用如下两种方式
1、SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT') report FROM dual; -----生成文本
2、SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'active') report FROM dual; -----生成html格式
查询之前加上下面格式化下，输出结果会比较好看
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT') report FROM dual;
Enter value for sqlid: 5zh82u75cjxzt
old 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT') report FROM dual
new 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '5zh82u75cjxzt', type => 'TEXT') report FROM dual
SQL Monitoring Report
SQL Text
------------------------------
select /*+ monitor */ count(*) from t
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SCOTT (125:17)
SQL ID : 5zh82u75cjxzt
SQL Execution ID : 16777216
Execution Started : 06/13/2018 00:57:03
First Refresh Time : 06/13/2018 00:57:03
Last Refresh Time : 06/13/2018 00:57:03
Duration : .000207s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@QXY1.localdomain (TNS V1-V3)
Fetch Calls : 1
Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.00 | 0.00 | 1 | 2 |
=======================================
SQL Plan Monitoring Details (Plan Hash Value=2966233522)
==========================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==========================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |
| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |
| 2 | TABLE ACCESS FULL | T | 14 | 3 | 1 | +0 | 1 | 14 | | |
==========================================================================================================================
Elapsed: 00:00:00.07
SQL>
7、如果sql执行时间超过5s会自动记录到v$sql_monitor里面
SQL>
SQL> select count(*) from dba_extents;
《=======时间很长，还没有返回结果
SQL> select sid, sql_id, status, username,sql_text from v$sql_monitor where username = 'SCOTT';
125 1f9jvntajsr7v EXECUTING SCOTT select count(*) from dba_extents 《=======状态是执行中
125 5zh82u75cjxzt DONE (ALL ROWS) SCOTT select /*+ monitor */ count(*) from t
142 7rnnuxvqm98y2 DONE (ERROR) SCOTT declare
v_num number;
begin
for i in 1..10000000 loop
select count(*) into v_num from test;
end loop;
end;
Elapsed: 00:00:00.00
SQL> 9、查询monitor信息
SQL> SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT') report FROM dual;
Enter value for sqlid: 1f9jvntajsr7v
old 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT') report FROM dual
new 1: SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '1f9jvntajsr7v', type => 'TEXT') report FROM dual
SQL Monitoring Report
SQL Text
------------------------------
select count(*) from dba_extents
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SCOTT (125:17)
SQL ID : 1f9jvntajsr7v
SQL Execution ID : 16777216
Execution Started : 06/13/2018 01:06:10
First Refresh Time : 06/13/2018 01:06:16
Last Refresh Time : 06/13/2018 01:08:16
Duration : 126s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@QXY1.localdomain (TNS V1-V3)
Global Stats
===================================================================
| Elapsed | Cpu | IO | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===================================================================
| 125 | 124 | 0.00 | 1.17 | 9772 | 76 | 12MB |
===================================================================
SQL Plan Monitoring Details (Plan Hash Value=2677681623)
=================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | | (%) | (# samples|
=================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | |
| 1 | SORT AGGREGATE | | 1 | | | | 1 | | | | |
| 2 | VIEW | DBA_EXTENTS | 458 | 3609 | | | 1 | | | | |
| 3 | UNION-ALL | | | | | | 1 | | | | |
| 4 | HASH JOIN | | 1 | 1803 | | | 1 | | | | |
| 5 | NESTED LOOPS | | 1 | 22 | | | 1 | | | | |
| 6 | TABLE ACCESS FULL | UET$ | 1 | 22 | | | 1 | | | | |
| 7 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | | | | | | |
| 8 | VIEW | SYS_DBA_SEGS | 6853 | 1781 | | | | | | | |
| 9 | UNION-ALL | | | | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 22 | | | | | | | |
| 11 | NESTED LOOPS OUTER | | 1 | 21 | | | | | | | |
| 12 | NESTED LOOPS | | 1 | 20 | | | | | | | |
| 13 | NESTED LOOPS | | 1 | 20 | | | | | | | |
| 14 | TABLE ACCESS FULL | UNDO$ | 18 | 2 | | | | | | | |
| 15 | TABLE ACCESS CLUSTER | SEG$ | 1 | 1 | | | | | | | |
| 16 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | | | | | | | |
| 17 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | | | | | | |
| 18 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | |
| 19 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | | | | | | | |
| 20 | INDEX UNIQUE SCAN | I_TS# | 1 | | | | | | | | |
| 21 | NESTED LOOPS | | 6852 | 1759 | | | | | | | |
| 22 | VIEW | VW_JF_SET$CB2157A3 | 6852 | 1758 | | | | | | | |
| 23 | UNION-ALL | | | | | | | | | | |
| 24 | HASH JOIN RIGHT OUTER | | 6851 | 1701 | | | | | | | |
| 25 | INDEX FULL SCAN | I_USER2 | 103 | 1 | | | | | | | |
| 26 | HASH JOIN | | 6851 | 1700 | | | | | | | |
| 27 | VIEW | SYS_OBJECTS | 10141 | 1581 | | | | | | | |
| 28 | UNION-ALL | | | | | | | | | | |
| 29 | TABLE ACCESS FULL | TAB$ | 3007 | 393 | | | | | | | |
| 30 | TABLE ACCESS FULL | TABPART$ | 155 | 4 | | | | | | | |
| 31 | TABLE ACCESS FULL | CLU$ | 10 | 392 | | | | | | | |
| 32 | TABLE ACCESS FULL | IND$ | 5326 | 393 | | | | | | | |
| 33 | TABLE ACCESS FULL | INDPART$ | 302 | 5 | | | | | | | |
| 34 | TABLE ACCESS FULL | LOB$ | 1307 | 389 | | | | | | | |
| 35 | TABLE ACCESS FULL | TABSUBPART$ | 32 | 2 | | | | | | | |
| 36 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 2 | | | | | | | |
| 37 | TABLE ACCESS FULL | LOBFRAG$ | 1 | 2 | | | | | | | |
| 38 | MERGE JOIN CARTESIAN | | 50994 | 117 | | | | | | | |
| 39 | NESTED LOOPS | | 1 | 56 | | | | | | | |
| 40 | TABLE ACCESS FULL | SEG$ | 1 | 55 | | | | | | | |
| 41 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | | | | | | | |
| 42 | INDEX UNIQUE SCAN | I_TS# | 1 | | | | | | | | |
| 43 | BUFFER SORT | | 75479 | 116 | | | | | | | |
| 44 | INDEX FAST FULL SCAN | I_OBJ1 | 75479 | 61 | | | | | | | |
| 45 | NESTED LOOPS | | 1 | 57 | | | | | | | |
| 46 | NESTED LOOPS OUTER | | 1 | 56 | | | | | | | |
| 47 | TABLE ACCESS FULL | SEG$ | 1 | 55 | | | | | | | |
| 48 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | |
| 49 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | | | | | | | |
| 50 | INDEX UNIQUE SCAN | I_TS# | 1 | | | | | | | | |
| 51 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | | | | | | |
| 52 | NESTED LOOPS | | 457 | 1806 | | | 1 | | | | |
| -> 53 | HASH JOIN | | 6853 | 1806 | 121 | +6 | 1 | 0 | 1M | | |
| -> 54 | VIEW | SYS_DBA_SEGS | 6853 | 1781 | 121 | +6 | 1 | 3363 | | | |
| -> 55 | UNION-ALL | | | | 121 | +6 | 1 | 3363 | | | |
| 56 | NESTED LOOPS | | 1 | 22 | 1 | +6 | 1 | 18 | | | |
| 57 | NESTED LOOPS OUTER | | 1 | 21 | 1 | +6 | 1 | 18 | | | |
| 58 | NESTED LOOPS | | 1 | 20 | 1 | +6 | 1 | 18 | | | |
| 59 | NESTED LOOPS | | 1 | 20 | 1 | +6 | 1 | 18 | | | |
| 60 | TABLE ACCESS FULL | UNDO$ | 18 | 2 | 1 | +6 | 1 | 18 | | | |
| 61 | TABLE ACCESS CLUSTER | SEG$ | 1 | 1 | 1 | +6 | 18 | 18 | | | |
| 62 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 1 | +6 | 18 | 18 | | | |
| 63 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 1 | +6 | 18 | 18 | | | |
| 64 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | 1 | +6 | 18 | 18 | | | |
| 65 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 1 | +6 | 18 | 18 | | | |
| 66 | INDEX UNIQUE SCAN | I_TS# | 1 | | 1 | +6 | 18 | 18 | | | |
| -> 67 | NESTED LOOPS | | 6852 | 1759 | 121 | +6 | 1 | 3345 | | | |
| -> 68 | VIEW | VW_JF_SET$7C596FAF | 6852 | 1758 | 121 | +6 | 1 | 3345 | | | |
| -> 69 | UNION-ALL | | | | 121 | +6 | 1 | 3345 | | | |
| -> 70 | HASH JOIN RIGHT OUTER | | 6851 | 1701 | 121 | +6 | 1 | 3345 | 2M | | |
| 71 | INDEX FULL SCAN | I_USER2 | 103 | 1 | 1 | +6 | 1 | 103 | | | |
| -> 72 | HASH JOIN | | 6851 | 1700 | 126 | +1 | 1 | 3345 | 2M | 70.40 | Cpu (88) |
| 73 | VIEW | SYS_OBJECTS | 10141 | 1581 | 1 | +6 | 1 | 10196 | | | |
| 74 | UNION-ALL | | | | 1 | +6 | 1 | 10196 | | | |
| 75 | TABLE ACCESS FULL | TAB$ | 3007 | 393 | 1 | +6 | 1 | 3007 | | | |
| 76 | TABLE ACCESS FULL | TABPART$ | 155 | 4 | 1 | +6 | 1 | 177 | | | |
| 77 | TABLE ACCESS FULL | CLU$ | 10 | 392 | 1 | +6 | 1 | 10 | | | |
| 78 | TABLE ACCESS FULL | IND$ | 5326 | 393 | 1 | +6 | 1 | 5328 | | | |
| 79 | TABLE ACCESS FULL | INDPART$ | 302 | 5 | 1 | +6 | 1 | 325 | | | |
| 80 | TABLE ACCESS FULL | LOB$ | 1307 | 389 | 1 | +6 | 1 | 1316 | | | |
| 81 | TABLE ACCESS FULL | TABSUBPART$ | 32 | 2 | 1 | +6 | 1 | 32 | | | |
| 82 | TABLE ACCESS FULL | INDSUBPART$ | 1 | 2 | | | 1 | | | | |
| 83 | TABLE ACCESS FULL | LOBFRAG$ | 1 | 2 | 1 | +6 | 1 | 1 | | | |
| -> 84 | MERGE JOIN CARTESIAN | | 50994 | 117 | 121 | +6 | 1 | 254M | | 18.40 | Cpu (23) |
| -> 85 | NESTED LOOPS | | 1 | 56 | 121 | +6 | 1 | 3360 | | | |
| -> 86 | TABLE ACCESS FULL | SEG$ | 1 | 55 | 121 | +6 | 1 | 3360 | | | |
| -> 87 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 121 | +6 | 3360 | 3360 | | | |
| -> 88 | INDEX UNIQUE SCAN | I_TS# | 1 | | 121 | +6 | 3360 | 3360 | | | |
| -> 89 | BUFFER SORT | | 75479 | 116 | 125 | +2 | 3360 | 254M | 3M | 11.20 | Cpu (14) |
| 90 | INDEX FAST FULL SCAN | I_OBJ1 | 75479 | 61 | 1 | +6 | 1 | 75509 | | | |
| 91 | NESTED LOOPS | | 1 | 57 | | | | | | | |
| 92 | NESTED LOOPS OUTER | | 1 | 56 | | | | | | | |
| 93 | TABLE ACCESS FULL | SEG$ | 1 | 55 | | | | | | | |
| 94 | INDEX RANGE SCAN | I_USER2 | 1 | 1 | | | | | | | |
| 95 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | | | | | | | |
| 96 | INDEX UNIQUE SCAN | I_TS# | 1 | | | | | | | | |
| -> 97 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | 121 | +6 | 3345 | 3345 | | | |
| 98 | FIXED TABLE FULL | X$KTFBUE | 100K | 24 | | | | | | | |
| 99 | INDEX UNIQUE SCAN | I_FILE2 | 1 | | | | | | | | |
=================================================================================================================================================================
Elapsed: 00:00:00.16
SQL>10，相关的查询语句
-----生成html格式
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool 1.html
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'ACTIVE') from dual;
spool off
----生成text
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '&sqlid', type => 'TEXT') report FROM dual;
393
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
