Package DBMS SQL MONITOR used for SQL Monitoring Oracle

Package DBMS SQL MONITOR used for SQL Monitoring performance issue

DBMS_SQL_MONITOR for real time monitoring the SQL Statements.
You will get the real time execution for the query.
For using DBMS_SQL_MONIOR, you need to the STATISTICS_LEVEL parameter to be set to ‘TYPICAL’ or ‘ALL’, and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to ‘DIAGNOSTIC+TUNING’.
It’s mainly available in enterprise edition.

Steps to use the DBMS_SQL_MONITOR package for monitoring the SQL Statements:

1. Start the process of Monitoring with following package

variable v_sql_mon_beg number;
begin
:v_sql_mon_beg := dbms_sql_monitor.begin_operation (dbop_name =>'QUERY1', dbop_eid => null , forced_tracking => 'Y');
END;
/
PRINT :v_sql_mon_beg
v_sql_mon
---------
1

2. Execute your SQL statement/load which need to be checked.

Select count(*) from ic.tran;

3. End the operation with following package:

Exec dbms_sql_monitor.end_operation (dbop_name => 'Query1' , dbop_eid => :v_sql_mon) ;

4. Fetch the monitoring report

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SPOOL E:\report1.html
SELECT DBMS_SQL_MONITOR.report_sql_monitor(
dbop_name => 'Query1',
type => 'HTML',
report_level => 'ALL') AS report
FROM dual;
SPOOL OFF

Advertisements

2 thoughts on “Package DBMS SQL MONITOR used for SQL Monitoring Oracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.