Histograms on Varchar2(32) - 11GR2 Optimizer Improvement

This will be the prolongation of  previous post. Oracle 11GR2 Optimizer got improved and able to provide very accurate cardinality beyond 33 byte string.

On Varchar2(33) bytes of String datas - Estimated Cardinality matches with Actual Cardinality.

rajesh@ORA11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  nologging
  3  as
  4  select rownum as x,
  5        rpad('*',28,'*')||to_char(rownum,'fm00000') as y,
  6        sysdate as z,
  7        a.*
  8  from all_objects a;

Table created.

Elapsed: 00:00:05.70
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.43
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname =>'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     method_opt=>'for all indexed columns size 254' );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.79
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAME          COUNT(*)
-------------------- --------
Y                         255

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable y varchar2(40);
rajesh@ORA11GR2> exec :y := rpad('*',28,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain statistics;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select x,y,z
  2  from t
  3  where y = :y;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    56 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    56 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("Y"=:Y)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        567  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>
rajesh@ORA11GR2>

On Varchar2(34) bytes of String datas - Estimated Cardinality matches with Actual Cardinality.

rajesh@ORA11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  nologging
  3  as
  4  select rownum as x,
  5        rpad('*',29,'*')||to_char(rownum,'fm00000') as y,
  6        sysdate as z,
  7        a.*
  8  from all_objects a;

Table created.

Elapsed: 00:00:05.68
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.42
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname =>'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     method_opt=>'for all indexed columns size 254' );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.78
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAME          COUNT(*)
-------------------- --------
Y                         255

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable y varchar2(40);
rajesh@ORA11GR2> exec :y := rpad('*',29,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain statistics;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select x,y,z
  2  from t
  3  where y = :y;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    57 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    57 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("Y"=:Y)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        568  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>

On Varchar2(35) bytes of String datas - Estimated Cardinality starts variying with Actual Cardinality.
rajesh@ORA11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  nologging
  3  as
  4  select rownum as x,
  5        rpad('*',30,'*')||to_char(rownum,'fm00000') as y,
  6        sysdate as z,
  7        a.*
  8  from all_objects a;

Table created.

Elapsed: 00:00:05.60
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.45
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname =>'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     method_opt=>'for all indexed columns size 254' );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAME          COUNT(*)
-------------------- --------
Y                          72

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable y varchar2(40);
rajesh@ORA11GR2> exec :y := rpad('*',30,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain statistics;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select x,y,z
  2  from t
  3  where y = :y;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   351 | 20358 |    13   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   351 | 20358 |    13   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |   351 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("Y"=:Y)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        569  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>


On Varchar2(37) bytes of String datas - Estimated Cardinality varies with Actual Cardinality and produced wrong plan.

rajesh@ORA11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t
  2  nologging
  3  as
  4  select rownum as x,
  5        rpad('*',32,'*')||to_char(rownum,'fm00000') as y,
  6        sysdate as z,
  7        a.*
  8  from all_objects a;

Table created.

Elapsed: 00:00:05.70
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.48
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname =>'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     method_opt=>'for all indexed columns size 254' );
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.76
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAME          COUNT(*)
-------------------- --------
Y                           1

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> variable y varchar2(40);
rajesh@ORA11GR2> exec :y := rpad('*',32,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace traceonly explain statistics;
rajesh@ORA11GR2>
rajesh@ORA11GR2> select x,y,z
  2  from t
  3  where y = :y;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 35867 |  2101K|   428   (1)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| T    | 35867 |  2101K|   428   (1)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("Y"=:Y)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1540  consistent gets
          0  physical reads
          0  redo size
        571  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@ORA11GR2>
rajesh@ORA11GR2> set autotrace off;
rajesh@ORA11GR2>


This shows that Histograms in 11GR2 Optimizer are good to handle with 34 bytes of character data's where as 10G Optimizer is limited to 32 Bytes of character datas.