January 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle usersJanuary 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Detailed Execution Plan Statistics

You’re invited to submit your Oracle-related questions to us at askdbspecialists01@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Ron in Chicago, IL: The v$sql_plan view lets me see the exact execution plan being used by Oracle for a SQL statement in the shared SQL area. Is there a way to see detailed statistics for each step of the execution plan? I know I can get this information by turning on tracing and then using TKPROF, but it would be great if there were an easier way to get at detailed execution plan statistics

Ian Jones of the Database Specialists team responds: The v$sql_plan view was introduced in Oracle 9i release 1 (Oracle 9iR1). This view provides the execution plan in use and associated optimizer information (estimated cost, cardinality, etc) for SQL statements that are currently in the shared SQL area. There are many uses for this information. For example, Statspack snapshots with snapshot level six or greater capture the v$sql_plan information and the sprepsql.sql script reports an analysis of the execution plan(s) of a particular SQL statement over time. These tools make it easy to identify changes in a statement’s execution plan.

Oracle introduced a new view in Oracle 9iR2 called v$sql_plan_statistics_all. This view conveniently contains information that is separately available in three other views: v$sql_plan, v$sql_plan_statistics, and v$sql_workarea. The critical feature of v$sql_plan_statistics_all is that it contains the execution plan expectations, including the optimizer predicted costs and cardinalities, together with the actual execution statistics for each row source in the execution plan.

In order to collect information in v$sql_plan_statistics_all we need to have either set the statistics_level parameter to ALL or enabled tracing for our session. As you would expect, there is overhead associated with collecting this extra information. Therefore you should enable collection only when required.

The v$sql_plan_statistics_all view contains more than 50 columns (65 in Oracle 10g) of detailed information in four broad areas:

  • Execution plans.
  • Estimated statistics for the execution plans as predicted by the cost-based optimizer.
  • Actual statistics from the most recent execution.
  • Actual cumulative statistics from multiple executions.

You can find a listing of the columns and a description of each in the Oracle Database Reference manual in the documentation set.

Unless we are tracing a single statement, we typically identify the SQL statement of interest by querying the v$sql view. Then we query v$sql_plan_statistics_all using the address, hash_value and child_number that we found in v$sql. (In Oracle 10g we use sql_id instead of hash_value.)

Following is a sample query to display the execution plan, estimated cost, and actual resources used for a query identified by a specific address, hash_value, and child_number:

SELECT   LPAD (' ', DEPTH) || operation operation, options,
         object_owner, object_name, cardinality "EST ROWS",
         last_output_rows "ACTUAL ROWS", bytes "EST BYTES",
         cost "EST COST", executions, last_cr_buffer_gets,
         -- Uncomment the next line on Oracle 10g databases
         -- time "EST TIME (Secs)",
         last_elapsed_time / 100000 "ACTUAL TIME (Secs)",
         last_elapsed_time "ACTUAL TIME (Microsecs)",
         access_predicates, filter_predicates
FROM     v$sql_plan_statistics_all
WHERE    address = '&address'
-- Uncomment the next line on Oracle 10g databases
-- AND      sql_id = '&sql_id'
-- Comment out the next line on Oracle 10g databases
AND      hash_value = &hash_value
AND      child_number = &child_number

It is important to include the child_number column in the WHERE clause when querying v$sql_plan_statistics_all. Statements can have multiple “children” when the same statement is executed from different environments that prevent the sharing of a single execution plan. For example, if two different sessions execute the same statement but have different values for the optimizer_mode parameter, then two children will result.

The information collected in v$sql_plan_statistics_all can be used to research poorly performing SQL statements, determine root causes, and develop solutions. There are many ways to evaluate this information. A few examples are:

  • Steps in an execution plan where the cardinality column differs significantly from last_output_rows likely indicate that the optimizer statistics are out of date or that there is an underlying data skew which the optimizer is unaware of.
  • Execution plan steps can be sorted by the last_elapsed_time column to find the steps that are consuming the most time. Performance analysis resources could be focused on these steps.
  • Steps in an execution plan where the time column (Oracle 10g only) differs significantly from the last_elapsed_time column indicate situations where the optimizer made a poor estimate of how long an operation would take to perform. These steps could be causing sub-optimal performance and could be investigated further.

The v$sql_plan_statistics_all view, introduced in Oracle 9iR2, can be used to evaluate SQL statement performance at a detailed level without using tracing and TKPROF. This view provides yet another tool for optimizing SQL performance.

Database Specialists at RMOUG Training Days

Two members of the Database Specialists team will speak at the Rocky Mountain Oracle Users Group Training Days 2006. The event takes place in Denver, Colorado February 15-16, and typically draws over 900 attendees.

How Well Does Oracle 10g’s ASM Perform?

One of Oracle 10g’s many new features is Automatic Storage Management, or ASM. In a nutshell, ASM acts as a logical volume manager, allocating space on physical storage devices to hold database files. Companies looking to deploy new Oracle 10g systems or upgrade existing systems, need to decide whether to leverage ASM or store files on a filesystem or raw devices instead.

Bert Scalzo, author of TOAD Handbook, has run a series of benchmarks using Oracle 10g Release 2 running on Red Hat Enterprise Linux 4. In his tests, he measured Oracle performance using ASM as well as Red Hat’s logical volume manager. He based his conclusions on a few different TPC benchmarks plus additional tests he developed himself. You can read his writeup at http://www.linuxjournal.com/article/8539.

NoCOUG Winter Conference

You won’t want to miss the Winter Conference of the Northern California Oracle Users Group (NoCOUG). The educational event takes place on Tuesday, February 14 at the Oracle Conference Center in Redwood Shores, California.

Look for Database Specialists at the conference. It’s sure to be an information-packed Day full of networking and education. Following are some of the great topics and speakers already lined up for the conference:

  • Lessons Learned from a Very Large RAC Benchmark, by Hanan Hit, Transparency Software
  • Active Session History: A New Performance Monitoring Paradigm, by Kyle Hailey, Embarcadero Technologies
  • Performance Diagnosis Usage Model in Oracle Database 10g, by Prabhaker Gongloor, Oracle Corporation
  • Advanced Research Techniques, in Oracle by Tanel Põder, Integrid

Check the NoCOUG website at http://www.nocoug.org for additional information and to register.


Database Specialists Senior Staff Consultant Terry Sutton returns for his third year as a speaker at the conference, this time presenting: What’s Up with dbms_stats? He’ll provide detailed examples of options available with dbms_stats and explain how your choice of which options to use can dramatically affect results, both the accuracy of statistics and performance of the statistics gathering operation itself.

Database Specialists President Roger Schrag will give two presentations. In the first one, How Much Do Concurrent Updates Impact Query Performance in Oracle?, Roger will look at how to detect and measure performance degradation caused by concurrent activities. Roger will share additional real-life experience in his second presentation, entitled: An Oracle 10g Upgrade Case Study: Looking at System Performance Before and After the Upgrade. RMOUG Training Days include over 70 technical sessions with educational tracks such as:

  • Database Administration
  • Data Warehousing
  • Technology Management
  • Application Design
  • Network Administration

Leave a Reply

Your email address will not be published. Required fields are marked *