May 2005

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

May 2005
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: Super-Detailed Execution Plan Statistics

You’re invited to submit your Oracle-related questions to us at askdbspecialists05@dbspecialists.com. Include your name and telephone number in your email. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).

This month’s question comes to us from Rick in Kansas City, MO: I know that execution plans shown in a TKPROF report show the number of rows processed at each step of the plan. On some of my databases TKPROF shows even more information—reads and elapsed time—for each step of the execution plan. My 9.2.0.4 database shows this extra detail but my 9.2.0.6 database does not. Why would that be? How do I get TKPROF to display the most detailed execution plan statistics possible on all of my databases?

Roger Schrag of the Database Specialists team responds: TKPROF is a tool provided by Oracle that reads a trace file generated by the database and formats the information into a useful report. As far as execution plan statistics are concerned, TKPROF simply displays whatever information it finds in the trace file. Starting in Oracle 9i, if you enable SQL trace and set the statistics level for your session to ALL, Oracle will write additional statistics to the trace file that enable TKPROF to show how many consistent reads, physical reads, physical writes, and how much time elapsed for each step of a statement’s execution plan. Here is an example:

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  SORT GROUP BY NOSORT (cr=2004 r=656 w=0 time=9656825 us)
    990   TABLE ACCESS BY INDEX ROWID ORDERS (cr=2004 r=656 w=0 time=9647523 us)
   1001    NESTED LOOPS  (cr=23 r=6 w=0 time=13367 us)
     10     TABLE ACCESS BY INDEX ROWID CUSTOMERS (cr=7 r=3 w=0 time=822 us)
     10      INDEX RANGE SCAN CUSTOMERS_N1 (cr=2 r=1 w=0 time=424 us)
    990     INDEX RANGE SCAN ORDERS_N1 (cr=16 r=3 w=0 time=9810 us)

From this report you can see that the nested loops join between the CUSTOMERS table and the ORDERS_N1 index performed 23 consistent reads, 6 physical reads, 0 physical writes, and took 13,367 microseconds. Two of the consistent reads came from a range scan of CUSTOMERS_N1, 5 from accessing the CUSTOMERS table, and 16 from scanning ORDERS_N1. The query got slow when the ORDERS table was accessed. This step added 1981 consistent reads (2004 minus 23), 656 physical reads, and 9,634,156 microseconds of elapsed time.

The query took over 9.6 seconds to complete, and almost all of the time was spent accessing the ORDERS table. If you did not have the extended statistics and only looked at the row counts shown at the left, you could not have known this.

The extra execution plan statistics were added in Oracle 9i. The intention by Oracle had been that these statistics would only be collected when the statistics_level parameter was set to ALL. Since this parameter defaults to TYPICAL, the extra statistics were not supposed to be collected by default.

Many releases of Oracle 9i up through 9.2.0.4 have a bug that causes Oracle to collect these extra statistics when SQL trace is enabled, even if the statistics_level parameter is not set to ALL. This bug has been fixed in a subsequent patch set. Many people have discovered that when they applied the 9.2.0.6 patch set to their system, the execution plan statistics disappeared from TKPROF reports. This is due to the bug fix.

So, the upshot is this: If you are running Oracle 9i or 10g and you would like to trace a session and see the most detailed execution plan statistics possible, set the statistics level in the session being traced with the following command:

ALTER SESSION SET statistics_level = ALL;

Setting the statistics_level parameter to ALL does impose a fair amount of overhead. For this reason, you probably will not want to set this parameter to ALL at the instance level.

New Oracle Installation Guide: Oracle 10g on Solaris

The Database Specialists, Inc. step-by-step Oracle installation guides continue to be our most popular resources. Hot off the press, you won’t want to miss our latest resource: Installing and Configuring Oracle Database 10g on the Solaris Platform. In this paper, author Roger Schrag will walk you through the steps of installing Oracle Database 10g release 1 in a Sun Solaris SPARC environment. Everything you read in this paper is hands on, roll-up-your-sleeves-and-get-busy material for Oracle users who want to get an Oracle database up and running quickly without reading hundreds of pages of documentation and “readme” files.

These steps are meant to get you up and running as fast as possible, while leveraging best practices in order to set up a scalable, robust database environment that offers high performance. You’ll find it at http://dbspecialists.wpengine.com/presentations.html#oracle10gsolaris.

 

Code Tips Collection

The Oracle Technology Network has a section where you can read and share Oracle-related tips and techniques with other users. Their summary listing of “tips of the week” date back to 2002, and you can read the tips submitted and contact the authors directly with any questions. Here is a brief listing of some recent “tips of the week.” You’ll find them all at http://www.oracle.com/technology/oramag/code/tips2005/index.html.

Oracle FAQ Script Exchange

“These scripts are potentially dangerous…” That is the warning on the Orafaq.com script exchange page. Of course, you should always review, customize, and test anything that could affect your database. Keeping this in mind, you could spend hours poking around the Oracle FAQ site looking at the scripts and tools available. Here are a few of the sections you’ll find at http://www.orafaq.com/scripts/index.htm. Database Specialists Featured at Users Group Conference

  • General SQL Scripts
  • General PL/SQL Scripts
  • General DBA Scripts
  • Object Management Scripts
  • Space Management Scripts
  • Security Auditing Scripts
  • Database Performance Tuning Scripts
  • Backup and Recovery Scripts
  • Advanced Replication Scripts
  • UNIX Shell Scripts

 

Once again, we are excited to be participating at the Spring Conference of the Northern California Oracle Users Group (NoCOUG) on May 19. This will be an action-packed day with three Oracle-related educational tracks.

The Database Specialists crew will lead a session on a case study of upgrading to Oracle 10g. You can read about the case study at http://dbspecialists.wpengine.com/presentations.html#case_study_10g. To learn more about NoCOUG and its Spring Conference, visit http://www.nocoug.org/next.html.

 

  • Protect Objects from Deletion
  • Determine Depth of Foreign Key Constraints
  • Find Full Table Scans
  • Report Materialized View Log Entries
  • Convert Numbers into Words

Leave a Reply

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