Monday, August 25, 2008

Oracle Performance Tuning and Optimization Part II

As I mentioned in my last blog on oracle performance, we will see some more better ways of writing SQL queries in oracle.

While Joining multiple tables specify smallest table at last.

As we saw, using joins produce better results than inner query, we have to order the table in such a way that the small table will be specified at last in SQL, so that the number of times oracle compare the records will be reduce.

Before Optimization After Optimization
select so.documnet_number
         count(1)
from activation a,
       serv_ord so,
       task t
where t.documnet_number=so.document_number and
         so.serv_item_id=a.serv_item_id 
group by so.document_number
select so.documnet_number
         count(1)
from task t,
       serv_ord so,
       activation a,
where t.documnet_number=so.document_number and
         so.serv_item_id=a.serv_item_id 
group by so.document_number
Time Taken : 10 Sec Time Taken : 2.1 Sec

Replace NOT IN with NOT EXISTS

This is very similar to avoiding sub query

Before Optimization After Optimization
Select count(1)
from task t
where t.document_number not in (
select tt.document_number from task_bkp)
select count(1)
from task t
where not exists
(select tt.document_number from task_bkp)
Time Taken : 500 Sec Time Taken : 6 Sec

Use FORALL instead of FOR

This is a handy option available in oracle for inserting bulk records. I have already given more details about this in my blog on Best practices in PL/SQL.Think about FORALL whenever you write a cursor.It will reduce the loop execution time.

Before Optimization

After Optimization

DECLARE

TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;

TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;

pnums NumTab;

pnames NameTab;

BEGIN

FOR j IN 1..20000 LOOP -- load index-by tables

pnums(j) := j;

pnames(j) := 'Part No. ' || TO_CHAR(j);

END LOOP;

FOR i IN 1..20000 LOOP -- use FOR loop

INSERT INTO parts VALUES (pnums(i), pnames(i));

END LOOP;

END;

 

DECLARE

TYPE NumTab IS TABLE OF NUMBER(5) INDEX BY BINARY_INTEGER;

TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;

pnums NumTab;

pnames NameTab;

BEGIN

FOR j IN 1..20000 LOOP -- load index-by tables

pnums(j) := j;

pnames(j) := 'Part No. ' || TO_CHAR(j);

END LOOP;

FORALL I in 1 .. 20000 -- use FORALL

INSERT INTO parts VALUES (pnums(i), pnames(i));

END;

Time taken in sec: 11.0 Sec

Time Taken in Secs: 0.5 sec

FORALL will reduce the loop-back time between PL/SQL and SQL engine.You can download my presentation on best practices of PL/SQL from my site.

use BULK COLLECT

BULK COLLECT is an option provided by Oracle for avoiding the use of loops in collecting data from table.For any heavy data processing application BULK COLLECT will be useful.For example we need to select 1000 records from a table and process the records and insert it into another table, then we can either bulk collect.

Before Optimization

After Optimization

declare

Type bcode is table of products.barcode%TYPE;

i int;

barc bcode;

cursor cur_seq is

select barcode from products where rownum<100001;

begin

i:=0;

for cur_dta in cur_seq loop

i:=i+1;

barc:=cur_dta.barcode;

end loop;

end;

declare

Type bcode is table of products.barcode%TYPE;

i int;

barc bcode;

begin

select barcode BULK COLLECT into barc from products where rownum<100001;

end;

Time taken : 17sec

Time taken : 1.41 sec

Summary of Optimization

  • Use uniform coding standards across the application
  • Avoid data type mismatch for index columns
  • Avoid functions on index columns
  • Move conditions from having clause to where clause
  • Use joins instead of nested selects, whenever possible
  • Replace Not IN by Not EXISTS or OUTER JOIN (but not in distributed environment)
  • Use bulk inserts when inserting more records
  • Use BULK COLLECT clause when fetching records

As a fan of Mr.Barney.

I demand, you fellow programmers to follow some best practices while writing PL/SQL code.

Post a Comment