Oracle 11g sql*monitor
发布时间：2020-12-12 16:30:57 所属栏目：百科 来源：网络整理
导读：在Oracle11g中，当SQL 并行执行时，会立即被实时监控到，或者当 SQL 单进程运行时，如果消耗超过 5 秒的 CPU 或 I/O 时间，它也会被监控到。监控数据被记录在 V$SQL_MONITOR 视图中,记录会保留一分钟,可以说是实时的。 SQL SET LONG 1000000 SQL SET LONGCHU
|
在Oracle11g中，当SQL 并行执行时，会立即被实时监控到，或者当 SQL 单进程运行时，如果消耗超过 5 秒的 CPU 或 I/O 时间，它也会被监控到。监控数据被记录在 V$SQL_MONITOR 视图中,记录会保留一分钟,可以说是实时的。 SQL> SET LONG 1000000 SQL> SET LONGCHUNKSIZE 1000000SQL> 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 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |