ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 45D60B598AC75CF8E0630100007F2E4E DBA Profiler Time Unit Y select 'Seconds' value, null description from dual union all select 'Milliseconds' value, null description from dual US 45D005A16DB72200E0630100007F822E with function ds_text(p_unit_type in varchar2, p_unit_owner in varchar2, p_unit_name in varchar2, p_line# pls_integer) return varchar2 is begin for c in (select ds.text from dba_source ds where p_unit_type=ds.type and p_unit_owner=ds.owner and p_unit_name=ds.name and p_line#=ds.line) loop return c.text; end loop; return null; end ds_text; select x.* from ( select dfi.runid, cast(dr.run_timestamp as date) run_date, dr.total_elapsed_time/decode(:time_unit,'Seconds',1000000,1000) total_time, dfi.type, dfi.owner||nvl2(dfi.module,'.'||dfi.module,null) module, dfi.function, dfi.sql_id, dfi.function_elapsed_time*100/sum(dfi.function_elapsed_time) over (partition by dfi.runid) percentage, dfi.calls, nvl2(dpci.parentsymid,dfi.subtree_elapsed_time/decode(:time_unit,'Seconds',1000000,1000),null) subtree_time, dfi.function_elapsed_time/decode(:time_unit,'Seconds',1000000,1000) time, dfi.line#, ds_text(dfi.type,dfi.owner,dfi.module,dfi.line#) line_text, dfi.sql_text, dfi.namespace, dfi.symbolid, dfi.hash from dbmshp_runs dr, dbmshp_function_info dfi, (select distinct dpci.runid, dpci.parentsymid from dbmshp_parent_child_info dpci) dpci where 1=1 and dr.runid=dfi.runid and dr.runid=dpci.runid(+) and dfi.symbolid=dpci.parentsymid(+) ) x where 2=2 order by x.runid desc, x.time desc Y US DBA Hierarchical Profiler Data Excel version of Oracle's hierarchical profiler dbms_hprof PLSQL performance analysis. PL/SQL units that have been compiled in NATIVE mode cannot be profiled. To gather information, you must ensure that the PL/SQL code is INTERPRETED." Before compilation of the profiled code, execute: alter session set plsql_code_type=interpreted; Create and setup access to profiler tables as sys: exec dbms_hprof.create_tables(force_it=>true); create public synonym dbmshp_trace_data for sys.dbmshp_trace_data; create public synonym dbmshp_runs for sys.dbmshp_runs; create public synonym dbmshp_function_info for sys.dbmshp_function_info; create public synonym dbmshp_parent_child_info for sys.dbmshp_parent_child_info; create public synonym dbmshp_runnumber for sys.dbmshp_runnumber; create public synonym dbmshp_tracenumber for sys.dbmshp_tracenumber; grant select, insert, update, delete on dbmshp_trace_data to public; grant select, insert, update, delete on dbmshp_runs to public; grant select, insert, update, delete on dbmshp_function_info to public; grant select, insert, update, delete on dbmshp_parent_child_info to public; grant select on dbmshp_runnumber to public; grant select on dbmshp_tracenumber to public; To start and stop profiling code, use the following commands: declare l_trace_id pls_integer; l_sqlmonitor_clob clob; l_runid pls_integer; begin l_trace_id:=dbms_hprof.start_profiling; xxen_api.clear; --code to profile l_sqlmonitor_clob:=dbms_hprof.stop_profiling; l_runid:=dbms_hprof.analyze(l_trace_id); end; To purge and reset the profiler data, execute the following as sys: truncate table dbmshp_parent_child_info; truncate table dbmshp_function_info; truncate table dbmshp_runs; truncate table dbmshp_trace_data; alter sequence dbmshp_runnumber restart start with 1; alter sequence dbmshp_tracenumber restart start with 1; Enginatics 1=1 2=2 :time_unit 1 10 1=1 dfi.runid=:runid LOV custom Y select dr.runid value, cast(dr.run_timestamp as date)||nvl2(dr.run_comment,' - '||dr.run_comment,null)||' ('||xxen_util.time(dr.total_elapsed_time/1000000)||')' description from dbmshp_runs dr order by dr.runid desc select max(dr.runid) from dbmshp_runs dr Y US Run Id 2 20 1=1 dfi.owner=substr(:module_name,1,instr(:module_name,'.')-1) and dfi.module=substr(:module_name,instr(:module_name,'.')+1) LOV custom Y select distinct dfi.owner||nvl2(dfi.module,'.'||dfi.module,null) value, sum(dfi.function_elapsed_time) over (partition by dfi.owner||'.'||dfi.module)/decode(:$flex$.time_unit,'Seconds',1000000,1000) description from dbmshp_function_info dfi where dfi.runid=:$flex$.run_id order by description desc, value US Module 3 30 2=2 x.time>:time_from Number US Time From 4 40 2=2 x.percentage>:percentage_from Number US Percentage From 5 50 2=2 lower(x.line_text) like '%'||lower(:line_text_contains)||'%' Char US Line Text Contains 6 60 1=1 dfi.line#>=:line_from Number US Line Number From 7 70 1=1 dfi.line#<=:line_to Number US Line Number To 8 80 :time_unit LOV DBA Profiler Time Unit 45D60B598AC75CF8E0630100007F2E4E Y select 'Seconds' value, null description from dual union all select 'Milliseconds' value, null description from dual Seconds Y US Time Unit :$flex$.run_id Run Id Module :$flex$.time_unit Time Unit Module