Re: sanity check - histograms, cursor sharing & bind var peeking

From: Jo Holvoet <jo.holvoet_at_gmail.com>
Date: Fri, 1 Jul 2016 12:57:12 +0200
Message-ID: <CA+4k+Su+ueAXm0okR0s1WQ1OHpTtsEOrpK7OfeuTE1QADf0_wQ_at_mail.gmail.com>



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_at_soocs.de> 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_at_T10DB:139> create table t1 (a number, b number);
> TEST_at_T10DB:139> insert into t1 values (1,10);
> TEST_at_T10DB:139> insert into t1 values (2,10);
> TEST_at_T10DB:139> insert into t1 values (3,10);
> TEST_at_T10DB:139> insert into t1 values (4,10);
> TEST_at_T10DB:139> insert into t1 values (5,10);
> TEST_at_T10DB:139> insert into t1 values (6,10);
> TEST_at_T10DB:139> insert into t1 values (7,10);
> TEST_at_T10DB:139> insert into t1 values (8,10);
> TEST_at_T10DB:139> insert into t1 values (9,10);
> TEST_at_T10DB:139> insert into t1 values (10,0);
> TEST_at_T10DB:139> commit;
>
> TEST_at_T10DB:139> exec dbms_stats.gather_table_stats(USER,'T1',method_opt=>
> 'for all columns size skewonly');
>
> TEST_at_T10DB:139> alter session set cursor_sharing=SIMILAR;
>
> TEST_at_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_at_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_at_T10DB:139> alter system flush shared_pool;
> TEST_at_T10DB:139> alter session set "_optim_peek_user_binds"=FALSE;
>
> TEST_at_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_at_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: _at_OracleSK
>
> > Stefan Koehler <contact_at_soocs.de> hat am 1. Juli 2016 um 10:28
> geschrieben:
> >
> >
> > Hey Jo,
> > not quite sure about that old 10.2.0.4 stuff (only have 10.2.0.5 as the
> oldest version here), but ...
> >
> > 1) By disabling bind peeking you will prevent the optimizer from peeking
> at the initial bind value and it will not use the histogram to determine
> > the
> > cardinality estimates for the statement. Instead the Optimizer will
> assume a uniform distribution of rows across the distinct values in a
> 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
> )
> >
> > 2) You are using "cursor_sharing = similar", so your literals are not
> necessarily replaced by binds and so bind peeking does not matter at all in
> > these cases. Cursor_sharing = similar. There are two different cases for
> this:
> > * There is a histogram on the job column. In this case, literal
> replacement will not take place. The presence of a histogram
> > indicates that the column is skewed, and the optimal plan may depend on
> the literal value. Hence, the optimizer sees the query as:
> > select * from employees where job = 'Clerk' and subsequent executions
> with a different literal will not necessarily use the same
> > plan.
> > * There is no histogram on the job column. This indicates that the
> column is not skewed, and the optimizer is likely to choose the
> > same plan no matter the literal, so literal replacement takes place.
> > (Source:
> https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
> )
> >
> >
> > So verify in your environment if literal replacement is not taking place
> (as it should be) in case of histograms - if this is true your histograms
> > have impact due to "cursor_sharing = similar".
> >
> > Best Regards
> > Stefan Koehler
> >
> > Freelance Oracle performance consultant and researcher
> > Homepage: http://www.soocs.de
> > Twitter: _at_OracleSK
>

-- 
mvg/regards,

Jo

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 01 2016 - 12:57:12 CEST

Original text of this message