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.
About The Specialist 