Performance measures in Pl/Sql

By Vinay | October 16, 2009 | 2,044 views
Category Common, Database, Pl/Sql, Uncategorized

About author I am Oracle/Java professional.Working on J2EE technologies and i.e Oracle ADF,Java,J2ee,PL/sql,Top Link,Apps for 2+ years.I am passionate about learning new technologies.I am sharing my knowledge. Give your views and suggestion. http://www.linkedin.com/in/vinaykumar2 Read more from this author

Cases should be avoided -1. DO NOT use FUNCTIONS (,TO_NUMBER ,Decode,NVL, TO_CHAR) unnecessarily when a simple join would work
2. DO NOT use ‘SELECT *’ explicitly. Make sure that you select only required columns from the tables.
3. DO NOT use HAVING clause where a WHERE clause would do.
4. DO NOT use ORDER BY clause in a query unless necessarily by logic.
5. DO NOT use UNION and DISTINCT together in a single query
6. DO NOT use CURSOR LOOPS unnecessarily when a simple/single DML/SELECT can serve the purpose
7. DO NOT use DUAL table unnecessarily in programs and queries.

Cases should be followed -
1. Use UNION ALL as far as possible instead of UNION.
2. Use single/direct DML’s/SELECT’s instead of LOOP’s.
3. Use BULK COLLECT & FORALL for looping when the loop is going to get executed multiple time (take > 100 iterations as a base).
4. Use MATERIALIZED VIEWS or GLOBAL TEMPORARY TABLE for queries fetching data across dblinks
5. Use EXISTS in place of IN in the queries.
6. Care should be taken to use the LIMIT clause while using BULK operations in order to limit memory utilization. LIMIT should be set between 500-1000 and reduced or decreased as per performance behavior of the program

References – http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/design.htm#i29012

Read more post on Performance measures in Pl/Sql Pl/Sql

7 comments | Add One

Comments

Vivek kompella - 10/20/2009 at 10:47 pm
Hey Vinay,

Its good to list out these points, but it will cretainly be helpful if you can also list out the reasons behind do not use…
Marcin - 10/21/2009 at 1:13 am
Good summary … but it’s just induction – missing content with explenation and examples.
Sriram - 10/26/2009 at 1:40 pm
Good cheat sheet!

BULK COLLECTs are somtimes not good when processing like Millions of records.Cursors are better when dealing tonnes of records. Do you agree with me friend ?
vinay - 10/27/2009 at 2:55 am
Bulk collect are good when to fetech the records at once .cursor are bad for large number of record processing.that is done sequently and it impact the perfomance issue.
Mandeep Grewal - 10/30/2009 at 12:37 am
Hello,
Good article.