Thanks Christian, I agree to 'see' it I will need to run it, I just don't have enough disk to load the database into 10g, I was hoping to import the user sans data and compile to 'see' if feature will be used. The presentation went into detail about not all queries will use array processing - only 'static sql cursor for loop' which in the presentation has the form
for j in (select ... From tbl) Loop Yada yada; End loop;
I'm not sure if the code is wrapped, but the select that is problematic is likely not of that form. Obviously a code re-write is in order, but I have been asked to estimate the benefit of a quick jump to 10g for this issue. I am now leaning to telling management the silver bullet is gray and having them push the application vendor.
Thanks again
-- --Original Message-- -- From: "Christian Antognini"<Christian.Antognini@(protected)> Sent: 3/17/05 10:57:06 AM To: "bruce.mccartney@(protected)"<bruce.mccartney@(protected)> Cc: "Oracle-l@(protected)"<Oracle-l@(protected)> Subject: RE: PL/SQL Automatic BULK
Hi Bruce
>Saw this mentioned at OOW, anybody seen it work?
If you enable SQL trace you will "see" it.
>The jist is that the new optimizing compiler will >take a static sql cursor for loop and 'bulkify' it.
It's a prefetching. Basically instead of fetching one single row at a = time, now 100 rows are prefetched.
>What limits are in the optimizer bulkification process
The prefetching feature is only enabled for queries, i.e. there's no = automatic FORALL...
>Can you tell if bulkification is performed without running the p/sql.
If PLSQL_OPTIMIZE_LEVEL is set to 2 the feature is activated.
HTH Chris -- http://www.freelists.org/webpage/oracle-l
