【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

SQL> SET LINESIZE 1000
SET PAGESIZE 200
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO ON
SET FEEDBACK ON
SQL> SQL> SQL> SQL> SQL> SQL> 
SQL> select last_refresh_time, status, sid, sql_id, sql_plan_hash_value, 
 elapsed_time, cpu_time, fetches, buffer_gets, disk_reads
 from v$sql_monitor where sql_id=‘dmtsu5j0r3pfn‘;
  2    3  
LAST_REFR STATUS                     SID SQL_ID        SQL_PLAN_HASH_VALUE ELAPSED_TIME   CPU_TIME    FETCHES BUFFER_GETS DISK_READS
--------- ------------------- ---------- ------------- ------------------- ------------ ---------- ---------- ----------- ----------
14-JUL-16 DONE (ALL ROWS)             13 dmtsu5j0r3pfn          2473516258    260492225  248014296          1    90001200          0
14-JUL-16 DONE (ALL ROWS)            125 dmtsu5j0r3pfn          1112930440    689653997  656385214          1   219601830          0

2 rows selected.

SQL> select plan_line_id, plan_operation || ‘ ‘ || plan_options operation,
starts, output_rows, last_refresh_time,IO_INTERCONNECT_BYTES,PLAN_CPU_COST
from v$sql_plan_monitor where sql_id=‘dmtsu5j0r3pfn‘
order by plan_line_id;  2    3    4  

PLAN_LINE_ID OPERATION                                                         STARTS OUTPUT_ROWS LAST_REFR IO_INTERCONNECT_BYTES PLAN_CPU_COST
------------ ------------------------------------------------------------- ---------- ----------- --------- --------------------- -------------
           0 SELECT STATEMENT                                                       1           1 14-JUL-16                     0             0
           0 SELECT STATEMENT                                                       1           1 14-JUL-16                     0             0
           1 SORT AGGREGATE                                                         1           1 14-JUL-16                     0
           1 SORT AGGREGATE                                                         1           1 14-JUL-16                     0
           2 NESTED LOOPS                                                           1    18750000 14-JUL-16                     0    2.5143E+13
           2 NESTED LOOPS                                                           1    36000000 14-JUL-16                     0    3.4007E+12
           3 TABLE ACCESS FULL                                                      1      120000 14-JUL-16                     0      30259980
           3 TABLE ACCESS FULL                                                      1       75000 14-JUL-16                     0      65184623
           4 TABLE ACCESS FULL                                                 120000    36000000 14-JUL-16                     0      30260000
           4 TABLE ACCESS FULL                                                  75000    18750000 14-JUL-16                     0      67026793

10 rows selected.

SQL> 

版权声明：本文为博主原创文章，转载必须注明出处，本人保留一切相关权力！http://blog.csdn.net/lukeunique

参考

Database PL/SQL Packages and Types Reference
>140 DBMS_SQLTUNE

Oracle? Databaseリファレンス　11gリリース2 (11.2)　B56311-12
>V$SQL_PLAN_MONITOR

Oracle blogs
The Data Warehouse Insider

Oracle Database 11g: Real-Time SQL Monitoring
http://www.oracle.com/technetwork/database/manageability/sqlmonitor-084401.html

【SQL Performance】实时SQL监控功能(Real-Time SQL Monitoring)

标签：