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 :)
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? :-)
Yep I mentioned this too in the end of the mail. I would use it when there are lots of values in the list (which I didn’t want to keep in the PGA).
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
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
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.
Thanks, simple and obvious (now) .
Regards
GregG
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)
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…
Which db version is in your case?
Damir – 11.2.0.2
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.
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
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)
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�
Cool, thanks!
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.
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.
Mark, I will remember this :)
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.