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.