Re: Outcome of my research on OCP.

On Wed, 22 Jun 2005 07:05:35 -0700, DA Morgan <damorgan_at_psoug.org> wrote:

>simon wrote:
>
>>>True to a point. But we all know cursor loops exist ... but that does
>>>not mean you should use them. The same thing goes for unique indexes.
>>>There is no valid reason I can think of, in Oracle, to ever create a
>>>unique index.
>>>
>>>Addendum: And yes I am going to intentionally wait for someone to ask
>>>me why even though this has been covered in this group many times before.
>>
>>
>> Yes, may I ask why CURSOR LOOP (I assume you mean CURSOR FOR LOOP in the
>> PLSQL stored proc) should not be used. I am new to this group and missed
>> many old posts - and I think it doesnt hurt to discuss the same issue again
>> as there are always newcomers learning Oracle from scratch.
>
>Because they are far less efficient than bulk binding. There is no valid
>reason for making 100 separate trips to the database for 1 record each
>when you can collect all 100 records at once. Same thing goes for using
>FORALL to perform DML.
>
>Expect to see, for the simple addition of one line of code, performance
>improvements of 25 - 1000%.

 If you're on 10g where you may want to look at this article from Tom Kyte: (the really relevant stuff starts with the line "Use Bulk Processing"):

 http://asktom.oracle.com/pls/ask/download_file?p_file=3067171813508366601

>Demos of bulk binding can be found in Morgan's Library at www.psoug.org

 I've had a go with the demos on my (somewhat resource constrained...) dev machine running 10.1.0.4, and I'm consistently seeing the "old_way" procedure running faster than "bulk_test".

 I've also knocked up a procedure that uses bulk binding into two arrays instead of one array of an object type, and whilst that's faster than the "bulk_test", it's still a little slower than "old_way" on 10.1.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production With the Partitioning and Data Mining options

SQL> @bulk_test.sql
SQL> set timing on
SQL> exec old_way

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.12
SQL> set timing off
SQL> truncate table child;

Table truncated.

SQL> set timing on
SQL> exec bulk_test

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.78
SQL> set timing off
SQL> truncate table child;

Table truncated.

SQL> set timing on
SQL> exec bulk_array_test

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.70
SQL> set timing off
SQL> truncate table child;

Table truncated.

 Can't argue with FORALL for DML though - clear gains there.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool