My Oracle Support Banner

Performance of New PL/SQL Features (Doc ID 104377.1)

Last updated on AUGUST 29, 2017

Applies to:

PL/SQL - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Aug-2017***

Purpose

The purpose of this article is to present a method for investigating the performance of PL/SQL new (8.1 and above) features and DBMS_PROFILER package is used.

The examples contain testing of the COLLECTIONS (nested table, varray, index by table) types for caching values and relative performance of NUMBER datatypes (NUMBER, BINARY_INTEGER, PLS_INTEGER).

Scope

The knowledge of new PL/SQL features is needed. The PL/SQL types are explained further in the PL/SQL User's Guide and in the SQL Reference.

In Oracle version 8.1 and above , there are some PL/SQL performance improvements. These improvements are version specific and the underlying hardware (CPU speed, available memory,...) can also influence performance. Results can vary from environment to environment. So, testing these features in your environment is advised.

The DBMS_PROFILER Package helps to Investigate PL/SQL new Features and below are instructions to install and test DBMS_PROFILER Package.

Installation:

a. Install the DBMS_PROFILER package as SYS with the
$ORACLE_HOME/rdbms/admin/profload.sql script.

b. Create tables in the schema of the user who will do the test by running
$ORACLE_HOME/rdbms/admin/proftab.sql

Testing:

a. The test method template is:

CREATE OR REPLACE xxx_test IS ...

declare
err number;
begin
err:=DBMS_PROFILER.START_PROFILER (to_char(sysdate,'dd-Mon-YYYY hh:mi:ss'));
xxx_test; -- substitute number_test, coll_test
err:=DBMS_PROFILER.STOP_PROFILER ;
end;
/

b. Find the interested runid using this select statement:

column RUN_COMMENT format a40
select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid;

c. Run this query to obtain the performance report:

column unit_name format a15
column occured format 999999
column line# format 99999
column tot_time format 999.999999

select p.unit_name, p.occured, p.tot_time, p.line# line,
substr(s.text, 1,75) text
from
(select u.unit_name, d.TOTAL_OCCUR occured,u.unit_type type,
(d.TOTAL_TIME/1000000000) tot_time, d.line#
from plsql_profiler_units u, plsql_profiler_data d
where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
and d.TOTAL_OCCUR >0
and u.runid= 4) p, -- change 28 to the selected runid
user_source s
where p.unit_name = s.name(+) and p.line# = s.line (+) and p.type = s.type (+)
order by p.unit_name, p.line#;

d. Sample output will look similar to:

UNIT_NAME           OCCURED    TOT_TIME       LINE                  TEXT
  ---------------                 -------             -----------             -----       ----------------------------------
NUMBER_TEST             1                  .000006              13           dummy:='end of PLSQL';
...

OCCURED is the number of times the given line was executed, TOT_TIME is the sum of the execution times of the given line in seconds.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.