The SQL monitor is introduced in Oracle 11g database is a very effective way to monitor long running SQL statements. By default, SQL monitoring automatically starts when a SQL statement runs parallel, or when it has consumed at least 5 seconds of CPU or I/O time in a single execution. The /*+MONITOR*/ hint enables monitoring for statements and the DBMS_SQLTUNE.REPORT_SQL_MONITOR package creates a monitoring report for a specific SQL statement. It's interactive with the Enterprise Manager and statistics performance metrics are capture (elapsed time, CPU time, I/O time, reads/writes related, etc.
Below are few examples:
Example1 /*+ MONITOR */ hint
------------------------------------------------
SQLPLUS >
select /*+ MONITOR */ emp_nbr from DBALAB.EMPLOYEE where emp_nbr is not null and rownum <=200;
set long 1000000
set longchunksize 1000000
set linesize 600
set pagesize 0
set trim on
set trimspool on
select dbms_sqltune.report_sql_monitor (session_id=>sys_context('userenv','sid'), report_level=>'ALL') as report from dual;
SQL Monitoring Report
SQL Text
------------------------------
select /*+ MONITOR */ emp_nbr from DBALAB.EMPLOYEE where emp_nbr is not null and rownum <=200
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : SYS (689:10593)
SQL ID : 9hhhwr7ns2y9y
SQL Execution ID : 16777216
Execution Started : 05/04/2012 15:02:55
First Refresh Time : 05/04/2012 15:02:55
Last Refresh Time : 05/04/2012 15:02:55
Duration : .425631s
Module/Action : sqlplus@vmsrv01.la.com (TNS V1-V3)/-
Service : SYS$USERS
Program : sqlplus@vmsrv01.la.com (TNS V1-V3)
Fetch Calls : 15
Global Stats
======================================================================================
| Elapsed | Cpu | IO | Cluster | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
======================================================================================
| 0.43 | 0.00 | 0.41 | 0.00 | 0.01 | 15 | 23 | 3 | 112KB |
======================================================================================
SQL Plan Monitoring Details (Plan Hash Value=238925293)
==========================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
==========================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 200 | | | | |
| 1 | COUNT STOPKEY | | | | 1 | +0 | 1 | 200 | | | | |
| 2 | TABLE ACCESS FULL | DBALAB.EMPLOYEE | 200 | 4 | 2 | +0 | 1 | 200 | 3 | 112KB | 100.00 | db file scattered read (1) |
==========================================================================================================================================================
Example2: DBMS_SQLTUNE.REPORT_SQL_MONITOR
-----------------------------------------------------------------
select sid, sql_id
from v$session
where status = 'ACTIVE';
SID SQL_ID
-------- -------------
232 7fnqw0qww759d
343
set long 1000000
set longchunksize 1000000
set linesize 600
set pagesize 0
set trim on
set trimspool on
select dbms_sqltune.report_sql_monitor(sql_id =>'7fnqw0qww759d', type=>'TEXT') as report from dual;
SQL Monitoring Report
SET LINESIZE 300
COLUMN sql_text FORMAT A100
SELECT sql_id, status, sql_text FROM v$sql_monitor;
acc988uzvjmmt DONE delete from WRH$_SYSMETRIC_HISTORY tab where (:beg_snap <= tab.snap_id and tab.snap_id <= :e
nd_snap and dbid = :dbid) and not exists (select 1 from WRM$_BASELINE b
where (tab.dbid = b.dbid) and (tab.snap_id >= b.start_snap_id) and
(tab.snap_id <= b.end_snap_id))
7fnqw0qww759d DONE begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
SET LONG 1000000
SET FEEDBACK OFF
SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'7fnqw0qww759d',type=> 'TEXT')
2 AS report FROM dual;
SQL Monitoring Report
SQL Text
------------------------------
begin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
Global Information
------------------------------
Status : DONE
Instance ID : 1
Session : (1600:2927)
SQL ID : 05s9358mm6vrr
SQL Execution ID : 16777218
Execution Started : 04/30/2012 21:37:39
First Refresh Time : 04/30/2012 21:37:43
Last Refresh Time : 04/30/2012 21:37:44
Duration : 5s
Module/Action : MMON_SLAVE/0000010 FINISHED190
Service : SYS$BACKGROUND
Program : oracle@vmsrv01.la.com (M000)
Global Stats
==========================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
==========================================================================================================================
| 4.22 | 2.16 | 1.19 | 0.01 | 0.03 | 0.07 | 0.77 | 204K | 1682 | 24MB | 4 | 32768 |
==========================================================================================================================
SQL>
No comments:
Post a Comment