PL/SQL : Stop Making The Same Performance Mistakes

PL/SQL is great, but like any programming language it is capable of being misused. Rather than listing a bunch of 'best practices', this presentation highlights the common mistakes made when developing in PL/SQL, turning what should be an elegant solution into a resource hog. With live demonstrations, this presentation will help you get the best performance from your PL/SQL.

Transcript

1. http://www.oracle-base.com Tim Hall Oracle ACE Director Oracle ACE of the Year 2006 OakTable Network OCP DBA (7, 8, 8i, 9i, 10g, 11g) OCP Advanced PL/SQL Developer Oracle Database: SQL Certified Expert http://www.oracle-base.com Books Oracle PL/SQL Tuning Oracle Job Scheduling
2. http://www.oracle-base.com  … when you could use SQL.  PL/SQL is a procedural extension to SQL, not a replacement for it.  SQL is usually quicker than the PL/SQL alternative.  Be an SQL expert who knows some PL/SQL, not the other way round.  Learning cool stuff like Analytic Functions will help you avoid writing unnecessary PL/SQL.
3. http://www.oracle-base.com  Stop using UTL_FILE to read text files if you can use external tables. (external_table.sql)  Stop writing PL/SQL merges if you can use the MERGE statement. (merge.sql)  Stop coding multi-table inserts manually. (multitable.sql)  Stop using FORALL when you could use DML error logging (DBMS_ERRLOG) to trap failures in DML (dml_el.sql)  All use DML, which is easily parallelized.
4. http://www.oracle-base.com • 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. Oracle Server PL/SQL Engine PL/SQL Block PL/SQL Block Procedural Statement Executor SQL Statement Executor
5. http://www.oracle-base.com  Populate collections directly from SQL using BULK COLLECT. (bulk_collect.sql)  Collections are held in memory, so watch collection sizes. (bulk_collect_limit.sql)  Implicit array processing introduced in 10g. (implicit_array_processing.sql) 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;
6. http://www.oracle-base.com  Bind data in collections into DML using FORALL. (insert_forall.sql)  Triggers may not work as you expect.  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);
7. http://www.oracle-base.com  By-value: Procedure uses temporary buffer. Copies value back on successful completion.  By-reference: Procedure uses original memory location directly.  The NOCOPY hint allows OUT and IN OUT parameter to be passed by-reference, rather than by-value. (nocopy.sql)  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;
8. http://www.oracle-base.com  When you use the wrong datatypes, Oracle performs implicit conversions.  Datatype conversions take/waste time.  Oracle provide multiple numeric datatypes with differing performance characteristics. (numeric_types.sql)  Use the appropriate datatype for the job.
9. http://www.oracle-base.com  Stop using index searches when you can use ROWIDs.  Stop using custom code when Oracle provide built-in functions.  Stop using explicit cursors.  Stop avoiding instrumentation in your code.  Short-Circuit Evaluations.  Logic/Branching order.  Stop waiting for requests to complete when you could decouple processes.
10. http://www.oracle-base.com  Questions?  References: http://www.oracle-base.com  Demos: http://www.oracle-base.com/workshops