The limitations of CURSOR_SHARING = FORCE and FORCE_MATCHING_SIGNATURE for SQL plan stability

It’s a well known fact that the cursor_sharing parameter can be set to FORCE for making Oracle replace any literals with system-generated bind variable placeholders and then calculating the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage.

Also, the same mechanism (of replacing literal values with bind variable placeholders before calculating the SQL hash value for library cache lookup) can be used for enforcing a SQL profile for SQL statements which differ by literal values in them. You just accept a SQL profile using DBMS_SQLTUNE.ACCEPT_PROFILE( …, FORCE_MATCH=>TRUE).

However this “force matching” has one limitation which can make it almost useless for achieving plan stability in databases where the applications use dynamically generated SQL.

Here’s an example – I’m just setting up my sqlplus environment, set cursor_sharing = FORCE (which is not needed for DBMS_SQLTUNE’s FORCE_MATCH by the way, I’m just doing it for displaying the “new” SQL text used for hash value computation):


SQL> COL force_matching_signature FOR 99999999999999999999999999
SQL> COL sql_text FOR A100
SQL>
SQL> ALTER SESSION SET cursor_sharing = FORCE;

Session altered.

SQL>
SQL> SELECT * FROM dual WHERE rownum IN (1,2,3);

D
-
X

And now I’ll query V$SQL to see what the actual SQL text looks like (and its related hash values):


SQL> SELECT sql_id,hash_value,force_matching_signature,sql_text FROM v$sql WHERE sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = USERENV('SID'));

SQL_ID HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- ---------- --------------------------- ----------------------------------------------------------------------------------------------------
fqcq5k1wd4d4h 2027041936 1308158718700150644 SELECT * FROM dual WHERE rownum IN (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2")

Scroll right to see how the literal values 1,2,3 have been replaced with :SYS_B_x bind variables in SQL text. The SQL hash value (and SQL_ID and force_matching_signature) are calculated after this replacement was done.

So, now I’ll run a similar statement with just different literal values:


SQL> SELECT * FROM dual WHERE rownum IN (999,888,777);

no rows selected

SQL> SELECT sql_id,hash_value,force_matching_signature,sql_text FROM v$sql WHERE sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = USERENV('SID'));

SQL_ID HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- ---------- --------------------------- ----------------------------------------------------------------------------------------------------
fqcq5k1wd4d4h 2027041936 1308158718700150644 SELECT * FROM dual WHERE rownum IN (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2")

See, different literal values (999,888,777), but as they got replaced with exactly the same number of bind variables (in the same order), the resulting SQL text is exactly the same – therefore the SQL text hash values are also exactly the same as you see above.

So this is how these features work – the literal values are replaced in SQL text string just before calculating the hash value for library cache lookup.

However, let’s run one more query, this time with 4 literal values instead of 3:


SQL> SELECT * FROM dual WHERE rownum IN (999,888,777,666);

no rows selected

SQL> SELECT sql_id,hash_value,force_matching_signature,sql_text FROM v$sql WHERE sql_id = (SELECT prev_sql_id FROM v$session WHERE sid = USERENV('SID'));

SQL_ID HASH_VALUE FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- ---------- --------------------------- ----------------------------------------------------------------------------------------------------
8btxq8q6avt6b 2360206539 15602626316910109322 SELECT * FROM dual WHERE rownum IN (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3")

Now as we used 4 literals (999,888,777,666), they got replaced by 4 bind variables in the SQL text (as opposed to 3 in earlier queries) and thanks to that the resulting SQL text is different! Therefore the hash values will also be different and anything that’s matching/looking up cursors based on SQL text hash value, won’t find the previous query plan anymore. Note that the force_matching_signature is also different from previous thanks to this. So, a new hard parse and library cache load would be done for this cursor – and also any SQL Profile used with first 2 queries with FORCE_MATCH = TRUE would not be applicable to this new query – simply because it can’t find a profile matching the new force_matching_signature.

Now you might say who cares, I’ll just create a profile for this query with 4 literal values in the inlist and we are done. Yes true, but now you’d only have a profile for the query with 3 and 4 inlist values. Often in data warehouses, reporting environments and also in some OLTP systems, the developers are using dynamically generated in-lists extensively. By now I have seen queries with tens of thousands of literal values in  inlists (using OR concatenation to work around the ORA-01795: maximum number of expressions in a list is 1000 error). There’s also a bug in MOS about someone’s query failing with 60 000 bind variables in it! :)

So if you have a dynamically generated query with multiple IN-lists used for filtering different tables and the number of items in each inlist fluctuates anywhere between 1 and tens of thousands, you may end up with a very large number of possible combinations – and each of such combinations of inlist item counts would have its own force_matching_signature. Thus even if you think you have fixed your plans with a force matching profile, some day you will hit a yet another combination and have to reactively fix that “new” query again.

So what can you do in such case? (other than making sure that your schema, code, stats and configuration is good enough so that the CBO would come up with good plans for new queries by itself). Well, don’t use such dynamically generated inlist strings!

Instead of dynamically generating an inlist string with 10000 literal values (or binds), you can just create an array of these values in your application side and bind that entire array to your query as a single bind variable. So regardless of how many values you have to pass in to this in-list, they would all be still stored in a single bind variable, leaving the SQL hash values & force_matching_signature the same. PL/SQL supports it, OCI-based tools support it, Oracle’s JDBC supports it (and probably ODP.NET too). So there’s no excuse not to use it :)

I didn’t find my own demo code anymore, so I googled for a quick example in Java – http://blogs.itemis.de/kloss/2009/03/05/arrays-preparedstatements-jdbc-and-oracle/. If you do have your own code examples, feel free to post links to these into comments.

There are other working, but less clean options, like passing in a comma separated string as a single bind variable (either as VARCHAR2 or even a CLOB) and then using a string tokenizer function in the inlist to break this into individual values) or even always inserting the inlist items into a GTT and then joining it to the main query block (this could be useful if you have millions of inlist values and you don’t want to use too much of private memory used for storing bind values and PL/SQL arrays).

There’s possible one challenge though – the CBO’s cardinality estimates for such array-based inlists may be incorrect, so some adjustment might be needed either via the cardinality hint or a SQL profile or a variety of other means :)

Update: Karen Morton has written about another limitation of force_matching_signature. IIRC this used to be a bug, but apparently now it’s documented, so it’s a new feature :)

This entry was posted in Oracle. Bookmark the permalink.

26 Responses to The limitations of CURSOR_SHARING = FORCE and FORCE_MATCHING_SIGNATURE for SQL plan stability

  1. One solution used by developers that I’ve seen for this problem, was filling a global temporary table with the list values and then joining against this table. What do you think? :-)

  2. Hi Tanel,
    I hit similar problem with dynamic IN generation and SQL Plan Baselines. Changing IN into GTT solve problem but I see more possible solutions now. Thanks for sharing it.

    regards,
    Marcin

  3. GregG says:

    Thanks Tanel for pointing this out, we solved similar issue with function providing in list elements and cardinality hint with arbitrary set value (10 in our case) .
    Since Your blog is about cursor (got string cursor :)).
    Let me ask about diagnosing ref cursor leak (not closing ref cursor) .
    How can I diagnose this ?
    I suppose there is some indication in UGA memory structures or more generaly session state object .
    But not sure about details so maybe its good subject for another post ?:)
    Regards
    GregG

    • Tanel Poder says:

      Greg, every open cursor holds two slots of the library cache lock array (x$kgllk / v$open_cursor) so you can just query this and group by SID or SQL_ID to find out which session / SQL statement has the most cursors open against it. Once you know the SQL_ID, you should know where in the app it comes from and look in the app code.

      A common reason for cursor leaks is where the developer closes a cursor in the end of the normal execution of a code block, but doesn’t handle exceptions which would transfer control out of the code block. So I’d look into where the SQL comes from and see whether there are some locations where an exception can be raised and whether there’s proper exception handling in place – which would close any open cursors before continuing.

  4. Not quite related to force (so a bit off topic), there is a note in metalink (1169017.1) regarding the decomissioning of SIMILAR in Oracle 12.

    Similar has the sid effect of generating a very large number of child cursor with generated sql and Oracle 11. And awful performance in some cases (litterally thousands of cursors generated for the same statement)

    • Tanel Poder says:

      Laurent – I agree! The SIMILAR option caused a lot of trouble ( SIMILAR + histograms was especially messy ). Oracle guys probably realized themselves that it was a half-baked feature (like the initial bind variable peeking!) and deprecated it…

  5. Damir Vadas says:

    Which db version is in your case?

  6. Olivier says:

    Hi Tanel,
    We have also tried to use an array in (in java) and bind (also using the CARDINALITY hint) to workaround the “in” limitation. However we have faced many issues in 10g and 11g.

    10g : The CADINALITY hint is obviously bypassed by the optimizer (run time execution plan did not display any value for the numer of estimated rows returned) and we used to have suboptimal join orders.

    In 11gR2, using the same workaround, the cardinality is displayed but we faced another issue with partition pruning not working as expected (predicates not being pushed through the view, a least with a union all) and we got the following explaination from MOS (for which i’m not 100% sure it’s relevant) :

    “We have this Bug 13607764 created for us and now it is closed as not a bug, the reason that this is not a bug is that The TABLE expression operates such that how we apply the
    predicates with it (using column=VALUE(KOKBF$) mean the query cannot be transformed in the way it can be with the IN which are just normal predicates
    You will have to write the query with the IN list in order to get it to work using the partition pruning (ie to be able to push the predicate into the VIEW and, therefore, prune )”

    that was just for sharing :P

    Thanks again for all your very valuable stuff.

  7. Antony says:

    Tanel,

    I have also seen too many number of child cursors(900+) for one SQL_ID with 4 PLAN_HASH_VALUE in one of our company’s production database(10.2.0.4) with cursor_sharing set to SIMILAR.
    What are the possible reasons of a SQL generating 900+ cursors,when it was reusing only 4 plans?

    Thanks

    • Tanel Poder says:

      cursor_sharing = SIMILAR is one common reason why Oracle creates lots of child cursors (as it thinks some predicates may have way different number of matching rows across executions – called “unsafe” literals/predicates). If I recall correctly, then predicates with >, <, BETWEEN, LIKE conditions are “unsafe”, thus new child cursors are created whenever you rerun the query with different literals – and when your columns have histograms then even equality predicates are considered unsafe! (I haven’t used SIMILAR for long time, so don’t remember exact details anymore).

      The solution is really to not use SIMILAR. It’s deprecated (in 11g) anyway. Code your app to use bind variables for frequently executed cursors – or use cursor_sharing = FORCE if can’t fix the app (and your shared pool/CPUs can’t handle all that extra parsing). Or just use EXACT if its some reporting env running mostly long-running queries. Use anything but SIMILAR :)

      If you absolutely need to keep using SIMILAR for some weird reason, then getting rid of histograms on the accessed table columns would reduce the child cursor creation rate somewhat.

      Btw, in 10g there’s a bug that V$SQL_SHARED_CURSOR doesn’t show any reason codes for cursors created due to SIMILAR … in 11g (and maybe later 10.2.x too) there’s a new reason HASH_MATCH_FAILED for that)

  8. wateenmooiedag says:

    It is possible to bind an array as a single bind variable when using odp.net, see here: https://forums.oracle.com/forums/thread.jspa?messageID=3869879&#3869879

  9. Joy says:

    I have a database activity monitoring (DAM) tool monitoring oracle database. The DAM logs bind variable. How can the DAM capture literal values to aid my investigation.

  10. Not as a general panacea, but as a reasonable solution in some cases for generating a much smaller set of IN list predicates is to either use NULL or a duplicate value (your mileage will vary) so that you might have frequency sensitive (or just binary increasing) numbers of bind variables per query. So you might generate a predicate with equals for one value, 2, 4, 8, etc., or adjust this to be more optimal if you know a lot of times it will be 3 exactly.

  11. Yes. This is a possible issue when using ORM (object relational models) like Hibernate when writing Java code against Oracle. Hibernate solves the binding of IN-lists by using this same technique: col IN (:b1, :b2, :b3 … for the number of literals in your in-list). If your IN-list could be “infititly” long, OR with more than one IN-list in your query, you easily could get into good old latching contention. I’ve seen this a lot of times happening in version 9i. I haven’t seen this happen on 10g or 11g yet. I know the introduction of mutexes have helped on this issue, but I imagine that heavy hard parsing still could end up in contention issues.

  12. Charlie says:

    I know why this sql returns a row: SELECT * FROM dual WHERE rownum IN (1,2,3); –> there is a row 1 in the result set.

    However, replace the parameters with a value GT 1 like SELECT * FROM dual WHERE rownum IN (999,888,777); –> there is no rownum other than 1 in the result set so “no rows returned” is correct.

    I’m I missing something?

    • Charlie says:

      Oh, I get it, you want the same hash value for 1, 2, 3…. variables in the statement. Yeah, you’re right…this makes it unusable. My goodness, this is so very minor with so many work-arounds to use a phrase like ” this “force matching” has one limitation which can make it almost useless for achieving plan stability ” is really seeing a mountain from a molehill.

      • Tanel Poder says:

        Do you happen to work for Oracle?
        You conveniently left out the last half of my sentence. Let me quote it again, in full:

        “However this “force matching” has one limitation which can make it almost useless for achieving plan stability in databases where the applications use dynamically generated SQL.

        See the last part? Dynamically generated SQL statements – from applications / code generators / etc. Often the code generators are 3rd party apps which you can’t change yourself – and they generate multiple IN-lists in different parts of the query, with hundreds or even thousands of elements in the IN-list. Sometimes with 345 elements, sometimes with 346, sometimes with 344, sometimes with 900 elements. Multiple inlists with variable number of elements.

        Now, you will get a phone call at 3am from 1st line support about bad batch job performance. You figure it out and create a FORCE_MATCHING profile to fix this SQL (one of those workarounds you mentioned). Next morning 3am, another phone call from 1st line support – about the same batch job! You look into it – the SQL is pretty much the same, but with a different number of dynamically generated IN-list elements – so your previous SQL profile does not help! I hope you see it now.

        And regarding “so many workarounds”, that’s what my blog entry was about – saying that if you get hit by this problem of SQL plan stability issues coming back regarless of a SQL profile, you know why this has happened and you know about the workarounds (which all require you to change code).

  13. max says:

    Hi Tanel,

    Just a quick Note on this…You can correct me if i am wrong…below is a quote from your post….

    ” Oracle replace any literals with system-generated bind variable placeholders and then calculating the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage.
    Also, the same mechanism (of replacing literal values with bind variable placeholders before calculating the SQL hash value for library cache lookup) can be used for enforcing a SQL profile for SQL statements which differ by literal values in them. You just accept a SQL profile using DBMS_SQLTUNE.ACCEPT_PROFILE( …, FORCE_MATCH=>TRUE).”

    You mentioned that same mechanism can be used for enforcing a SQL profile…but this would not eliminate the Hard parsing part….i ran a quick test where the create a SQL profile for a statement and executed that same SQL (with different literal values)…yes it did use the profile but always kept going through a HARD parse…so this(SQL PROFILE) would NOT eliminate the hard parsing issue….

    any ideas how to fix hard parsing issue(as code cannot be changed) and we are hesitant to turn on cursor_sharing parameter at DB level(we tested it in dev and it was a diaster)….is there anyway to not parse a SQL statement over and over again if EXACT_MATCHING_SIGNATURE is same(becoz of literal values)….

    • Tanel Poder says:

      These features solve a different problem. CURSOR_SHARING = FORCE allows you to avoid hard parses even when your application is using literal values – so it’s a hard parsing CPU usage and shared pool memory/latch scalability workaround feature. The SQL Profiles are SQL plan optimization features, nothing to do with reducing hard parsing counts.

      The SQL Profile allows you to influence the optimization whenever it happens, hopefully towards a better plan. It will kick in whenever a hard parse happens. If you are not using bind variables, you should use FORCE_MATCH = TRUE, as the SQL_ID you created the profile for would be different from all the SQL_IDs that are computed for all your different query texts – and the profile won’t get used.

      Long story short – only cursor_sharing = FORCE allows you to avoid excessive hard parses of code not using bind variables (leaving SIMILAR out as it’s deprecated). If you use CURSOR_SHARING = FORCE and create a profile for the query SQL_ID which is already transformed with the FORCE logic, you do not need FORCE_MATCH (as the SQL_ID is always the same).

      • max says:

        Thanks Tanel, Quick question. If lets say i do put a profile in place and use force_match=true i understand it will go through a hard parse but if lets say its a 2 table join and we are using SQL Profile on it…..i believe there will be some less work done during hard parse due to profile….but how much of the reduction percent wise are we talking here? Are we talking about 40%-50% reduction during the hard parse process or something less?

        • Tanel Poder says:

          While the actual hard parsing operation may take less CPU due to forcing optimizer to follow a narrower “optimization space”, you still need to find and allocate memory from shared pool for your cursor (possibly flushing out & invalidating many other objects) etc. So I wouldn’t expect your shared pool latch contention and the SP memory allocation/flushing related CPU usage go away. So I don’t think things would go 40-50% faster… your frequently executed queries (executed many times per second) should use bind variables regardless of any profiles you might have in place.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>