My Oracle Support Banner

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

Last updated on JANUARY 30, 2022

Applies to:

PL/SQL - Version and later
Information in this document applies to any platform.


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).


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.


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


a. The test method template is:


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

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
(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 = 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.


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

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