Boost Performance with PL/SQL Programming Best Practices

1. Boost Performance with PL/SQL Programming Best Practices Tim Hall Oracle ACE Director Oracle ACE of the Year 2006OCP DBA (7, 8, 8i, 9i, 10g, 11g) OCA PL/SQL Developer http://www.oracle-base.com Oracle PL/SQL Tuning (Rampant) Oracle Job Scheduling (Rampant) http://www.oracle-base.com

2. http://www.oracle-base.com

3. Bind Variables • Oracle performs a CPU intensive hard parse for all new statements. Unnecessary parses waste CPU and memory. • Statements already present in the shared pool only require a soft parse. • Statement matching uses “Exact Text Match”, so literals, case and whitespaces are a problem. • Make sure client application use bind variables when calling your code. • Use CURSOR_SHARING parameter when you can’t alter code. • Be careful when using dynamic SQL. SELECT * FROM emp WHERE empno = 1; SELECT * FROM emp WHERE empno = 2; SELECT * FROM emp WHERE empno = :p_empno; ALTER SESSION SET cursor_sharing = force; http://www.oracle-base.com

4. PL/SQL Engine PL/SQL Block PL/SQL Block Procedural Statement Executor Oracle Server SQL Statement Executor Overview of the PL/SQL Engine • PL/SQL contains procedural and SQL code. • Each type of code is processed separately. • Switching between code types causes an overhead. • The overhead is very noticeable during batch operations. • Bulk binds minimize this overhead. http://www.oracle-base.com

5. Bulk-Binds – BULK COLLECT • Populate collections directly from SQL using BULK COLLECT. • Demo • Collections are held in memory, so watch collection sizes. • Demo • Implicit array processing introduced in 10g. • Demo SELECT * BULK COLLECT INTO l_tab FROM tab1; OPEN c1; LOOP FETCH c1 BULK COLLECT INTO l_tab LIMIT 1000; EXIT WHEN l_tab.count = 0; -- Process chunk. END LOOP; CLOSE c1; FOR cur_rec IN (SELECT * FROM tab1) LOOP -- Process row. END LOOP; http://www.oracle-base.com

6. Bulk-Binds – FORALL • Bind data in collections into DML using FORALL. • Demo • Use INDICIES OF and VALUES OF for sparse collections. • Use SQL%BULK_ROWCOUNT to return the number of rows affected by each statement. • The SAVE EXCEPTIONS allows bulk operations to complete. • Exceptions captured in SQL%BULK_EXCEPTIONS. FORALL i IN l_tab.FIRST .. l_tab.LAST INSERT INTO tab2 VALUES l_tab(i); http://www.oracle-base.com

7. Short-Circuit Evaluations and Logic Order • If left side of an OR expression is TRUE, the whole expression is TRUE.TRUE OR FALSE = TRUETRUE OR TRUE = TRUE • If the left side of an AND expression is FALSE, the whole expression is FALSE.FALSE AND FALSE = FALSEFALSE AND TRUE = FALSE • In these cases Oracle doesn’t evaluate the second half of the expresson. • Place “least expensive” tests to the left of expressions. • Evaluations of ELSIF and CASE statements stops once a match is found. • Place the “most likely outcomes” at the top of branching constructs. IF l_continue OR fn_rec_count > 10 THEN -- Do Something END IF; IF l_continue AND fn_rec_count > 10 THEN -- Do Something END IF; IF l_rec_type = 'POPULAR' THEN -- Do Something ELSIF l_rec_type = 'MEDIUM' THEN -- Do Something ELSIF l_rec_type = ‘UNPOPULAR' THEN -- Do Something END IF; CASE l_rec_type WHEN 'POPULAR' THEN -- Do Something WHEN 'MEDIUM' THEN -- Do Something WHEN ‘UNPOPULAR' THEN -- Do Something END CASE; http://www.oracle-base.com

8. Declarations in Loops • Code within loops gets run multiple times. • Variable declarations and procedure/function calls in loops impact on performance. • Simplify code within loops to improve performance. • Oracle 11g offsets solves some of the performance impact with automatic subprogram inlining. • Don’t stop using modular code because of this. Keep these results in context! -- Bad idea. FOR i IN 1 .. 100 LOOP DECLARE l_str VARCHAR2(200); BEGIN -- Do Something. END; END LOOP; -- Better idea. DECLARE l_str VARCHAR2(200); BEGIN FOR i IN 1 .. 100 LOOP -- Do Something. END LOOP; END; http://www.oracle-base.com

9. Efficient Function Calls • When functions are called in SQL statements, minimize the number of calls by filtering the data if possible. • Demo • When function calls are present in the WHERE clause, consider function-based indexes. • Demo • Consider maintenance costs, disk space requirements and global affect of function-based indexes. SELECT SQRT(num_val), COUNT(*) AS amount FROM tab1 GROUP BY SQRT(num_val); SELECT SQRT(num_val), amount FROM (SELECT num_val, COUNT(*) AS amount FROM tab1 GROUP BY num_val)) CREATE INDEX efficient_functions_fbidx ON efficient_functions (SQRT(data_length)); SELECT COUNT(*) FROM efficient_functions ef WHERE SQRT(ef.data_length) = 5.47722558; http://www.oracle-base.com

10. Using the NOCOPY Hint • The NOCOPY hint allows OUT and IN OUT parameter to be passed by-reference, rather than by-value. • By-value: Procedure uses temporary buffer. Copies value back on successful completion. • By-reference: Procedure uses original memory location directly. • Beware of affect of error handling and parameter aliasing on parameter values. • It’s a hint, not a directive, so it can be ignored PROCEDURE myproc (p_tab IN OUT NOCOPY CLOB) IS BEGIN -- Do something. END; http://www.oracle-base.com

11. PLSQL_OPTIMIZE_LEVEL • The PLSQL_OPTIMIZE_LEVEL parameter was introduced in 10g to control how much optimization the compiler performs: • 0 : Code will compile and run in a similar way to 9i and earlier. New actions of BINARY_INTEGER and implicit array processing lost. • 1 : Performs a variety of optimizations, including elimination of unnecessary computations and exceptions. Does not alter source order. • 2 : Performs additional optimizations, including reordering source code if necessary. The is the default setting in 10g and 11g. • 3 : New in 11g. Yet more optimizations and subprogram inlining. • The optimization level associated with the library unit is visible using the %_PLSQL_OBJECT_SETTINGS view. • Adjust only if package load times are adversely affected. ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=0; ALTER PROCEDURE my_big_package COMPILE; http://www.oracle-base.com

12. Conditional Compilation CREATE OR REPLACE PROCEDURE debug (p_text IN VARCHAR2) AS $IF $$debug_on $THEN l_text VARCHAR2(32767); $END BEGIN $IF $$debug_on $THEN DBMS_OUTPUT.put_line(p_text); $ELSE NULL; $END END debug; • Conditional compilation was introduced in 10g to allow source to be tailored to specific environments using compiler directives. • Compiler flags are identified by the “$$” prefix. Conditional control is provided by the $IF-$THEN-$ELSE-$END syntax. • The database source contains all the directives, but the post-processed source is displayed using the DBMS_PREPROCESSOR package. • The PLSQL_CCFLAGS clause is used to set the compiler flags. • The compiler flags associated with the library unit is visible using the %_PLSQL_OBJECT_SETTINGS view. ALTER PROCEDURE debug COMPILE PLSQL_CCFLAGS = 'debug_on:TRUE' REUSE SETTINGS; CREATE OR REPLACE PROCEDURE debug (p_text IN VARCHAR2) AS l_text VARCHAR2(32767); BEGIN DBMS_OUTPUT.put_line(p_text); END debug; CREATE OR REPLACE PROCEDURE debug (p_text IN VARCHAR2) AS BEGIN NULL; END debug; http://www.oracle-base.com

13. Native Compilation of PL/SQL • By default PL/SQL is interpreted. • Set PLSQL_CODE_TYPE parameter to NATIVE before creating or compiling code. • Prior to 11g, native compilation converts PL/SQL to C, which is then compiled in shared libraries. • Improves performance of procedural logic. • Demo • Doesn’t affect the speed of database calls. • The PLSQL_CODE_TYPE associated with the library unit is visible using the %_PLSQL_OBJECT_SETTINGS view. ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE; ALTER PROCEDURE my_proc COMPILE; http://www.oracle-base.com

14. INTEGER Types • NUMBER and it’s subtypes use an Oracle internal format, rather than the machine arithmetic. • INTEGER and other constrained type need additional runtime checks compared to NUMBER. • PLS_INTEGER uses machine arithmetic to reduce overhead. • BINARY_INTEGER is slow in 8i and 9i, but fast in 10g because it uses machine arithmetic. • Demo • 11g includes SIMPLE_INTEGER which is quick in natively compiled code. • Use the appropriate datatype for the job. http://www.oracle-base.com

15. BINARY_FLOAT and BINARY_DOUBLE • New in 10g. • They use machine arithmetic, like PLS_INTEGER and BINARY_INTEGER. • Require less storage space. • Fractional values not represented precisely, so avoid when accuracy is important. • Approximately twice the speed of NUMBER. • Demo • Use the appropriate datatype for the job. http://www.oracle-base.com

16. Avoid unnecessary PL/SQL • SQL is usually quicker than PL/SQL. • Don’t use UTL_FILE to read text files if you can use external tables. • Don’t write PL/SQL merges if you can use the MERGE statement. • Use multi-table inserts, rather than coding them manually. • Use DML error logging (DBMS_ERRLOG) to trap failures in DML, rather than coding PL/SQL. • All use DML, which is easily parallelized. http://www.oracle-base.com

17. Quick Points • Use ROWIDs for update when data is selected for subsequent update. • Use built-in functions where possible. They are usually more efficient that your custom code. • Datatype conversions take time. Reduce them. • Implicit cursors are faster and do more exception checking than explicit cursors. Use them. • Hide performance problems by decoupling. Queue requests and process in batch. http://www.oracle-base.com

18. Best Practices Summary • Use bind variables to reduce the CPU and memory overheads associated with parsing similar statements multiple times. • Use bulk-binds. • Order logical expressions and branching structures to increase the speed of code. • Be mindful of the complexity of code blocks inside loops. • Reduce unnecessary function calls from SQL statements. • Use the NOCOPY hint. • Use PLSQL_OPTIMIZE_LEVEL for performance improvements and decreased load times. • Remove unnecessary code using conditional compilation. • Natively compile PL/SQL to improve the speed of procedural code. • Use PLS_INTEGER, BINARY_INTEGER, BINARY_FLOAT and BINARY_DOUBLE types instead of internal numeric types. • Avoid unnecessary PL/SQL by using leaner equivalents. • Improve record access speeds by using internal rowids rather than primary key searches. • Don’t duplicate functionality of built-in string functions. • Minimize the number of datatype conversions. • Use of implicit rather than explicit cursors. • Hide performance problems from users by decoupling processes. http://www.oracle-base.com

19. The End… • Questions? • References: http://www.oracle-base.com • Demos:http://www.oracle-base.com/workshops/plsql-best-practices/demos.zip http://www.oracle-base.com