Oracle Performance and Engineering

I got some emails on why I named a particular method "Blind Tuning".

It's because the options to 'try out' were very limited. I received the query in email and a bunch of statistics - I had no access to customer data, database and thus was restricted to just 'methods'. Then I tried the 'if I were optimizer how I would approach the problem' method, found out the disparity with the explain plan I was sent - and suggested a cure.

However, I finally finished my paper and presentation. While making the last two slides on the importance of bulking up in PL/SQL - I wrote a small test case. Putting it up here. The results were gotten on a 9.2.0.5 database on Itanium Linux.

create table t (x number, y date);

Declare
begin
for I in 1 .. 100000
Loop
Insert into t values (i, sysdate);
End loop;
End;
/
-- t has 100K records

create table t1 as select * from t where 1 = 2;
-- t1 has 10K records.

-- X is indexed on both tables.

-- Business need is to update T set t.x = t1.x where there exists a record t.y = t1.y

-- LOOP method / Old iteration

Declare
begin
for c1_rec in (select x,y from t1)
Loop
update t set y = c1_rec.y where x = c1_rec.x;
End loop;
End;
/

1.94 secs

--using BULK COLLECT and FORALL

Declare
type i_num is table of number;
type i_date is table of date;
-- declare PL/SQL "collections"

p_num1 i_num;
p_num2 i_date;
-- initialize PL/SQL collections to hold CURSOR

begin

select x, y
BULK COLLECT into p_num1, p_num2
from t1 ;
--BULK BIND

forall indx in p_num1.FIRST .. p_num1.LAST
update t set y = p_num2(indx) where x = p_num1(indx);
-- BULK DML

End;
/

0.70 sec.

i.e., BULKING up saves about 64% time!

What if we try to do this non-procedurally through a single SQL ;)
Say,

update t set t.y = (select t1.y from t1
where t1.x = t.x)
where exists (select '' from t1 where t.x = t1.x);

2.83 sec

How to tune this up? ;)