Oracle11g Tips by Burleson Consulting
Oracle 11g Real Time SQL
January 8, 2008
This is a work in
progress excerpt from
the book "Oracle
11g New Features" by Rampant TechPress.
In Oracle 11g,
we see a replacement for the cumbersome
v$session_longops view to monitor long-running SQL. In Oracle
10g, the dbms_monitor package can be used to initiate SQL
tracing for sessions based on their service, module and action
attributes, making the use of
dbms_application_info.set_session_longops necessary to track
long running SQL. Statistics collection has now made automatic
Inside reactive SQL Monitoring
Long-running batch jobs are common
to the Oracle DBA who sits for hours hoping that the SQL completes
execution within the allotted timeframe. These batch SQL
streams are very common with:
- Reorganizing a zillion row table can take hours, even in
- Rollups and Aggregations
- The SQL to pre-compute aggregate data and materialized view
can run for many hours.
- Data loads
- ETL database often must process feeds of millions of rows
within a single SQL statement.
- Many shops clean-out stale
data and these DML statements can run a long time.
One of the problems with monitoring
long-running SQL statements was the 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 a in-flight SQL statement.
While Oracle has a wealth of SQL
monitoring tools and techniques, most of these are aimed at
capturing historical SQL execution details. Tools such as AWR
and STATSPACK capture execution plans and execution details from SQL.
This is perfect for holistic proactive SQL tuning.
But what about reactive SQL tuning?
Reactive SQL tuning has not been a priority because there is little
that the DBA can do to assist an in-flight SQL statements
performance. Oracle 11g has introduced a new utility called
v$sql_monitor and v$sql_plan_monitor, managed via the
dbms_sqltune package. This view allows the DBA a window
into the internals of an in-flight, executing SQL statement.
While real-time SQL monitoring is
not useful for fast transactions, SQL that is included in
long-running batch jobs (data loads, ETL, CTAS) is perfect for
monitoring with v$sql_monitor.
This new SQL monitoring is enabled
by default for all SQL statements that consume more than 5 seconds
of resources (I/O or CPU) and all SQL that is running in parallel
(full scan operations). Oracle claims that the real-time SQL
monitoring captures cumulative database resource consumption
data. This will be useful because it will reveal the specific
execution plan steps where the SQL is consuming the most resources
and spending the most time.
Similar to the extended execution
plan details on 10g, the v$sql_monitor view displays the
number of executions, the rows processed, as well as TEMP (disk
sorting, hash joins) and RAM consumption for the in-flight SQL
statement, supplementing the traditional execution information from
Like the Active Session History
(ASH) has details that are rolled into AWR, the real-time SQL
monitoring keeps super-detailed information about the executing SQL.
This information is
refreshed every second and kept for at least five minutes.
Starting in 11g, SQL monitoring is automatic
for these statements:
Parallel SQL - Any parallelized
statement (i.e. Oracle parallel query) will be monitored in
v$sql_monitor and v$sql_plan_monitor.
High resource consumers Any SQL
that consumes more than five seconds of I/O time of CPU time is
Oracle 11g will automatically collect the
execution plans for these long-running statements (which are visible
within v$sql_plan_monitor), and we see the v$sql_monitor
view to display execution statistics. The important execution
statistics inside v$sql_monitor include:
Within the extra cost 11g Tuning Pack, you can
monitor SQL statements with the Real Time SQL Monitoring feature.
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.