DBA DBMS Profiler Data

➜ Back to Library
Description
Categories: Enginatics, Kcapps
Repository: Github
Excel version of Oracle's dbms_profiler PLSQL performance analysis, see Oracle note:
Using DBMS_PROFILER (Doc ID 97270.1)
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=97270.1 ...  Excel version of Oracle's dbms_profiler PLSQL performance analysis, see Oracle note:
Using DBMS_PROFILER (Doc ID 97270.1)
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=97270.1

PL/SQL units that have been compiled in NATIVE mode cannot be profiled using the DBMS_PROFILER package.
To gather information using DBMS_PROFILER, you must ensure that the PL/SQL code is INTERPRETED."
Before compilation of the profiled code, execute:
alter session set plsql_code_type=interpreted;

To start and stop profiling code, use the following commands (see use https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_profil.htm#i1000047):
dbms_profiler.start_profiler(optional run_comment);
dbms_profiler.stop_profiler;   more
Run DBA DBMS Profiler Data and other Oracle EBS reports with Blitz Report™ on our demo environment
select
ppr.runid,
ppr.run_date,
ppr.run_total_time/decode(:time_unit,'Seconds',1000000000,1000000) total_time,
sum(ppd.total_time) over (partition by ppd.runid)/decode(:time_unit,'Seconds',1000000000,1000000) plsql_time,
ppu.unit_type type,
ppu.unit_owner||'.'||ppu.unit_name name,
ppd.total_time*100/sum(ppd.total_time) over (partition by ppd.runid) percentage,
ppd.line#,
ppd.total_occur executions,
ppd.total_time/decode(:time_unit,'Seconds',1000000000,1000000) time,
(select ds.text from dba_source ds where ppu.unit_type=ds.type and ppu.unit_owner=ds.owner and ppu.unit_name=ds.name and ppd.line#=ds.line) line_text
from
plsql_profiler_runs ppr,
plsql_profiler_units ppu,
plsql_profiler_data ppd
where
1=1 and
ppr.runid=ppu.runid and
ppu.runid=ppd.runid and
ppu.unit_number=ppd.unit_number
order by
ppr.runid desc,
ppd.total_time desc
Parameter Name SQL text Validation
Run Id 
ppr.runid=:runid
 LOV
Name 
ppu.unit_owner=substr(:unit_name,1,instr(:unit_name,'.')-1) and
ppu.unit_name=substr(:unit_name,instr(:unit_name,'.')+1)
 LOV
Line Number From 
ppd.line#>=:line_from
 Number
Line Number To 
ppd.line#<=:line_to
 Number
Time Unit 
 
 LOV
Download
 
Related Blitz Reports
DBA AWR SQL Performance Summary DBA SGA Blocking Session Summary DBA AWR Blocking Session Summary DBA AWR Active Session History DBA SGA Active Session History FND Concurrent Requests 11i FND Concurrent Requests DBA SGA SQL Performance Summary FND Applications
Blitz Report™

Blitz Report™ provides multiple benefits: