Thanks for the extensive test case Stefan. I'll definitely run it on
10.2.0.4 but I have no doubt it will confirm your findings.
thanks & regards
Jo
On Fri, Jul 1, 2016 at 10:59 AM, Stefan Koehler <contact@xxxxxxxx> wrote:
Hey Jo,
i just created a tiny test case on my 10.2.0.5. "cursor_sharing = similar"
evaluation kicks in after bind peeking. Histograms do not matter at all in
your case as you have disabled bind peeking. Please test on your 10.2.0.4
as well for verification.
Here is the test case
-----------8<--------------------
TEST@T10DB:139> create table t1 (a number, b number);
TEST@T10DB:139> insert into t1 values (1,10);
TEST@T10DB:139> insert into t1 values (2,10);
TEST@T10DB:139> insert into t1 values (3,10);
TEST@T10DB:139> insert into t1 values (4,10);
TEST@T10DB:139> insert into t1 values (5,10);
TEST@T10DB:139> insert into t1 values (6,10);
TEST@T10DB:139> insert into t1 values (7,10);
TEST@T10DB:139> insert into t1 values (8,10);
TEST@T10DB:139> insert into t1 values (9,10);
TEST@T10DB:139> insert into t1 values (10,0);
TEST@T10DB:139> commit;
TEST@T10DB:139> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>
'for all columns size skewonly');
TEST@T10DB:139> alter session set cursor_sharing=SIMILAR;
TEST@T10DB:139> select * from t1 where b=10;
SQL_ID c76f0az9cchf8, child number 0
-------------------------------------
select * from t1 where b=:"SYS_B_0"
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 9 | 45 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
TEST@T10DB:139> select * from t1 where b=0;
SQL_ID c76f0az9cchf8, child number 1
-------------------------------------
select * from t1 where b=:"SYS_B_0"
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 5 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
TEST@T10DB:139> alter system flush shared_pool;
TEST@T10DB:139> alter session set "_optim_peek_user_binds"=FALSE;
TEST@T10DB:139> select * from t1 where b=10;
SQL_ID c76f0az9cchf8, child number 0
-------------------------------------
select * from t1 where b=:"SYS_B_0"
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 5 | 25 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
TEST@T10DB:139> select * from t1 where b=0;
SQL_ID c76f0az9cchf8, child number 0
-------------------------------------
select * from t1 where b=:"SYS_B_0"
Plan hash value: 3617692013
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 5 | 25 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
-----------8<--------------------
As you can see as soon as you have disabled bind peeking literal
replacement still takes place, but histogram is not evaluated anymore (as
no bind
peeking) and plain NDV (in my case 1/2*<num_rows>) is used. No additional
child cursors are created. Bind peeking is essential in case of
"cursor_sharing=SIMILAR" to use the histogram information and create a
child cursor for each value.
Also found an Oracle CBO blog post about 10g, which confirms my test case
as well:
"In this case the statement with hundreds of children falls into the last
category in the above table, having CURSOR_SHARING set to SIMILAR and a
histogram on the columns used in the where clause predicate of the
statement. The presence of the histogram tells the optimizer that there is
a data
skew in that column. The data skew means that there could potentially be
multiple execution plans for this statement depending on the literal value
used. In order to ensure we don't impact the performance of the
application, we will peek at the bind variable values and create a new
child cursor
for each distinct value. Thus ensuring each bind variable value will get
the most optimal execution plan." (Source:
https://blogs.oracle.com/optimizer/entry/whydo_i_have_hundreds_of_child_cursors_when_cursor_sharing_is_set_to_similar_in_10g
)
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Stefan Koehler <contact@xxxxxxxx> hat am 1. Juli 2016 um 10:28geschrieben:
oldest version here), but ...
Hey Jo,
not quite sure about that old 10.2.0.4 stuff (only have 10.2.0.5 as the
at the initial bind value and it will not use the histogram to determine
1) By disabling bind peeking you will prevent the optimizer from peeking
theassume a uniform distribution of rows across the distinct values in a
cardinality estimates for the statement. Instead the Optimizer will
column and
will use NDV to determine the cardinality estimate. (Source:http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
)
necessarily replaced by binds and so bind peeking does not matter at all in
2) You are using "cursor_sharing = similar", so your literals are not
these cases. Cursor_sharing = similar. There are two different cases forthis:
* There is a histogram on the job column. In this case, literalreplacement will not take place. The presence of a histogram
indicates that the column is skewed, and the optimal plan may depend onthe literal value. Hence, the optimizer sees the query as:
select * from employees where job = 'Clerk' and subsequent executionswith a different literal will not necessarily use the same
plan.column is not skewed, and the optimizer is likely to choose the
* There is no histogram on the job column. This indicates that the
same plan no matter the literal, so literal replacement takes place.https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
(Source:
)
(as it should be) in case of histograms - if this is true your histograms
So verify in your environment if literal replacement is not taking place
have impact due to "cursor_sharing = similar".
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
