Pruning on Hash partitioned columns

Learnt something newly from Oracle product documentation, its about pruning on Hash partitioned columns.

Oracle Database prunes partitions when you use range, LIKE , equality, and IN-list predicates on the range or list partitioning columns, and when you use equality and IN-list predicates on the hash partitioning columns. When you use LIKE predicates on Hash partitioned key columns Oracle has to prune all partitions to answer queries and not the specific partitions.

rajesh@10GR2> create table t(
  2     x varchar2(30),
  3     y number,
  4     z date
  5  )partition by hash(x)
  6  (
  7     partition p1,
  8     partition p2,
  9     partition p3,
 10     partition p4
 11  ) nologging;

Table created.

Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> insert /*+ append */ into t(x,y,z)
  2  select object_name, mod(rownum,10),created
  3  from all_objects;

56197 rows created.

Elapsed: 00:00:03.50
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.12
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> SELECT *
  2  FROM t
  3  WHERE x = 'I_CON2';

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 407646786

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Pstart| Pstop |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     3 |       |       |
|   1 |  PARTITION HASH SINGLE|      |     3 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL   | T    |     3 |     1 |     1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"='I_CON2')

As you see from plan that partition pruning is kicked, optimizer scanned only first partition to answer this query. Now lets see what happens in case of LIKE predicates..

rajesh@10GR2> SELECT *
  2  FROM t
  3  WHERE x LIKE 'I_CON2';

Elapsed: 00:00:00.25

Execution Plan
----------------------------------------------------------
Plan hash value: 3609007437

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Pstart| Pstop |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  3459 |       |       |
|   1 |  PARTITION HASH ALL|      |  3459 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL| T    |  3459 |     1 |     4 |
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X" LIKE 'I_CON2')

The ROWS, BYTES, COST, and TIME columns were removed from this plan output to allow it to fit on the page.
Now the optimizer scanned 4 partitions to answer this query. This shows clearly optimizer is blind to LIKE predicated on HASH partitions.

Indexes on Nested Tables

This will the continuation of Earlier post. One big difference here is that in the nested table implementation, we could have created an index on the nested table's ID column, and the optimizer would have been able to use that. but that is not possible using Varray.

rajesh@10GR2> create type too as object(
  2     id number,
  3     dt date,
  4     data varchar2(20)
  5  );
  6  /

Type created.

Elapsed: 00:00:00.15
rajesh@10GR2> create type foo is table of too;
  2  /

Type created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> create table t(
  2  x number,
  3  y foo) nested table y store as t_y;

Table created.

Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> INSERT
  2  INTO t
  3  SELECT level,
  4    CAST( multiset
  5        (SELECT too(level,sysdate,rpad('*',20,'*'))
  6        FROM dual
  7          CONNECT BY level <= 1000
  8        ) AS foo)
  9  FROM dual
 10  CONNECT BY level <= 5;

5 rows created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.06
rajesh@10GR2> create unique index t_ind on t_y(nested_table_id,id);

Index created.

Elapsed: 00:00:00.14
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain;
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select t.x,t_tab.id
  2  from t, table(t.y) t_tab
  3  where t.x = 2
  4  and t_tab.id <= 5;
Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1251723223

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     5 |   230 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |       |     5 |   230 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T     |     1 |    23 |     5   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | T_IND |     5 |   115 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."X"=2)
   3 - access("T_TAB"."NESTED_TABLE_ID"="T"."SYS_NC0000200003$" AND
              "T_TAB"."ID"<=5)

rajesh@10GR2>

Sorted Hash Clusters

Sorted hash clusters are new data structures available from Oracle 10g Release 1 that enable faster retrieval of data for applications where data is consumed in the order in which it was inserted " using sorted hash clusters will skip the sorting step on your data when the order by in your query matches the sort in the hash cluster ".

rajesh@10GR2> create cluster t_clust(
  2     x number,
  3     y number sort,
  4     z number sort
  5  )HASHKEYS 100 HASH is x;

Cluster created.

Elapsed: 00:00:00.53
rajesh@10GR2>
rajesh@10GR2> create table t (
  2     c1 number,
  3     c2 number sort,
  4     c3 number sort,
  5     dt date,
  6     usr varchar2(10)
  7  )cluster t_clust(c1,c2,c3);

Table created.

Elapsed: 00:00:00.10
rajesh@10GR2>
rajesh@10GR2> insert /*+ append */ into t
  2  select     mod(rownum,100),
  3             rownum ,
  4             rownum,
  5             sysdate,
  6             user
  7  from dual
  8  connect by level <= 500000;

500000 rows created.

Elapsed: 00:00:49.54
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.06
rajesh@10GR2>

created a Sorted hash cluster and inserted data. Now, i will create a Heap organized table with data available cluster table. 

rajesh@10GR2> create table t_heap as select * from t;

Table created.

Elapsed: 00:00:07.29
rajesh@10GR2> begin
  2     dbms_stats.gather_table_stats(user,'T');
  3     dbms_stats.gather_table_stats(user,'T_HEAP');
  4  end;
  5  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.43
rajesh@10GR2>

Now, looking at the Tkprof it show the below.

********************************************************************************
select *
from t
where c1 = 57
order by c2,c3

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       35      0.03       0.00          0         62          0        5000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       37      0.03       0.01          0         64          0        5000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100 

Rows     Row Source Operation
-------  ---------------------------------------------------
   5000  TABLE ACCESS HASH T (cr=62 pr=0 pw=0 time=5056 us)
********************************************************************************
select *
from t_heap
where c1 = 57
order by c2,c3

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       35      0.03       0.04          0       2296          0        5000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       37      0.03       0.04          0       2296          0        5000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100 

Rows     Row Source Operation
-------  ---------------------------------------------------
   5000  SORT ORDER BY (cr=2296 pr=0 pw=0 time=38697 us)
   5000   TABLE ACCESS FULL T_HEAP (cr=2296 pr=0 pw=0 time=6498 us)
********************************************************************************

look at the elimination of " Sort Order by " Access path  in Explain plan for query using Sorted hash clusters. so using sorted hash clusters will skip the sorting step on your data when the order by in your query matches the sort in the hash cluster.

Nested Tables Vs VArrays

• Nested tables may be modified (inserted/updated/deleted) from, using SQL.

rajesh@10GR2> create type person_add as object(
  2     street_address1 varchar2(40),
  3     street_address2 varchar2(40),
  4     city varchar2(40),
  5     state varchar2(40),
  6     zip number
  7  );
  8  /

Type created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> create type person_typ is table of person_add;
  2  /

Type created.

Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> create table person(
  2     person_id number,
  3     first_name varchar2(30),
  4     last_name  varchar2(30),
  5     address   person_typ
  6  ) nested table address store as address_ntt;

Table created.

Elapsed: 00:00:00.17
rajesh@10GR2>
rajesh@10GR2> insert into person values (1,'Test1','Test1',person_typ(
  2                     person_add('123 main1',null,'CA','MN',123),
  3                     person_add('245 main1',null,'NJ','MN',245))
  4                                               );

1 row created.

Elapsed: 00:00:00.01
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
  2  from person p,table(p.address) p_add;

STREET_ADDRESS1                          STREET_ADDRESS2
---------------------------------------- ----------------------------------------
123 main1
245 main1

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> update table (select address from person)
  2  set street_address2 = street_address1;

2 rows updated.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
  2  from person p,table(p.address) p_add;

STREET_ADDRESS1                          STREET_ADDRESS2
---------------------------------------- ----------------------------------------
123 main1                                123 main1
245 main1                                245 main1

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> delete from table (select address from person);

2 rows deleted.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
  2  from person p,table(p.address) p_add;

no rows selected

Elapsed: 00:00:00.00
rajesh@10GR2>
rajesh@10GR2>

• VARRAYs must be procedurally modified. You cannot: UPDATE , DELETE as you could with a nested table

rajesh@10GR2> create type person_add as object(
  2     street_address1 varchar2(40),
  3     street_address2 varchar2(40),
  4     city varchar2(40),
  5     state varchar2(40),
  6     zip number
  7  );
  8  /

Type created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> create type person_typ is Varray(3) of person_add;
  2  /

Type created.

Elapsed: 00:00:00.04
rajesh@10GR2> create table person(
  2     person_id number,
  3     first_name varchar2(30),
  4     last_name  varchar2(30),
  5     address   person_typ
  6  );

Table created.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> insert into person values (1,'Test1','Test1',person_typ(
  2                     person_add('123 main1',null,'CA','MN',123),
  3                     person_add('245 main1',null,'NJ','MN',245))
  4                                               );

1 row created.

Elapsed: 00:00:00.01
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03
rajesh@10GR2> select p_add.street_address1,p_add.street_address2
  2  from person p,table(p.address) p_add;

STREET_ADDRESS1                          STREET_ADDRESS2
---------------------------------------- ----------------------------------------
123 main1
245 main1

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> update table (select address from person)
  2  set street_address2 = street_address1;
set street_address2 = street_address1
    *
ERROR at line 2:
ORA-25015: cannot perform DML on this nested table view column


Elapsed: 00:00:00.00
rajesh@10GR2> delete from table (select address from person);
delete from table (select address from person)
                                       *
ERROR at line 1:
ORA-22906: cannot perform DML on expression or on nested table view column

Elapsed: 00:00:00.01
rajesh@10GR2>

Piece-wise updates of a varray value are not supported. Thus, when a varray is updated, the entire old collection is replaced by the new collection

• Nested tables will perform a relational JOIN to bring the collect back with the row. For small collections, this may be expensive

rajesh@10GR2> create type person_add as object(
  2     street_address1 varchar2(40),
  3     street_address2 varchar2(40),
  4     city varchar2(40),
  5     state varchar2(40),
  6     zip number
  7  );
  8  /

Type created.

Elapsed: 00:00:00.10
rajesh@10GR2> create type person_typ_ntt is table of person_add;
  2  /

Type created.

Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> create table person_ntt(
  2     person_id number,
  3     first_name varchar2(30),
  4     last_name  varchar2(30),
  5     address   person_typ_ntt
  6  )nested table address store as person_ntt_nest;

Table created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> insert into person_ntt
  2  select object_id,object_name,object_name,person_typ_ntt(
  3                     person_add('123 main1',null,'CA','MN',123),
  4                     person_add('245 main1',null,'NJ','MN',245))
  5  from all_objects;

56175 rows created.

Elapsed: 00:00:20.32
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.03
rajesh@10GR2> begin
  2     dbms_stats.gather_table_stats(ownname=>user,tabname=>'PERSON_NTT',estimate_percent=>100,method_opt=>'for all columns size 254');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.65

select p.person_id,p_add.street_address1,p_add.street_address2
from person_ntt p,table(p.address) p_add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.31          0          1          0           0
Execute      1      0.00       0.02          0          0          0           0
Fetch      750      0.23       0.27          0       2007          0      112350
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      752      0.25       0.60          0       2008          0      112350

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100 

Rows     Row Source Operation
-------  ---------------------------------------------------
 112350  HASH JOIN  (cr=2007 pr=0 pw=0 time=486462 us)
  56175   TABLE ACCESS FULL PERSON_NTT (cr=581 pr=0 pw=0 time=71 us)
 112350   TABLE ACCESS FULL PERSON_NTT_NEST (cr=1426 pr=0 pw=0 time=112409 us)

• VARRAYs do not join. The data is accessed inline for small collections, and as a LOB segment for large collections. In general, there will be less overhead associated with accessing a VARRAY compared to a nested table. There is potentially more overhead associated with updating a VARRAY as compared to a nested table however, since the entire VARRAY must be replaced ‐ not just an element of it.

rajesh@10GR2> create type person_varr is Varray(60000) of person_add;
  2  /

Type created.

Elapsed: 00:00:00.07
rajesh@10GR2> create table person_varr_tab(
  2     person_id number,
  3     first_name varchar2(30),
  4     last_name  varchar2(30),
  5     address   person_varr
  6  );

Table created.

Elapsed: 00:00:00.54
rajesh@10GR2>
rajesh@10GR2> insert into person_varr_tab
  2  select object_id,object_name,object_name,person_varr(
  3                     person_add('123 main1',null,'CA','MN',123),
  4                     person_add('245 main1',null,'NJ','MN',245))
  5  from all_objects;

56177 rows created.

Elapsed: 00:00:02.73
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.07
rajesh@10GR2> begin
  2     dbms_stats.gather_table_stats(ownname=>user,tabname=>'PERSON_VARR_TAB',estimate_percent=>100,method_opt=>'for all columns size 254');
  3  end;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.06

select p.person_id,p_add.street_address1,p_add.street_address2
from person_varr_tab p,table(p.address) p_add

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      751      0.95       0.79          0       1914          0      112354
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      753      0.95       0.79          0       1914          0      112354

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100 

Rows     Row Source Operation
-------  ---------------------------------------------------
 112354  NESTED LOOPS  (cr=1914 pr=0 pw=0 time=1011273 us)
  56177   TABLE ACCESS FULL PERSON_VARR_TAB (cr=1914 pr=0 pw=0 time=112428 us)
 112354   COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=275780 us)

• Nested tables are physically stored as a parent child table with surrogate keys.

test_user@10GR2> create type person_add as object(
  2     street_address1 varchar2(40),
  3     street_address2 varchar2(40),
  4     city varchar2(40),
  5     state varchar2(40),
  6     zip number
  7  );
  8  /

Type created.

Elapsed: 00:00:00.10
test_user@10GR2> create type person_typ_ntt is table of person_add;
  2  /

Type created.

Elapsed: 00:00:00.06
test_user@10GR2> create table person_ntt(
  2     person_id number,
  3     first_name varchar2(30),
  4     last_name  varchar2(30),
  5     address   person_typ_ntt
  6  )nested table address store as person_ntt_nest;

Table created.

Elapsed: 00:00:00.15
test_user@10GR2>
test_user@10GR2>
test_user@10GR2> column obj# format 99999999;
test_user@10GR2> column col# format 99999;
test_user@10GR2> column name format a30;
test_user@10GR2>
test_user@10GR2> SELECT obj#, col#, name
  2  FROM sys.col$
  3  WHERE obj# IN
  4  (SELECT object_id
  5  FROM user_objects
  6  WHERE object_name IN ('PERSON_NTT','PERSON_NTT_NEST'))
  7  order by 1,2
  8  /

     OBJ#   COL# NAME
--------- ------ ------------------------------
   146579      1 PERSON_ID
   146579      2 FIRST_NAME
   146579      3 LAST_NAME
   146579      4 ADDRESS
   146579      4 SYS_NC0000400005$
   146580      0 NESTED_TABLE_ID
   146580      0 SYS_NC_ROWINFO$
   146580      1 STREET_ADDRESS1
   146580      2 STREET_ADDRESS2
   146580      3 CITY
   146580      4 STATE
   146580      5 ZIP

12 rows selected.

Elapsed: 00:00:00.03
test_user@10GR2>

VARRAYs are stored as a RAW column or as a LOB. There is minimal overhead introduced for the functionality.

test_user@10GR2> create type person_add as object(
  2     street_address1 varchar2(40),
  3     street_address2 varchar2(40),
  4     city varchar2(40),
  5     state varchar2(40),
  6     zip number
  7  );
  8  /

Type created.

Elapsed: 00:00:00.04
test_user@10GR2> create type person_varr is Varray(15) of person_add;
  2  /

Type created.

Elapsed: 00:00:00.04
test_user@10GR2> create table PERSON_VARR_TAB(
  2     person_id number,
  3     first_name varchar2(30),
  4     last_name  varchar2(30),
  5     address   person_varr
  6  );

Table created.

Elapsed: 00:00:00.04
test_user@10GR2>
test_user@10GR2> SELECT  col#, name,length
  2  FROM sys.col$
  3  WHERE obj# =
  4  (SELECT object_id
  5  FROM user_objects
  6  WHERE object_name = 'PERSON_VARR_TAB')
  7  order by 1,2;

      COL# NAME                               LENGTH
---------- ------------------------------ ----------
         1 PERSON_ID                              22
         2 FIRST_NAME                             30
         3 LAST_NAME                              30
         4 ADDRESS                              3002

Elapsed: 00:00:00.01
test_user@10GR2>
test_user@10GR2> Select object_type, object_name,
  2  decode(status,'INVALID','*','') status,
  3  tablespace_name
  4  from user_objects a, user_segments b
  5  where a.object_name = b.segment_name (+)
  6  order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME                    S TABLESPACE_NAME
------------------- ------------------------------ - ---------------------
TABLE               PERSON_VARR_TAB                  USERS
TYPE                PERSON_ADD
TYPE                PERSON_VARR

Elapsed: 00:00:00.12
test_user@10GR2>

Oracle has added a 3000 byte column to support our VARRAY implementation. The data for our VARRAY will be stored inline (in the row itself). This raises an interesting question; what will happen if our array could exceed 4,000 bytes ?

test_user@10GR2> alter table PERSON_VARR_TAB drop column address;

Table altered.

Elapsed: 00:00:00.15
test_user@10GR2> create or replace type person_varr is Varray(25) of person_add;
  2  /

Type created.

Elapsed: 00:00:00.14
test_user@10GR2> alter table person_varr_tab add address person_varr;

Table altered.

Elapsed: 00:00:00.06
test_user@10GR2>
test_user@10GR2> Select object_type, object_name,
  2  decode(status,'INVALID','*','') status,
  3  tablespace_name
  4  from user_objects a, user_segments b
  5  where a.object_name = b.segment_name (+)
  6  order by object_type, object_name;

OBJECT_TYPE         OBJECT_NAME                    S TABLESPACE_NAME
------------------- ------------------------------ - ------------------------------
LOB                 SYS_LOB0000146626C00004$$        USERS
TABLE               PERSON_VARR_TAB                  USERS
TYPE                PERSON_ADD
TYPE                PERSON_VARR

Elapsed: 00:00:00.09
test_user@10GR2>

• Nested table ʹArrayʹ elements have no specific order. The data in the collection may be returned in a very different order than it was in when you inserted it. 

rajesh@10GR2> create type too as object(
  2     id number,
  3     dt date,
  4     data varchar2(20)
  5  );
  6  /

Type created.

Elapsed: 00:00:00.15
rajesh@10GR2> create type foo is table of too;
  2  /

Type created.

Elapsed: 00:00:00.18
rajesh@10GR2>
rajesh@10GR2> create table t(
  2     x number,
  3     y foo)
  4  nested table y store as t_y;

Table created.

Elapsed: 00:00:00.11
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> INSERT INTO t
  2    (
  3      x,
  4      y
  5    )
  6  SELECT level ,
  7    CAST(multiset
  8    (SELECT too(level,sysdate,rpad('*',20,'*'))
  9    FROM dual
 10      CONNECT BY level <= 1000
 11    ) AS foo)
 12  FROM dual
 13  CONNECT BY level <= 5;

5 rows created.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> SELECT x,
  2    tab.id
  3  FROM t ,
  4    TABLE(t.y) tab
  5  WHERE t.x   = 2
  6  AND rownum <= 5;

         X         ID
---------- ----------
         2        191
         2        192
         2        193
         2        194
         2        195

Elapsed: 00:00:00.01

rajesh@10GR2>
rajesh@10GR2>

• VARRAYs are true arrays. The data will remain inherently ordered as you left it

rajesh@10GR2> create type too as object(
  2     id number,
  3     dt date,
  4     data varchar2(20)
  5  );
  6  /

Type created.

Elapsed: 00:00:00.12
rajesh@10GR2> create type foo is Varray(1000) of too;
  2  /

Type created.

Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> create table t(
  2     x number,
  3     y foo);

Table created.

Elapsed: 00:00:00.09
rajesh@10GR2>
rajesh@10GR2> declare
  2     l_foo foo;
  3  begin
  4     for k in 1..5
  5     loop
  6             l_foo := foo();
  7             for i in 1..1000
  8             loop
  9                     l_foo.extend;
 10                     l_foo(l_foo.last) := too(i,sysdate,rpad('*',20,'*'));
 11             end loop;
 12     insert into t values (k,l_foo);
 13     end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> SELECT p.x,
  2    t_tab.id
  3  FROM t p,
  4  TABLE(p.y) t_tab
  5  where p.x = 2
  6  and rownum <= 5;

         X         ID
---------- ----------
         2          1
         2          2
         2          3
         2          4
         2          5

Elapsed: 00:00:00.00
rajesh@10GR2>
rajesh@10GR2>

Seeing a Restart

It is easier to see a restart than you might at first think. We’ll be able to observe one, in fact, using a simple one-row table. This is the table we’ll use to test with:

rajesh@10GR2> create table t (x number,y number);

Table created.

Elapsed: 00:00:00.04
rajesh@10GR2> insert into t values(1,1);

1 row created.

Elapsed: 00:00:00.01
rajesh@10GR2> commit;

Commit complete.

Elapsed: 00:00:00.01
rajesh@10GR2>
To observe the restart, all we need is a trigger to print out some information. We’ll use a BEFORE UPDATE FOR EACH ROW trigger to simply print out the before and after image of the row as the result of an update:

rajesh@10GR2> create or replace trigger t_trig
  2  before update on t
  3  for each row
  4  begin
  5     dbms_output.put_line(' old.x = '||:old.x||' old.y = '||:old.y);
  6     dbms_output.put_line(' new.x = '||:new.x||' new.y = '||:new.y);
  7  end;
  8  /

Trigger created.

Now we’ll update that row:

rajesh@10GR2> update t set x = x + 1;
 old.x = 1 old.y = 1
 new.x = 2 new.y = 1

1 row updated.

Elapsed: 00:00:00.01

So far, everything is as we expect: the trigger fired once, and we see the old and new values. Note that we have not yet committed, however—the row is still locked. In another session, we’ll execute this update:

rajesh@10GR2> update t set x = x + 1 where x > 0;

That will immediately block, of course, since the first session has that row locked. If we now go back to the first session and commit, we’ll see this output (the update is repeated for clarity) in the second session:

rajesh@10GR2> update t set x = x + 1 where x > 0;
 old.x = 1 old.y = 1
 new.x = 2 new.y = 1
 old.x = 2 old.y = 1
 new.x = 3 new.y = 1

1 row updated.

Elapsed: 00:00:07.43

As you can see, that row trigger saw two versions of that row here. The row trigger was fired two times: once with the original version of the row and what we tried to modify that original version to, and again with the final row that was actually updated. Since this was a BEFORE FOR EACH ROW trigger, Oracle saw the read-consistent version of the record and the modifications we would like to have made to it. However, Oracle retrieved the block in current mode to actually perform the update after the BEFORE FOR EACH ROW trigger fired. It waits until after this trigger fires to get the block in current mode, because the trigger can modify the :NEW values. So Oracle cannot modify the block until after this trigger executes, and the trigger could take a very long time to execute. Since only one session at a time can hold a block in current mode,      Oracle needs to limit the time we have it in that mode.

After this trigger fired, Oracle retrieved the block in current mode and noticed that the column used to find this row, X, had been modified. Since X was used to locate this record and X was modified, the database decided to restart our query. Notice that the update of X from 1 to 2 did not put this row out of scope; we’ll still be updating it with this UPDATE statement. Rather, it is the fact that X was used to locate the row, and the consistent read value of X (1 in this case) differs from the current mode read of X (2). Now, upon restart, the trigger sees the value of X=2 (following modification by the other session) as the :OLD value and X=3 as the :NEW value. So, that shows that these restarts happen.

An interesting observation is that triggers themselves may cause restarts to occur even when the statement itself doesn’t warrant them. Normally, the columns referenced in the WHERE clause of the UPDATE or DELETE statement are used to determine whether or not the modification needs to restart. Oracle will perform a consistent read using these columns and, upon retrieving the block in current mode, it will restart the statement if it detects that any of them have changed. Normally, the other columns in the row are not inspected. For example, let’s simply rerun the previous example and use WHERE Y>0 to find the rows:

rajesh@10GR2> update t set x = x + 1 where y > 0;
 old.x = 1 old.y = 1
 new.x = 2 new.y = 1
 old.x = 2 old.y = 1
 new.x = 3 new.y = 1

1 row updated.

You might at first wonder, “Why did Oracle fire the trigger twice when it was looking at the Y value? Does it examine the whole row?” As you can see from the output, the update was in fact restarted and the trigger again fired twice, even though we were searching on Y>0 and did not modify Y at all. But, if we re-create the trigger to simply print out the fact that it fired, rather than reference the :OLD and :NEW values

rajesh@10GR2> create or replace trigger t_trig
  2  before update on t
  3  for each row
  4  begin
  5     dbms_output.put_line (' Trigger Fired ');
  6  end;
  7  /

Trigger created.

rajesh@10GR2>
rajesh@10GR2> update t set x = x + 1;
 Trigger Fired

1 row updated.

Elapsed: 00:00:00.00

and go into that second session again and run the update, we observe it gets blocked (of course). After committing the blocking session, we’ll see the following:


rajesh@10GR2> update t set x = x + 1 where y > 0;
 Trigger Fired

1 row updated.


The trigger fired just once this time, not twice. This shows that the :NEW and :OLD column values, when referenced in the trigger, are also used by Oracle to do the restart checking. When we referenced :NEW.X and :OLD.X in the trigger, X’s consistent read and current read values were compared and found to be different. A restart ensued. When we removed the reference to that column from the trigger, there was no restart.
So the rule is that the set of columns used in the WHERE clause to find the rows plus the columns referenced in the row triggers will be compared. The consistent read version of the row will be compared to the current read version of the row, and if any of them are different the modification will restart.

Note     You can use this bit of information to further understand why using an AFTER FOR EACH ROW trigger is more efficient than using a BEFORE FOR EACH ROW. The AFTER trigger won’t have the same effect.

AUTO sampling statistics Improvement in Oracle 11g

It is not always easy for users to pick the appropriate sampling percentage. If the specified sampling percentage is too high, it can take longer to gather statistics. On the contray, if the data is skewed and the specified sampling percentage is too low, the resulting statistics can be inaccurate. For this reason, Oracle introduced the AUTO value for the estimate_percent parameter ( dbms_stats.auto_sample_size ).

The advantage of using AUTO sample size over a fixed number is two-folds

1. First, when AUTO sample size is specified, the system automatically determines the appropriate sampling percentage       
2. Second, AUTO sample size is more flexible than a fixed sampling percentage. A fixed sampling percentage size that was good at some point in time may not be appropriate after the data distribution in the table has changed. On the other hand when AUTO value is used Oracle will adjust the sample size when the data distribution changes

In Oracle 11g, we improved the behavior when the AUTO value is used.

1. First, AUTO sampling now generates deterministic statistics. 
2. Second, and more importantly, AUTO sampling generates statistics that are almost as accurate as 100% sampling but takes much less time than 100% sampling.

rajesh@11GR2> create table big_table
  2  nologging
  3  as
  4  select *
  5  from all_objects a;

Table created.

Elapsed: 00:00:06.83
rajesh@11GR2> begin
  2     for i in 1..6
  3     loop
  4             insert /*+ append */ into big_table
  5             select * from big_table;
  6             commit;
  7     end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:36.54

rajesh@11GR2> select /*+ parallel(ir) */ count(*) from big_table ir;

  COUNT(*)
----------
   6602624

Elapsed: 00:00:18.82

rajesh@11GR2> select dbms_stats.get_param('DEGREE') from dual;

DBMS_STATS.GET_PARAM('DEGREE')
-----------------------------------------------------------------------------------------------------------------------------------
NULL

So, i don't  have any parallelism set at system level.


rajesh@11GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'BIG_TABLE',estimate_percent=>1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.03
rajesh@11GR2> select column_name, num_distinct, density, num_nulls, num_buckets, histogram
  2  from user_tab_col_statistics
  3  where table_name ='BIG_TABLE'
  4  order by 1
  5  /

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ---------- ----------- ---------------
CREATED                                4674  .00021395          0           1 NONE
DATA_OBJECT_ID                         8912 .000112208    5569800           1 NONE
GENERATED                                 2         .5          0           1 NONE
LAST_DDL_TIME                          4779 .000209249          0           1 NONE
OBJECT_ID                             56444 .000017717          0           1 NONE
OBJECT_NAME                           29502 .000033896          0           1 NONE
OBJECT_TYPE                              37 .027027027          0           1 NONE
OWNER                                    29 .034482759          0           1 NONE
SECONDARY                                 1          1          0           1 NONE
STATUS                                    2         .5          0           1 NONE
SUBOBJECT_NAME                         1052  .00095057    6192300           1 NONE
TEMPORARY                                 2         .5          0           1 NONE
TIMESTAMP                              4830 .000207039          0           1 NONE

13 rows selected.

Now analyzing table using dbms_stats.Auto_sample_size parameter

rajesh@11GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'BIG_TABLE',estimate_percent=>dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

Elapsed: 00:01:22.16
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> select column_name, num_distinct, density, num_nulls, num_buckets, histogram
  2  from user_tab_col_statistics
  3  where table_name ='BIG_TABLE'
  4  order by 1
  5  /

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ---------- ----------- ---------------
CREATED                                5536 .000180636          0           1 NONE
DATA_OBJECT_ID                         8903 .000112322    5567257           1 NONE
GENERATED                                 2         .5          0           1 NONE
LAST_DDL_TIME                          5690 .000175747          0           1 NONE
OBJECT_ID                             56588 .000017672          0           1 NONE
OBJECT_NAME                           31850 .000031397          0           1 NONE
OBJECT_TYPE                              40       .025          0           1 NONE
OWNER                                    29 .034482759          0           1 NONE
SECONDARY                                 1          1          0           1 NONE
STATUS                                    2         .5          0           1 NONE
SUBOBJECT_NAME                         1136 .000880282    6196071           1 NONE
TEMPORARY                                 2         .5          0           1 NONE
TIMESTAMP                              5774  .00017319          0           1 NONE

13 rows selected.

Elapsed: 00:00:00.07

Now analyzing table ( big_table ) using estimate_percent = 100

rajesh@11GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'BIG_TABLE',estimate_percent=>100);

PL/SQL procedure successfully completed.

Elapsed: 00:01:36.38
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> select column_name, num_distinct, density, num_nulls, num_buckets, histogram
  2  from user_tab_col_statistics
  3  where table_name ='BIG_TABLE'
  4  order by 1
  5  /

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ---------- ----------- ---------------
CREATED                                5536 .000180636          0           1 NONE
DATA_OBJECT_ID                         8903 .000112322    5567257           1 NONE
GENERATED                                 2         .5          0           1 NONE
LAST_DDL_TIME                          5690 .000175747          0           1 NONE
OBJECT_ID                             56291 .000017765          0           1 NONE
OBJECT_NAME                           31815 .000031432          0           1 NONE
OBJECT_TYPE                              40       .025          0           1 NONE
OWNER                                    29 .034482759          0           1 NONE
SECONDARY                                 1          1          0           1 NONE
STATUS                                    2         .5          0           1 NONE
SUBOBJECT_NAME                         1136 .000880282    6196071           1 NONE
TEMPORARY                                 2         .5          0           1 NONE
TIMESTAMP                              5774  .00017319          0           1 NONE

13 rows selected.

Elapsed: 00:00:00.42
rajesh@11GR2>

Now the results are very promising, AUTO sampling generates statistics that are almost as accurate as 100% sampling but takes much less time than 100% sampling.

Repeated the above test in Oracle 10.2.0.1.0 instance and AUTO sampling generates statistics that are far way from the 100% sampling.

rajesh@10GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'BIG_TABLE',estimate_percent=>dbms_stats.auto_sample_size);

PL/SQL procedure successfully completed.

Elapsed: 00:00:28.87
rajesh@10GR2> select column_name, num_distinct, density, num_nulls, num_buckets, histogram
  2  from user_tab_col_statistics
  3  where table_name ='BIG_TABLE'
  4  order by 1
  5  /

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ---------- ----------- ---------------
CREATED                                1865 .000536193          0           1 NONE
DATA_OBJECT_ID                         9065 .000110314    5573646           1 NONE
GENERATED                                 2         .5          0           1 NONE
LAST_DDL_TIME                          1839 .000543774          0           1 NONE
OBJECT_ID                             56711 .000017633          0           1 NONE
OBJECT_NAME                           29007 .000034474          0           1 NONE
OBJECT_TYPE                              32     .03125          0           1 NONE
OWNER                                    25        .04          0           1 NONE
SECONDARY                                 1          1          0           1 NONE
STATUS                                    2         .5          0           1 NONE
SUBOBJECT_NAME                         1032 .000968992    6216552           1 NONE
TEMPORARY                                 2         .5          0           1 NONE
TIMESTAMP                              1862 .000537057          0           1 NONE

13 rows selected.

Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'BIG_TABLE',estimate_percent=>100);

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.74
rajesh@10GR2> select column_name, num_distinct, density, num_nulls, num_buckets, histogram
  2  from user_tab_col_statistics
  3  where table_name ='BIG_TABLE'
  4  order by 1
  5  /

COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ---------- ---------- ----------- ---------------
CREATED                                5536 .000180636          0           1 NONE
DATA_OBJECT_ID                         8903 .000112322    5567257           1 NONE
GENERATED                                 2         .5          0           1 NONE
LAST_DDL_TIME                          5690 .000175747          0           1 NONE
OBJECT_ID                             56291 .000017765          0           1 NONE
OBJECT_NAME                           31815 .000031432          0           1 NONE
OBJECT_TYPE                              40       .025          0           1 NONE
OWNER                                    29 .034482759          0           1 NONE
SECONDARY                                 1          1          0           1 NONE
STATUS                                    2         .5          0           1 NONE
SUBOBJECT_NAME                         1136 .000880282    6196071           1 NONE
TEMPORARY                                 2         .5          0           1 NONE
TIMESTAMP                              5774  .00017319          0           1 NONE

13 rows selected.

Elapsed: 00:00:00.06
rajesh@10GR2>

Adaptive Cursor Sharing - Part II

This will be continuation of previous post Adaptive cursor sharing in 11gR2. What a difference, If we consider LIKE predicates on highly skewed columns 11gR2 optimizer is not picking up the best execution plan, instead reusing the same execution plans. I set up a test to benchmark.

rajesh@11GR2> select * from v$version;

BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.01
rajesh@11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> create table t
  2  as
  3  select 'aaa' as id,
  4       a.*
  5  from all_objects a;

Table created.

Elapsed: 00:00:05.76
rajesh@11GR2>
rajesh@11GR2> insert into t
  2  select 'bbb' as id,
  3        a.*
  4  from all_objects a
  5  where rownum = 1;

1 row created.

Elapsed: 00:00:00.09
rajesh@11GR2>
rajesh@11GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t(id) nologging;

Index created.

Elapsed: 00:00:00.48
rajesh@11GR2>
rajesh@11GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
rajesh@11GR2>

variable x varchar2(10);

exec :x := 'bbb';


SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=3 size=101 card=1)
      1   INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 74805)
 
The plan indicates that the optimizer chose an index range scan, which is expected because of the selectivity (only 1%) of the value 'bbb'.

exec :x := 'aaa';
SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.15       0.15          0       2156          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.15       0.15          0       2156          0       71490

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS BY INDEX ROWID T (cr=2156 pr=0 pw=0 time=136187 us cost=3 size=101 card=1)
  71490   INDEX RANGE SCAN T_IND (cr=628 pr=0 pw=0 time=50003 us cost=2 size=0 card=1)(object id 74805)

Now I have given adaptive cursor sharing a chance to figure out "made a mistake",

exec :x := 'aaa';
SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.09       0.15          0       2156          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.09       0.15          0       2156          0       71490

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS BY INDEX ROWID T (cr=2156 pr=0 pw=0 time=135291 us cost=3 size=101 card=1)
  71490   INDEX RANGE SCAN T_IND (cr=628 pr=0 pw=0 time=51538 us cost=2 size=0 card=1)(object id 74805)



exec :x := 'aaa';
SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID like :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.18       0.15          0       2156          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.18       0.15          0       2156          0       71490

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS BY INDEX ROWID T (cr=2156 pr=0 pw=0 time=135291 us cost=3 size=101 card=1)
  71490   INDEX RANGE SCAN T_IND (cr=628 pr=0 pw=0 time=51538 us cost=2 size=0 card=1)(object id 74805)

As you see above, The optimizer is not kicking up adaptive cursor sharing even for multiple execution of same cursors.

Why are there more cursors in 11g for my query containing bind variables? ( Adaptive Cursor Sharing )

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables. However, there was a problem with this approach, when the column used in the WHERE clause with the bind contained a data skew. If there is data skew in the column, it is likely that a histogram has been created on this column during statistics gathering. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, it is not guaranteed that this plan will be good for all possible values for the bind variable. In other words, the plan is optimized for the peeked value of the bind variable, but not for all possible values

rajesh@10GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.89
rajesh@10GR2>
rajesh@10GR2> create table t
  2  as
  3  select 1 as id,
  4       a.*
  5  from all_objects a;

Table created.

Elapsed: 00:00:04.81
rajesh@10GR2>
rajesh@10GR2> insert into t
  2  select 2 as id,
  3        a.*
  4  from all_objects a
  5  where rownum = 1;

1 row created.

Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> commit;

Commit complete.

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

Index created.

Elapsed: 00:00:01.21
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.57

variable x number;
exec :x := 2;

SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID = :X


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.13          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.14          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 100 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=45 us)
      1   INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=31 us)(object id 145050)

This is the Optimal plan, since we have one record for id = 2 out of 56250 record in Table ' T ', so doing an INDEX RANGE SCAN which is valid. How ever for the second query, Now the optimizer peeks at the value of the user-defined bind variable ( id = 1 )and re-uses the same plan again. which is far better to use FULL TABLE SCAN rather than INDEX RANGE SCAN.

exec :x := 1;

SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID = :X


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      376      0.06       0.24          0       1657          0       56250
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      378      0.06       0.24          0       1657          0       56250

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 100 

Rows     Row Source Operation
-------  ---------------------------------------------------
  56250  TABLE ACCESS BY INDEX ROWID T (cr=1657 pr=0 pw=0 time=168785 us)
  56250   INDEX RANGE SCAN T_IND (cr=486 pr=0 pw=0 time=56272 us)(object id 145050)

Now repeating the same scenario on Oracle 11gR2 database involves Adaptive Cursor Sharing into picture.

rajesh@11GR2> drop table t purge;

Table dropped.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> create table t
  2  as
  3  select 1 as id,
  4       a.*
  5  from all_objects a;

Table created.

Elapsed: 00:00:06.34
rajesh@11GR2>
rajesh@11GR2> insert into t
  2  select 2 as id,
  3        a.*
  4  from all_objects a
  5  where rownum = 1;

1 row created.

Elapsed: 00:00:00.09
rajesh@11GR2>
rajesh@11GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> create index t_ind on t(id) nologging;

Index created.

Elapsed: 00:00:00.50
rajesh@11GR2>
rajesh@11GR2> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'T',estimate_percent=>100,method_opt=>'for all indexed columns size 254');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.90
rajesh@11GR2>

variable x number;
exec :x := 2;

SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID = :X


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=2 size=100 card=1)
      1   INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74786)

rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');

     SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
     SQL_ID-------------- avh2ay05hnmdv
     CHILD_NUMBER-------- 0
     EXECUTIONS---------- 1
     BUFFER_GETS--------- 3
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- N
     IS_SHAREABLE-------- Y

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware ( IS_BIND_AWARE = 'Y') , the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

Since now the cursor available in share pool is not bind aware ( IS_BIND_AWARE = 'N' ), Consequently, the optimizer uses the same index range scan for the value id = 1 as for the value id = 2.

exec :x := 1;

SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID = :X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.12       0.13          0       2139          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.12       0.13          0       2139          0       71490

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS BY INDEX ROWID T (cr=2139 pr=0 pw=0 time=122607 us cost=2 size=100 card=1)
  71490   INDEX RANGE SCAN T_IND (cr=618 pr=0 pw=0 time=42074 us cost=1 size=0 card=1)(object id 74786)

rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');

     SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
     SQL_ID-------------- avh2ay05hnmdv
     CHILD_NUMBER-------- 0
     EXECUTIONS---------- 2
     BUFFER_GETS--------- 2142
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- N
     IS_SHAREABLE-------- Y

Now execute the query using the ID = 1 a second time. The database compares statistics for previous executions and marks the cursor as bind-aware. In this case, the optimizer decides that a new plan is warranted, so it performs a hard parse of the statement and generates a new plan.

exec :x := 1;

SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID = :X


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      478      0.15       0.73          0       1545          0       71490
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      480      0.15       0.73          0       1545          0       71490

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
  71490  TABLE ACCESS FULL T (cr=1545 pr=0 pw=0 time=48043 us cost=315 size=7149000 card=71490)

rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');
     SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
     SQL_ID-------------- avh2ay05hnmdv
     CHILD_NUMBER-------- 0
     EXECUTIONS---------- 2
     BUFFER_GETS--------- 2142
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- N
     IS_SHAREABLE-------- Y

     SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
     SQL_ID-------------- avh2ay05hnmdv
     CHILD_NUMBER-------- 1
     EXECUTIONS---------- 1
     BUFFER_GETS--------- 1545
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- Y
     IS_SHAREABLE-------- Y

After you execute the query twice with value ID = 1, you execute it again using the more selective value ID = 2. Because of adaptive cursor sharing, the optimizer "adapts" the cursor and chooses an index range scan rather than a full table scan for this value.

exec :x := 2;

SELECT /* ACS */ *
FROM
 T LOOK_FOR_ME WHERE ID = :X


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=2 size=100 card=1)
      1   INDEX RANGE SCAN T_IND (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74786)

rajesh@ORCL> exec print_table(' select sql_text,sql_id,child_number,executions,buffer_gets,is_bind_sensitive,is_bind_aware,IS_SHAREABLE from v$sql where sql_te
xt like ''%SELECT%ACS%FROM%T%LOOK_FOR_ME%WHERE%ID%=%:X%'' ');
     SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
     SQL_ID-------------- avh2ay05hnmdv
     CHILD_NUMBER-------- 0
     EXECUTIONS---------- 2
     BUFFER_GETS--------- 2142
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- N
     IS_SHAREABLE-------- N

     SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
     SQL_ID-------------- avh2ay05hnmdv
     CHILD_NUMBER-------- 1
     EXECUTIONS---------- 1
     BUFFER_GETS--------- 1545
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- Y
     IS_SHAREABLE-------- Y

     SQL_TEXT------------ SELECT /* ACS */ * FROM T LOOK_FOR_ME WHERE ID = :X
     SQL_ID-------------- avh2ay05hnmdv
     CHILD_NUMBER-------- 2
     EXECUTIONS---------- 1
     BUFFER_GETS--------- 3
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- Y
     IS_SHAREABLE-------- Y

Database is now using adaptive cursor sharing, the database no longer uses the original cursor (child 0), which is not bind-aware.

(Also please be sure that Adaptive cursor sharing is available only from 11.1.0.7.0, but not before that. So if you try this in 11.1.0.6.0  you will never achieve).