Achieving PL/SQL Excellence

Top 200 Oracle PL/SQL Tips for Tuning

Steven Feuerstein
Me - [Link]
PL/Solutions - [Link]
RevealNet - [Link]
[Link] – [Link]

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 1
 One Hour, 200 Tips, No Problem!

Improve the
performance of
your application
1000 fold for
only $19.95 a
month!*

*Plus shipping and handling and technical support. All

performance degradation the responsibility of the user.

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 2
 Resources for PL/SQL Tuning
‹ Interested in "Oracle tuning"? The world is your oyster:
– Oracle documentation
– Numerous tuning books and web sites
– Many, many tools

‹ But PL/SQL tuning? Slim pickings...

– PL/SQL books and general Oracle tuning books offer some
coverage, but it is minimal and piecemeal.
– Code Complete by Steve McConnell (Microsoft Press)
» Many tuning tips are not language-specific. This book offers an excellent
treatment of tuning philosophies and issues you have to address in any
programming language.

‹ PL/SQL tuning is tough, compared to SQL tuning.

– Optimize algorithms, write SQL in PL/SQL properly, tune PL/SQL
execution in the SGA.

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 3
 Putting Tuning in Context
OPTIMAL PATH TO OPTIMAL CODE
Write well-structured, readable code following
established best practices.

‹ Performance is just one aspect of high-quality software

and usually not the most important.
‹ You can’t improve performance without quantitative
analysis.
– Where are the bottlenecks?
– How much did my code’s performance improve?

‹ The 80/20 Rule: most programs spend most of their time in

a small portion of the code. [Link]
[Link]
– Ah, but which portion? This is hard to predict. [Link]

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 4
 Possible Tuning Topics for PL/SQL
‹ Analyze Performance of Your Application
‹ Optimize SQL inside Your PL/SQL
‹ Manage Code in the Database and SGA
‹ Optimize Algorithms
‹ Use Data Structures Efficiently

‹ All source code examples downloadable from the

RevealNet PL/SQL Pipeline Archive…
– Under Miscellaneous, PL/SQL Seminar Files
– [Link]
[Link]
[Link]

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 5
 PL/SQL Tuning and Best Practices

Writing SQL
in PL/SQL
‹ What's the Big Deal?
‹ Some Rules to Follow
‹ Synchronize Code with Data Structures
‹ Avoid Repetition of SQL
‹ Optimize the PL/SQL we write in SQL

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 6
 "SQL in PL/SQL" Rules to Follow
‹ Rule #1: Write your code so that it
adapts automatically (with
nothing more than a compile) to
changes in the underlying data
structures.
– Anchor declarations with %TYPE and
%ROWTYPE.
– Fetch into records, not variables.

‹ Rule #2: Never repeat any of the SQL (inserts, updates,

deletes, queries, DDL) in your code.
– Build layers of code around your data structures (table
encapsulation packages).

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 7
 Build SQL Encapsulation Packages
Insert
Update
Application
Employee
Delete Code
GetRow

‹ Store all of your SQL inside packages: one per table or

"business object".
– All DML statements written by an expert, behind a procedural
interface, with standardized exception handling.
– Commonly-needed cursors and functions to return variety of data
(by primary key, foreign key, etc.).
– If the encapsulation package doesn't have what you need, add the
new element, so that everyone can take advantage of it.
– Could create separate packages for query-only and change-related
functionality.
te_employee.*
9/16/2006 Copyright 2000 PL/Solutions
PL/SQL Tuning and Optimization - page 8
 Answer the Question Being Asked
‹ Are you a good listener?
Listening to what other people
is an excellent skill to have and
develop -- and it applies to
programming as well.
‹ All too often, we don't listen or
read carefully enough to the
requirement -- and we answer
the wrong question.

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 9
 If We Have At Least One…
CREATE OR REPLACE PROCEDURE drop_dept
(deptno_in IN NUMBER, reassign_deptno_in IN NUMBER)
IS
temp_emp_count NUMBER;
BEGIN
-- Do we have any employees in this department to transfer?
SELECT COUNT(*)
INTO temp_emp_count
FROM emp WHERE deptno = deptno_in;
-- Reassign any employees
IF temp_emp_count >0
THEN
UPDATE emp
SET deptno = reassign_deptno_in
WHERE deptno = deptno_in;
END IF;
DELETE FROM dept WHERE deptno = deptno_in;
COMMIT;
END drop_dept;

‹ How much is wrong with this code?

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 10
 The Minimalist Approach
‹ At least one row?
BEGIN
OPEN cur; Use an explicit cursor,
FETCH cur INTO rec; fetch once and then
IF cur%FOUND check the status.
THEN
...

‹ More than one row?

BEGIN
OPEN cur;
FETCH cur INTO rec;
IF cur%FOUND Use an explicit cursor,
THEN fetch once and then
FETCH cur INTO rec; fetch again. "Two
IF cur%FOUND times" is the charm.
THEN
...
[Link]

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 11
 Give W/One Hand, Take W/the Other
‹ Oracle has a habit of offering improvements in ways that
can make it very difficult for us to take advantage of them.
‹ When are SQL statements the same and yet different?

UPDATE ceo_compensation begin update ceo_compensation

SET stock_options = 1000000, set stock_options = 1000000,
salary = salary * 2.0 salary = salary * 2.0
WHERE layoffs > 10000; where layoffs > 10000; end;

UPDATE ceo_compensation BEGIN

SET stock_options = 1000000, UPDATE CEO_COMPENSATION
salary = salary * 2 SET STOCK_OPTIONS = 1000000,
WHERE layoffs > 10000; SALARY = SALARY * 2.0
WHERE LAYOFFS > 10000;
END;
begin UPDATE ceo_compensation
SET stock_options = 1000000,
salary = salary * 2 WHERE
All these statements are executed at 10 AM.
layoffs > 10000; end;
How many times does Oracle parse?

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 12
 SQL Cursors in the SGA
‹ As of Version 7, all parsed cursors (SQL statements as
well as PL/SQL blocks) are cached in the SGA.
‹ Every time you request a parse (again: SQL and PL/SQL
blocks), Oracle hashes the string.
– If it finds an exact, physical match already in the SGA, then it uses
that pre-parsed cursor.
– This can lead to significant performance improvements.
– But also a problem: we live, work and breathe at the logical level.
Now we have to be aware of the physical form of our code!
‹ Some things to keep in mind:
– White space counts -- unless the SQL statement is executed inside
a PL/SQL block.
– In this case, the PL/SQL engine does some pre-formatting -- all
upper case, no extra white space.

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 13
 Analyzing SGA-Cached Cursors
‹ The best way to understand the requirements and activity
of the PL/SQL code in the SGA is to look at the SGA.
‹ Oracle offers a variety of data structures to get this
information:
– V$ROWCACHE: check for data dictionary cache hits/misses
– V$LIBRARYCACHE: check for object access hits/misses
– V$SQLAREA: statistics on shared SQL area, one row per SQL
string (cursor or PL/SQL block)
– V$DB_OBJECT_CACHE: displays info on database objects that are
cached in the library cache.
SQL> exec insga.show_similar
*** Possible Statement Redundancy:
begin fix_me (1); end;
begin fix_me(1); end;
*** Possible Statement Redundancy:
select * from EMP grantv$.sql
select * from emp [Link]
[Link]

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 14
 Best Way to Code Single Row Query?
‹ Let's end the debate over implicit vs. explicit cursors.
There are pluses and minuses for each approach...
Reuse: There is no way to reuse an implicit cursor, except by calling
the program in which the cursor is executed. A cursor declared in a
package specification can be used in multiple programs.
Performance: Implicits in 7.3 and above can be faster than explicits.
Implicits are more likely, however, to be coded repetitively. Explicit
cursors improve chance of using pre-parsed SQL in the SGA
Programmatic control: With explicits, you're not forced into the
exception section when various data conditions arise.

Developer productivity: Why lose time trying to decide which way to code
each single-row queries? Give yourself one less thing to think about.

‹ The real question is how can we make sure that our

queries are always encapsulated?
[Link]
[Link]

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 15
 Don't Dither – Encapsulate!
‹ Whichever way you go, put the logic in a function.

FUNCTION i_empname ( FUNCTION e_empname (

employee_id_in IN employee_id_in IN
employee.employee_id%TYPE)
employee.employee_id%TYPE) RETURN fullname_t
RETURN fullname_t IS
IS rec allcols_cur%ROWTYPE;
retval fullname_t; BEGIN
BEGIN OPEN allcols_cur (employee_id_in);
SELECT last_name FETCH allcols_cur INTO rec;
INTO retval CLOSE allcols_cur;
FROM employee
WHERE employee_id = IF rec.employee_id IS NOT NULL
employee_id_in; THEN
RETURN retval; RETURN rec.last_name)
EXCEPTION ELSE
WHEN NO_DATA_FOUND RETURN NULL;
THEN RETURN NULL; END IF;
WHEN TOO_MANY_ROWS END;
THEN log_error; RAISE;
END;
[Link]
9/16/2006 Copyright 2000 PL/Solutions
PL/SQL Tuning and Optimization - page 16
 Ah, the Wonders of Dynamic SQL!

CREATE OR REPLACE PROCEDURE updnumval (

col_in IN VARCHAR2,
ename_in IN [Link]%TYPE,
val_in IN NUMBER)
IS
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;
dmlstr PLV.dbmaxvc2 :=
'UPDATE emp SET ' || col_in || ' = ' || val_in ||
' WHERE ename LIKE UPPER (''' || ename_in || ''')';
BEGIN
DBMS_SQL.PARSE (cur, dmlstr, DBMS_SQL.NATIVE);

fdbk := DBMS_SQL.EXECUTE (cur);

Just throw a bunch of
DBMS_OUTPUT.PUT_LINE ( strings together and off
'Rows updated: ' || TO_CHAR (fdbk));
you go!
DBMS_SQL.CLOSE_CURSOR (cur); Well, maybe not...
END;
/

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 17
 DynSQL: Bind Whenever Possible
‹ You can concatenate rather than bind, but binding is
almost always preferable. Two key reasons:
– Simpler code to build and maintain
– Improved application performance

‹ Simpler code to build and maintain

– Concatenation results in much more complicated and error-prone
code unless you are doing a very simple operation.

‹ Improved application performance

– Concatenates requires an additional call to DBMS_SQL.PARSE
and also increases the likelihood that the SQL statement will be
physically different, requiring an actual re-parsing and
unnecessary SGA utilization.

‹ Note: you cannot bind schema elements, like table names.

[Link]
[Link]
[Link]
9/16/2006 Copyright 2000 PL/Solutions [Link]
PL/SQL Tuning and Optimization - page 18
 PL/SQL Tuning & Best Paractices

Optimize Algorithms

‹ Avoid Unnecessary Code Execution

‹ Answer the Question Being Asked
‹ Do Lots of Stuff At the Same Time
‹ Avoid the Heavy Lifting

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 19
 Do No Unnecessary Thing - 1
‹ What’s wrong with this code?

DECLARE
CURSOR emp_cur
IS
SELECT last_name, TO_CHAR (SYSDATE, 'MM/DD/YYYY') today
FROM employee;
BEGIN
FOR rec IN emp_cur
LOOP
IF LENGTH (rec.last_name) > 20
THEN
rec.last_name := SUBSTR (rec.last_name, 20);
END IF;
process_employee_history (rec.last_name, today);
END LOOP;
END;
/

slowalg_q1.sql
slowalg_a1.sql

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 20
 Do No Unnecessary Thing - 2
‹ This program is running slowly. How can I improve it?
– This is a test of analyzing algorithms for unnecessary and/or slow
program performance, and tuning of DBMS_SQL code.

CREATE OR REPLACE PROCEDURE insert_many_emps

IS
cur INTEGER := DBMS_SQL.open_cursor;
rows_inserted INTEGER;

BEGIN
DBMS_SQL.parse (cur,
'INSERT INTO emp (empno, deptno, ename)
VALUES (:empno, :deptno, :ename)',
DBMS_SQL.native);

FOR rowind IN 1 .. 1000

LOOP
DBMS_SQL.bind_variable (cur, 'empno', rowind);
DBMS_SQL.bind_variable (cur, 'deptno', 40 * rowind);
DBMS_SQL.bind_variable (cur, 'ename', 'Steven' || rowind);
rows_inserted := DBMS_SQL.execute (cur);
slowsql_q2.sql
END LOOP;
slowsql_a2.sql
slowsql_a2.tst
DBMS_SQL.close_cursor (cur); loadlots*.*
END;
9/16/2006 Copyright 2000 PL/Solutions
PL/SQL Tuning and Optimization - page 21
 IF There Are Too Many IFs…
‹ How can I optimize this code?

PROCEDURE exec_line_proc (line IN INTEGER)

IS
BEGIN
IF line = 1 THEN exec_line1; END IF;
IF line = 2 THEN exec_line2; END IF;
IF line = 3 THEN exec_line3; END IF;
...
IF line = 2045 THEN exec_line2045; END IF;
END;

So big, it
won’t even
compile slowalg_q2.sql
slowalg_a2.sql

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 22
 PL/SQL Tuning and Best Practices

Use Data
Structures
Efficiently

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 23
 Shortest Path Between Two Points?

Data User

‹ What’s the shortest/fastest way to connect these two?

– Keep the data as close as possible to the user/program that needs
the data.

‹ Packages offer an ideal caching mechanism.

– Any data structure defined at the package level (whether in
specification or body) serves as a persistent, global structure.
– Remember: separate copy for each connection to Oracle

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 24
 Cache Session-static Information
‹ Great example: the USER function.
– The value returned by USER never changes in a session.
– Each call to USER is in reality a SELECT FROM dual.
– So why do it more than once?

CREATE OR REPLACE PACKAGE thisuser

IS ‹ Hide package data!
FUNCTION name RETURN VARCHAR2; – If exposed, you
END;
cannot guarantee
CREATE OR REPLACE PACKAGE BODY thisuser integrity of data.
IS – Build "get and set"
/* Persistent "global" variable */
g_user VARCHAR2(30) := USER;
programs around it.

FUNCTION name RETURN VARCHAR2 IS

BEGIN
[Link]
RETURN g_user; [Link]
END; emplu.*
END;

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 25
 Leverage Oracle Hashing
‹ Hashing algorithms transform strings to numbers.
– Standard usage: generate unique values for distinct strings.

FUNCTION DBMS_UTILITY.GET_HASH_VALUE
(name IN VARCHAR2,
base IN NUMBER,
hash_size IN NUMBER)
RETURN NUMBER;

‹ Provide the string, the base or starting point, and the

hash size (total number of possible return values).
‹ Tips for hashing:
– You must use the same base and hash size to obtain consistent
hash values.
– Maximum hash size is upper limit of BINARY_INTEGER: 2**31-1.
– No guarantee that two different strings will not hash to the same
number. Check for and resolve conflicts.
9/16/2006 Copyright 2000 PL/Solutions
PL/SQL Tuning and Optimization - page 26
 Hashing for an Alternative Index
‹ Index-by tables allow only a single index -- the row number.
– So to locate the row in which a particular string is located, you have
to do a "full table scan" -- or do you?

‹ Use the hash function to build an alternative index to the

contents of the PL/SQL table.
Hash Name to
Produce Alt Index Row # Indexing Table
Data Table

Row # Full Set of Employee Data Row # Employee ID and Name

1055 SMITH, 12-JAN-99, ... 78955 1055 SMITH

3458 FELLON, 10-MAR-82, ... 203055 3458 FELLON
7988 DIONA, 22-JUL-90, ... 1109878 7988 DIONA

Row # is Complete the round trip to find the row for a name.
employee
ID altind*.pkg
number. [Link]

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 27
 Yes, You Can Write Blazing Fast PL/SQL!

‹ VROOOM, VROOM!
– (This may be the closest you get to driving around in the types of
cars preferred by Our Most Exalted Larry Ellison)
‹ Tuning PL/SQL code is an iterative and incremental
process.
– You are unlikely to uncover a "silver bullet" that is not related to
some SQL statement.
– You can, however, have a substantial impact on the performance
of your and others' code.

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 28
 Closing Comments
‹ Write code with efficiency in mind, but save intensive
tuning until entire components are complete and you can
perform benchmarking.
‹ MOST IMPORTANT! Avoid repetition and dispersion of
SQL statements throughout your application.
‹ Be especially careful to analyze code executed within
loops (including SQL statements).
‹ PL/SQL code is executed from shared memory. You must
tune the shared pool to avoid excessive swapping of code.

Visit the PL/SQL Pipeline ([Link]/plsql-pipeline) to share

what you learn about tuning and to get your questions answered.

9/16/2006 Copyright 2000 PL/Solutions

PL/SQL Tuning and Optimization - page 29
 PL/SQL Happy Hour!
‹ Sponsored by O'Reilly and Associates and the Oracle
PL/SQL Development Team.
‹ Drinks, snacks, discussion and "news you can use" from:
– Chris Racicot, Senior Manager, PL/SQL and Precompilers, Oracle
Corporation
– Steve Muench, author of Building Oracle XML Applications, and
Lead XML Evangelist & Consulting Product Manager BC4J & XSQL
Servlet DevelopmentTeams
– Bill Pribyl, author of Oracle PL/SQL Language Pocket Reference,
andco-author of Oracle PL/SQL Language Pocket Reference
– And I'll throw in a few words on utPLSQL, a fantastic new utility for
unit testing of PL/SQL code.

Monday, October 2, 2000

San Francisco Marriott Hotel, 55 Fourth Street
6:30 pm - 8:30 pm
9/16/2006 Copyright 2000 PL/Solutions
PL/SQL Tuning and Optimization - page 30