SQL Monitoring in Oracle 11g

Starting with 11g, Oracle introduced a new tool to add to the performance optimizer’s toolkit called “Real Time SQL Monitoring”.  Real Time SQL Monitoring, or just “SQL Monitoring”, enables you to measure the performance of SQL statements while they are executing.  SQL Monitoring is enabled any time a statement runs in parallel or when it consumes more than 5 seconds of CPU or IO time.
Oracle populates V$SQL_MONITOR and V$SQL_PLAN_MONITOR views with performance characteristics of running SQL statements, and stores these as long as the SQL_ID remains in the library cache.  You can use these two views along with V$SQL, V$SQL_PLAN, V$ACTIVE_SESSION_HISTORY, V$SESSION, and V$SESSION_LONGPS to gather more detail about the SQL statement being run.
V$SQL_MONITOR tracks key performance statistics for each qualifying SQL statement (a subset of what’s in V$SQL), and V$SQL_PLAN_MONITOR provides statistics for each execution path step for the statement.  This allows tools such as Enterprise Manager Grid Control to provide a graphical, intuitive representation of the time taken at each step in the path, as well as IO consumption, CPU consumption, etc.

 

Contrary to V$SQL, V$SQL_MONITOR tracks performance statistics for each execution of a SQL statement, not a summary of accumulated statistcis.

 

You can use DBMS_SQLTUNE.REPORT_SQL_MONITOR, Grid Control, or EM Database Control to report on V$SQL_MONITOR and V$SQL_PLAN_MONITOR.

 

Using Enterprise Manager

Enterprise Manager Grid Control or Database Control has a SQL Monitoring link in the Performance tab that can be used to show SQL Monitoring details.  See below:

 

 

When you click on it, it’ll show SQL Monitoring statistics for anything running over 5 seconds or running in parallel:

 

 

You can see the performance-related timings and details from the screen, and if you click on the SQL_ID link, it will show various details of the SQL statement’s performance profile.   

 

 

 

 

 

 

As you can see, there is a tremendous amount of information contained in various tabs for each SQL_ID monitored.  Also, note the graphical representation of time-per-step – this should provide the SQL optimizer a very good idea of what to look for when optimizing.

Let’s take a look at a slightly more complicated query that does a handful of joins:

 

 

From the above and below, we can clearly see the step that’s taking the most time.

 

 

You can also click on “View Report” to view the SQL Monitoring report.  The output looks like the below (I’ve got several screen shots, but this all shows up on one page in EM):

 

 

 

 

 

The screen shot below shows the SQL Monitoring display after several long-running jobs have run:

 

 

Using DBMS_SQLTUNE.REPORT_SQL_MONITOR

What if you don’t have EM Grid Control or DB Control?  Oracle provides a means to report on SQL Monitoring details using DBMS_SQLTUNE.REPORT_SQL_MONITOR.  Below, I’ll show this after an execution of a long-running piece of SQL:

 

<< output deleted >>

 

 

 

As you can see from the above, DBMS_SQLTUNE.REPORT_SQL_MONITOR picked up the last SQL statement that ran and qualified for SQL monitoring, since we didn’t provide a SQL_ID.

 

Run SQL Monitor Report for “Last SQL”

Let’s do a shorter-running SQL statement so other SQL statements don’t enter the queue first:

 

 

As you can see, there is a great deal of information in the report, but it’s missing a nice graphical time representation of how long each step took.

You’ll also likely think to yourself – how can I get a SQL report for a given SQL ID?  The next section goes into this.

Run SQL Monitor Report for a given SQL_ID

If you know the SQL_ID ahead of time, you can enter it as a parameter to the call to DBMS_SQLTUNE.REPORT_SQL_MONITOR:

460

Let’s run a SQL statement, get the SQL_ID in question (which we’ll get from V$SESSION.PREV_SQL_ID)

 

 

 

Run SQL Monitor Report and generate HTML Report

You can also generate an HTML file output.  This is helpful because it shows a graphical representation of how much time each step in the execution plan spent.

 

 

 

 

Some handy SQL Monitor scripts