Dingjun123:DBMS_PROFILER

 

DECLARE
ReturnCode BINARY_INTEGER;
BEGIN
ReturnCode := DBMS_PROFILER.start_profiler ('Profiler Demo 2');
nocopy_test.test_copy (400, 1);
ReturnCode := DBMS_PROFILER.stop_profiler;
DBMS_OUTPUT.put_line ('Profiler return code=' || ReturnCode);
COMMIT;
END;

 

SQL> WITH plsql_qry AS (
2 SELECT u.unit_name, line#,
3 ROUND (d.total_time / 1e9) time_ms,
4 round(d.total_time * 100 / sum(d.total_time) over(),2)
pct_time,
5 d.total_occur as execs,
6 substr(ltrim(s.text),1,40) as text,
7 dense_rank() over(order by d.total_time desc) ranking
8 FROM plsql_profiler_runs r JOIN plsql_profiler_units u
USING (runid)
9 JOIN plsql_profiler_data d USING (runid, unit_number)
10 LEFT OUTER JOIN all_source s
11 ON ( s.owner = u.unit_owner
12 AND s.TYPE = u.unit_type
13 AND s.NAME = u.unit_name
14 AND s.line = d.line# )
15 WHERE r.run_comment = ‘Profiler Demo 2’
16 )
17 select unit_name,line#,time_ms,pct_time,execs,text
18 from plsql_qry
19 where ranking <=5
20 ORDER BY ranking;

 

用这个，很容易定位过程中的top5