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.
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.
