PLSQL Performance Tuning DBMS_PROFILER

DBMS_PROFILER

DBMS_PROFILER - DBMS_PROFILER package is Oracle supplied package to tune PLSQL code.

Steps :- 1) Environment Setup (One Time Setup)
              2) Start DBMS_PROFILER then Execute PLSQL Package (Which need to Tune) then Stop the DBMS_PROFILER
              3) Analyze Profiler data
              4) Optimize PLSQL code then again repeat step 2)

1) Environment Setup :- a) Check DBMS_PROFILER Package available in SYS schema.

select object_name,object_type from all_objects where object_name like '%PROF%';

b) check that you are able to execute package DBMS_PROFILER from your user. if you are not able to excute then grant execute priviledge
on package DBMS_PROFILER from sys user to your user.

grant execute on DBMS_PROFILER to scott;

c) Create rquired table through 'proftab.sql'

@?/RDBMS/ADMIN/proftab.sql

d) check profile tables creates

select * from user_tables where table_name like '%PROF%';

PLSQL_PROFILER_RUNS
PLSQL_PROFILER_UNITS
PLSQL_PROFILER_DATA


2) Start DBMS_PROFILER then Execute PLSQL Package (Which need to Tune) then Stop the DBMS_PROFILER :-

exec DBMS_PROFILER.START_PROFILER('TEST');
exec procedure;
exec DBMS_PROFILER.STOP_PROFILER();


3) Analyze Profiler data :-

select * from PLSQL_PROFILER_RUNS;
select * from PLSQL_PROFILER_UNITS;
select * from PLSQL_PROFILER_DATA;


SELECT u.runid,
       u.unit_number,
       u.unit_type,
       u.unit_owner,
       u.unit_name,
       d.line#,
       d.total_occur,
       d.total_time,
       d.min_time,
       d.max_time
FROM   plsql_profiler_units u
       JOIN plsql_profiler_data d ON u.runid = d.runid AND u.unit_number = d.unit_number
WHERE  u.runid = 1
ORDER BY u.unit_number, d.line#;


-- Top 10 slow statements

SELECT * FROM (
select trim(decode(unit_type,'PACKAGE SPEC','PACKAGE',unit_type)||
 ' '||trim(pu.unit_owner)||'.'||trim(pu.unit_name))||
' (line '|| pd.line#||')' object_name
, pd.total_occur
, pd.total_time
, pd.min_time
, pd.max_time
, src.text
, rownum sequencenr
from plsql_profiler_units pu
,    plsql_profiler_data pd
,    all_source src
where pu.unit_owner = user
and pu.runid = &1
and pu.runid=pd.runid
and pu.unit_number = pd.unit_number
and src.owner = pu.unit_owner
and src.type = pu.unit_type
and src.name = pu.unit_name
and src.line = pd.line#
) where sequencenr <=10;



4) Optimize PLSQL code :-

PLSQL code need to modified with help of following  :-

Bulk Collection
Global Temporary Table
Inbuilt Functions
Nocopy Compiler Hint



Truncate sequence for Profiler tables data :-

truncate table PLSQL_PROFILER_DATA;
truncate table PLSQL_PROFILER_UNITS;
truncate table PLSQL_PROFILER_RUNS;