Author: Burleson

Many times SQL we experience long-running SQL statements and as a database administrator we want to know where a SQL statement is in its execution plan and which point is taking too long to execute.

Oracle has made the life of database administrators easy by making statistics collection automatic in 11g. Oracle 11g Real-Time SQL Monitoring allows you to monitor the performance of SQL statements while they are executing as well as see the breakdown of time and resources used for recently completed statements.

Oracle v$session_longops view:

In previous versions of Oracle database administrators had to deal with the cumbersome v$session_longops view to monitor long-running SQL. The v$session_longops view allows Oracle professional contract the amount of time that is used by long-running DLL and DML statements.

Oracle dbms_monitor package:

Oracle dbms_monitor package can be used to control additional tracing and statistics gathering. The dbms_monitor package can be used to initiate SQL tracing for sessions based on their service, module and action attributes. This makes the use of  dbms_application_info.set_session_longops necessary t o track long running SQL.

Long-running SQL statements:

For long-running batch jobs it is uncommon that SQL completes execution within the allotted timeframe.  These batch SQL streams are very common with

Monitoring long-running SQL statements has limited usefulness.  While a real-time SQL monitor reveals details on the steps and resource consumption within the execution, there is very little that the DBA can do to correct anything within in-flight SQL statement.

SQL Execution Plans:

Oracle provides a number of SQL monitoring tools and techniques. Most of these tools and techniques aim at capturing historical SQL execution details.  Tools such as AWR and STATSPACK capture execution plans and execution details from SQL

Oracle 11g Real-Time SQL Monitoring:

Oracle 11g supports v$sql_monitor and v$sql_plan_monitor managed by dbms_sqltune package.  Any parallelized statement like Oracle parallel query will be monitored in v$sql_monitor and v$sql_plan_monitor . These views allow the database administrator a window into the internals of an in-flight, executing SQL statement.

Enabling Oracle 11g Real-Time SQL Monitoring:

The real-time SQL monitoring is enabled by default when STATISTICS_LEVEL is set to to ALL or TYPICAL (the default value) and monitors statements that consume more than 5 seconds of CPU or IO time, as well as any parallel execution (PQ, PDML, PDDL). One can override the default actions by using the MONITOR or NO_MONITOR hint.

Cumulative database resource consumption:

The real-time SQL monitoring captures the cumulative database resource consumption that can help you reveal the specific execution plan steps where the SQL is consuming the most resources and spending the most time.

In-flight SQL statement:

The v$sql_monitor view displays the number of executions, the rows processed, as well as TEMP like disk sorting, hash joins and RAM consumption for the in-flight SQL statement supplementing the traditional execution information from v$sql.

Information about executing SQL:

The real-time SQL monitoring keeps detailed information about the executing SQL refreshed every second and kept for at least five minutes.

Oracle 11g Real-time SQL monitoring is automatic for below statements

v$sql_monitor execution statistics:

With Oracle 11g real-time SQL monitoring feature Oracle automatically collects the execution plans for long-running statements visible within v$sql_plan_monitor. We can see the v$sql_monitor view to display execution statistics.  The important execution statistics inside v$sql_monitor as as follows

Oracle 11g Tuning Pack:

You can monitor SQL statements with the Real Time SQL Monitoring feature within the extra cost Oracle 11g Tuning Pack.  Read Again!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips: Is your SYSMAN Account Locked??
   Important Concerns related to Oracle Compression!!
   Effective Tips for Fixing Online Redo Log Corruption!!
   DBA Tips for Oracle Regular Expressions!!