copyright © 2012 feuerstein and associates high performance pl/sql steven feuerstein pl/sql...

Copyright © 2012 Feuerstein and Associates

High Performance PL/SQL

Steven FeuersteinPL/SQL Evangelist, Quest Softwarewww.quest.com [email protected]

Copyright © 2012 Steven Feuerstein

Welcome to My Obsession

www.plsqlchallenge.comDaily PL/SQL quiz, weekly SQL, APEX and logic quizzes. Prizes!

www.plsqlchannel.com27+ hours of detailed video training on Oracle PL/SQL

www.stevenfeuerstein.comSign up for monthly PL/SQL newsletter

www.toadworld.com/SFQuest Software-sponsored portal for PL/SQL developers; download powerpoints and demo.zip

http://www.plsqlchallenge.com/

http://www.plsqlchallenge.com/

http://www.stevenfeuerstein.com/

http://www.toadworld.com/SF


What I won't be talking about

• Tuning SQL statements– I am not an expert in explain plans, hints, the

CBO, etc. BUT DO THIS FIRST!

• Configuring the database– The DBA must make sure that all of the many

caches in the System Global Area are big enough to avoid swapping due to application of the Least Recently Used (LRU) algorithm.

• Instead, I will focus on changes you can make only in your PL/SQL code.– And prioritize those changes that lead to an order

of magnitude or more improvement.


High Performance PL/SQL Agenda

• BULK COLLECT and FORALL• Data caching• NOCOPY hint• Pipelined table functions

• Note: most of these features rely on collections, PL/SQL’s arrays. For more info on how to leverage collections, check out:

“Can Collections Speed Up Your PL/SQL?” by Patrick Barel, AMIS

Wednesday June 27, Session 14, 11:15 am - 12:15 pm


Oracle features and utilities that assist in optimization

• Memory management and PL/SQL code– If you are not careful, you can crash your session

and/or server.

• Profiling execution of code– Identify performance bottlenecks

• Calculating elapsed time of execution– Critical for granular analysis of performance and

comparison of performance between different implementations of same program


System Global Area (SGA) of RDBMS Instance

PL/SQL Runtime Memory Architecture

Shared Pool

Large Pool

Reserved Pool

show_empscalc_totals upd_salaries

Select * from emp

Shared SQL

Pre-parsedUpdate emp Set sal=...

Library cache

Session 1 memory UGA – User Global AreaPGA – Process Global Area

emp_rec emp%rowtype;tot_tab pkg.tottabtype;

Session 2 memory UGA – User Global AreaPGA – Process Global Area

emp_rec emp%rowtype;tot_tab pkg.tottabtype;Session 1 Session 2


How PL/SQL uses the SGA, PGA and UGA

• The SGA contains information that can be shared across schemas connected to the instance.– From the PL/SQL perspective, this is limited to package

static constants.

• The Process Global Area contains session-specific data that is released when the current server call terminates.– Local data

• The User Global Area contains session-specific data that persists across server call boundaries– Package-level data plsql_memory.pkg

plsql_memory_demo.sqlshow_memory.sp

PACKAGE Pkg is Nonstatic_Constant CONSTANT PLS_INTEGER := My_Sequence.Nextval; Static_Constant CONSTANT PLS_INTEGER := 42;END Pkg;


Profiling execution of PL/SQL code

• Two profilers:– DBMS_PROFILER: line by line performance– DBMS_HPROF: hierarchical profiler, rollup to program

units• Both help identify bottlenecks in your code.

– Relative performance of each line of code in your programs and program unit level performance

– Code coverage analysis

BEGIN DBMS_OUTPUT.PUT_LINE ( DBMS_PROFILER.START_PROFILER ( 'my_application ' || TO_CHAR (SYSDATE, 'YYYYMMDD HH24:MI:SS') ) ); run_your_application; DBMS_PROFILER.STOP_PROFILER;END;


Calculating Elapsed Time of Programs• Many options for analyzing Oracle performance:

TKPROF, SET TIMING ON, etc.– But they usually don't offer the granularity I need for my

PL/SQL performance analysis.• Oracle offers DBMS_UTILITY.GET_TIME and

GET_CPU_TIME (10g) to compute elapsed time down to the hundredth of a second. – Can also use SYSTIMESTAMP

DECLARE l_start_time PLS_INTEGER;BEGIN l_start_time := DBMS_UTILITY.get_time;

-- Do stuff here...

DBMS_OUTPUT.put_line ( DBMS_UTILITY.get_time – l_start_time);END;

sf_timer.*get_time.sql

plvtmr.*plvtmr_ts.pkg

tmr.otthisuser*.*


Turbo-charge SQL with Bulk Processing Statements

• Improve the performance of repeated SQL operations by an order of magnitude or more with bulk/array processing in PL/SQL!

CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE)IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in;BEGIN FOR rec IN emp_cur LOOP

adjust_compensation (rec, newsal_in);

UPDATE employee SET salary = rec.salary WHERE employee_id = rec.employee_id; END LOOP;END upd_for_dept;

Row by row processing: elegant but inefficient


Oracle server

PL/SQL Runtime Engine SQL Engine

PL/SQL blockProcedural statement executor

SQL statement executor

FOR rec IN emp_cur LOOP UPDATE employee SET salary = ... WHERE employee_id = rec.employee_id;END LOOP;

Performance penalty for many “context switches”

Row by row processing of DML in PL/SQL


Bulk processing with FORALL

Oracle server

PL/SQL Runtime Engine SQL Engine

PL/SQL blockProcedural statement executor

SQL statement executor

FORALL indx IN list_of_emps.FIRST.. list_of_emps.LAST UPDATE employee SET salary = ... WHERE employee_id = list_of_emps(indx);

Fewer context switches,same SQL behavior

Update...

Update...

Update...

Update...

Update...

Update...

Update...

Update...

Update...

Update...

Update...

Update...


Bulk Processing in PL/SQL• FORALL

– Use with inserts, updates and deletes.– Move data from collections to tables.

• BULK COLLECT– Use with implicit and explicit queries.– Move data from tables into collections.

• In both cases, the "back back" end processing in the SQL engine is unchanged.– Same transaction and rollback segment

management– Same number of individual SQL statements will be

executed.– But BEFORE and AFTER statement-level triggers

only fire once per FORALL statement.


Use BULK COLLECT INTO for Queries

DECLARE TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;

l_employees employees_aat;BEGIN SELECT * BULK COLLECT INTO l_employees FROM employees; FOR indx IN 1 .. l_employees.COUNT LOOP process_employee (l_employees(indx)); END LOOP;END;

bulkcoll.sqlbulktiming.sql

Declare a collection of

records to hold the queried data.

Use BULK COLLECT to

retrieve all rows.

Iterate through the collection

contents with a loop. BULK COLLECT will not raise

NO_DATA_FOUND if no rows are found.Collection is always filled sequentially starting

from index value 1.


Limit the number of rows returned by BULK COLLECTCREATE OR REPLACE PROCEDURE bulk_with_limit (deptno_in IN dept.deptno%TYPE) IS CURSOR emps_in_dept_cur IS SELECT * FROM emp WHERE deptno = deptno_in;

TYPE emp_tt IS TABLE OF emps_in_dept_cur%ROWTYPE; emps emp_tt;BEGIN OPEN emps_in_dept_cur; LOOP FETCH emps_in_dept_cur BULK COLLECT INTO emps LIMIT 1000;

EXIT WHEN emps.COUNT = 0;

process_emps (emps); END LOOP;END bulk_with_limit;

Use the LIMIT clause with the INTO to manage the amount of memory used with the BULK COLLECT operation.

Definitely the preferred approach in production applications with large or varying datasets.

Always check the contents of the collection, as opposed to the %NOTFOUND attributes, to determine if you fetched and processed all rows.

bulklimit.sql


Use the FORALL Bulk Bind Statement• Instead of executing repetitive, individual DML

statements, you can write your code like this:

• Things to be aware of when using FORALL:– You MUST know how to use collections to use this feature!– Only a single DML statement is allowed per FORALL.– You can use static and dynamic SQL with FORALL.

PROCEDURE upd_for_dept (...) ISBEGIN FORALL indx IN list_of_emps.FIRST .. list_of_emps.LAST UPDATE employee SET salary = newsal_in WHERE employee_id = list_of_emps (indx);END;

bulktiming.sql


Key features and limitations of FORALL• Use SQL%BULK_ROWCOUNT to determine the

number of rows modified by each statement executed.– SQL%ROWCOUNT returns total number of rows modified by

the entire FORALL.

• Use SAVE_EXCEPTIONS and SQL%BULK_EXCEPTIONS to continue past exceptions in any of the statements.– Granular error recovery and logging

• The binding array must be sequentially filled through Oracle 9i.– In Oracle10g, use INDICES OF or VALUES OF.

• You cannot reference fields of collections of records within the DML statement.

bulk_rowcount.sqlbulkexc.sql


From Old–Fashioned to Modern Code

• Traditional PL/SQL code often involves a cursor FOR loop and multiple DML statements inside the loop.– An integrated, row-by-row approach with the

option of trapping and continuing past exceptions.– Elegant and flexible, but slow.

• Bulk processing means a switch to "phased approach".– Phase 1: retrieve data with BULK COLLECT– Phase 2: prepare data in collections– Phases 3 – N: execute a FORALL for each DML

statement cfl_to_bulk_0.sqlcfl_to_bulk_5.sql


Bulk Processing Conclusions

• Most important performance tuning feature in PL/SQL.– Almost always the fastest way to execute multi-

row SQL operations in PL/SQL.• You trade off increased complexity of code

for dramatically faster execution.– But in Oracle Database 10g and above, the

compiler will automatically optimize cursor FOR loops to BULK COLLECT efficiency.

– No need to convert unless the loop contains DML.• Watch out for the impact on PGA/UGA

memory!emplu.pkg(emplu3)


Data Caching Options

• Why cache data?– Because it is static and therefore you want to

avoid the performance overhead of retrieving that data over and over again.

• Options for caching data:– The SGA: Oracle does lots of caching for us, but it

is not always the most efficient means.– Package data structures: PGA memory has less

access overhead than SGA.– Oracle11g Function Result Cache– Deterministic functions


Packaged collection caching• Prior to Oracle 11g, the best caching option for

PL/SQL programs involves declaring a package-level data structure.– It persists for the entire session.– Usually a collection, to store multiple rows of data.

• Why query information from the database (SGA) if that data does not change during your session?– Trivial example: the USER function– More interesting: static tables, usually lookup or reference

code tables.• Instead, load it up in a package variable!

Very simple example:thisuser.*


Function

PGA

Data Caching with PL/SQL Collections

First access

Subsequent accesses

PGAFunction

Database/ SGA

Not in cache;Request datafrom database

Pass Datato Cache

Application

Application Requests Data

Data retrieved from cache Data returned

to application

Application

Application Requests Data

Data returned to application

Data retrieved from cache

Database/ SGA

Data found incache. Databaseis not needed.

emplu.pkgemplu.tst


PGA Caching: Things to keep in mind

• Must use package-level data so that it persists.– Memory is consumed by the PGA and so is

multiplied for all users of the application. • Cache cannot be shared across sessions

practically speaking.• Very difficult to update the cache once the

data source is changed.– Especially by other sessions.

• Useful under specific scenarios....– Small, static dataset or running batch process

syscache.pkg


The Oracle 11g Function Result Cache

• Oracle offers a far superior caching solution in 11g: the Function Result Cache.

• This cache is...– stored in the SGA – shared across sessions– purged of dirty data automatically

• You can use and should use it to retrieve data from any table that is queried more frequently than updated.


How the Function Result Cache Works

• Add the RESULT_CACHE clause to your function's header.

• When a call is made to function, Oracle compares IN argument values to the cache.

• If no match, the function is executed and the inputs and return data are cached.

• If a match is found, the function is not executed; cached data is returned.

• If changes to a "relies on" table are committed, the cache is marked invalid and will be re-built.


Function Result Cache ExampleCREATE OR REPLACE PACKAGE emplu11gIS FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE) RETURN employees%ROWTYPE RESULT_CACHE;END emplu11g;

CREATE OR REPLACE PACKAGE BODY emplu11gIS FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE) RETURN employees%ROWTYPE RESULT_CACHE RELIES_ON (employees) IS onerow_rec employees%ROWTYPE; BEGIN SELECT * INTO onerow_rec FROM employees WHERE employee_id = employee_id_in;

RETURN onerow_rec; END onerow;END emplu11g;

11g_emplu*.*

The specification must indicate you are using a result cache.

The body specifies the "relies on" dependencies, if any.


Result Cache – Things to Keep in Mind - 1

• If you have uncommitted changes in your session, dependent caches are ignored.– The cache will not override your own changed data.

• Caching is not performed for complex types: records with CLOBs, collections, etc.

• The cache is not related to SQL statements in your function.– It only keeps track of the input values and the

RETURN clause data.

11g_frc_demo.sql


Result Cache – Things to Keep in Mind - 2

• You cannot use the result cache with invoker rights program units.– Bypass execution of function body, Oracle cannot

resolve references to objects - the whole point of IR.

• Functions with session-specific dependencies must be "result-cached" with great care.– Virtual private database configurations– References to SYSDATE, reliance on

NLS_DATE_FORMAT, time zone changes– Application contexts

• Solution: move all dependencies into parameter list.

11g_frc_vpd.sql11g_frc_vpd2.sql


Tuning the Result Cache

• Oracle offers a number of ways to manage the result cache and tune it to your specific application needs:

• RESULT_CACHE_SIZE initialization parameter

• DBMS_RESULT_CACHE management package

• v$RESULT_CACHE_* performance views


Deterministic Functions (caching in all versions of Oracle)

• Deterministic: the function's return value is determined completely by its inputs.– Same inputs, same return value.– No side effects (SQL, for example).

• Function-based indexes are based on deterministic functions.

• Results of deterministic functions called within a SQL statement will be cached.– Pass in the same inputs, and Oracle will not execute

the function.show_deterministc.sql

deterministc.sql


Look for opportunities to cache!

• Whether you are on 9i, 10g or 11g, you should always look for opportunities to cache.– Are there tables that are always static during user

sessions?– Are there tables that are queried much more

frequently than they are changed?

• Apply the most appropriate technique, but don't leave the users waiting...


Specialized performance enhancements

• There are a whole range of techniques for improving PL/SQL execution that will likely only help you in more "extreme" circumstances.– The NOCOPY hint– Take advantage of pipelined table functions


The NOCOPY hint

• By default, Oracle passes all OUT and IN OUT arguments by value, not reference.– This means that OUT and IN OUT arguments

always involve some copying of data.– All IN arguments are always passed by reference

(no copying).• With NOCOPY, you turn off the copy

process.– But it comes with a risk: Oracle will not

automatically "rollback" or reverse changes made to your variables if the NOCOPY-ed program raises an exception.

nocopy*.*string_nocopy.*


Pipelined table functions

• A table function is a function that you can call in the FROM clause of a query, and have it be treated as if it were a relational table.

• Table functions allow you to perform arbitrarily complex transformations of data and then make that data available through a query: "just" rows and columns!– Not everything can be done in SQL.

• Combined with cursor variables, you can now more easily transfer data from within PL/SQL to host environments.– Java, for example, can fetch natively from a cursor

variable.


Building a table function

• A table function must return a nested table or varray based on a schema-defined type.– Types defined in a PL/SQL package can only be

used with pipelined table functions.• The function header and the way it is called

must be SQL-compatible: all parameters use SQL types; no named notation.– In some cases (streaming and pipelined functions),

the IN parameter must be a cursor variable -- a query result set.


Streaming data with table functions• You can use table functions to "stream" data

through several stages within a single SQL statement.– Example: transform one row in the stocktable to two

rows in the tickertable.CREATE TABLE stocktable ( ticker VARCHAR2(20), trade_date DATE, open_price NUMBER, close_price NUMBER)

CREATE TABLE tickertable ( ticker VARCHAR2(20), pricedate DATE, pricetype VARCHAR2(1), price NUMBER)

tabfunc_streaming.sql


Use pipelined functions to enhance performance.

• Pipelined functions allow you to return data iteratively, asynchronous to termination of the function.– As data is produced within the function, it is passed

back to the calling process/query.

• Pipelined functions can only be called within a SQL statement.– They make no sense within non-multi-threaded

PL/SQL blocks.

CREATE FUNCTION StockPivot (p refcur_pkg.refcur_t) RETURN TickerTypeSet PIPELINED


Applications for pipelined functions

• Execution functions in parallel.– In Oracle9i Database Release 2 and above, use the

PARALLEL_ENABLE clause to allow your pipelined function to participate fully in a parallelized query.

– Critical in data warehouse applications.• Improve speed of delivery of data to user

interfaces.– Use a pipelined function to "serve up" data to the

webpage and allow users to begin viewing and browsing, even before the function has finished retrieving all of the data.

• And pipelined functions use less PGA memory than non-pipelined functions!


Piping rows out from a pipelined function

CREATE FUNCTION stockpivot (p refcur_pkg.refcur_t) RETURN tickertypeset PIPELINED IS out_rec tickertype := tickertype (NULL, NULL, NULL); in_rec p%ROWTYPE;BEGIN LOOP FETCH p INTO in_rec; EXIT WHEN p%NOTFOUND; out_rec.ticker := in_rec.ticker; out_rec.pricetype := 'O'; out_rec.price := in_rec.openprice;

PIPE ROW (out_rec); END LOOP; CLOSE p;

RETURN;END;

tabfunc_setup.sqltabfunc_pipelined.sql

Add PIPELINED keyword to header

Pipe a row of data back to calling block

or query

RETURN...nothing at all!


Enabling Parallel Execution

• You can use pipelined functions with the Parallel Query option to avoid serialization of table function execution.

• Include the PARALLEL_ENABLE hint in the program header.– Choose a partition option that specifies how the

function's execution should be partitioned. – "ANY" means that the results are independent of the

order in which the function receives the input rows (through the REF CURSOR).

{[ORDER | CLUSTER] BY column_list} PARALLEL_ENABLE ({PARTITION p BY [ANY | (HASH | RANGE) column_list]} )


Table functions - Summary

• Table functions offer significant new flexibility for PL/SQL developers.

• Pipelined table functions add performance on top of that flexibility.

• Consider using them when you...– Need to pass back complex result sets of data

through the SQL layer (a query);– Want to call a user defined function inside a query

and execute it as part of a parallel query.


High Performance PL/SQL – a summary

• Make sure you take advantage of the most important optimization features:– BULK COLLECT and FORALL– Cached data

• Prioritize maintainability of code over obsessively optimized code.

• Then identify bottlenecks and apply the more specialized optimization techniques.

copyright © 2012 feuerstein and associates high performance pl/sql steven feuerstein pl/sql...

Documents

oracle plsql

steven feuerstein page

plsql performance analysis

plsql developers

plsql perspective

rec emp

steven feuerstein welcome

oracle performance