This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 13, 2013 11:41 AM by ji li RSS

11gR2  cursor_sharing=force  and  adaptive cursor sharing

ji li Pro
Currently Being Moderated
From what I'm understanding with Oracle 11gR2, ACS (adapter cursor sharing) is enabled when setting CURSOR_SHARING=FORCE.

In our OLTP environment, we saw that our soft_parse hit ratio was down near 50% so I set CURSOR_SHARING=FORCE.
(This was originally set to EXACT.)

Initially, everything seems good, and when I check our soft_parse hit ratio, it improved to 99.25%.
Wow! What an improvement.

But, it seems the application queries are suffering and running slower. Why?

i check the explain plans, and for example, I see:
select statement
  count stopkey
     view
        table access by index rowid   (highest cost)
           index full scan descending
We do have histograms in place (from what I can tell by selecting from the DBA_TAB_HISTOGRAMS table).
We also have optimizer_use_sql_plan_baselines=TRUE.
We are using AMM so shared_pool=0, but shared_pool_reservied_size=1G so this should not be an issue.

Any ideas?
  • 1. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    JohnWatson Guru
    Currently Being Moderated
    There is no relationship between ACS and the CURSOR_SHARING parameter.

    Your use of optimizer_use_sql_plan_baselines=TRUE means that ACS is generating multiple plans, but they are not being used. You need to run DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE against all the non-accepted plans listed in DBA_SQL_PLAN_BASELINES.

    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com
  • 2. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    Centinul Guru
    Currently Being Moderated
    ji li wrote:
    From what I'm understanding with Oracle 11gR2, ACS (adapter cursor sharing) is enabled when setting CURSOR_SHARING=FORCE.
    That's incorrect. Per the documentation
    Adaptive cursor sharing is enabled for the database by default and cannot be disabled...Adaptive cursor sharing is independent of the CURSOR_SHARING initialization parameter
    >
    In our OLTP environment, we saw that our soft_parse hit ratio was down near 50% so I set CURSOR_SHARING=FORCE.
    (This was originally set to EXACT.)

    Initially, everything seems good, and when I check our soft_parse hit ratio, it improved to 99.25%.
    Wow! What an improvement.
    To be honest trying to improve any one ratio in isolation is likely going to cause problems. Depending on your environment 50% could potentially be correct. Not all systems must hit 100% on the ratios.
    But, it seems the application queries are suffering and running slower. Why?
    When you set CURSOR_SHARING = FORCE Oracle will convert any literals in the SQL statement to bind variables in an attempt to create SQL that can be "shared." So you could have a situation where you had two SQL statements like:
    SELECT * FROM table_x WHERE col_x = 'A';
    and
    SELECT * FROM table_x WHERE col_x = 'B';
    Where in the first statement the optimal plan might be an indexed access path, and the second may be a full table scan. However, now that is presented to the optimizer as:
    SELECT * FROM table_x WHERE col_x = :SYS_B_0;
    The optimizer will perform a "bind peek" and likely get the correct execution plan the first time (for 'A'), but it might not get it right for subsequent executions (for 'B'). This is where Adaptive Cursor Sharing (ACS) comes into action. I haven't done a lot of testing with it, but from what I understand it is a "learning program." It looks at estimates from the optimizer and compares them to actuals to see if the plan is optimal. So in that case statements will execute poorly for a while.

    Either way I wouldn't go flipping global settings because that is likely to cause global problems. You should always try and fix problems at the correct scope.

    I hope this helps!
  • 3. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    Dom Brooks Guru
    Currently Being Moderated
    From what I'm understanding with Oracle 11gR2, ACS (adaptIVE cursor sharing) is enabled when setting CURSOR_SHARING=FORCE.
    Not true. Bind variables are required.
    As CURSOR_SHARING=FORCE will replace literals with binds then yes, this could affect the take up of ACS.
    Does it make sense to set this across the system? No.
    Might it make sense to set it for certain users/modules? Sometimes.
    Wow! What an improvement.
    But, it seems the application queries are suffering and running slower. Why?
    Who or what has benefited from this change apart from some meaningless ratio?
    If there's no benefit, revert the setting and stop worrying about a ratio which tends not to be an effective tuning methodology.
    But, it seems the application queries are suffering and running slower. Why?
    Further investigation required.
    It may be relevant to your change, may be irrelevant, impossible to tell without more information.
    You need to establish whether the issues is a few sql statments or database wide.
    And whether it is related to your change or not.

    i check the explain plans, and for example, I see:
    This is part of an execution plan and not terribly helpful.
    If you want SQL tuning input then the information required is:
    HOW TO: Post a SQL statement tuning request - template posting

    We do have histograms in place (from what I can tell by selecting from the DBA_TAB_HISTOGRAMS table).
    Histograms and bind variables are designed for opposite usage.
    See http://jonathanlewis.wordpress.com/2009/05/06/philosophy-1/

    Bind variables are designed for shareable sql.
    Histograms exist to provide information about data distribution for specific values.
    Bind variable peeking happens to provide information about binds supplied at hard parse time such that execution plans are generated for these specific binds using histogram information if relevant to provide shareable execution plans for subsequent executions probably with different parameters.
    Features like ACS are introduced for subsequent executions of a statement once a previous execution realises that the above has led to a problem.
    It's all a cycle of nonsense.


    The bottom line is that best practice is to:
    1. Use bind variables when it makes sense to use bind variables
    2. Use literals when it makes sense to use literals
    This should mean that you get statements with both binds and literals when it makes sense to do so.
    3. Review usage of histograms - use them where you need them which is probably in a fraction of the places where you actually have them.
  • 4. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    ji li Pro
    Currently Being Moderated
    Thank you for the clarification regarding ACS and cursor_sharing.

    Also, thank you for your suggestion regarding the SPM.
  • 5. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    Mohamed Houri Pro
    Currently Being Moderated
    From what I'm understanding with Oracle 11gR2, 
    ACS (adapter cursor sharing) is enabled when setting CURSOR_SHARING=FORCE.
    First, for the ACS to kicks off there should be two important information from v$sql:

    (1) is_bind_sensitive = Y
    (2) is_bind_aware = Y

    There is no chance for you to benefit from ACS if those two columns are not set as shown above.

    Then for you query to be bind sensitive you should

    (a)     Use bind variable. If you don’t use bind variables then your cursor will never be bind sensitive
    (b)     your query predicates should have histograms or should be a range predicates

    If you are not using bind variable then you could set the cursor_sharing to FORCE in order to hope ACS kicks off for you providing the conditions mentioned above

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 6. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    ji li Pro
    Currently Being Moderated
    Thank you also for the clarification regarding ACS and cursor_sharing.
    I can't find the pages now, but i just read this morning that setting cursor_sharing to force enabled ACS.
    But I confirmed you are correct and thank you for the clarification.

    One thing I also found related to this is:

    Adaptive cursor sharing. This was introduced in 11gR1 to address issues related to bind variable peeking.
    See support note 740052.1 and documentation for details. For the purposes of this doc, if you want to get
    consistent behavior with 10g and overall if you want to disable this feature, you may want to add the hint: /*+ NO_BIND_AWARE */.

    According to support note 11657468.8 adaptive cursor sharing can be disabled by setting the following 2 parameters (say at session level):
    optimizeradaptive_cursor_sharing = false,
    optimizerextended_cursor_sharing_rel = "none"


    Anyway, most web pages I've read highly suggest setting cursor_sharing to FORCE for OLTP databases, and set it to SIMILAR for DSS (reports and stuff).

    I do agree that when literals are used instead of bind variables, that it would seem Oracle then has to rewrite the query and replace the literals with system bind variables (like :"SYS_B_5").
    This would seem like an extra step and more expensive operation, especially if our application used literals most of the time.

    Also, I found:

    ACS will not mark the cursor as bind sensitive if:

    It is using Parallel query
    Hints are in use
    Outlines are being used
    It is not recursive query
    The number of binds is more than 14

    Edited by: ji li on Feb 13, 2013 8:09 AM
  • 7. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    ji li Pro
    Currently Being Moderated
    Thanks for your reply Dom.
    I see now why I read that ACS was enabled when cursor_sharing=FORCE.
    Your explanation is helpful to understand that relationship.

    Regarding your suggestions on best practices:
    The bottom line is that best practice is to:
    1. Use bind variables when it makes sense to use bind variables
    2. Use literals when it makes sense to use literals
    This should mean that you get statements with both binds and literals when it makes sense to do so.
    3. Review usage of histograms - use them where you need them which is probably in a fraction of the places where you actually have them.
    Unfortunately, this is a vendor application and we are unable to modify their code.
    In fact, we have seen that many of the queries by the application use literals instead of bind variables, which is why we thought to set cursor_sharing=FORCE.

    As for histograms, I ran a job to collect stats on all tables with method_opt=>'for all columns size auto'
    I've also done the same gathering schema_stats with method_opt=>'for all columns size auto'

    I believe this is the correct syntax to gather histograms. Since that time, however, I've rebuilt indexes, but I also have cascade=>true to gather stats on indexes.
    Do you think I need to run the schema or table stats again? And, how frequently?
    Since the oracle maintenance runs stats on tables with stale stats, I don't think it runs histograms (if I remember correctly).
  • 8. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    Dom Brooks Guru
    Currently Being Moderated
    Unfortunately, this is a vendor application and we are unable to modify their code.
    In fact, we have seen that many of the queries by the application use literals instead of bind variables, which is why we thought to set cursor_sharing=FORCE.
    Ok - a not uncommon problem likely with no comeback on the vendor to change their code.
    Ideally this would have been identified during period of evaluating vendors - that's when vendors are most pliable about accomodating such requests.


    Has this setting significantly improved performance of something / anything?
    If not then revert.

    If it has improved performance significantly of some things but degraded performance in others, then you have a choice.
    Is the benefit worth it?
    If it is then considering turning off cursor sharing for statements where it has caused an issue.
    For untouchable vendor code, this could be accomplished using the cursor_sharing_exact hint injected via the sql patch functionality.
    In the cases where it is causing problems if the issue is the combination of histograms and bind variables then go through a review of the data model and the data and get rid of unnecessary ones.
    method_opt=>'for all columns size auto'
    Since the oracle maintenance runs stats on tables with stale stats, I don't think it runs histograms
    With this setting, a whole bunch of unnecessary will almost certainly have been created.
  • 9. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    ji li Pro
    Currently Being Moderated
    Hi Dom.

    I wrote....
     method_opt=>'for all columns size auto'
    Since the oracle maintenance runs stats on tables with stale stats, I don't think it runs histograms
    And you responded with....
    With this setting, a whole bunch of unnecessary will almost certainly have been created. 
    Why is that?
  • 10. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    Aman.... Oracle ACE
    Currently Being Moderated
    Did you happen to read these two posts from the optimizer development group?
    https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1
    https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force

    Aman....
  • 11. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    ji li Pro
    Currently Being Moderated
    Yes, i sure did. That's what initiated this whole investigation.
    We originally had cursor_sharing set to EXACT.
    Then, in December, we decided to try setting cursor_sharing to FORCE when we saw our soft parse hit ratio was in the 50% range, and knowing our application uses mainly literals instead of bind varaibles.
    But reading these articles you referenced, it seems that we should use SIMILAR along with having HISTOGRAMS in place, but this will be deprecated in v12.
  • 12. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    Dom Brooks Guru
    Currently Being Moderated
    But reading these articles you references, it seems that we should use SIMILAR along with having HISTOGRAMS in place, but this will be deprecated in v12.
    How did you come to this conclusion?
    We must be reading different articles.
  • 13. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    Dom Brooks Guru
    Currently Being Moderated
    Then, in December, we decided to try setting cursor_sharing to FORCE when we saw our soft parse hit ratio was in the 50% range
    I can't believe the driver was this was some ratio rather than actual performance issues / latch contention over parsing.
    Just revert the change if there's no driver for it and no actual, tangible, significant benefit.

    Tuning by ratios was debunked over a decade ago.
    Why is that? Do you mean because I should only have created histograms on indexes columns (using method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY').
    I thought AUTO would be best.
    There are different degrees of "good".
    Best is probably having good knowledge of the data and manually creating a select few histograms where you know you need them and they make a different, then using SIZE REPEAT.
  • 14. Re: 11gR2  cursor_sharing=force  and  adaptive cursor sharing
    ji li Pro
    Currently Being Moderated
    How did you come to this conclusion?
    We must be reading different article
    For SIMILAR with histograms present... Query Performance: Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt

    Am I not understanding this correctly?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points