可以使用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语句，这些信息将会在数据库重启后清除。
