Sunday, May 29, 2011

Histograms on Varchar2(32)

Oracle will gather statistics on first 32 bytes of string data - if you have a string that has lots of repeating long strings on the front of it - the statistics are not going to be very good.

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

rajesh@10GR2>
rajesh@10GR2> create table t
  2  nologging
  3  as
  4  select rownum as x,
  5        rpad('*',26,'*')||to_char(rownum,'fm00000') as y,
  6        sysdate as z,
  7        a.*
  8  from all_objects a;

Table created.

Elapsed: 00:00:07.86
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.39
rajesh@10GR2>
rajesh@10GR2> 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:01.85
rajesh@10GR2>
rajesh@10GR2> column column_name format a20;
rajesh@10GR2> column count(*) format 9999999;
rajesh@10GR2>
rajesh@10GR2> 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.15
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',26,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    54 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    54 |     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
        288  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>

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

rajesh@10GR2> drop table t purge;

Table dropped.

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

Table created.

Elapsed: 00:00:07.80
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> 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:01.95
rajesh@10GR2>
rajesh@10GR2> 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.14
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',27,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    55 |     4   (0)| 00:00:01
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    55 |     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
        303  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>

On Varchar2(33) bytes of String datas - Estimated Cardinality varies from Actual Cardinality.

rajesh@10GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> 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:07.79
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.51
rajesh@10GR2>
rajesh@10GR2> 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:01.03
rajesh@10GR2>
rajesh@10GR2> 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.12
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',28,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    10 |   560 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    10 |   560 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |    10 |       |     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
        304  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>
rajesh@10GR2>


On Varchar2(34) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.

rajesh@10GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> 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:07.91
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> 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.92
rajesh@10GR2>
rajesh@10GR2> 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.12
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',29,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 |  5700 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   100 |  5700 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |   100 |       |     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
        305  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>

On Varchar2(35) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.

rajesh@10GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.28
rajesh@10GR2>
rajesh@10GR2> 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:07.97
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.45
rajesh@10GR2>
rajesh@10GR2> 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.93
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAME          COUNT(*)
-------------------- --------
Y                          59
Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',30,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.26

Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   977 | 56666 |    30   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   977 | 56666 |    30   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |   977 |       |     9   (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
        306  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>

On Varchar2(36) bytes of String datas - Estimated Cardinality still varies from Actual Cardinality.

rajesh@10GR2> drop table t purge;

Table dropped.

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

Table created.

Elapsed: 00:00:08.04
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> 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.92
rajesh@10GR2>
rajesh@10GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAME          COUNT(*)
-------------------- --------
Y                           6

Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',31,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.34

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

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

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>


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

rajesh@10GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.26
rajesh@10GR2>
rajesh@10GR2> 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:07.84
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create index t_ind on t(y) nologging;

Index created.

Elapsed: 00:00:00.40
rajesh@10GR2>
rajesh@10GR2> 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:01.01
rajesh@10GR2>
rajesh@10GR2> 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.20
rajesh@10GR2>
rajesh@10GR2> variable y varchar2(40);
rajesh@10GR2> exec :y := rpad('*',32,'*')||to_char(55,'fm00000');

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.43

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 58649 |  3436K|   284   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 58649 |  3436K|   284   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("Y"=:Y)

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

rajesh@10GR2>
rajesh@10GR2> set autotrace off;
rajesh@10GR2>

This doesnot mean Histograms are useless, They are very usefull in most datas like number, dates and Strings less than 33 Bytes of datas. If you have longer strings, you will typically be performing a text search on them with Oracle Text and the CONTAINS operator.

No comments:

Post a Comment

Post a Comment