October 2004

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

October 2004
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: When EXPLAIN PLAN Gives the Wrong Plan

You’re invited to submit your Oracle-related questions to us at askdbspecialists10@dbspecialists.com. Include your name, telephone number and problem description. 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 Linda in Chicago, IL: We have a query that runs slowly in production for certain bind variable values. But if I run EXPLAIN PLAN for the query, I get back a very efficient execution plan. In fact, I can run the query from SQL*Plus against the production database and the results come back very quickly. But when the application runs the same query with the same bind values against the same database, it takes five minutes to run. What’s going on?

Roger Schrag of the Database Specialists team responds: Bind variable peeking, dynamic instance parameters, use of the cursor_sharing parameter, among other influences can cause two statements that appear identical to get different execution plans. An important thing to understand about the EXPLAIN PLAN command is that it shows you the execution plan Oracle might use if the statement were to be hard parsed right now—which could be different from the true execution plan currently in use for the very same statement.

The first time your application executed the SQL statement that is causing you trouble, Oracle did not find it in the library cache and so a hard parse was performed. Oracle peeked at the values of the bind variables, considered instance- and session-level parameter settings, looked at various other conditions, and chose what it believed to be the best execution plan.

The second time the application executed the SQL statement, Oracle performed a soft parse because the statement was already in the library cache. With a soft parse, Oracle uses the execution plan already in the library cache. Because the bind values (and perhaps other things as well) were different this second time around, the execution plan may or may not have been the best. But Oracle will stick with what it has got.

When you use the EXPLAIN PLAN command to see the execution plan for a SQL statement, Oracle appears to always do a hard parse, ignoring what may already be in the library cache. I’ve also heard that Oracle does not peek at the bind values in this case. So hopefully you can see how the execution plan generated by an EXPLAIN PLAN command could be quite different from the true execution plan being used by the database.

As for the query running quickly in SQL*Plus but slowly in the application, all it takes is one byte of difference—such as two blanks between the words ORDER and BY instead of one—in order for your SQL*Plus query to incur a hard parse. This could lead to the SQL*Plus query getting a different execution plan than the application, even though the two queries are (almost) identical.

Beginning in Oracle 9i it is possible to see the true execution plan in play for a given SQL statement, instead of trying to simulate it with the EXPLAIN PLAN command. The following SQL*Plus script will show you the execution plan associated with a given statement in the library cache:

 

COL id          FORMAT 999
COL parent_id   FORMAT 999 HEADING "PARENT"
COL operation   FORMAT a35 TRUNCATE
COL object_name FORMAT a30

SELECT     id, parent_id, LPAD (' ', LEVEL - 1) || operation || ' ' ||
           options operation, object_name
FROM       (
           SELECT id, parent_id, operation, options, object_name
           FROM   v$sql_plan
           WHERE  address = '&address'
           AND    hash_value = &hash_value
           AND    child_number = &child_number
           )
START WITH id = 0
CONNECT BY PRIOR id = parent_id;

To use this script, find the address, hash value, and child number of an entry in the library cache by querying v$sql. This script is very simple, and just intended to get you started. The v$sql_plan view has many other useful columns that you might want to add to the query.

Querying v$sql_plan to see the execution plan in play is a better way to go than using the EXPLAIN PLAN command or the AUTOTRACE feature of SQL*Plus. There are just too many variables out there that influence an execution plan, so you are better off where possible looking at the actual plan in use instead of using EXPLAIN PLAN and hoping it comes up with the same results.

Although this does not fix the problem of your query running slowly in production, hopefully it clears up the mystery of why the EXPLAIN PLAN command sometimes appears to give the “wrong” plan.

Free Associate Membership in IOUG

The International Oracle Users Group works to “keep you apprised of the latest information in the Oracle database world, and provide topnotch education, training, and networking opportunities for your professional growth and expertise in the marketplace.” The IOUG website offers a lot of resources that Oracle professionals will find valuable. An IOUG associate membership, which is free, gives you access to the following IOUG features and services: Database Specialists at Upcoming NoCOUG Conference

  • Associate e-News (monthly newsletter)
  • IOUG Tip of the Month
  • IOUG Discussion Forums
  • IOUG Calendar of Events
  • The Experts Exchange
  • IOUG Local User Group information

Learn more about associate membership in IOUG and sign up at https://www.ioug.org/secure/membership/index.cfm?requestedMemberType=AM.

 

Roger Schrag, founder of Database Specialists, will speak at the Fall Conference of the Northern California Oracle Users Group, sharing his knowledge of performance tuning and SQL optimization techniques. The educational event takes place on Thursday, November 4 at the Computer History Museum in Mountain View, CA. Solaris Tips and Tricks

Well-known Oracle performance tuning trainer Craig Shallahamer will be the keynote speaker at the event and will also give a presentation on Conquering Oracle Latch Contention. Author Peter Koletzke will present Introduction to Java—PL/SQL Developers Take Heart! and Virag Saksena will present Common Performance Monitoring Mistakes to Avoid. Plus, there’s lots more! (Click here for more information.)

 

Oracle running on Solaris is a very common environment. We’ve found a website that is handy for Oracle DBAs who occasionally find themselves in the position of performing Solaris administrative tasks. For instance, if you are getting ready to install Oracle on a Sun box, this site will help you with everything from verifying that the system is configured to run in 64 bit mode to securing the server.

Even if you don’t have Sun servers in your shop, you will probably still find some of the material helpful, such as the discussion of why RAID 1+0 is better than RAID 0+1. You can easily run through the alphabetical list on this site and find topics ranging from backups to filesystems, to SSH and much more.

Check it out at http://sysunconfig.net/unixtips/solaris.html.

 

Leave a Reply

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