A problem with 10 Gb CBO

Source: Internet
Author: User

Declare
Type RC is ref cursor;
Rochelle RC;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Y all_objects.object_name % type;
Begin
For I in 1000
Loop
Open l_rc
'Select object_name
From all_objects
Where object_id = 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;

To switch to eygle: I think the specific cause should be on the optimizer. By default, 10 Gb uses the CBO optimizer.
9i if no statistical information is available, the default value is RBO.

For such a large number of cyclic tests, the impact of CBO is very obvious.

Please refer to my test results under 10 GB:

Code:
  
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>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.