PL/SQL : Stop making the same performance mistakes

PL/SQL : Stop making the same performance mistakes Tim Hall Oracle ACE Director Oracle ACE of the Year 2006 OakTable NetworkOCP 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 http://www.oracle-base.com

Stop using PL/SQL… • … 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 helpyou avoid writing unnecessary PL/SQL.(setup.sql) http://www.oracle-base.com

Stop using unnecessary PL/SQL • 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 useDML error logging (DBMS_ERRLOG) to trapfailures in DML (dml_el.sql) • All use DML, which is easily parallelized. http://www.oracle-base.com

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

Stop Avoiding Bulk-Binds (BULK COLLECT) • 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; http://www.oracle-base.com

Stop Avoiding Bulk-Binds (FORALL) • Bind data in collections into DMLusing 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 thenumber of rows affected by each statement. • The SAVE EXCEPTIONS allows bulk operations tocomplete. • 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

Stop Using Pass-By-Value (NOCOPY) • 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; http://www.oracle-base.com

Stop Using the Wrong Datatypes • 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. http://www.oracle-base.com

Quick Points • 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 youcould decouple processes. http://www.oracle-base.com

The End… • Questions? • References: http://www.oracle-base.com • Demos:http://www.oracle-base.com/workshops http://www.oracle-base.com

PL/SQL : Stop making the same performance mistakes