绑定变量介绍

Comments

5 responses to “绑定变量介绍”

1. 

   admin

   August 20, 2010

   Bind Peeking By Example [ID 430208.1]

   Applies to:

   Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.3
   Information in this document applies to any platform.

   Goal

   This document demonstrates the bind peeking feature introduced in 9i and 10g.

   Solution

   The purpose of this note is to demonstrate the concept of bind peeking.

   For more details about the feature you can refer back to
   Note 387394.1 Query using Bind Variables is suddenly slow.

   Feature was introduced in Oracle 9i and 10g.

   From The Oracle Database Performance Tuning Guides for both 9i and 10g
   Chapter 13 “The Query Optimizer”
   Section 13.4.1.2 Peeking of User-Defined Bind Variables

   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, as well as if literals have
   been used instead of bind variables. 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.

   Note in Oracle 10g Release 1 Documentation, this is under Chapter 14

   Lets see how this works ===>

   Let us prepare test environment first.
   Please make sure you read this script thoroughly before applying it.

   Conn /as sysdba
   /

   Drop tablespace BP_Tablespace
   Including Contents and Datafiles
   /

   Create Tablespace BP_Tablespace
   Blocksize 8K
   Datafile ‘/tmp/bp_tbs.dbf’ Size 25M Reuse
   Extent Management Local
   Uniform Size 1M
   Segment Space Management Manual
   /

   Drop User BP Cascade
   /

   Create User BP Identified By BP
   Default Tablespace BP_Tablespace
   Quota Unlimited On BP_Tablespace
   /

   Grant DBA To BP
   /

   Conn BP/BP
   /

   — My db_block_size is 8k
   SQL> Show Parameter db_block_size

   NAME TYPE VALUE
   ————— ———– —–
   db_block_size integer 819

   — Set Optimizer mode to choose
   Alter Session Set Optimizer_Mode=’ALL_ROWS’
   /

   — Enabling bind peeking feature
   Alter Session Set “_optim_peek_user_binds” = True
   /

   — Testing with CPU costing off
   Alter Session Set “_Optimizer_Cost_Model”=’io’
   /

   — Optimizer Features must be set to 9.2.x or higher
   Alter Session Set Optimizer_Features_Enable = ‘10.2.0.3’
   /

   — Set Cursor Sharing to Exact
   Alter Session Set Cursor_Sharing=Exact;

   Drop Table PreferredMeals
   /

   Create Table PreferredMeals
   (EmpID Number(5),
   Meal Varchar2(10)
   )
   /

   Create table with skewed data to be able to generate two different execution
   plans based on the predicate values.
   In the following table, we have a survey that shows 74998 people prefer to eat mansaf
   while only one person likes Pasta and another person favours Kabab over mansaf and pasta. For sure the survey results were collected in Jordan; not in Italy nor Turkey 🙂
   For information about Mansaf see http://en.wikipedia.org/wiki/Mansaf

   Begin
   For i in 1..74998 Loop
   Insert Into PreferredMeals Values (i, ‘Mansaf’);
   End Loop;
   Insert Into PreferredMeals Values (74999, ‘Kabab’);
   Insert Into PreferredMeals Values(75000, ‘Pasta’);
   End;
   .
   /

   Drop Index PrefMl_Indx
   /

   Create Index PrefMl_Indx
   On
   PreferredMeals(Meal)
   /

   Gather statistics and let Oracle decide which columns requires histograms and how many buckets are required.

   Exec DBMS_Stats.Gather_Table_Stats(OwnName => ‘BP’, TabName => ‘PreferredMeals’, Method_Opt => ‘For All Indexed Columns Size Auto’, Cascade => True)
   /

   Select Count(*)
   From PreferredMeals
   Where Meal = ‘Mansaf’
   /

   Examine the execution plan.
   Do not use the Set AutoTrace EXP neither the Explan commands, as we need to see the actual plan executed by accessing the memory directly.
   This can be achieved either by using the new package DBMS_XPlan and/or accessing V$SQLArea and V$SQL_Plan.

   Select * From Table(DBMS_XPlan.Display_Cursor);

   You will see either Index Fast Full Scan or Table Full Access (this depends on many other factors that are out of the realm of this note).

   Now lets see the plan for the single value:

   Select Count(*)
   From PreferredMeals
   Where Meal = ‘Kabab’
   /

   Select * From Table(DBMS_XPlan.Display_Cursor);

   We see Index Range Scan, as the optimizer knows that there is one row satisfying the condition.
   So the optimizer chooses Index Range access path which is cheaper.

   Now  define a bind variable and assign different values to it and examine the execution plans:

   Var BP Varchar2(10)
   Exec :BP := ‘Mansaf’

   Select Count(*)
   From PreferredMeals
   Where Meal = :BP
   /

   Examine the plan.

   Select * From Table(DBMS_XPlan.Display_Cursor);

   Since cursor sharing is Exact the query reparsed, and new plan is generated which has Index Fast Full Scan or FTS.

   Assign another value for the bind variable:
   Exec :BP := ‘Pasta’

   Select Count(*)
   From PreferredMeals
   Where Meal = :BP
   /

   Examine the plan again.

   Select * From Table(DBMS_XPlan.Display_Cursor); 

    

   The  same plan as above is revealed which is not the optimal plan for this value.

   Ok! Now,  invalidate the plans in shared pool and try the test again.

   To invalidate any cursor,  one of the followings  can be done:

   1. Issue the command Alter System Flush Shared_Pool;
   2. Delete or change the cursor underlying objects statistics
   3. Grant and/or revoke privileges on any of the cursor underlying objects
   4. Alter cursor underlying objects
   5. Bounce the instance

   Alternatively we can either add dummy hint to the same query or change the query
   text(such as replace small letters with capitals) to enforce hard parsing it.

   Select /*+ Hard parse me please */ Count(*)
   From PreferredMeals
   Where Meal = :BP
   /

   Examine the plan.

   Select * From Table(DBMS_XPlan.Display_Cursor); 

   Oracle has peeked on the value of :BP and chosen the optimal plan once again.

   Conclusion ===>
   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 works for a specific set of clients, not all clients.

   Note:
   You get all plans generated by the optimizer for the same SQL statement (i.e. with same SQL_ID) by using the function Display_Cursor in DBMS_Xplan package as follows:
   SQL> select * from table(dbms_xplan.display_cursor(‘<sql id>’,null,’ADVANCED’);

    

   Reply
2. 

   admin

   August 20, 2010

   Query using Bind Variables is suddenly slow

   Applies to:

   Oracle Server – Enterprise Edition – Version: 9.0.1 to 10.2
   This problem can occur on any platform.

   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 <whatever>
   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       <—— From Dict.
   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      <—— 1 / 1344
   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 281793.1 “Oracle Database 10g stats history using dbms_stats.restore_table_stats”.

   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”

    

   Reply
3. 

   admin

   August 20, 2010

   Unsafe Literals or Peeked Bind Variables

   Applies to:

   Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.2 – Release: 9.2 to 11.2
   Information in this document applies to any platform.
   ALL PRODUCT ACCESS

   Purpose

   This document explains regarding safe and unsafe literals and binds and their effect on query optimization.

   Scope and Application

   This document is intended for Support Analysts and customers

   Unsafe Literals or Peeked Bind Variables

   Introduction

   When bind variables are peeked. The parse engine makes a decision as to the ‘safety’ of these peeked values for creating plans based upon whether it is felt that different values could produce different plans.

   The usual (but not the only) reason for such different plans is the use of CURSOR_SHARING=SIMILAR and the presence of histogram column statistics on the column that the bind is being compared with when using the Cost Based Optimizer (CBO). If there are histograms on the column, then the bind value may be deemed to be ‘unsafe’ because there is the potential chance that the different values could produce a different explain plan and the selection of a single plan for all values may not be ‘safe’ in terms of performance. If the bind is deemed ‘unsafe’ then multiple children are created for each set of different bound values so that different plans can be associated with them. This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.

   CURSOR_SHARING=SIMILAR

   With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

   Is the operator NEITHER of   ‘=’ or ‘!=’
   OR
   Are there Column Histograms present on the column.

   If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, <, >=, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see:

   Note:261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

   With histogram stats on an equality predicate, this can cause severe problems (in terms of sharing) if there is, for example, a histogram on a main key column.eg:

   select … from orders where orderid=’literal’;

   If  there is a histogram on “orderid” then the bind will likely get marked unsafe and a new child will be produced for every single value of ‘literal’. The SQL would only be shared if the value of ‘literal’ matches exactly to a value used already.

   Remember that if literals are converted to binds due to CURSOR_SHARING then they are subject to this checking, though unsafe binds are really only an issue if CURSOR_SHARING is SIMILAR.

   In an OLTP type environment it would be sensible to only use histogram statistics on columns that need it (i.e. where there are only a few distinct values with heavy skew and where different plans are needed for different predicate values). This way most SQL is shared and the absence of histograms keeps predicates safe except where needed.

   CURSOR_SHARING = FORCE

   If CURSOR_SHARING = FORCE binds can still be “unsafe” (in terms of performance) if used by the optimizer in decisions but this should not then affect shareability of the SQL since CURSOR_SHARING=FORCE does not care about unsafe literals, but the cursor should stil lbe shared. e.g.: In the above example with orderid=’literal’, without histograms, the CBO does not need to look at ‘literal’ to determine the selectivity of the predicate and so the bind does not get marked unsafe.
   If there is histograms, the predicate is marked as unsafe, but since FORCE uses the same plan whatever the circumstance, this does not matter. Only where non data literals for whom different values alter the actual meaning of the SQL (e.g. order by 1 versus order by 2) will an unsafe predicate have an affect on plans.

   Note that, prior to 11g, unsafe literals are NOT covered by ‘bind mismatch’ in V$SQL_SHARED_CURSOR  as this is for user bind metadata mismatches. ie: different max bind lengths or bind type mismatches.
   In 11g R2 (and 11.1.0.7 Patchset) a new column has been added to V$SQL_SHARED_CURSOR to check if literal replacement is used with CURSOR_SHARING=SIMILAR. The new column HASH_MATCH_FAILED  is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement. The unshared child cursors may have histogram data
   on key columns used in equality predicates, or range predicates with literals which the optimizer has marked
   as unsafe.

   From the optimizer point of view, these additional plans for ‘unsafe’ bind variables explain why multiple good plans may occur for peeked bind variables, even though the standard behavior for binds is to use peeked binds for the initial parse and then use the resultant plan for all other iterations. With unsafe binds, different plans for different peeked values can occur.

   Background Information

   This issue has been coming more in to focus with dynamic sampling in 10.2 since the default was changed from 1 to 2. When optimizer_dynamic_sampling is greater than 1 then Dynamic sampling emulates statistics + histograms. If histograms are created, then binds may be marked as unsafe and produce different plans for different values. With optimizer_dynamic_sampling > 1 a predicate can create a new version of a sql statement for each different value, even if there are no histograms (or even statistics) on a table (since dynamic sampling may create these in the background).

    

   Reply
4. 

   admin

   August 20, 2010

   Parameter : OPTIMIZER_DYNAMIC_SAMPLING [ID 336267.1]

   Applies to:

   Oracle Server – Enterprise Edition – Version: 9.2 to 10.2
   Information in this document applies to any platform.

   Purpose

   This article is provides some information regarding the parameter “optimizer_dynamic_sampling”

   Scope and Application

   Parties interested in dynamic sampling details

   Parameter : OPTIMIZER_DYNAMIC_SAMPLING

   Dynamic Sampling is a method of gathering additional statistics during optimization by recursively sampling statements. When dynamic sampling is enabled, queries are recursively generated by Oracle to test various selectivities based upon real values in order to improve their accuracy. This can result in the production of better explain plans. It can be especially useful where there are correlated predicates in the query, where statistics are missing resulting in the use of guesses and also in cases where intermediate results are generated in temporary tables for which the optimizer would also have to use guesses otherwise.

   There are overheads associated with dynamic sampling, and for fast executing queries, which already use a good access path, it is unlikely that further gains will be made. In cases where the sampling makes up a small proportion of the total time (for all executions) then the overall benefits can outweigh the time taken to generate and execute the recursive queries that gather the dynamic statistics.

   The parameter OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer.

   The default level is 2 with levels up to 4 being most commonly used, higher levels simply sampling more data. In simplified form, Levels 0-4 have the following effects:

   • Level 0: Disables dynamic sampling.
   • Level 1: Sample all tables that have not been analyzed that meet certain criteria.
   • Level 2: Apply dynamic sampling to all unanalyzed tables.
   • Level 3: As per Level 2, plus all tables for which standard selectivity estimates used a guess for some predicate that is a potential dynamic sampling predicate.
   • Level 4: As per Level 3, plus all tables that have single-table predicates that reference 2 or more columns.

    

    

   Reply
5. 

   admin

   August 20, 2010

   Handling and resolving unshared cursors/large version_counts

   Applies to:

   Oracle Server – Enterprise Edition
   Information in this document applies to any platform.

   Purpose

   The Troubleshooting Guide is provided to assist in debugging SQL sharing issues. When possible, diagnostic tools are included in the document to assist in troubleshooting problems. This document does not contain bugs/patches as these topics are addressed in the articles referenced at the bottom of this document.

   Last Review Date

   January 15, 2009

   Instructions for the Reader

   A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

   Troubleshooting Details

   1. What is shared SQL ?
   
   The first thing to remember is that all SQL is implicitly sharable. When a SQL statement is entered, the RDBMS will create a hash value for text of the statement and that hash value then helps the RDBMS to easily find SQL already in the shared pool. It is not in the scope of this article to discuss this in any great detail, so let’s just assume entering a series of text results in a hash value being created

   For instance :- ‘select count(*) from emp’ hashes to the value 4085390015

   We now create a parent cursor for this sql and a single child. It does not matter that a SQL statement may never be shared – when it is first parsed a parent and a single child are created. The easy way to think of this is that the PARENT cursor is a representation of the hash value and the child cursor(s) represent the metadata for that SQL

   What is ‘SQL Metadata’?

   Metadata is all the information which enables a statement to run. For instance, in the example I have given EMP is owned by scott and therefore has an OBJECT_ID which points to the EMP table owned by this user. When the user SCOTT logged in, optimizer parameters are initialised in that session for use by the statement, so this too is used by the optimizer and is therefore metadata. There are other examples of Metadata which will be mentioned further in this document.

   Let’s say this session logs out and back in again now. It then runs the same command again (as the same user). This time we already have the SQL in the shared pool (but we don’t know this yet). What we do is hash the statement and then search for that hash value in the shared pool. If we find it, we can then search through the children to determine if any of them are usable by us (ie the metadata is the same). If it is, then we can share that SQL statement
   I would still have one version of that SQL in the shared pool because the metadata enabled me to share the statement with the already existent child. The fundementals are that the parent is not shared, it is the children which determine shareability.

   Now – another user ‘TEST’ has it’s own version of EMP. If that user was to now run the select statement above then what would happen is :-

   1. The statement is hashed – it is hashed to the value 4085390015
   2. The SQL will be found in the shared pool as it already exists
   3. The children are scanned (at this point we have one child)
   4. Because the OBJECT_ID of the EMP table owned by TEST is different the OBJECT_ID owned by scott we have a ‘mismatch’

   (Essentially, what happens here is that we have a linked list of children which we traverse, comparing the metadata of the current SQL with that of all the children. If there were 100 children then we would scan each of them (looking for a possible mismatch and moving on) until we found one we could share. If we cannot share any (ie. have exhausted the list of children) then we need to create a new child)

   5. We therefore have to create a new child – we now have 1 PARENT and 2 CHILDREN.

   2. How do I see the versions and why they are not shared ?

   Lets use the example above and take a look at what SQL we can use to see this in the shared pool.

   SCOTT runs select count(*) from emp

   I can now run the following to see the PARENT statement and it’s hash value and address

   select sql_text, hash_value,address from v$sqlarea where sql_text like ‘select count(*) from emp%’;

   SQL_TEXT                 HASH_VALUE    ADDRESS
   ————————————- —————-
   select count(*) from emp 4085390015   0000000386BC2E58

   To see the CHILDREN (I expect to see 1 at this point) :-

   9i – select * from v$sql_shared_cursor where kglhdpar = ‘0000000386BC2E58’

   10G – select * from v$sql_shared_cursor where address = ‘0000000386BC2E58’

   ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
   —————- —————- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
   0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

   We can see we have a single child (ADDRESS 0000000386BC2D08). The mismatch information (USOOSL etc) is all N because this is the first child. Now, if I log in as another user and run the same select (select count(*) from emp) and look again I will get the following output:-

   ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
   —————- —————- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – –
   0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
   0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N

   We can now see the 2nd child ( 0000000386A91AA0) and also the reasons why it could not be shared with the first (The ‘Y’s denote a mismatch) The reasons are (1) AUTH_CHECK_MISMATCH and (2) TRANSLATION_MISMATCH This is basically because the objects under my new user do not map to those of SCOTT (the current child). So , authentication fails because I cannot access SCOTTs objects and translation fails because we have different object_ids.
   [top]
   3. What do the reasons given in v$SQL_SHARED_CURSOR mean?

   Below are the list of reasons given as well as some workable examples (Those denoted by ** are the ones most often seen) :-

   1. UNBOUND_CURSOR – The existing child cursor was not fully built (in other words, it was not optimized)

   2. SQL_TYPE_MISMATCH – The SQL type does not match the existing child cursor

   3. **OPTIMIZER_MISMATCH – The optimizer environment does not match the existing child cursor

   select count(*) from emp; ->> 1 PARENT, 1 CHILD
   alter session set optimizer_mode=ALL_ROWS
   select count(*) from emp; ->> 1 PARENT, 2 CHILDREN (The optimizer mode has changed and therefore the existing child cannot be reused)

   (The same applies with events – if I turned on tracing with 10046 than I would get the OPTIMIZER_MISMATCH again and a 3rd child)
   OUTLINE_MISMATCH – The outlines do not match the existing child cursor

   If my user had created stored outlines previously for this command and they were stored in seperate categories (say “OUTLINES1” and “OUTLINES2”) running:-

   alter session set use_stored_outlines = OUTLINES1;
   select count(*) from emp;
   alter session set use_stored_oulines= OUTLINES2;
   select count(*) from emp;

   –> Would create a 2nd child as the outline used is different than the first run.
   STATS_ROW_MISMATCH – The existing statistics do not match the existing child cursor

   Check that 10046/sql_trace is not set on all sessions as this can cause this.

   LITERAL_MISMATCH – Non-data literal values do not match the existing child cursor

   SEC_DEPTH_MISMATCH – Security level does not match the existing child cursor

   EXPLAIN_PLAN_CURSOR – The child cursor is an explain plan cursor and should not be shared

   Explain plan statements will generate a new child by default – the mismatch will be this.
   BUFFERED_DML_MISMATCH – Buffered DML does not match the existing child cursor

   PDML_ENV_MISMATCH – PDML environment does not match the existing child cursor

   INST_DRTLD_MISMATCH – Insert direct load does not match the existing child cursor

   SLAVE_QC_MISMATCH -The existing child cursor is a slave cursor and the new one was issued by the coordinator

   (or, the existing child cursor was issued by the coordinator and the new one is a slave cursor).

   TYPECHECK_MISMATCH – The existing child cursor is not fully optimized

   AUTH_CHECK_MISMATCH – Authorization/translation check failed for the existing child cursor

   The user does not have permission to access the object in any previous version of the cursor. A typical example would be where each user has it’s own copy of a table
   **BIND_MISMATCH – The bind metadata does not match the existing child cursor

   variable a varchar2(100);
   select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD
   variable a varchar2(400);
   select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN (The bind ‘a’ has now changed in definition)

   DESCRIBE_MISMATCH – The typecheck heap is not present during the describe for the child cursor

   LANGUAGE_MISMATCH – The language handle does not match the existing child cursor

   TRANSLATION_MISMATCH – The base objects of the existing child cursor do not match

   The definition of the object does not match any current version. Usually this is indicative of the same issue as “AUTH_CHECK_MISMATCH” where the object is different anyway

   ROW_LEVEL_SEC_MISMATCH – The row level security policies do not match

   INSUFF_PRIVS – Insufficient privileges on objects referenced by the existing child cursor

   INSUFF_PRIVS_REM – Insufficient privileges on remote objects referenced by the existing child cursor

   REMOTE_TRANS_MISMATCH – The remote base objects of the existing child cursor do not match

   USER1: select count(*) from table@remote_db
   USER2: select count(*) from table@remote_db (Although the SQL is identical, the dblink pointed to by remote_db may be a private dblink which resolves
   to a different object altogether)

   LOGMINER_SESSION_MISMATCH

   INCOMP_LTRL_MISMATCH

   OVERLAP_TIME_MISMATCH – error_on_overlap_time mismatch

   SQL_REDIRECT_MISMATCH – sql redirection mismatch

   MV_QUERY_GEN_MISMATCH – materialized view query generation

   USER_BIND_PEEK_MISMATCH – user bind peek mismatch

   TYPCHK_DEP_MISMATCH – cursor has typecheck dependencies

   NO_TRIGGER_MISMATCH – no trigger mismatch

   FLASHBACK_CURSOR – No cursor sharing for flashback

   ANYDATA_TRANSFORMATION – anydata transformation change

   INCOMPLETE_CURSOR – incomplete cursor 

   When bind length is upgradeable (i.e. we found a child cursor that matches everything
   else except that the bind length is not long enough). In this case, we mark the old cursor is not usable
   and build a new one.  This means the version can be ignored.

   TOP_LEVEL_RPI_CURSOR – top level/rpi cursor

   In a Parallel Query invocation this is expected behaviour (we purposely do not share)

   DIFFERENT_LONG_LENGTH – different long length

   LOGICAL_STANDBY_APPLY – logical standby apply mismatch

   DIFF_CALL_DURN – different call duration

   BIND_UACS_DIFF – bind uacs mismatch

   PLSQL_CMP_SWITCHS_DIFF – plsql compiler switches mismatch

   CURSOR_PARTS_MISMATCH – cursor-parts executed mismatch

   STB_OBJECT_MISMATCH – STB object different (now exists)

   ROW_SHIP_MISMATCH – row shipping capability mismatch

   PQ_SLAVE_MISMATCH – PQ slave mismatch

   Check you want to be using PX with this reason code, as the problem could be caused by running lots of small SQL statements which do not really need PX. If you are on < 11i you may be hitting BUG4367986

   TOP_LEVEL_DDL_MISMATCH – top-level DDL cursor

   MULTI_PX_MISMATCH – multi-px and slave-compiled cursor

   BIND_PEEKED_PQ_MISMATCH – bind-peeked PQ cursor

   MV_REWRITE_MISMATCH – MV rewrite cursor

   ROLL_INVALID_MISMATCH – rolling invalidation window exceeded

   See NOTE: 557661.1 . This is caused by the rolling invalidation capability in DBMS_STATS. The child cannot be shared as it’s invalidation window is exceeded.

   OPTIMIZER_MODE_MISMATCH – optimizer mode mismatch

   PX_MISMATCH – parallel query mismatch

   If running 11.1.0.6 and RAC see BUG7352775. Check that if (on each instance) paralle_instance_groups is set then instance_groups is set to the same.

   MV_STALEOBJ_MISMATCH – mv stale object mismatch

   FLASHBACK_TABLE_MISMATCH – flashback table mismatch

   LITREP_COMP_MISMATCH – literal replacement compilation mismatch

   New in 11g (incomplete):

   PLSQL_DEBUG – debug mismatch 

   Session has debugging parameter plsql_debug set to true

   LOAD_OPTIMIZER_STATS  – Load optimizer stats for cursor sharing

   ACL_MISMATCH   –  Check ACL mismatch

   FLASHBACK_ARCHIVE_MISMATCH  – Flashback archive mismatch

   LOCK_USER_SCHEMA_FAILED  – Failed to lock user and schema

   REMOTE_MAPPING_MISMATCH  – Remote mapping mismatch

   LOAD_RUNTIME_HEAP_FAILED  – Runtime heap mismatch

   HASH_MATCH_FAILED  – Hash mismatch

   Set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement (See BUG3461251)

   4. What further tracing is available ?
   
   Solution:
   In 10G it is possible to use CURSORTRACE to aid the investigation of why cursors are not being shared. This event should only be used under the guidance of support and the resultant trace file is undocumented. To get the trace for a particular SQL statement you first of all need to get the hash_value (See the above select from v$sqlarea). You then set the trace on using:-

   alter system set events
   ‘immediate trace name cursortrace level 577, address hash_value’;

   (level 578/580 can be used for high level tracing (577=level 1, 578=level 2, 580=level 3)

   This will write a trace file to user_dump_dest each time we try to reuse the cursor.

   To turn off tracing use:-

   alter system set events
   ‘immediate trace name cursortrace level 2147483648, address 1’;

   Please note: BUG5555371 exists in 10.2 (fixed in 10.2.0.4) where cursor trace cannot fully be turned off and single line entries will still be made to the trace
   file as a result. The w/a is to restart the instance. How invasive this BUG is depends on the executions of the cursor (and the size of the resultant trace file additions)

   5. Are there any times when a high version count is expected even though BINDS are being used?
   
   Solution:
   Consider the following where cursor_sharing=SIMILAR

   select /* TEST */ * from emp where sal > 100;
   select /* TEST */ * from emp where sal > 101;
   select /* TEST */ * from emp where sal > 102;
   select /* TEST */ * from emp where sal > 103;
   select /* TEST */ * from emp where sal > 104;

   SELECT sql_text,version_count,address
   FROM V$SQLAREA
   WHERE sql_text like ‘select /* TEST */%’;

   SELECT * FROM V$SQL_SHARED_CURSOR WHERE kglhdpar = ‘&my_addr’;

   You will see several versions , each with no obvious reason for not being shared

   Explanation:

    
   One of the cursor sharing criteria when literal replacement is enabled with cursor_sharing as similar is that bind value should match initial bind value if the execution plan is going to change depending on the value of the literal. The reason for this is we _might_ get a sub optimal plan if we use the same cursor. This would typically happen when depending on the value of the literal optimizer is going to chose a different plan. Thus in this test case we have a predicate with > , if this was a equality we would always share the same child cursor. If application developers are ready to live with a sub-optimal plan and save on memory , then they need to set the parameter to force.

   “The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans.
   Setting CURSOR_SHARING to FORCE forces similar statements to share the SQL area potentially deteriorating execution plans.”

   It is also possible to tell from 10046 trace (level 4/12 – BINDS) if a bind is considered to be unsafe

   The flag oacfl2 in 9i and fl2 in 10g will show if a variable is unsafe.

   BINDS #2:
   bind 0: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24
   offset=0
   bfp=1036d6408 bln=22 avl=04 flg=09
   value=16064
   bind 1: dty=2 mxl=22(04) mal=00 scl=00 pre=00 oacflg=10 oacfl2=500 size=24
   offset=0
   bfp=1036d4340 bln=22 avl=04 flg=09

    

   Reply