Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 


Oracle 11g
Real Time SQL Monitoring tips

Oracle11g Tips by Burleson Consulting

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 in 11g.

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:

  • Reorgs - Reorganizing a zillion row table can take hours, even in parallel.
     
  • 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.
     
  • Archiving/purging - 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 v$sql.

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 automatically monitored.

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:

v$sql_monitor. elapsed_time
v$sql_monitor. cpu_time
v$sql_monitor. fetches
v$sql_monitor. buffer_gets
v$sql_monitor. disk_reads
v$sql_monitor. direct_writes
v$sql_monitor. application_wait_time
v$sql_monitor. concurrency_wait_time
v$sql_monitor. cluster_wait_time
v$sql_monitor. user_io_wait_time
v$sql_monitor. plsql_exec_time
v$sql_monitor. java_exec_time

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational