Starting version 11g, Oracle database allows to monitor the performance of SQL statements while they are running.
By default, SQL monitoring starts automatically when:
SQL statement runs parallel
or
SQL statement consumes at least 5 seconds of CPU or I/O time in a single execution.
There is an option to enable/disable SQL monitoring on a statement level.
After monitoring is initiated, the database adds an entry to the dynamic performance view V$SQL_MONITOR.
All you know, that V$SQL has a cumulative monitoring statistics over several executions.
Instead, one entry in V$SQL_MONITOR is dedicated to a single execution of a SQL statement.
To get more information about a real-time SQL monitoring please read
Oracle® Database Performance Tuning Guide - Instance Tuning Using Performance Views document:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/instance_tune.htm
Example:
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Aug 23 19:29:50 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc myuser.sometable
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
SUBJ VARCHAR2(100)
SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 130
SQL> select /*+ MONITOR */ * from myuser.sometable where id=1;
ID SUBJ
---------- ----------------------------------------------------------------------------------------------------
1 Nataly
SQL> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
---------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
---------------------------------------------------------------------------------------------------------------
select /*+ MONITOR */ * from myuser.sometable where id=1
---------------------------------------------------------------------------------------------------------------
Global Information
Status : DONE (ALL ROWS)
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------------------------------------
Instance ID : 1
Session ID : 140
SQL ID : 7d5h3kjwnyns4
SQL Execution ID : 16777217
Plan Hash Value : 1859298268
Execution Started : 08/23/2011 19:30:19
First Refresh Time : 08/23/2011 19:30:19
Last Refresh Time : 08/23/2011 19:30:19
---------------------------------------
| Elapsed | Other | Fetch | Buffer |
REPORT_SQL_MONITOR
---------------------------------------------------------------------------------------------------------------
| Time(s) | Waits(s) | Calls | Gets |
---------------------------------------
| 0.00 | 0.00 | 1 | 2 |
---------------------------------------
SQL Plan Monitoring Details
=================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Starts | Rows | Activity | Act. Detail |
REPORT_SQL_MONITOR
----------------------------------------------------------------------------------------------------------------------------------
| | | | (Estim) | | Active(s) | Active | | (Actual) | (percent) | (sample #)
==================================================================================================================================
| 0 | SELECT STATEMENT | | | 1 | | | 1 | | |
| 1 | TABLE ACCESS BY INDEX ROWID | SOMETABLE | 1 | 1 | | | 1 | | |
| 2 | INDEX UNIQUE SCAN | SOMETABLE_PK | 1 | 0 | 1 | +0 | 1 | 1 | |
==================================================================================================================================
REPORT_SQL_MONITOR
----------------------------------------------------------------------------------------------------------------------------------
SQL>
No comments:
Post a Comment