PL/SQL Best Practices

Number	PL/SQL Best Practice	Comments
1	Use the functions and procedures provided by Oracle	Try minimize writing your own functions and procedures.
2	Use Oracle's Searching and Sorting routines	The built-in routines are highly optimized.
3	Take advantage of the ways Oracle performs control logic evaluation	If you have multiple conditions that control branching, Oracle evaluates them in the order you provide them. It will not evaluate all the conditions unless it needs to. Order your conditions in a manner that allows Oracle to take short cuts. If there are two conditions, then put the most restrictive condition first.
4	Avoid implicit datatype conversions	Avoid comparing variables that have different datatypes. The time spent on the implicit datatype conversions during each execution could be reclaimed if the datatypes were converted to a consistent set prior to comparisons.
5	Size VARCHAR2 variables properly	VARCHAR2(1000)   vs.  VARCHAR2(2000) If the size is less than 2000, then PL/SQL allocates enough memory to hold the declared length of the variable. But if the size is greater than or equal to 2000, PL/SQL dynamically allocates only enough memory to handle the actual value.
6	Use PL/SQL within SQL statements	There are potential performance gains by including a PL/SQL function as part of a query.
7	Use DBMS_PROFILER to identify problems	Capture the profiling statistics to identify the lines in your code that take the most time.
8	Use PL/SQL features for Bulk operations	Avoid row by row operations and use FORALL and  BULK COLLECT.
9	Use JOIN methods carefully	Based on the conditions in your query, the available indexes, and available statistics, the optimizer chooses which JOIN operation to use. You can influence the optimizer to use a different JOIN method.