绑定变量介绍

Oracle在执行SQL语句时,普遍存在以下几个步骤:

  1. 当SQL语句首次执行,Oracle将确认该句语句的语法是否正确(语法解析Syntax parse)并进一步确认语句相关表和列的存在性等因素(语义解析semantic parse)以及优化器决定执行计划等步骤。整个过程称之为硬解析,硬解析消耗大量的CPU时间和系统资源。硬解析过多会有效降低系统性能。
  1. 若之前已进行过硬解析,且解析后的分析树和执行计划仍存在于共享池中,则同样的SQL仅需要软解析。软解析将输入的SQL语句转换为哈希代码,同共享池内哈希链表上的已有记录进行对比,找出对应的游标信息,使用已有的执行计划执行。
  1. 绑定变量,将实际的变量值代入SQL语句中。
  1. 执行SQL语句,查询语句将返回结果集。

不使用绑定变量的SQL语句,Oracle无法将它们视为相同的,如以下两句语句:

select * from emp where empno=1234

select * from emp where empno=5678

因为自由变量的不同,Oracle认为以上是2句不同的语句,则当第一条被硬解析后,第二条SQL执行时仍无法避免硬解析。实际在以上不使用绑定变量的情况中,只要自由变量有所改变则需要一次硬解析。这是强烈建议使用绑定变量的主要原因,使用绑定变量的语句变量的实际值仅在SQL执行的最后阶段被代入。如以下语句:

select * from emp where empno=:x

该语句使用绑定值:x替代自由变量,在应用中语句可能以预编译或普通编译的方式存在,仅在执行阶段代入变量值,多次执行仅需要一次硬解析,较不使用绑定变量情况性能大大提升。

同时过多的硬解析还会引发共享池碎片过多的问题。因为每当需要硬解析一个SQL或者PLSQL语句时,都需要从shared pool中分配一块连续的空闲空间来存放解析结果。Oracle首先扫描shared pool查找空闲内存,如果没有发现大小正好合适的空闲chunk,就查找更大的chunk,如果找到比请求的大小更大的空闲chunk,则将它分裂,多余部分继续放到空闲列表中。因为过多的硬解析加剧了内存段分配的需求,这样就产生了碎片问题。系统经过长时间运行后,就会产生大量小的内存碎片。当请求分配一个较大的内存块时,尽管shared pool总空闲空间还很大,但是没有一个单独的连续空闲块能满足需要。这时,就可能产生 ORA-4031错误。

通常我们可以通过以下SQL语句将系统中非绑定变量的语句找出:

SELECT substr(sql_text,1,40) “SQL”,

count(*) ,

sum(executions) “TotExecs”

FROM v$sqlarea

WHERE executions < 5 –-语句执行次数

GROUP BY substr(sql_text,1,40)

HAVING count(*) > 30 –-所有未共享的语句的总的执行次数

ORDER BY 2;

以上语句在实际使用中substr函数截取到的字符串长度需要视乎实际情况予以变化。

对于非绑定变量且短期内无法修改的应用,Oracle存在参数cursor_sharing可以改善其表现。cursor_sharing默认为exact,对使用自由变量的语句不做额外处理;当设为force时,非绑定变量的SQL语句被进一步处理以达到共享SQL的目的,但以上处理步骤同样要消耗一定的CPU时间;当设为similar时,若数据库存在语句相关统计信息则其表现如exact,若无统计信息则表现为force。cursor_sharing参数是Oracle针对无法修改的非绑定变量应用所提出的折中方案,但cursor_sharing为force值时存在一定SQL引发bug或语句无效的情况,且额外的处理操作同样需要消耗一定量的CPU时间和系统资源。故针对系统性能的最优方案往往是直接修改应用代码,使用绑定变量特性。


Posted

in

by

Tags:

Comments

5 responses to “绑定变量介绍”

  1. admin Avatar
    admin

    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’);

     

  2. admin Avatar
    admin

    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”

     

  3. admin Avatar
    admin

    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).

     

  4. admin Avatar
    admin

    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.

     

     

  5. admin Avatar
    admin

    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

     

Leave a Reply

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