Database News You Can Use
A monthly newsletter for Oracle users
You’re invited to submit your Oracle-related questions to us at firstname.lastname@example.org. 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 22.214.171.124 database shows this extra detail but my 126.96.36.199 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 188.8.131.52 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 184.108.40.206 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.
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.
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.
“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
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.