Query using Bind Variables is suddenly slow
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 10.2.0.4 - Release: 9.0.1 to 10.2
Information in this document applies to any platform.
Symptoms
9.x or above database is running and sometimes, for no apparent reason, some SQL which has been running fine suddenly runs very poorly. There are no changes to the data, the SQL, or the statistics for the objects involved.
On further examination of the SQL, it can be seen that it is using bind variables.
Cause
One reason for this behavior. may be explained by the use of a feature introduced in 9.x called bind
peeking.
With this feature, the query optimizer peeks at the values of user-defined bind variables on the first
invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE
clause condition, based on this value just as if a literal had been used instead of a bind variable.
On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on
the standard cursor-sharing criteria, even if subsequent invocations use different bind values.
Thus, if the first set of bind values that happen to be used when the cursor is first hard-parsed are not
representative, then the plan may be inappropriate for subsequent executions.
The Oracle 10.2 Database Performance Tuning Guide manual Chapter 13 "The Query Optimizer" says the following about peeking bind variables :-
"When bind variables are used in a statement, it is assumed that cursor sharing is intended and that
different invocations are supposed to use the same execution plan. If different invocations of the
cursor would significantly benefit from different execution plans, then bind variables may have been
used inappropriately in the SQL statement."
Bind peeking has been known to cause a different execution plan to be used on different nodes of a RAC cluster because each node has its own Shared Pool. Despite the same SQL, data, and statistics, the first time a cursor was hard parsed on each node a different set of bind values was presented to the optimizer, choosing a different plan on each node.
There is a hidden parameter which controls this feature's behavior, whose default value is TRUE.
Although a stable plan can be achieved by setting the parameter off, it must be realized that this stable
plan is not necessarily the optimum plan for all bind values. Consider the following simple example where table has 10,000 rows and col1 has an index.
SELECT
FROM tablex
WHERE col1 BETWEEN :bind1 AND :bind2;
If this SQL is used, for example, with values 123 and 124 in order to pick out 2 rows from the
10,000 rows in the table, then using an index would be the obvious choice.
However, if the same SQL is used with bind values 123 and 9999, then we would be getting the vast majority of the rows and a full table scan would be more appropriate. But the optimizer cannot now know this, and does not change the plan accordingly.
Solution
In this sort of situation, it would perhaps be a good idea to modify the application and have two separate modules/sections each with the above SQL, but with a variation (perhaps modified with a hint) that will result in the desired plan. The appropriate module could then be invoked programmatically. An example might be a situation where you use essentially the same SQL to query the pay for one employee or all 10,000 employees. The query for one employee should use indexes, and the query for all employees should do a full table scan.
N.B. The cursor will be moved out of the Shared Pool and therefore require a hard parse on subsequent
invocation in a number of circumstances, such as :-
1) database shutdown/restart
2) cursor not in use by any session, and aged out by LRU algorithm
3) change to the stats associated with any referenced object (eg following a gather stats job)
4) change to the structure of any referenced object (eg alter table)
5) Granting/revoking privileges on a referenced object
It will NOT get moved out by flushing the Shared Pool if it is pinned (ie in use)
CONCLUSION
==========
It is desirable to share cursors, particularly in an OLTP environment, for all the good reasons outlined in
Note 62143.1 Understanding/Tuning the Shared Pool in Oracle7, 8, 8i
Thus coding bind variables or perhaps using CURSOR_SHARING values of SIMILAR or FORCE is an appropriate path to follow, but it must be realized that having bind peeking may result in unpredictable execution plans dependent on the first set of bind values presented to the optimizer on hard parse.
Tuning SQL with hints and coding your application to allow the use of the appropriate "version" of the
SQL or using literal values is the preferred method of dealing with SQL having changing
execution plans due to bind peeking, but if necessary this feature can also be disabled.
To set this feature off for the whole database :-
a) set _OPTIM_PEEK_USER_BINDS=FALSE in the spfile/init.ora
or just for the session :-
b) use alter session set "_OPTIM_PEEK_USER_BINDS"=FALSE;
For a good case study where this was a factor, please see following note:
Note 369427.1 "Case Study: The Mysterious Performance Drop"
Details:
In some situations, bind peeking can occur when it should not-- eg: Bind peeking can occur for user binds even if "_optim_peek_user_binds" is set to FALSE.
This can cause binds to be marked "unsafe" leading to cursors not being shared when they should be.
This fix is notable as plan changes could occur if statements suffering this problem execute in a release with this fix as the CBO will no longer have peeked data to use when determining an execution plan.
ii) (unpublished) Bug: 4567767 Abstract: UNEXPLAINED PLAN CHANGES CAN OCCUR WITHOUT STATS REGATHER (Fixed in 10.2.0.4 and 11.x)
Details:
It is possible for queries' execution plans to change without any modification in statistics or optimizer environment. Usually it is interpreted as the plans changed "out of the blue". The reason for the change is that density is being reevaluated as 1/ndv instead of taking the statistic stored in the data dictionary when the table is reloaded to the row cache for whatever reason, like a shared pool flush.
It is not easy to catch in the act but can be seen on a 10053 trace file when the query is hardparsed before and after the table is reloaded to the row cache.
Before:
Column: ISOCODE Col#: 7 Table: PL_X_NP Alias: X
NDV: 1344 NULLS: 0 DENS: 1.5152e-02 NO HISTOGRAM: #BKT: 1 #VAL: 2
After:
Column: ISOCODE Col#: 7 Table: PL_X_NP Alias: X
NDV: 1344 NULLS: 0 DENS: 7.4405e-04 NO HISTOGRAM: #BKT: 1 #VAL: 2
To turn this fix off (in 11g and 10gR2):
Set "_fix_control"='4567767:off'
Workaround
Set event 10139 :-
alter session set events '10139 trace name context forever';
or
event="10139 trace name context forever"
This bug is described in following note:
Note:338113.1 "Plans can change despite no stats being regathered"
iii) Document: 5364143.8 Abstract: UNPREDICTABLE CHANGE IN QUERY OPTIMIZER PLAN (Fixed in 10.2.0.4 and 11.x)
Details:
It is possible for queries' execution plans to change without any modification in statistics or optimizer environment. Usually, its interpreted like the plans changed "out of the blue". The reason for the change is that the cursor was taken out of the library cache for whatever reason (flush, Memory Pressure, DDLs,etc) and upon reload sometimes bind peeking is skipped for the cursor.
Note: Disabling Bind Peeking DOES NOT workaround the issue.
SUMMARY
=======
In summary, the bind peeking feature can give the optimizer better information and allow a more appropriate execution plan if the bind values presented on hard parsing the cursor are representative. However, if there is a possibility they are NOT representative, then a plan which is sub-optimal for subsequent invocations may result. Under these circumstances, one of the above strategies should be considered. Ultimately, in order to make the most appropriate decision, a good knowledge of both the application and the data is required.
FOOTNOTE
========
Once a good plan is in operation for a key SQL statement, it always good practice to do the following: :-
a) for a 9.2 database capture, the statistics for the objects involved using DBMS_STATS.EXPORT_TABLE_STATS.
(See Metalink Note 117203.1 "How to Use DBMS_STATS to Move Statistics to a Different Database" for more information on how to do this).
These statistics could then be imported in an "emergency" to restore use of a good plan while a rogue plan is investigated. (NB take a copy of the "bad" stats before importing, of course).
In 10g, whenever optimizer statistics are modified using the DBMS_STATS package, old versions of the statistics are saved automatically for future restoration, so the above is not necessary.
See Note 452011.1 "Restoring table statistics in 10G onwards".
b) capture the good execution plan so it can be used as a baseline reference in the event that an
undesired change occurs.
For 9.2 use Note 260942.1: "Display Execution plans from Statements in V$SQL_PLAN".
In 10g, the view DBA_HIST_SQL_PLAN has historical information with respect to execution plans. Use the following sql:
select * from table(dbms_xplan.display_awr('&sql_id'))
...as documented in following:
Note 362887.1 "A 10g Equivalant Process To The 9i Statspack Level 6 Execution Plan Output"
