XMLAGG Vs ODCI

I was recently asked about handling string aggregation using XMLAGG rather than using Oracle Data Cartridge Interface (ODCI).

Set up a test case to benchmark this phenomena.

rajesh@ORA10GR2> create or replace type str_agg as object
  2      (
  3         data  SYS.ODCIVARCHAR2LIST,
  4
  5         static function
  6              ODCIAggregateInitialize(sctx IN OUT str_agg )
  7              return number,
  8
  9         member function
 10             ODCIAggregateIterate(self IN OUT str_agg ,
 11                                  value IN varchar2 )
 12             return number,
 13
 14        member function
 15             ODCIAggregateTerminate(self IN str_agg,
 16                                    returnValue OUT  varchar2,
 17                                    flags IN number)
 18             return number,
 19
 20        member function
 21             ODCIAggregateMerge(self IN OUT str_agg,
 22                                ctx2 IN str_agg)
 23             return number
 24     );
 25  /

Type created.

Elapsed: 00:00:01.62
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace type body str_agg
  2      is
  3
  4      static function ODCIAggregateInitialize(sctx IN OUT str_agg)
  5      return number
  6      is
  7      begin
  8          sctx := str_agg( SYS.ODCIVARCHAR2LIST() );
  9          return ODCIConst.Success;
 10     end;
 11
 12     member function ODCIAggregateIterate(self IN OUT str_agg,
 13                                          value IN varchar2 )
 14     return number
 15     is
 16     begin
 17         data.extend;
 18         data(data.count) := value;
 19         return ODCIConst.Success;
 20     end;
 21
 22     member function ODCIAggregateTerminate(self IN str_agg,
 23                                            returnValue OUT varchar2
 24                                            flags IN number)
 25     return number
 26     is
 27         l_data varchar2(4000);
 28     begin
 29         for x in ( select column_value from TABLE(data) order by 1
 30         loop
 31                 l_data := l_data || ',' || x.column_value;
 32         end loop;
 33         returnValue := ltrim(l_data,',');
 34         return ODCIConst.Success;
 35     end;
 36
 37     member function ODCIAggregateMerge(self IN OUT str_agg,
 38                                        ctx2 IN str_agg)
 39     return number
 40     is
 41     begin
 42         for i in 1 .. ctx2.data.count
 43         loop
 44                 data.extend;
 45                 data(data.count) := ctx2.data(i);
 46         end loop;
 47         return ODCIConst.Success;
 48     end;
 49     end;
 50     /

Type body created.

Elapsed: 00:00:00.43
rajesh@ORA10GR2>
rajesh@ORA10GR2>   CREATE or replace
  2      FUNCTION stragg_fnc(input varchar2 )
  3      RETURN varchar2
  4      PARALLEL_ENABLE AGGREGATE USING str_agg;
  5      /

Function created.

Elapsed: 00:00:00.18
rajesh@ORA10GR2> create table t
  2  nologging
  3  as
  4  select ceil(level/4) as x,
  5         case when mod(level,4) = 0 then 'A'
  6              when mod(level,4) = 1 then 'B'
  7              when mod(level,4) = 2 then 'C'
  8              when mod(level,4) = 3 then 'D'
  9         end as val
 10  from dual
 11  connect by level <= 500000;

Table created.

Elapsed: 00:00:02.75
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.68
rajesh@ORA10GR2>
rajesh@ORA10GR2>

exec runstats_pkg.rs_start;

select x,stragg_fnc(val)
from t
group by x;

exec runstats_pkg.rs_middle;

select x,
     rtrim(xmlagg(xmlelement(e, val || ',')).extract ('//text()'), ',') enames
from T
group by x;

exec runstats_pkg.rs_stop;

RunStats results show's me this.

rajesh@ORA10GR2> exec runstats_pkg.rs_stop;
Run1 ran in 13372 hsecs
Run2 ran in 22016 hsecs
run 1 ran in 60.74% of the time

Name                                  Run1        Run2        Diff
STAT...global enqueue get time           0           1           1
STAT...calls to kcmgcs                   4           5           1
STAT...calls to kcmgas                   1           0          -1
STAT...redo entries                      9          10           1
STAT...redo ordering marks               1           0          -1
STAT...redo subscn max counts            1           0          -1
STAT...gc local grants                   1           0          -1
STAT...workarea executions - o           9           8          -1
STAT...parse time cpu                    1           2           1
STAT...sorts (rows)              1,049,827   1,049,826          -1
LATCH.session switching                  2           3           1
LATCH.SGA IO buffer pool latch           0           1           1
LATCH.ktm global data                    1           0          -1
LATCH.global KZLD latch for me           0           1           1
LATCH.library cache pin alloca           1           2           1
LATCH.SQL memory manager latch           0           1           1
LATCH.ncodef allocation latch            2           3           1
STAT...messages sent                     2           0          -2
STAT...session cursor cache hi           4           2          -2
STAT...sorts (memory)                    4           2          -2
LATCH.event group latch                  0           2           2
LATCH.gcs remastering latch              0           2           2
LATCH.FAL subheap alocation              3           5           2
LATCH.FAL request queue                  3           5           2
LATCH.list of block allocation           0           2           2
LATCH.Shared B-Tree                      4           2          -2
LATCH.kwqbsn:qsga                        4           2          -2
LATCH.JS Sh mem access                   0           2           2
STAT...ges messages sent                 2           5           3
STAT...free buffer requested             7          10           3
STAT...parse count (total)               8           5          -3
LATCH.sequence cache                     0           3           3
STAT...opened cursors current            5           1          -4
STAT...enqueue releases                  6           2          -4
STAT...shared hash latch upgra           1           5           4
STAT...gc current blocks recei           1           5           4
STAT...index scans kdiixs1               4           0          -4
LATCH.process group creation             0           4           4
LATCH.FIB s.o chain latch                0           4           4
LATCH.OS process: request allo           0           4           4
LATCH.ges process table freeli           0           4           4
LATCH.archive control                    4           8           4
LATCH.user lock                          0           4           4
LATCH.ASM map operation freeli           0           4           4
STAT...enqueue requests                  7           2          -5
STAT...gcs messages sent                 2           7           5
STAT...consistent changes               37          32          -5
LATCH.dml lock allocation                2           7           5
LATCH.JS mem alloc latch                 0           5           5
LATCH.JS queue access latch              0           5           5
STAT...db block changes                 48          42          -6
LATCH.channel handle pool latc           0           6           6
LATCH.OS process                         0           6           6
LATCH.library cache lock alloc          19          13          -6
LATCH.KFMD SGA                           0           6           6
STAT...global enqueue gets syn          13          20           7
STAT...global enqueue releases          13          20           7
STAT...no work - consistent re         832         825          -7
STAT...opened cursors cumulati          13           5          -8
STAT...bytes sent via SQL*Net    2,025,450   2,025,441          -9
LATCH.business card                      4          14          10
STAT...db block gets                    41          28         -13
STAT...db block gets from cach          41          28         -13
LATCH.FOB s.o list latch                 0          13          13
LATCH.parameter list                     7          22          15
LATCH.JS broadcast load blnc l          27          45          18
LATCH.resmgr:schema config              20           0         -20
LATCH.ges deadlock list                 38          60          22
LATCH.JS broadcast add buf lat          30          52          22
LATCH.JS broadcast drop buf la          30          52          22
STAT...redo size                     2,964       2,940         -24
STAT...undo change vector size       2,196       2,220          24
LATCH.ges resource scan list            28          52          24
LATCH.KMG MMAN ready and start          45          73          28
LATCH.error message lists               14          43          29
LATCH.session timer                     47          76          29
LATCH.transaction branch alloc          47          76          29
LATCH.lgwr LWN SCN                      45          76          31
LATCH.mostly latch-free SCN             45          76          31
LATCH.OS process allocation             45          77          32
LATCH.archive process latch             51          83          32
LATCH.Consistent RBA                    40          72          32
LATCH.ASM network background l          54          88          34
LATCH.ksuosstats global area            49          14         -35
LATCH.post/wait queue                    0          50          50
LATCH.ASM db client latch               91         148          57
LATCH.resmgr group change latc         113          49         -64
STAT...bytes received via SQL*      10,136      10,201          65
LATCH.name-service pending que         130         198          68
STAT...workarea memory allocat          25         -45         -70
LATCH.parallel query stats             105          32         -73
LATCH.PL/SQL warning settings          102          21         -81
LATCH.compile environment latc          99          13         -86
LATCH.resmgr:actses change gro          99          13         -86
LATCH.redo allocation                  130         224          94
LATCH.name-service memory obje         178         296         118
LATCH.query server freelists           216          79        -137
LATCH.resmgr:free threads list         198          26        -172
LATCH.dummy allocation                 198          26        -172
LATCH.parameter table allocati         198          26        -172
LATCH.session state list latch         198          24        -174
LATCH.undo global data                  77         261         184
LATCH.resmgr:active threads            218          26        -192
LATCH.longop free list parent          329         532         203
LATCH.process queue                    319          99        -220
LATCH.ges process hash list            112         372         260
LATCH.session idle bit               2,752       2,442        -310
LATCH.client/application info          541         193        -348
LATCH.queued dump request              669       1,101         432
LATCH.message pool operations          625         137        -488
LATCH.library cache lock             1,860       1,267        -593
LATCH.parallel query alloc buf         844         228        -616
LATCH.file cache latch                   0         715         715
LATCH.ges resource table freel         143         869         726
LATCH.threshold alerts latch             3         730         727
LATCH.ges timeout list                  44         805         761
LATCH.process allocation             1,229       2,040         811
LATCH.session allocation             1,007         175        -832
LATCH.name-service namespace b       1,342       2,254         912
LATCH.name-service request que       1,491       2,428         937
LATCH.ges caches resource list       1,940       3,121       1,181
LATCH.JS queue state obj latch       1,782       2,970       1,188
LATCH.gcs opaque info freelist       9,748      11,084       1,336
STAT...recursive cpu usage           1,738          21      -1,717
LATCH.SQL memory manager worka       3,085       5,056       1,971
LATCH.ges domain table               4,022       6,108       2,086
STAT...DB time                       4,784       6,895       2,111
LATCH.KJC message pool free li       3,205       5,596       2,391
LATCH.ges enqueue table freeli       4,304       6,789       2,485
STAT...CPU used when call star       4,254       6,838       2,584
STAT...CPU used by this sessio       4,254       6,839       2,585
LATCH.KFK SGA context latch          5,681       8,894       3,213
LATCH.ges group table                5,928       9,165       3,237
LATCH.sort extent pool               7,512      12,011       4,499
LATCH.enqueues                       7,593      12,456       4,863
LATCH.active checkpoint queue        9,026      14,493       5,467
LATCH.redo writing                   9,241      14,859       5,618
LATCH.ges resource hash list         7,309      13,422       6,113
LATCH.active service list            7,006         746      -6,260
LATCH.ges process parent latch       8,727      15,286       6,559
LATCH.enqueue hash chains           11,088      18,105       7,017
LATCH.transaction allocation         7,200      14,402       7,202
STAT...Elapsed Time                 13,391      22,034       8,643
LATCH.multiblock read objects       25,342      38,017      12,675
LATCH.channel operations paren      15,999      32,736      16,737
LATCH.messages                      27,444      45,354      17,910
LATCH.object queue header heap      36,106      57,968      21,862
LATCH.process queue reference       57,824      84,210      26,386
LATCH.gcs shadows freelist          36,199      69,637      33,438
LATCH.simulator hash latch         120,246     189,659      69,413
LATCH.checkpoint queue latch       154,709     248,320      93,611
LATCH.gcs resource freelist        112,884     210,168      97,284
LATCH.cache buffers lru chain      157,487     265,648     108,161
STAT...execute count               125,009           5    -125,004
STAT...session uga memory          392,448     261,632    -130,816
LATCH.KJCT flow control latch      245,838     425,612     179,774
LATCH.ASM map operation hash t     308,505     522,227     213,722
STAT...index fetch by key          250,890          21    -250,869
STAT...rows fetched via callba     250,890          21    -250,869
STAT...table fetch by rowid        250,893          21    -250,872
STAT...calls to get snapshot s     375,902          28    -375,874
LATCH.gcs resource hash            648,925   1,074,796     425,871
LATCH.object queue header oper     724,401   1,153,719     429,318
LATCH.KCL gc element parent la     636,220   1,077,684     441,464
STAT...buffer is not pinned co     501,786          42    -501,744
STAT...recursive calls             625,057           4    -625,053
LATCH.cache buffers chains       2,977,887   3,713,272     735,385
LATCH.gcs partitioned table ha   1,050,572   1,789,356     738,784
STAT...consistent gets - exami     752,677          64    -752,613
STAT...consistent gets             753,519         900    -752,619
STAT...consistent gets from ca     753,519         900    -752,619
STAT...session logical reads       753,560         928    -752,632
LATCH.shared pool simulator        129,635     882,814     753,179
LATCH.library cache pin              4,104   1,750,700   1,746,596
LATCH.kokc descriptor allocati           2   1,746,654   1,746,652
LATCH.shared pool                    2,465   2,624,667   2,622,202
LATCH.library cache                  7,512   2,630,087   2,622,575

STAT...session pga memory          393,216   3,473,408   3,080,192

LATCH.row cache objects              1,071   7,866,893   7,865,822

STAT...session uga memory max    2,485,872 924,803,712 922,317,840
STAT...session pga memory max    2,031,616 929,693,696 927,662,080

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
   7,612,145  28,693,840  21,081,695     26.53%

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.96

since XMLAGG produces more latches in single user environment. If you are planning to deal with High volume of data sets then ODCI is far better than XMLAGG.
 

Peeking of User Defined Bind variables 10GR2 and 11GR2

Not something entirely learned new today - but rather revisited "something I learned". Its all about Bind in 10G and 11G database.

I will set up a very skewed set of data for emphasis.

rajesh@ORA10GR2> create table t
  2  as
  3  select case when rownum = 1 then 1 else 99 end as id,
  4        a.*
  5  from all_objects a;

Table created.

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

Index created.

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

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
rajesh@ORA10GR2>

So, its clear that WHERE id=1 it will return one record and WHERE id=99 it will return all the rest. Also the optimizer is aware of this fact due to histograms in place.

Now, lets see what happens if we parse the query using literals.

rajesh@ORA10GR2> set autotrace traceonly explain statistics;
rajesh@ORA10GR2> select *
  2  from t
  3  where id = 1;

Elapsed: 00:00:00.00

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

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

rajesh@ORA10GR2>
rajesh@ORA10GR2> select *
  2  from t
  3  where id = 99;

50086 rows selected.

Elapsed: 00:00:00.21

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50082 |  4695K|   166   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 50082 |  4695K|   166   (4)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=99)

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

rajesh@ORA10GR2> set autotrace off;

We see two different plans for two different literals.

But what happens if we use binds select * from t where id = :id during hard parsing Oracle will wait for the bind value to be supplied before figuring out the right way to optimize the query. This is called Bind Variable Peeking.

rajesh@ORA10GR2> exec :x := 99;

PL/SQL procedure successfully completed.

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

50086 rows selected.

Elapsed: 00:00:00.23
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50087 |  4695K|   167   (4)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    | 50087 |  4695K|   167   (4)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=TO_NUMBER(:X))

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

rajesh@ORA10GR2>

We started off with a id=99  as a bind and Full tablescan was choosen with 1046 Logical IOs

rajesh@ORA10GR2> exec :x := 1;

PL/SQL procedure successfully completed.

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

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50087 |  4695K|   167   (4)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    | 50087 |  4695K|   167   (4)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=TO_NUMBER(:X))

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

rajesh@ORA10GR2>

Now, for the bind id=1 Oracle has reused the same explain plan used for id=99 and went for 716 Logical IOs. We can turn on the sql_trace and see the performance characterstic of this query.

rajesh@ORA10GR2> alter session set sql_trace=true;

Session altered.

Elapsed: 00:00:00.07
rajesh@ORA10GR2>
rajesh@ORA10GR2> print x;

      X
----------
         1

rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t
  2  where id = :x;

Elapsed: 00:00:00.04

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

rajesh@ORA10GR2>

If you turn off the sql_trace you will see this.

rajesh@ORA10GR2> alter session set sql_trace=false;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA10GR2> select * from t
  2  where id = :x;

Elapsed: 00:00:00.01

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

rajesh@ORA10GR2>

Again turning on the sql_trace, went for 4 IOs

rajesh@ORA10GR2> alter session set sql_trace=true;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t
  2  where id = :x;

Elapsed: 00:00:00.00

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

rajesh@ORA10GR2>

This beviour of bind peeking is changed in Oracle 11G with the introduction of Adaptive cursor sharing.

rajesh@ORA11GR2> set autotrace traceonly statistics;
rajesh@ORA11GR2> variable x number;
rajesh@ORA11GR2> exec :x := 99;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2> select * from t
  2  where id = :x;

Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
       1533  consistent gets
          0  physical reads
          0  redo size
    2895013  bytes sent via SQL*Net to client
       5662  bytes received via SQL*Net from client
        479  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      71655  rows processed

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec :x := 1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from t
  2  where id = :x;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1057  consistent gets
          0  physical reads
          0  redo size
       1448  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

The optimizer had made the mistake and reused the same plan it used for id=99 on re-running the query again, Adaptive cursor sharing will kick start and you see this.

rajesh@ORA11GR2> /

Elapsed: 00:00:00.03

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1451  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>

And looking at the shared pool it shows me this.

rajesh@ORA11GR2> 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_text like ''select *
 from t where id = :x''');

     SQL_TEXT------------ select * from t where id = :x
     SQL_ID-------------- 9pn7r50tytugm
     CHILD_NUMBER-------- 0
     EXECUTIONS---------- 2
     BUFFER_GETS--------- 2590
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- N
     IS_SHAREABLE-------- Y
--------------------------------------------
     SQL_TEXT------------ select * from t where id = :x
     SQL_ID-------------- 9pn7r50tytugm
     CHILD_NUMBER-------- 1
     EXECUTIONS---------- 1
     BUFFER_GETS--------- 4
     IS_BIND_SENSITIVE--- Y
     IS_BIND_AWARE------- Y
     IS_SHAREABLE-------- Y
--------------------------------------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12

rajesh@ORA11GR2>

So the possible solutions in 10G database is to don't gather Histograms that cause the plan to change.

rajesh@ORA10GR2>
rajesh@ORA10GR2> create table t
  2  nologging
  3  as
  4  select case when rownum = 1 then 1 else 99 end as id,
  5        a.*
  6  from all_objects a;

Table created.

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

Index created.

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

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly statistics;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select * from t where id = 99;

Statistics
----------------------------------------------------------
         44  recursive calls
          0  db block gets
       1050  consistent gets
          0  physical reads
          0  redo size
    2049496  bytes sent via SQL*Net to client
       4070  bytes received via SQL*Net from client
        336  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      50127  rows processed

rajesh@ORA10GR2> select * from t where id = 1;

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

rajesh@ORA10GR2> variable x number;
rajesh@ORA10GR2> exec :x := 99;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

rajesh@ORA10GR2> select * from t where id = :x;

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

rajesh@ORA10GR2> exec :x := 1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

rajesh@ORA10GR2> select * from t where id = :x;

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

rajesh@ORA10GR2>
rajesh@ORA10GR2> alter session set sql_trace=true;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA10GR2> select * from t where id = :x;

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

rajesh@ORA10GR2> alter session set sql_trace=false;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA10GR2> select * from t where id = :x;

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

rajesh@ORA10GR2> alter session set sql_trace=true;

Session altered.

Elapsed: 00:00:00.07
rajesh@ORA10GR2> select * from t where id = :x;

Elapsed: 00:00:00.06

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

rajesh@ORA10GR2>

The another solution is to change cursor_sharing=similar

begin   
 dbms_stats.gather_table_stats(
 ownname =>user,
 tabname =>'T',
 estimate_percent=>100,
 method_opt=>'for all indexed columns size 254');
end;
/

alter session set cursor_sharing=similar;

select * from t test_4 where id = 1 and object_id = 100;
select * from t test_4 where id = 2 and object_id = 100;
select * from t test_4 where id = 3 and object_id = 100;

select * from t test_4 where id = 1 and object_id = 101;
select * from t test_4 where id = 1 and object_id = 102;

alter session set cursor_sharing=exact;

rajesh@ORA10GR2> SELECT sql_text,executions
  2  FROM V$SQL
  3  WHERE sql_text LIKE 'select * from t test_4 where id %'
  4  /

SQL_TEXT                                                                    EXECUTIONS
--------------------------------------------------------------------------- ----------
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              3
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              1
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              1

Elapsed: 00:00:00.06

rajesh@ORA10GR2>

Whenever the bind value for the id column changes, Oracle replace the literal with bind variable and produce child cursors for each unique bind values.

So we have 3 executions for id=1 and 1 execution for each id=2 and id=3. So in total we have three unique child cursors.

rajesh@ORA10GR2> alter session set cursor_sharing=similar;

Session altered.

Elapsed: 00:00:00.00
rajesh@ORA10GR2> select * from t test_4 where id = 4 and object_id = 100;

no rows selected

Elapsed: 00:00:00.01
rajesh@ORA10GR2> alter session set cursor_sharing=exact;

Session altered.

Elapsed: 00:00:00.01
rajesh@ORA10GR2>
rajesh@ORA10GR2> SELECT sql_text,executions
  2  FROM V$SQL
  3  WHERE sql_text LIKE 'select * from t test_4 where id %'
  4  /

SQL_TEXT                                                                    EXECUTIONS
--------------------------------------------------------------------------- ----------
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              3
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              1
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              1
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              1

Elapsed: 00:00:00.06
rajesh@ORA10GR2>

you see the fourth copy of sql available in v$sql for id = 4

What happens if we change the object_id to different values

alter session set cursor_sharing=similar;
select * from t test_4 where id = 1 and object_id = 500;
select * from t test_4 where id = 2 and object_id = 500;
select * from t test_4 where id = 3 and object_id = 500;
alter session set cursor_sharing=exact;

rajesh@ORA10GR2> SELECT sql_text,executions
  2  FROM V$SQL
  3  WHERE sql_text LIKE 'select * from t test_4 where id %';

SQL_TEXT                                                                    EXECUTIONS
--------------------------------------------------------------------------- ----------
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              4
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              2
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              2
select * from t test_4 where id = :"SYS_B_0" and object_id = :"SYS_B_1"              1

Elapsed: 00:00:00.03
rajesh@ORA10GR2>

Notice here how there are three copies of the sql in v$sql still! That is because we already had the plans for ID=1,2,3 - regardless of what the object_id was