About optimizer mode for rownum and cursors.

gistfile1.sql

SQL> create table t1 as select mod(level,100) a from dual connect by level<=1e4;

Table created.

Elapsed: 00:00:00.27
SQL> create table t2 as select mod(level,100) b from dual connect by level<=1e4;

Table created.

Elapsed: 00:00:00.08
SQL> exec dbms_stats.gather_table_stats('','T1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.75
SQL> exec dbms_stats.gather_table_stats('','T2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.25
SQL> 
SQL> declare
  2     cursor c is select/*+ 1 */ * from t1,t2 where a=b;
  3     cursor c4 is select/*+ 4 */ * from t1,t2 where a=b;
  4     v1 int;
  5     v2 int;
  6  begin
  7     open c;
  8     fetch c into v1,v2;
  9     close c;
 10  
 11     select/*+ 2 */ * into v1,v2 from t1,t2 where a=b and rownum<2;
 12  
 13     for r in (select/*+ 3 */ a,b from t1,t2 where a=b) loop
 14       v1:=r.a;
 15       v2:=r.b;
 16       exit;
 17     end loop;
 18  
 19     for r in c4 loop
 20       v1:=r.a;
 21       v2:=r.b;
 22       exit;
 23     end loop;
 24  end;
 25  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> col sql_text for a60
SQL> 
SQL> select s.sql_text
  2        ,s.sql_id
  3        ,s.BUFFER_GETS
  4        ,s.ROWS_PROCESSED
  5        ,s.OPTIMIZER_COST
  6        ,s.OPTIMIZER_MODE
  7        ,s.PLAN_HASH_VALUE
  8  from v$sql s
  9  where s.SQL_TEXT like 'SELECT/*+%T1%'
 10  order by 1;

SQL_TEXT                                                     SQL_ID        BUFFER_GETS ROWS_PROCESSED OPTIMIZER_COST OPTIMIZER_ PLAN_HASH_VALUE                                                         
------------------------------------------------------------ ------------- ----------- -------------- -------------- ---------- ---------------                                                         
SELECT/*+ 1 */ * FROM T1,T2 WHERE A=B                        g66vjvqy21z88          23              1             20 ALL_ROWS        1838229974                                                         
SELECT/*+ 2 */ * FROM T1,T2 WHERE A=B AND ROWNUM<2           0pay870d1y39b           8              1              4 ALL_ROWS        2317214572                                                         
SELECT/*+ 3 */ A,B FROM T1,T2 WHERE A=B                      690avsp8mkags          23            100             20 ALL_ROWS        1838229974                                                         
SELECT/*+ 4 */ * FROM T1,T2 WHERE A=B                        fdpt6967c5w7k          23            100             20 ALL_ROWS        1838229974                                                         

4 rows selected.

Elapsed: 00:00:00.04
SQL> spool off