Performance of New PL/SQL Features
(Doc ID 104377.1)
Last updated on JANUARY 30, 2022
Applies to:
PL/SQL - Version 10.2.0.1 and laterInformation in this document applies to any platform.
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:
$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:
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 |