Query using Bind Variables is suddenly slow

Query using Bind Variables is suddenly slow [ID 387394.1]
		修改时间 08-OCT-2010     类型 PROBLEM     状态 MODERATED

In this Document
  Symptoms
  Cause
  Solution

---

Platforms: 1-914CU;

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.1 - Release: 9.0.1 to 10.2
Information in this document applies to any platform.
Checked for relevance on 07-SEP-2010

Symptoms

You are running on a database at 9.x or above, and have observed that sometimes, for no apparent reason, some SQL which has been running fine suddenly runs very poorly. You have made 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 behaviour 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, and 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, and it thus chose a different plan on each node.

There is a hidden parameter which controls this feature's behaviour, 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
tablex 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 Note 369427.1 "Case Study: The Mysterious Performance Drop"

N.B. Please also be aware of the following

i) (unpublished) Bug:5082178 (fixed in 10.2.0.4 and 11.x).

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 Note:338113.1  "Plans can change despite no stats being regathered"

iii) Bug: 5364143 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

select * from table(dbms_xplan.display_awr('&sql_id'))

...as documented in Note 362887.1 "A 10g Equivalant Process To The 9i Statspack Level 6 Execution Plan Output"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23757700/viewspace-693714/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23757700/viewspace-693714/