10g CBO的一个问题

 declare
     type rc is ref cursor;
     l_rc rc;
     l_dummy all_objects.object_name%type;
     l_start number default dbms_utility.get_time;
     y all_objects.object_name%type;
 begin
     for i in 1 .. 1000
     loop
         open l_rc for
         'select object_name
            from all_objects
           where object_id = :x and object_name=:y' using i,y;
         fetch l_rc into l_dummy;
         close l_rc;
     end loop;
     dbms_output.put_line
     ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
       ' seconds...' );
 end;

转Eygle的话:我想具体原因应该在优化器上,10g缺省采用的是CBO优化器.
9i如果没有统计信息那么缺省的是RBO.

对于这类大批量循环测试,CBO的影响会非常明显.

请参考我在10g下的测试结果:

代码:
   
   


SQL> select * from v$version;



BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production



SQL> set serveroutput on

SQL> declare

  2       type rc is ref cursor;

  3       l_rc rc;

  4       l_dummy all_objects.object_name%type;

  5       l_start number default dbms_utility.get_time;

  6   begin

  7       for i in 1 .. 1000

  8       loop

  9           open l_rc for

 10           'select object_name

 11              from all_objects

 12             where object_id = ' || i;

 13           fetch l_rc into l_dummy;

 14           close l_rc;

 15       end loop;

 16       dbms_output.put_line

 17       ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||

 18         ' seconds...' );

 19   end;

 20  /

26.99 seconds...



PL/SQL procedure successfully completed.



SQL> 

SQL> declare

  2       type rc is ref cursor;

  3       l_rc rc;

  4       l_dummy all_objects.object_name%type;

  5       l_start number default dbms_utility.get_time;

  6   begin

  7       for i in 1 .. 1000

  8       loop

  9           open l_rc for

 10           'select /*+ rule */ object_name

 11              from all_objects

 12             where object_id = ' || i;

 13           fetch l_rc into l_dummy;

 14           close l_rc;

 15       end loop;

 16       dbms_output.put_line

 17       ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||

 18         ' seconds...' );

 19   end;

 20  /

16.37 seconds...



PL/SQL procedure successfully completed.



SQL>