Performance of New PL/SQL Features
(Doc ID 104377.1)
Last updated on OCTOBER 02, 2019
Applies to:PL/SQL - Version 10.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 26-Mar-2019***
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.
b. Create tables in the schema of the user who will do the test by running
CREATE OR REPLACE xxx_test IS ...
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
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.
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