Database and Application Object (PL/SQL) Performance Tuning

Database parameters that could help with Object level Performance:

Applies to Oracle 10g or later.

1. PLSQL_OPTIMIZE_LEVEL parameter will help with object performance.

To set at session level:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=1;

To set at system level:

ALTER SYSTEM SET PLSQL_OPTIMIZE_LEVEL=1;

Oracle Documentation Reference:

PLSQL_OPTIMIZE_LEVEL

Parameter type:  Integer
Default value: 2
Modifiable: ALTER SESSION, ALTER SYSTEM
Range of values:  0 to 2

PLSQL_OPTIMIZE_LEVEL specifies the optimization level that will be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.

Values:

Value 0: Maintains the evaluation order and hence the pattern of side effects, exceptions, and package initializations of Oracle9i and earlier releases. Also removes the new semantic identity of BINARY_INTEGER and PLS_INTEGER and restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database newer releases.

Value 1: Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions, but generally does not move source code out of its original source order.

Value 2: Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location.

2. Cursor Reuse: Sometimes, the problem appears to be relate to PL/SQL cursor reuse.  Like incorrectly reusing cursors from previous iterations of the loop if the date parameters do not change.  And If the dates don't change then the same value from the previous iteration is returned.

In that case, having Database parameter SESSION_CACHED_CURSORS set to zero seems to help.

At session level, try the following setting and if that works, it can be used at Database System level.

ALTER SESSION SET SESSION_CACHED_CURSORS=0;

3. Avoid Re-parse: Disable the 'Execute Immediate "avoid reparse when possible" optimization'

At session level, try the following setting and if that works, it can be used at Database System level.

alter session set events= '10933 trace name context forever, level 262144';

4.  Sometimes SQL queries will run fine at Database level but when they are retrieved using a cursor at Application or running SQL through Application will run slow or hang.  Slow performance after reading the results from the cursor is expected if Stats are enabled.

If that is the case;

- Check if any Database or Application Stats are enabled.  Typically running Stats for all tables involved and indexes could impact on the performance.
- Check if the tables are partitioned at Database level.

4.1. Check if you've set the following parameters in init.ora file.

  cursor_space_for_time=true -- init.ora setting
  timed_statistics=false -- session level or system level
  statistics_level=basic -- session level or system level

4.2 Sample Performance Check Code Block:

create or replace procedure chk_perf_100000
as
v date;
tsStartTime TIMESTAMP;
tsEndTime TIMESTAMP;
begin
tsStartTime := CURRENT_TIMESTAMP;
   FOR i IN 1 .. 100000 LOOP SELECT SYSDATE INTO v FROM dual;
   END LOOP;
tsEndTime := CURRENT_TIMESTAMP;
--DBMS_OUTPUT.PUT_LINE('Time elapsed:' || to_char(tsEndTime - tsStartTime));
DBMS_OUTPUT.PUT_LINE('Time elapsed:' || to_char(tsEndTime - tsStartTime,'HH:MI:SS.FF3'));
end;
/

SQL> set serverout on;

SQL> alter session set statistics_level=basic;

Session altered.

SQL> exec chk_perf_100000;
Time elapsed:+000000000 00:00:02.759105000

PL/SQL procedure successfully completed.

SQL> alter session set timed_statistics=false;

Session altered.

SQL> exec chk_perf_100000;
Time elapsed:+000000000 00:00:02.624074000

PL/SQL procedure successfully completed.

If you've set the above values to anything else or even otherwise, please reset those parameters to the above mentioned values.

4.3 You should be able to gain performance by tuning init.ora parameters, in particular cursor_space_for_time should be set to false.

Set cursor_space_for_time=false

(or)

Dynamic SQL Avoid Reparse :
alter session set events '10933 trace name context level 262144';

(or)

Disable Auto BULKIFICATION :
alter session set events '10933 trace name context level 16384';

Test the application after resetting the values to see the difference.

Thanks.

Reference / Read More:

Oracle Database Reference, 11g Release 1 (11.1), B28320-03
Section: PLSQL_OPTIMIZE_LEVEL
URL: http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams184.htm

Oracle Database PL/SQL Language Reference, 11g Release 2 (11.2), E25519-09
Chapter: 12 PL/SQL Optimization and Tuning
URL: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm

Oracle Database Performance Tuning Guide, 11g Release 2 (11.2), Part Number E16638-03
URL: http://docs.oracle.com/cd/E18283_01/server.112/e16638/toc.htm

Oracle Database Upgrade Guide, 10g Release 1 (10.1), Part Number B10763-01
Chapter 5 Compatibility and Interoperability
URL: http://docs.oracle.com/cd/B12037_01/server.101/b10763/compat.htm

plsql_optimize_level parameter, Oracle Tips by Burleson Consulting
URL: http://www.dba-oracle.com/t_plsql_optimization_level.htm

session_cached_cursors
URL: http://www.orafaq.com/forum/t/172526/0/