SQL性能的度量 - 利用Hints和dbms_sqltune进行SQL监控

可以使用Hints对某个SQL开启实时监控

select /*+monitor*/ count(*) from scott.emp where sal>2000;

使用dbms_sqltune查看监控信息

set long 9999999

set longchunksize 9999999

set linesize 200

select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR

---------------------------------------------------------------------------------------------------------------------------

SQL Monitoring Report

SQL Text

------------------------------

select /*+monitor*/ count(*) from scott.emp where sal>2000

Global Information

------------------------------

Status : DONE (ALL ROWS)

Instance ID : 1

Session : SYS (9:15084)

SQL ID : 6k8qh0ubz4mnw

SQL Execution ID : 16777216

Execution Started : 05/27/2017 23:10:31

First Refresh Time : 05/27/2017 23:10:31

Last Refresh Time : 05/27/2017 23:10:31

Duration : .0003s

Module/Action : sqlplus.exe/-

Service : SYS$USERS

Program : sqlplus.exe

Fetch Calls : 1

Global Stats

==================================================

| Elapsed | IO | Other | Fetch | Buffer |

| Time(s) | Waits(s) | Waits(s) | Calls | Gets |

==================================================

| 0.00 | 0.00 | 0.00 | 1 | 7 |

==================================================

SQL Plan Monitoring Details (Plan Hash Value=2083865914)

==========================================================================================================================

| 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 | EMP | 10 | 3 | 1 | +0 | 1 | 6 | | |

==========================================================================================================================

监控信息始终指向最后一次监控的SQL语句，这些信息将会在数据库重启后清除。