Adaptive Cursor Sharing - Friend or Foe?
By Jaromir D.B. Nemec
Let's recapitulate our story
thus far.
Bind variables solved the
problem of extensive parsing of similar SQL statements but introduced a new
problem with selectivity estimation.
Bind Variable Peeking solved the problem with selectivity estimation but introduced a new problem with (inappropriate) cursor sharing.
Adaptive Cursor Sharing was
introduced to finally solve all the problems?
Let's set up a typical skew table as a playground -
see Listing 1
The values stored in the
column status of our test table T_ADAPT are shown in the following table.
ENDPOINT_VALUE ENDPOINT_NUMBER
----------------------
----------------------
1 10
2 110
3 2000110
Let's start the discussion
with querying the table with a bind variable leading to a full table scan
(status = 3). After two executions which are executed using FTS we switch the
bind variable to a value that should lead to index range scan (status=1). The
autotrace option is activated to observe the used execution plans of the
queries - see Listing 2.
What is shown in Listing 2 is
somewhat confusing. All execution plans
reported by autotrace are identical and use full table scan.
The first hint that something
is wrong with the execution plan shown is the elapsed time of the statements –
the first three statement are finished in more than 3 seconds, the next two in
less than one second.
The final proof that the
execution plan changed is provided by the view V$SQL. It shows two cursors: one
with FTS, the second one with index range scan. (Remember the script
flushed shared pool before the start).
The simples explanation is
that the autotrace simple miss to select the right child cursor after the
activation of the adaptive cursor sharing – this is probably causing by the
fact, that autotrace uses a second session to report the statistics.
This leads us to the…
In the next step we repeat
the same statements but we will use the dbms_xplan.display_cursor
to observe the execution plan - see
Listing 3. The parameters null,null show reliable the
plan of the last cursor executed by the current session.
As expected the fourth and
fifth statement in Listing 3 was performed with the
execution plan using index range scan. The third execution should still used
the previous plan with FTS. But under the cover prepared the switch to the new
cursor.
Additional information is
provided in the V$SQL_CS family of the fixed views; which is shon at the end of
the script.
In the view v$sql_cs_selectivity we see the
selectivity of the predicate of 0.000005 (for cursor number 1). This correspond
exactly the selectivity of 10/ 2000110.
In the view v$sql_cs_statistics
are the monitored samples of the executions; the total execution count of two
is a bit less then the actual number of executions. The note [1] explains that only sample of
execution is monitored. So at least we see that two different sets of bind
variables were used so far.
The v$sql_cs_histogram
shows the exact count of 5 executions; three in child_number zero and two in
child_number one. The exact mapping of the bind variable values to bucket is
not self explanatory for me.
So far, so good! This is a
typical result of a presentation of adaptive cursor sharing as a new feature.
Let's move our attention to a
PL/SQL implementation of the same access pattern.
The script ac_plsql.sql repeats the very exact statements only using
PL/SQL.
SQL> declare
2
v_num_var number;
3
v_ret number;
4
begin
5 --
set variable leading to full table scan
6
v_num_var := 3;
7
select /*+ AC_test3 */ count(vpad) into v_ret from t_adapt where status
= v_num_var;
8 --
repeat FTS
9
select /*+ AC_test3 */ count(vpad) into v_ret from t_adapt where status
= v_num_var;
10 --
now swith the variable to the plan with index range scan
11
v_num_var := 1;
12
select /*+ AC_test3 */ count(vpad) into v_ret from t_adapt where status
= v_num_var;
13 --
and repeat
14 select /*+ AC_test3 */ count(vpad) into
v_ret from t_adapt where status = v_num_var;
15
select /*+ AC_test3 */ count(vpad) into v_ret from t_adapt where status
= v_num_var;
16
end;
17 /
PL/SQL procedure
successfully completed.
The results are shown in
Listing 4 - no surprise here; the same behaviour can be observed as in the
previous case – three execution with FTS plan and two execution via index range
scan.
But wait - this is not the
typical PL/SQL coding – the more common usage pattern is to use a LOOP
statement to repeatedly execute the same statement.
SQL> declare
2
v_num_var number;
3
v_ret number;
4
begin
5 --
set variable leading to full table scan
6
v_num_var := 3;
7 --
8
for i in 1..5 loop
9 select /*+ AC_test3 */ count(vpad) into v_ret
from t_adapt where status = v_num_var;
10 --
after 2nd run swith to index
11 if i>=3 then
12 v_num_var := 1;
13 end if;
14
end loop;
15
end;
16 /
PL/SQL procedure
successfully completed.
Listing 5 shows the result of this run. Contrary
to the previous run - no adaptive cursors sharing was activated. The V$SQL view shows only one cursor with
all five executions.
The v$sql_cs_histogram
view correctly shows total 5 execution in two different BV value bucktes, but
it was apparently not enough to deploy the adaptive cursor sharing.
Lets give Oracle a second
chance and repeat out test case with a little more processing in a loop (say
one five hundred execution in a loop instead of five so far).
OK, after several cups of
coffee (and a bottle of a beer - Elapsed: 00:48:49.09) the script finished only to
see in Listing 6 that nothing was changed - no
adaptive cursor sharing happens - the same sub optimal plan is used again and
again in the loop.
The last resort that can be
tried is to invalidate the cursor to force a re-parse of the statement.
We will use DBMS_STATS to
simulate invalidate the cursor.
begin
dbms_stats.gather_table_stats(
ownname => USER,
tabname => 'T_ADAPT',
estimate_percent => 100,
method_opt => 'FOR COLUMNS SIZE 254
STATUS',
no_invalidate => false
);
end;
/
So we re-run the script from
Listing 6 and after few second we run from other session the gather_table_stats
procedure to invalidate the cursor.
This works fine, the
statements immediately switches to the index range scan and the loop is
instantly finished. The results are shown in the Listing 7.
So if get in such situation
that a improper plan is used for a statement in a PL/SQL loop and we are lucky
the wrong cursor will be invalidated and the new parse resolves the problem.
Ironically well dimensioned
shared pool with no invalidation will conserve the wrong plan.
And above all our workaround to get the plan switch has nothing to do with adaptive cursor sharing. If we kick out the cursor from the shared pool it must be hard parsed on the next execution.
This leads us to the..
Lesson 2 - similar to the BV peeking the adaptive cursor sharing could fire at an unpredictable occasion.
Let’s finally check what can be expected while accessing the database using the JDBC connection.
We set up a script in Groovy
- see [2] to quickly script a JDBC code.
The JDBC drive used has
version 11.2.0.1.0.
Similar to the PL/SQL
processing we prepare the JDBC statement once (using prepareStatement) and
repeatedly execute it with different bind variable values.
The relevant part of the
script is shown below.
def stmt = con.prepareStatement('select
/*+ AC_test2 */ count(vpad) cnt from t_adapt where status = ?')
stmt.setInt(1,3) // full scan
boolean isQuery = stmt.executeQuery()
if (isQuery) {
def rs = stmt.getResultSet()
while(rs.next())
{
println "cnt = ${rs.getInt(1)}"
}
rs.close()
}
stmt.setInt(1,3) // full scan
isQuery = stmt.executeQuery()
if (isQuery) {
def rs = stmt.getResultSet()
while(rs.next())
{
// println "cnt =
${rs.getInt(1)}"
}
rs.close()
}
100.times { // repeat
stmt.setInt(1,1) // index range
isQuery = stmt.executeQuery()
if (isQuery) {
def rs = stmt.getResultSet()
while(rs.next())
{
// println "cnt = ${rs.getInt(1)}"
}
rs.close()
}
}
The Listing
8 shows the very same behaviour as for PL/SQL loop.
In V$SQL is only one cursor
after the execution.
This minas there was no
recovery from the bad execution plan within the re-use of the prepared
statement.
Lets recapitulate it as…
Lesson 3 - the unpredictable behaviour of adaptive cursor sharing is observed in JDBC as well
Except for the trivial case
in the SQL example (see Listing 3) the adaptive cursor
sharing did not deliver the expected
behaviour*). Is this a bad news?
Not really, a final lesson
could be formulated as follows: do
not use bind variables in statements that would switch the execution plans for
different bind values! This would make the adaptive cursor sharing
unnecessary.
*) Take this statement with care - this statement is
valid only for the Oracle version referenced above, only the statements used in
the example and for the particular data used.
Any generalisation is at your own risk!
References
[1] Metalink notes 740052.1;
836256.1
[2] programming language
Groovy http://groovy.codehaus.org/
6.8.2012
It was naïve from me to expect that autotrace will
show the real execution plan. Apparently autotrace uses EXPLAIN PLAN commend to
generate the execution plan; so not only adaptive cursor sharing but even a BV
peek can lead to a wrong display.
Let’s demonstrate this with a
script similar to the one in Listing 2. The only
difference is that the values for bind variables are switched; it means we
start with a statement leading to index range scan and expect that the plan
should switch to a FTS - see Listing 9.
Even the execution plan of
the first statement – where for sure no adaptive cursor sharing took place – is
wrong and identical with the one generated with the EXPLAIN PLAN statement.
This means that the Lesson 1
– though correct – has nothing to do with adaptive cursor sharing.
Thanks Christian Antognini
for pointing me to this behaviour.
Last revision 8.8.2012
Jaromir D.B. Nemec is a freelancer
specializing in data warehouse and integration solutions. He can be reached at http://www.db-nemec.com