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;
