Oracle Performance and Engineering

Earlier I've written a couple of entries on how Oracle 10g PL/SQL compiler has been re-written to make it around 200% faster than 9i on average. Based on some crude tests on an Enterprise Applications - I've suggested the same to a customer. The results - in his words - were *amazing*.
He got more than 3 times speed improvement for his critical PL/SQL programs.

Here's what I asked him to do - he was running on 9i --

(1) Upgrade to 10g. Make sure things work.
(2) Make sure the 'PLSQL_OPTIMIZE_LEVEL' is not changed from its default value 2.
(3) Assign a directory to 'PLSQL_NATIVE_LIBRARY_DIR' where compiled PL/SQL program units will be stored. Make sure the regular backup schedules backs this directory up.
(4) Change PLSQL_CODE_TYPE to 'native'.
(5) Recompile ALL database PL/SQL objects.

There's no 'win all' rule - but I would definitely ask you to test this route.

PL/SQL itself is about twice faster in 10g compared to 9i. When running the NATIVE compiler (as opposed to INTERPRETED) you expect to get another 35% or so speed improvement!

I've done a lot of studies and experiments on 10g and found out the 10g PL/SQL compiler rocks because of -

(on the descending order of 'rockstar factor' ;) ) -

1. Remember the code like -
   
   for c1 in (select ename from emp) LOOP
   ;
   end loop;
   
   This type of code is abundant in 'old' installs. But after Oracle 8i you always knew that is not the most efficient way of doing thing. You can just do BULK PROCESSING of the whole dataset using FORALL. FORALL is one single call compared to one call per record in FOR.
   
   With 10g, you can relax. 10g automatically 'bulkify' all STATIC SQLs like above.
   

Thus the compiler itself 'rewrites' the inefficient code for 'array processing'.

i.e., the rewritten code will be like -

open c1; -- for select ename from emp
loop
fetch c1 bulk collect into m_array
forall i in 1..m_array_var.count

end loop;

2. No more need to use DBMS_SQL for dynamic SQL. Native Dynamic SQL (EXECUTE IMMEDIATE) is faster with 10g.

Here's what I've found on 10g -

EXECUTE IMMEDIATE and DBMS_SQL are both about equally fast for single-row operations. They're, however, slightly slower than Static SQL doing the same single-row operation.
On 9i EXECUTE IMMEDIATE was considerably slower since everytime EXECUTE IMMEDIATE was fired, it had to hard parse again. From 10g, EXECUTE IMMEDIATE can 'reuse the cursor'. i.e, starting from 10g an EXECUTE IMMEDIATE statement can be found in PL/SQL cursor cache.

EXECUTE IMMEDIATE is significantly slower than static SQL for multiple row non-bulked code since PL/SQL optimizer bulkify the static SQL in such cases. *Thus*, bulking up - array processing - still is and will remain a good practice. 10g just helped with bulkifying the static SQL, nothing so is still happening with dynamic sql!

EXECUTE IMMEDIATE and static SQL are very comparable when both are bulked.

(Contd. with examples..)

posted by Nilendu  # 4:49 PM