Connected to Oracle Database 10g Enterprise Edition Release  10 . 1 . 0 . 5 . 0  
Connected as test

SQL> create table some_table as
   2   select rownum id, lpad ('*',  100 , '*') data 
   3   from dual
   4   connect by level <=  10000000 ;

Table created

SQL> alter table some_table add primary key (id);

Table altered

SQL> exec dbms_stats.gather_schema_stats (ownname => user);

PL/SQL procedure successfully completed

SQL> create type TIntTable is table of integer;
   2   /

Type created

SQL> set serveroutput on;
SQL> alter system flush shared_pool;

System altered

SQL> create or replace procedure select_by_where is
   2     stmt varchar2( 32000 ) := 'where id in (';
   3     crSomeTable sys_refcursor;
   4     recSomeTable some_table%rowtype;
   5     t1 timestamp;
   6     t2 timestamp;
   7     t3 timestamp;
   8   begin
   9     for i in  1 .. 999  loop
  10       stmt := stmt ||  5  * i || ', ';
  11     end loop;
  12     stmt := 'select /***1***/ * from some_table ' || stmt || '5000)';
  13     t1 := systimestamp;
  14     open crSomeTable for stmt;
  15     t2 := systimestamp;
  16     loop
  17       fetch crSomeTable into recSomeTable;
  18       exit when crSomeTable%notfound;
  19     end loop;
  20     close crSomeTable;
  21     t3 := systimestamp;
  22     dbms_output.put_line ('Where: open cursor = ' || (t2 - t1));
  23     dbms_output.put_line ('Where: fetch cursor = ' || (t3 - t2));
  24   end;
  25   /

Procedure created

SQL> exec select_by_where;

Where: open cursor = + 000000000   00 : 00 : 00 . 016000000 
Where: fetch cursor = + 000000000   00 : 00 : 00 . 031000000 

PL/SQL procedure successfully completed

SQL> select sql_id from v$sql where sql_text like '%***1***%'  and sql_text not like '%v$sql%';

SQL_ID
-------------
6bhbj6qkbvxjr

SQL> select * from table (dbms_xplan.display_cursor ('6bhbj6qkbvxjr'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6bhbj6qkbvxjr, child number  0 
-------------------------------------
select /***1***/ * from some_table where id in ( 5 ,  10 ,  15 ,  20 ,  25 ,  30 ,  35 ,  40 , 

... поскипано ...

Plan hash value:  1666979420 
--------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT             |              |       |       |     24  ( 100 
|    1  |  INLIST ITERATOR             |              |       |       |
|    2  |   TABLE ACCESS BY INDEX ROWID| SOME_TABLE   |   1000  |   103K|     24    ( 0 
|*   3  |    INDEX RANGE SCAN          | SYS_C0010485 |   1000  |       |      5    ( 0 
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
    3  - access(("ID"= 5  OR "ID"= 10  OR "ID"= 15  OR "ID"= 20  OR "ID"= 25  OR "ID"= 30  OR

... поскипано ...

 80  rows selected

SQL> create or replace procedure select_by_table is
   2     id_list TIntTable := TIntTable();
   3     cursor crSomeTable (id_list TIntTable) is
   4       select * from some_table where id in (select column_value from table (cast (id_list as TIntTable)));
   5     recSomeTable some_table%rowtype;
   6     t1 timestamp;
   7     t2 timestamp;
   8     t3 timestamp;
   9   begin
  10     id_list.extend ( 1000 );
  11     for i in  1 .. 1000  loop
  12       id_list (i) :=  5  * i;
  13     end loop;
  14     t1 := systimestamp;
  15     open crSomeTable (id_list);
  16     t2 := systimestamp;
  17     loop
  18       fetch crSomeTable into recSomeTable;
  19       exit when crSomeTable%notfound;
  20     end loop;
  21     close crSomeTable;
  22     t3 := systimestamp;
  23     dbms_output.put_line ('Table: open cursor = ' || (t2 - t1));
  24     dbms_output.put_line ('Table: fetch cursor = ' || (t3 - t2));
  25   end;
  26   /

Procedure created

SQL> alter system flush shared_pool;

System altered

SQL> exec select_by_table;

Table: open cursor = + 000000000   00 : 00 : 00 . 031000000 
Table: fetch cursor = + 000000000   00 : 00 : 00 . 016000000 

PL/SQL procedure successfully completed

SQL> select distinct sql_id from v$sql where sql_text like '%TINTTABLE%'  and sql_text not like '%v$sql%';

SQL_ID
-------------
8nv6pst8r1ar4

SQL> select * from table (dbms_xplan.display_cursor ('8nv6pst8r1ar4',  1 ));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8nv6pst8r1ar4, child number  1 
-------------------------------------
SELECT * FROM SOME_TABLE WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE (CAST (:B1
TINTTABLE)))
Plan hash value:  3292038186 
--------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cos
--------------------------------------------------------------------------------
|    0  | SELECT STATEMENT                    |              |       |       |    5 
|    1  |  NESTED LOOPS                       |              |    255  |  27540  |    5 
|    2  |   SORT UNIQUE                       |              |       |       |
|    3  |    COLLECTION ITERATOR PICKLER FETCH|              |       |       |
|    4  |   TABLE ACCESS BY INDEX ROWID       | SOME_TABLE   |      1  |    106  |
|*   5  |    INDEX UNIQUE SCAN                | SYS_C0010485 |      1  |       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
    5  - access("ID"=VALUE(KOKBF$))

 23  rows selected