SQL> select object_id,count(*) from skewed_data_tab group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         5         30
         6       2970
         7      10797
         8     150000
         9     300000
SQL> create index i_skewed_tab_data on skewed_data_tab(object_id);

SQL> exec dbms_stats.gather_table_stats(user,'SKEWED_DATA_TAB',cascade=>true,
method_opt=>'for all columns size 254');

Then traced with following options
1) 
alter session set events '10046 trace name context forever, level 12';

SQL> begin
for i_outer in(select n from ids order by tstamp)
loop
for i_inner in (select /* for exact */ object_id,object_name,object_type 
from skewed_data_tab where object_id=i_outer.n)
loop
null;
end loop;
end loop;
end;  2    3    4    5    6    7    8    9
 10  /

PL/SQL procedure successfully completed.


2) set termout off
alter session set events '10046 trace name context forever, level 12';
@/u04/scripts/exact.sql 5


cat /u04/scripts/exact.sql
select /* for exact */ object_id,object_name,object_type from skewed_data_tab where object_id=&1;

SQL> select object_id,count(*) from skewed_data_tab group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         5         30
         6       2970
         7      10797
         8     150000
         9     300000

select * from skewed_data_tab where object_id=n;

SQL> create table data as select * from t;

Table created.

SQL> select object_id,count(*) from data group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         5         30
         6       2970
         7      10797
         8     150000
         9     300000

SQL> create index i_data on data(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'DATA',cascade=>true,method_opt=>'for all columns size 254');

PL/SQL procedure successfully completed.

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
SQL>
SQL> select sql_text,child_number from v$sql where sql_text like 'select /* test exact cursor_sharing */ * from data%';

no rows selected

SQL> set autotrace traceonly explain
SQL> select /* test exact cursor_sharing */ * from data where object_id=9;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=367 Card=300000 Byte
          s=26400000)

   1    0   TABLE ACCESS (FULL) OF 'DATA' (Cost=367 Card=300000 Bytes=
          26400000)

SQL> select /* test exact cursor_sharing */ * from data where object_id=5;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=30 Bytes=2640
          )

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DATA' (Cost=4 Card=30 By
          tes=2640)

   2    1     INDEX (RANGE SCAN) OF 'I_DATA' (NON-UNIQUE) (Cost=3 Card
          =30)




SQL> select /* test exact cursor_sharing */ * from data where object_id=8;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=367 Card=150000 Byte
          s=13200000)

   1    0   TABLE ACCESS (FULL) OF 'DATA' (Cost=367 Card=150000 Bytes=
          13200000)




SQL> select /* test exact cursor_sharing */ * from data where object_id=7;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=164 Card=10797 Bytes
          =950136)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DATA' (Cost=164 Card=107
          97 Bytes=950136)

   2    1     INDEX (RANGE SCAN) OF 'I_DATA' (NON-UNIQUE) (Cost=24 Car
          d=10797)




SQL> select /* test exact cursor_sharing */ * from data where object_id=6;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=47 Card=2970 Bytes=2
          61360)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DATA' (Cost=47 Card=2970
           Bytes=261360)

   2    1     INDEX (RANGE SCAN) OF 'I_DATA' (NON-UNIQUE) (Cost=8 Card
          =2970)

SQL> set autotrace off

SQL> select sql_text,child_number,address from v$sql where sql_text like 'select /* test exact cursor_sharing */ * from data%';

SQL_TEXT
--------------------------------------------------------------------------------
CHILD_NUMBER ADDRESS
------------ ----------------
select /* test exact cursor_sharing */ * from data where object_id=6
           0 000000008E94CB68

select /* test exact cursor_sharing */ * from data where object_id=9
           0 000000008E8EADB0

select /* test exact cursor_sharing */ * from data where object_id=8
           0 000000008E8F4A50


SQL_TEXT
--------------------------------------------------------------------------------
CHILD_NUMBER ADDRESS
------------ ----------------
select /* test exact cursor_sharing */ * from data where object_id=7
           0 000000008E9EFEE8

select /* test exact cursor_sharing */ * from data where object_id=5
           0 000000008E913F38

SELECT column
FROM (SELECT column
      FROM table
      ORDER BY other column)
WHERE rownum = 1;

WHERE rownum = :bind;