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.

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.

Enabling Stored Outlines

rajesh@ORA10GR2> create table t
  2  nologging
  3  as
  4  select rownum as x,
  5        rpad('*',80,'*')||mod(rownum,10) as id,
  6        a.*
  7  from all_objects a;

Table created.

Elapsed: 00:00:04.12
rajesh@ORA10GR2>
rajesh@ORA10GR2> create index t_ind on t(id) nologging;

Index created.

Elapsed: 00:00:00.45
rajesh@ORA10GR2>
rajesh@ORA10GR2> 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 1',
  7     cascade => false);
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@ORA10GR2>

I have a B*Tree index defined on a Varchar2(80) column, having B*Tree index beyond Varchar2(32) data's will not provide efficient histograms which leads to improper cardinality and poor access paths and plans.

rajesh@ORA10GR2> variable x varchar2(100);
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec :x := rpad('*',80,'*')||'11';

PL/SQL procedure successfully completed.

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

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4986 |   462K|   296   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  4986 |   462K|   296   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=:X)

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

rajesh@ORA10GR2>

It took 1300 IO's, The optimizer skiped using index because Histograms doesn't yeild proper cardinality. so poor cardinality = poor plan.

Now let's see what happens when we force using index to this query.

rajesh@ORA10GR2> select /*+ index(t,t_ind) */ x,id
  2  from t
  3  where id = :x;

no rows selected

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4986 |   462K|  1364   (1)| 00:00:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  4986 |   462K|  1364   (1)| 00:00:17 |
|*  2 |   INDEX RANGE SCAN          | T_IND |  4986 |       |    67   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:X)

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

rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;

One best way to Tie this Explain plan ( plan using index) to this query is using Stored Outlines.

rajesh@ORA10GR2> ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> alter session set optimizer_mode = first_rows;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> select x,id
  2  from t
  3  where id = :x;

no rows selected

Elapsed: 00:00:00.04
rajesh@ORA10GR2>
rajesh@ORA10GR2> ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA10GR2>
rajesh@ORA10GR2> alter session set optimizer_mode = all_rows;

Session altered.

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

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4986 |   462K|   296   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  4986 |   462K|   296   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=:X)

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

rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;

As you see these outlines are not used by optimizer, untill they are enabled either at session (or) instance level.

rajesh@ORA10GR2>
rajesh@ORA10GR2> alter session set use_stored_outlines=default;

Session altered.

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

no rows selected

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4986 |   462K|  1364   (1)| 00:00:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  4986 |   462K|  1364   (1)| 00:00:17 |
|*  2 |   INDEX RANGE SCAN          | T_IND |  4986 |       |    67   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:X)
Note
-----
   - outline "SYS_OUTLINE_11052622281360902" used for this statement

Statistics
----------------------------------------------------------
         35  recursive calls
        135  db block gets
         24  consistent gets
          0  physical reads
        596  redo size
        322  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2>

The other way to enable the outlines at Instance level is

rajesh@ORA10GR2> connect sys/oracle as sysdba
Connected.
sys@ORA10GR2>
sys@ORA10GR2> alter system set use_stored_outlines = default;

System altered.

Elapsed: 00:00:00.00
sys@ORA10GR2>
sys@ORA10GR2> connect rajesh/oracle
Connected
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec :x := rpad('*',80,'*')||'11';

PL/SQL procedure successfully completed.

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

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1376202287
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  4986 |   462K|  1364   (1)| 00:00:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  4986 |   462K|  1364   (1)| 00:00:17 |
|*  2 |   INDEX RANGE SCAN          | T_IND |  4986 |       |    67   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:X)
Note
-----
   - outline "SYS_OUTLINE_11052622281360902" used for this statement

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

rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;

But remember that once database is bounced, you have to enable the outline's either at session level (or) instance level. If not the optimizer will not use the outlines.

rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2> connect sys/oracle as sysdba
Connected.
sys@ORA10GR2>
sys@ORA10GR2>
sys@ORA10GR2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA10GR2>
sys@ORA10GR2> startup;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             218106108 bytes
Database Buffers          385875968 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
sys@ORA10GR2>
sys@ORA10GR2> connect rajesh/oracle
Connected.

rajesh@ORA10GR2> variable x varchar2(100);
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec :x := rpad('*',80,'*')||'11';

PL/SQL procedure successfully completed.

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

no rows selected

Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  4986 |   462K|   296   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |  4986 |   462K|   296   (2)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=:X)

Statistics
----------------------------------------------------------
        361  recursive calls
          0  db block gets
       1367  consistent gets
       1302  physical reads
          0  redo size
        322  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed

rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace off;
rajesh@ORA10GR2>
rajesh@ORA10GR2>

Also outlines cannot be enabled at database level.

sys@ORA10GR2> alter database set use_stored_outlines = default;
alter database set use_stored_outlines = default
                   *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

Elapsed: 00:00:00.04
sys@ORA10GR2>

NOCOPY

rajesh@ORA10GR2> create or replace procedure p1(x out number,y out number)
  2  as
  3  begin
  4     x := 55;
  5     y := 55;
  6     raise no_data_found;
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:00.17
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     l_x number := 0;
  3     l_y number := 0;
  4  begin
  5     p1(l_x,l_y);
  6  exception
  7             when no_data_found then
  8                     dbms_output.put_line ('x = '||l_x);
  9                     dbms_output.put_line ('y = '||l_y);
 10  end;
 11  /
x = 0
y = 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
rajesh@ORA10GR2>


The values of OUT paramaters are assigned on the sucessfull completion of the subroutines. But that is not the case with NOCOPY they are like pointer variables. ( As soon as we modify them in sub-routines their values will be modified in calling routines, As you see below )

rajesh@ORA10GR2> create or replace procedure p2(x out NOCOPY number,y out NOCOPY number)
  2  as
  3  begin
  4     x := 55;
  5     y := 55;
  6     raise no_data_found;
  7  end;
  8  /

Procedure created.

Elapsed: 00:00:00.18
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     l_x number := 0;
  3     l_y number := 0;
  4  begin
  5     p2(l_x,l_y);
  6  exception
  7             when no_data_found then
  8                     dbms_output.put_line ('x = '||l_x);
  9                     dbms_output.put_line ('y = '||l_y);
 10  end;
 11  /
x = 55
y = 55

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15
rajesh@ORA10GR2>

When Sending bulk volume of data between subroutines NOCOPY hint doesn't share data's actually. It will copy values PASS BY REFERENCE Technique.

rajesh@ORA10GR2> create or replace procedure p3(x out dbms_sql.varchar2a)
  2  as
  3  begin
  4     for r in 1..200000
  5     loop
  6             x(r) := rpad('*',4000,'*');
  7     end loop;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.21
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     y dbms_sql.varchar2a;
  3  begin
  4     p3(y);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:50.86
rajesh@ORA10GR2>


Now you see the effect of NOCOPY here.


rajesh@ORA10GR2> create or replace procedure p4(x out NOCOPY dbms_sql.varchar2a)
  2  as
  3  begin
  4     for r in 1..200000
  5     loop
  6             x(r) := rpad('*',4000,'*');
  7     end loop;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.21
rajesh@ORA10GR2>
rajesh@ORA10GR2> declare
  2     y dbms_sql.varchar2a;
  3  begin
  4     p4(y);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.13
rajesh@ORA10GR2>