Dbms_Profiler Scripts

SELECT u.unit_name,
d.total_occur,
d.total_time / 1000 microsec,
substr(s.text, 1, 60) plsql_code
FROM plsql_profiler_units u
INNER JOIN plsql_profiler_data d ON (u.runid = d.runid AND
u.unit_number = d.unit_number)
INNER JOIN all_source s ON (s.owner = u.unit_owner AND
s.type = u.unit_type AND s.name = u.unit_name AND
s.line = d.line#)
WHERE u.runid = 53
AND u.unit_name LIKE 'PARSE_CSV%'
ORDER BY u.unit_number, d.line#;

SELECT runid, run_date, run_comment, run_total_time
FROM plsql_profiler_runs
ORDER BY runid;

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#;

SELECT line || ' : ' || text
FROM all_source
WHERE owner = 'MY_SCHEMA'
AND type = 'PROCEDURE'
AND name = 'DO_SOMETHING';

SELECT dbms_profiler.internal_version_check FROM dual;

DECLARE
i PLS_INTEGER;
BEGIN
i := dbms_profiler.flush_data;
i := dbms_profiler.pause_profiler;
i := dbms_profiler.resume_profiler;
END;

SELECT dbms_profiler.pause_profiler FROM dual;

SELECT dbms_profiler.resume_profiler FROM dual;

ROLLUP_RUN

CREATE OR REPLACE PROCEDURE proc1 IS vd VARCHAR2(5);
BEGIN
FOR i IN 1 .. 100 LOOP
SELECT dummy INTO vd FROM dual;
END LOOP;
END proc1;
/

DECLARE v_run NUMBER;
BEGIN
dbms_profiler.start_profiler('test', 'test1', v_run);
proc1;
dbms_profiler.stop_profiler;
dbms_profiler.rollup_run(v_run);
END;
/

ROLLUP_UNIT dbms_profiler.rollup_unit(run_number IN NUMBER, unit IN NUMBER);

-- executes the following code
UPDATE plsql_profiler_units
SET total_time = (SELECT SUM(total_time)
FROM plsql_profiler_data
WHERE runid = run_number
AND unit_number = unit);

SELECT * FROM plsql_profiler_units;

exec dbms_profiler.rollup_unit(8, 3);


SELECT u.runid, u.unit_type, u.unit_name, sum(d.total_time) / 1000 microsec
FROM plsql_profiler_units u
INNER JOIN plsql_profiler_data d ON (u.runid = d.runid AND
u.unit_number = d.unit_number)
WHERE u.runid between 53 and 57
AND u.unit_name LIKE 'PARSE_CSV%'
GROUP BY u.runid, u.unit_type, u.unit_name
ORDER BY u.runid, u.unit_name;

http://radino.eu/tag/dbms_profiler/

Statement_Tracer_for_Oracle : http://www.aboves.com/downloads/ -- ask IT Team to get Installed. ( free tool )

http://www.orafaq.com/wiki/Scripts

Your DBA may have to install the profiler in your database. The procedure for
installing this package is simple:

ć cd $ORACLE_HOME/rdbms/admin
ć Using SVRMGRL you would connect as SYS or INTERNAL
ć Run profload.sql

In order to actually use the profiler after that, you will need to have the profiling
tables installed. You can install these once per database but I recommend each developer
would have their own copy. Fortunately, the DBMS_PROFILER package is built with invokers
rights and unqualified tablenames so that we can install the tables in each schema and
the profiler package will use them. The reason you each want your own tables is so that
you only see the results of your profiling runs, not those of your coworkers. In order
to get a copy of the profiling tables in your schema, you would run
$ORACLE_HOME\rdbms\admin\proftab in SQLPlus. After you run proftab.sql, you¡¦ll need to
run profrep.sql as well. This script creates views and packages to operate on the
profiler tables in order to generate reports. This script is found in
$ORACLE_HOME\plsql\demo\profrep.sql. You should run this in your schema as well after
creating the tables.

I like to keep a small script around to reset these tables (clear them out). After I¡¦ve
done a run or two and have analyzed the results ¡V I run this script to reset the tables.
I have the following in a script I call profreset.sql:

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

finally, and to answer the question, you will find $ORACLE_HOME/plsql/demo/profsum.sql.
that is what generates the report.

set echo off
set linesize 5000
set trimspool on
set serveroutput on
set termout off

column owner format a11
column unit_name format a14
column text format a21 word_wrapped
column runid format 9999
column secs format 999.99
column hsecs format 999.99
column grand_total format 9999.99
column run_comment format a11 word_wrapped
column line# format 99999
column pct format 999.9
column unit_owner format a11

spool profsum.out

/* Clean out rollup results, and recreate */
update plsql_profiler_units set total_time = 0;

execute prof_report_utilities.rollup_all_runs;

prompt =
prompt =
prompt ====================
prompt Total time
select grand_total/1000000000 as grand_total
from plsql_profiler_grand_total;

prompt =
prompt =
prompt ====================
prompt Total time spent on each run
select runid,
substr(run_comment,1, 30) as run_comment,
run_total_time/1000000000 as secs
from (select a.runid, sum(a.total_time) run_total_time, b.run_comment
from plsql_profiler_units a, plsql_profiler_runs b
where a.runid = b.runid group by a.runid, b.run_comment )
where run_total_time > 0
order by runid asc;


prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, for each run separately

select p1.runid,
substr(p2.run_comment, 1, 20) as run_comment,
p1.unit_owner,
decode(p1.unit_name, '', '',
substr(p1.unit_name,1, 20)) as unit_name,
p1.total_time/1000000000 as secs,
TO_CHAR(100*p1.total_time/p2.run_total_time, '999.9') as percentage
from plsql_profiler_units p1,
(select a.runid, sum(a.total_time) run_total_time, b.run_comment
from plsql_profiler_units a, plsql_profiler_runs b
where a.runid = b.runid group by a.runid, b.run_comment ) p2
where p1.runid=p2.runid
and p1.total_time > 0
and p2.run_total_time > 0
and (p1.total_time/p2.run_total_time) >= .01
order by p1.runid asc, p1.total_time desc;

column secs form 9.99
prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, summarized across runs
select p1.unit_owner,
decode(p1.unit_name, '', '', substr(p1.unit_name,1, 25)) as unit_name,
p1.total_time/1000000000 as secs,
TO_CHAR(100*p1.total_time/p2.grand_total, '99999.99') as percentage
from plsql_profiler_units_cross_run p1,
plsql_profiler_grand_total p2
order by p1.total_time DESC;


prompt =
prompt =
prompt ====================
prompt Lines taking more than 1% of the total time, each run separate
select p1.runid as runid,
p1.total_time/10000000 as Hsecs,
p1.total_time/p4.grand_total*100 as pct,
substr(p2.unit_owner, 1, 20) as owner,
decode(p2.unit_name, '', '', substr(p2.unit_name,1, 20)) as unit_name,
p1.line#,
( select p3.text
from all_source p3
where p3.owner = p2.unit_owner and
p3.line = p1.line# and
p3.name=p2.unit_name and
p3.type not in ( 'PACKAGE', 'TYPE' )) text
from plsql_profiler_data p1,
plsql_profiler_units p2,
plsql_profiler_grand_total p4
where (p1.total_time >= p4.grand_total/100)
AND p1.runID = p2.runid
and p2.unit_number=p1.unit_number
order by p1.total_time desc;

prompt =
prompt =
prompt ====================
prompt Most popular lines (more than 1%), summarize across all runs
select p1.total_time/10000000 as hsecs,
p1.total_time/p4.grand_total*100 as pct,
substr(p1.unit_owner, 1, 20) as unit_owner,
decode(p1.unit_name, '', '',
substr(p1.unit_name,1, 20)) as unit_name,
p1.line#,
( select p3.text from all_source p3
where (p3.line = p1.line#) and
(p3.owner = p1.unit_owner) AND
(p3.name = p1.unit_name) and
(p3.type not in ( 'PACKAGE', 'TYPE' ) ) ) text
from plsql_profiler_lines_cross_run p1,
plsql_profiler_grand_total p4
where (p1.total_time >= p4.grand_total/100)
order by p1.total_time desc;

execute prof_report_utilities.rollup_all_runs;

prompt =
prompt =
prompt ====================
prompt Number of lines actually executed in different units (by unit_name)

select p1.unit_owner,
p1.unit_name,
count( decode( p1.total_occur, 0, null, 0)) as lines_executed ,
count(p1.line#) as lines_present,
count( decode( p1.total_occur, 0, null, 0))/count(p1.line#) *100
as pct
from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
'PROCEDURE', 'FUNCTION' ) )
group by p1.unit_owner, p1.unit_name;


prompt =
prompt =
prompt ====================
prompt Number of lines actually executed for all units
select count(p1.line#) as lines_executed
from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
'PROCEDURE', 'FUNCTION' ) )
AND p1.total_occur > 0;


prompt =
prompt =
prompt ====================
prompt Total number of lines in all units
select count(p1.line#) as lines_present
from plsql_profiler_lines_cross_run p1
where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
'PROCEDURE', 'FUNCTION' ) );

spool off
set termout on
edit profsum.out
set linesize 131

SELECT s.line line,
DECODE(p.occured,
0,
'' || substr(s.text, 1, length(s.text) - 1) ||
'',
s.text) text
FROM (SELECT d.TOTAL_OCCUR occured, u.unit_type, d.line#
FROM plsql_profiler_units u, plsql_profiler_data d
WHERE d.RUNID = u.runid
AND d.UNIT_NUMBER = u.unit_number
--AND d.TOTAL_OCCUR > 0
AND u.runid = :A3
AND u.unit_name = :A5) p,
user_source s
WHERE p.line#(+) = NVL(s.line, NULL)
AND p.unit_type(+) = NVL(s.TYPE, NULL)
AND s.NAME = :A5
ORDER BY s.TYPE, s.NAME, s.line

select s.line "Line",
p.total_occur "Occur",
p.total_time "Msec",
s.text "Text"
from all_source s,
(select u.unit_owner,
u.unit_name,
u.unit_type,
d.line#,
d.total_occur,
d.total_time / 1000000 total_time
from plsql_profiler_data d, plsql_profiler_units u
where u.runid = 5
and u.runid = d.runid
and u.unit_number = d.unit_number) p
where s.owner = p.unit_owner(+)
and s.name = p.unit_name(+)
and s.type = p.unit_type(+)
and s.line = p.line#(+)
and s.name = 'DO_SOMETHING'
and s.owner = 'USER'
order by s.line;

CREATE OR REPLACE TRIGGER bri_prof_memory
BEFORE INSERT
ON plsql_profiler_data
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
v_used_mem NUMBER;
BEGIN
SELECT p.pga_used_mem
INTO v_used_mem
FROM SYS.v_$process p, SYS.v_$session s
WHERE p.addr = s.paddr AND s.audsid = USERENV ('sessionid');

:new.spare1:=v_used_mem;

END bri_prof_memory;