Adaptive cursor sharing

This post was motivated by a recent discussion I had where I mentioned that Adaptive Cursor Sharing (ACS) does not work with embedded SQL in PL/SQL, but when looking for an existing post realized that I didn’t find any good and freely available examples of that limitation of ACS.

Therefore I decided to put together this post that outlines what ACS is supposed to do, its inherent limitations and finally shed some light on the specific limitation that started its life with the description that “it does not work with embedded SQL in PL/SQL”.

Adaptive Cursor Sharing (ACS) has been introduced in Oracle 11g to address the potential threat of cursor sharing and bind variables – yes, you have read correctly: The usage of bind variables can be a possible threat if different values of bind variables are executed using the same execution plan, but actually would require different execution plans to prevent poor performing executions.

So ACS allows Oracle to detect such problems and selectively “unshare” cursors, which means that Oracle creates one or more additional child cursors for different (ranges) of bind values.

However, there are a couple of limitations to this strategy.


Data recovery broken hard drive Let’s have a look at a working example of ACS. Database xe Therefore I run the following script against database versions 11.1.0.7 and 11.2.0.2:

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

———————————————————————————————————————————————-

———————————————————————————————————————————————-

———————————————————————————————————————————————-

So I intentionally use bind variables in a dangerous way here: The “object_id > :x” predicate is a potential threat – depending on the value of 😡 the usage of the index on OBJECT_ID can be a very good or a very bad idea.

1. Database yml mysql ACS is not limited to the typical “skewed column data” / “histogram” case. 5 database is locked We can see that I explicitly did not create any histograms (method_opt “for all columns size 1”), still ACS marked the cursor as “BIND_SENSITIVE” and eventually created a second child cursor with a more appropriate execution plan for the bind value, because it detected the “expression > :x” predicate.

2. Database fundamentals However, and this one of the most important aspects, for ACS to detect the problem, it first has to go wrong at least once. Database concepts As you can see from the example it was only the second execution using the “bad” bind value that created the child cursor. Database icon So depending on how “wrong” things can go – think of a more complex execution plan including multiple joins – it might already be “too late” for your application: If for example a query is not going to return in a reasonable amount of time your application might effectively be “broken” even if ACS might be able to “fix” the problem at the next execution…

3. Database versioning Another important point to consider is that the ACS information is not stored persistently but only exists in the Shared Pool. Database 2013 This means, if the information is aged out of the Shared Pool for whatever reason, the same mistake has to be made again for ACS to detect the problem. Database cursor So if your problematic cursors age out of the Shared Pool it will potentially go “wrong” again with the next execution before ACS can come for help.

From this you can see that – although ACS is a great feature – it is not a “Silver Bullet” for all possible cases. Database list You still might need to help your database for those special cases where ACS is not able to support your application properly. Database queries must be How you can help your database? More on that later.

Now let’s have a look at the point that ACS “does not work with embedded SQL in PL/SQL”. Database journal Therefore I put together the following script that in principle does the same as the first script, but this time uses the most commonly used techniques in PL/SQL to execute SQL:

1. Data recovery boston It can be seen that ACS does not seem to work with several of the used variations: Both the static and the dynamic SQL using EXECUTE IMMEDIATE and binds do not create a second child cursor and re-use the same execution plan for all ten executions. Database connection Although the cursor has been marked as BIND_SENSITIVE, it has not been marked as BIND_AWARE.

Both execution variants also share the fact that there seems to be a PL/SQL optimization in place that allows to keep the cursors open – there is only a single PARSE call recorded in V$SQL for this SQL statement, although we loop through the same code ten times and therefore should in principle see as many parse as execution calls.

You would even see the same optimization in place when explicit cursors were used with explicit OPEN / FETCH / CLOSE calls: Albeit the explicit CLOSE you would still see only a single PARSE call for the statement – PL/SQL holds the cursor open for maximum performance and scalibility.

2. S memo data recovery Another interesting side note is that the new feature “Cardinality Feedback” that has been introduced with Oracle 11.2 seems also to be not working in these examples, but this is definitely something for another post.

“Cardinality Feedback” allows Oracle to compare the cardinality estimates of the cost-based optimizer with the actual cardinalities at execution time and perform “instant tuning” by automatically adding OPT_ESTIMATE and other hints to the statement to correct the cardinality estimates and do a re-optimization of the “tuned” statement. Database structure Again this information only resides in the Shared Pool and will be potentially aged out like the ACS information.

3. Data recovery iso The clear winner in terms of both elapsed and performed logical I/O is the third variant that creates two different cursors using literals. Iphone 6 data recovery software For both cursors the optimal execution plan has been found and nothing had to go “wrong” first to be fixed afterwards, therefore the total elapsed time and number of buffer gets is significantly lower than in the other cases. Cpu z database This gives us already a hint regarding the point “How can we help the database in such cases?”

Note also that although the number of buffer gets is significantly lower compared to the other cases, the elapsed time is not that much different, which is another example that buffer gets are not necessarily a good indicator for the overall work performed. Data recovery kickass In this particular case a lot of time is spent on writing and reading from the temporary tablespace for the WINDOW SORT operation, and since this operation has more or less to do the same work in all cases where the whole table needs to read in whatever efficient or inefficient way, the overall runtime does not differ as dramatically as the number of buffer gets. A database can best be described as You could see this additional work in this example by checking the columns DISK_READS and DIRECT_WRITES from V$SQL and of course by checking the ASH / extended SQL trace profile.

4. Os x database The fourth variant using the dynamic SQL with OPEN obviously does not benefit from the PL/SQL optimization: V$SQL clearly shows that there have been as many PARSE calls as EXECUTE calls. Database field Interestingly in this case ACS worked and created a second child cursor. Data recovery diy This gives us already a hint what seems to prevent ACS from working.

The shown behaviour of ACS not working is officially documented in My Oracle Support, however not available in the Knowledge Base but only as bug number 8357294: ADAPTIVE CURSOR SHARING DOESN’T WORK FOR SQL FROM PL/SQL. Database transaction It is interesting to note that the very detailed bug description does not mention a fix release or patch, but does mention that setting “session_cached_cursors” to 0 allows ACS again to work.

This script simply runs the same SQL that we already know sufficiently often to ensure that it gets cached in the session cursor cache by executing it at least three times. Data recovery mac hard drive Then in one of the next executions the “bad” bind values is used to see if the caching in the session cursor cache somehow prevents ACS from kicking in. H2 database tutorial Here is the result, this time from 11.1.0.7 (but again I got the same from 11.2.0.2):

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

——————————————————————————————————————————————————–

———————————————————————————————————————————————-

———————————————————————————————————————————————-

———————————————————————————————————————————————-

So we can see that from the third execution on we seem to get a hit in the session cursor cache (increase from 2 to 3), but still ACS seems to work properly, although this time it had to go wrong twice before ACS created the second child cursor. Database interview questions We can also see that we still get as many PARSE calls as executions, so the session cursor cache does not avoid the parsing as outlined above.

All this seems to suggest that the ACS (and potentially the “Cardinality Feedback”, too) code is only triggered by a PARSE call and not evaluated as part of an EXECUTE call.

But if this was the case, then by no means the problem would be limited to PL/SQL and its clever optimizations that try to hold cursors open to avoid repeated PARSE calls.

Actually one of the core principles that we are told to make an application as scalable as possible is to parse only once but execute many times. R studio data recovery free download So let’s try to simulate such a well behaving application with this simple Java code snippet that accesses the database via JDBC:

This (by no means elegant or clever) piece of Java code again tries to do the same as our previous examples: It executes the known query ten times but behaves well since it parses only once and executes ten times using the two different bind values as already done before.

I only tested this with the JDBC thin driver that comes with Oracle 11.2.0.2 but not with the thick driver. Data recovery bad hard drive However, since I assume that 95%+ of all Java applications out there actually use the thin driver this probably covers the most relevant case.

The results correspond to those of the PL/SQL examples with the “keep cursors open” optimization enabled: Neither ACS nor “Cardinality Feedback” kick in, and the SQL is executed every time using the same execution plan. Database field definition The SQL is only parsed once, so the problem is not really limited to PL/SQL, but to every application that tries to avoid parse calls.

– Things have to go wrong first before ACS will be able to provide a fix. Data recovery windows 7 Depending on how “wrong” things can go, this might mean that ACS is actually not able to provide an efficient fix for certain cases

If you have a case where you need to help (or “co-operate” as Jonathan Lewis calls it in his brilliant presentation of the same name) your database, the simplest strategy is to use literals instead of bind variables. Nexus 4 data recovery If you can’t do this due to the issues introduced by this strategy (excessive hard parses, increased CPU and Shared Pool usage, library cache contention etc.) a more complex strategy – if applicable – is to “know your data” and generate a minimum amount of cursors that try to cover the cases where a different execution plan is desired.

Notice that using comments to make the SQL unique might not lead to the expected result since there are cases/environments where comments are stripped prior to execution.

Update: From a comment by Sokrates below that for some reason didn’t make to the comments section but also doesn’t show up in the “Spam” comments of the blog control panel I see that above description “dummy hint” might need a bit more explanation.

By “dummy hint” I mean to say that you should use hint syntax rather than normal comment syntax to make the SQL unique, because the normal comment might get filtered out whereas the comment using hint syntax is supposed to be kept. Database version 706 By “dummy hint” I mean any invalid hint comment in order to prevent any influence on the optimizer, for example /*+ CASE_A */ rather than /* CASE_A */

banner