Partition stats usage with Bind peeking disabled

Last week, I was working on a package, which is used to collect stats.

I realized we collect stats of all partioned tables,indexes at both partition and global level.

While working on that package, i was pondering when will we actually use partition/local level stats (over global stats) in real-world scenario as most of our "where" clause 

values are passed using bind variables. How will optimizer will come to know which partition (stats) to use because value passed will checked run time except in the case when it 

is run for the first time and Bind peek happens.

A bit confused with this I performed a test, where my test case was inspired by David Aldridge's blog post (however i conducted a different set of tests).

I first checked the difference in 10053 trace in sqls where we pass literals Vs bind variables - when bind peek is enabled (default - enabled, but not in our case).

Test case:

============

drop table test_par

/

create table test_par

   (

   col1 number not null,

   col2 number not null

   )

nologging compress pctfree 0

partition by range (col1)

(partition p1_to_4 values less than (5),

partition p5 values less than (6)

)

/

insert /*+ append */ into test_par

select mod(rownum,4)+1,rownum

from dual

connect by level <= 100000

/

insert into test_par values(5,10);

commit;

create index idx02_test_par

on test_par (col2)

local nologging

/

begin

DBMS_STATS.GATHER_TABLE_STATS (

   ownname          => user,

   tabname          => 'test_par',

   partname         => null,

   estimate_percent => 100,

   block_sample     => false,

   method_opt       => 'for all columns size 1',

   degree           => null,

   granularity      => 'ALL',

   cascade          => true,

   stattab          => NULL, 

   statid           => NULL,

   statown          => NULL,

   no_invalidate    => FALSE);

end;

/

So - i have 75000 rows in partition p1_to_4 and 1 row in p5 

SQL> select count(*),col1 from test_par partition(p1_to_4) group by col1;

  COUNT(*)       COL1

---------- ----------

     25000          1

     25000          2

     25000          4

     25000          3

SQL>  select count(*),col1 from test_par partition(p5) group by col1;

  COUNT(*)       COL1

---------- ----------

         1          5

Now we have the set up ready, we have to enable the trace and check which stats are used when.

Case 1: - Bind peek enable. I use literal value to query table and see which stats are used.

SQL> alter session set "_optim_peek_user_binds"=true;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> select * from test_par where col1=2 and col2=5;

Now checking the trace file, we see:

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [0]  -- Do you see Partition stats getting used?

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [0]

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

***************************************

So, with Bind peek enabled - we use partition stat (when we should) when literals are used

Case 2: Bind peek enable. I use Bind value to query table and see which stats are used.

SQL> alter session set "_optim_peek_user_binds"=true;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> variable lcol1 number;

SQL> variable lcol2 number;

SQL> execute :lcol1:=2;

SQL> execute :lcol2:=5;

SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;

Trace file:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [0]  -- Here again, partition stats getting used

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [0]

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

***************************************

Since this was the first time, we used this sql, Bind peek had to happen.

This is confirmed by following section of trace file:

*******************************************

Peeked values of the binds in SQL statement

*******************************************

kkscoacd

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0

  kxsbbbfp=2a973589d8  bln=22  avl=02  flg=05

  value=2

 Bind#1

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24

  kxsbbbfp=2a973589f0  bln=22  avl=02  flg=01

  value=5

CASE 3: Bind peek enabled - I use Bind value to query table and see which stats are used. -- This will be 2nd time - so this time i dont expect peeking to happen.

SQL> alter session set "_optim_peek_user_binds"=true;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> variable lcol1 number;

SQL> variable lcol2 number;

SQL> execute :lcol1:=5;

SQL> execute :lcol2:=10;

SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;

Trace file:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [0] -- Part Stats .. again

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [0]

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

***************************************

In this case - we still use old execution path and old stats - which is agony of 10g. 

We had only one row for this partition and we ended up using INDEX access when direct access would be really fast.

I heard about Adaptive cursor sharing in 11g will avoid this, but still to run tests on it.

Now we will run the same tests by keeping the bind peeking disabled (as in my environment)

CASE 4: Bind peek disabled - we use literals

SQL> alter system flush shared_pool;

SQL> alter session set "_optim_peek_user_binds"=false;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> select * from test_par where col1=5 and col2=10;

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [1] -- Picked the right stats of right partition

    #Rows: 1  #Blks:  1  AvgRowLen:  6.00

    #Rows: 1  #Blks:  1  AvgRowLen:  6.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [1]

    LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00

    LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00

***************************************

Literals - as seen knows where to go.

CASE 5: Bind peek disabled - we use binds

SQL> variable lcol1 number;

SQL> variable lcol2 number;

SQL> execute :lcol1:=5;

SQL> execute :lcol2:=10;

SQL> alter session set "_optim_peek_user_binds"=false;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;

Trace file:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  (Using composite stats)  -- These are global stats 

    #Rows: 100001  #Blks:  75  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2

    USING COMPOSITE STATS

    LVLS: 1  #LB: 223  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 150.00

***************************************

So - the conclusion is :

When we have BIND peeeking disabled, and with usage of bind values - we are most likely (any one of you could come up where above doesnt hold good) NOT using partiton stats.