oracle sql monitor report,Oracle 11g sql*monitor

在Oracle11g中，当sql 并行执行时，会立即被实时监控到，或者当 sql 单进程运行时，如果消耗超过 5 秒的 cpu 或 I/O 时间，它也会被监控到。监控数据被记录在 V$sql_MONITOR 视图中,记录会保留一分钟,可以说是实时的。

sql> SET LONG 1000000

sql> SET LONGCHUNKSIZE 1000000

sql> SET LINESIZE 1000

sql> SET PAGESIZE 0

sql> SET TRIM ON

sql> SET TRIMSPOOL ON

sql> SET ECHO OFF

sql> SET FeedBACK OFF

sql> SELECT DBMS_sqlTUNE.report_sql_monitor(sql_id => '0c4ms5ndp67x3',type => 'TEXT')

AS report FROM dual;

sql Monitoring Report

sql Text

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

delete from gg_ru_todo_task_all ta where exists (select * from gg_ru_todo_task t where ta.activity_ins_id = t.activity_ins_id and exists (select * from gg_ru_process_ins i where t.main_process_ins_id = i.main_process_ins_id and i.state = 2 a

Global Information

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

Status : DONE

Instance ID : 1

Session : LCAM_SYS (6657:9661)

sql ID : 0c4ms5ndp67x3

sql Execution ID : 16777217

Execution Started : 06/16/2017 02:01:28

First Refresh Time : 06/16/2017 02:01:32

Last Refresh Time : 06/16/2017 02:01:37

Duration : 9s

Service : SYS$USERS

Program : oracle@sccomtop15164 (J003)

PLsql Entry Ids (Object/Subprogram) : 831869,1

PLsql Current Ids (Object/Subprogram) : 831869,1

Global Stats

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

| Elapsed | cpu | IO | Buffer | Read | Read |

| Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes |

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

| 10 | 5.26 | 5.04 | 104K | 3561 | 625MB |

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

sql Plan Monitoring Details (Plan Hash Value=2825155903)

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

| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |

| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |

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

| 0 | DELETE STATEMENT | | | | | | 1 | | | | | | |

| 1 | DELETE | gg_RU_TODO_TASK_ALL | | | | | 1 | | | | | | |

| 2 | HASH JOIN RIGHT SEMI | | 511K | 39593 | | | 1 | | | | 175K | | |

| 3 | VIEW | VW_SQ_1 | 392K | 30872 | | | 1 | | | | | | |

| 4 | HASH JOIN RIGHT SEMI | | 392K | 30872 | 6 | +4 | 1 | 0 | | | 12M | | |

| 5 | TABLE ACCESS FULL | gg_RU_PROCESS_INS | 128K | 10769 | 8 | +1 | 1 | 126K | 1113 | 29MB | | 70.00 | cpu (1) |

| | | | | | | | | | | | | | db file scattered read (6) |

| 6 | TABLE ACCESS FULL | gg_RU_TODO_TASK | 515K | 17674 | 3 | +8 | 1 | 515K | 1215 | 506MB | | 30.00 | cpu (2) |

| | | | | | | | | | | | | | db file scattered read (1) |

| 7 | TABLE ACCESS FULL | gg_RU_TODO_TASK_ALL | 523K | 6059 | | | | | | | | | |

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

如果输出为报告：

set trimspool on

set trim on

set pages 0

set linesize 1000

set long 1000000

set longchunksize 1000000

spool d:/sqlmon_active.html

select dbms_sqltune.report_sql_monitor(type=>'active') from dual;

spool off