|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
Starting
oracle8i you can debug, trace, and profile PL/SQL using the
DBMS_PROFILE package. One advantage with using this method is that you
are not required to change the application code to include debugging
statements. Objects that can be profiled include packages, procedures,
functions and triggers.
An example of using DBMS_PROFILE
:
(1) Start the profiler for the
session. This can be done in 2 ways :
(1st method) From within the
sqlplus prompt :
declare
err number;
begin
err:=DBMS_PROFILER.START_PROFILER
('Test Proc: '||to_char(sysdate,'dd-Mon-YYYY hh:mi:ss'));
end;
/
(2nd method) From within the ON
LOGON trigger :
create or
replace trigger on_logon after logon on test_user.schema
declare
err number;
begin
err:=DBMS_PROFILER.START_PROFILER
('PROF_TEST: '||to_char(sysdate,'dd-Mon-YYYY hh:mi:ss'));
end;
/
NOTE : You can also include the
START_PROFILER statement as found above within your pl/sql code.
(2) Execute your Package,
procedure, function or the trigger.
(3) Stop the profiler :
declare
err number;
begin
err:=DBMS_PROFILER.STOP_PROFILER
;
end;
/
(4) Various execution profiles
are captured within oracle tables which can be viewed as follows :
To review profile results, first
find the runid of the given test:
From within sqlplus :
SQL> column RUN_COMMENT format
a40
SQL>select runid, run_date,
RUN_COMMENT from plsql_profiler_runs;
RUNID
RUN_DATE RUN_COMMENT
---------
--------- ----------------------------------------
1
10-OCT-05 PROF_TEST: 10-Oct-2005 10:10:10
Use this query to get timing
details (min, max, avg):
SQL>column
unit_name format a15
SQL>column
occured format 999999
SQL>column
line# format 99999
SQL>column
tot_time format 999.999999
SQL>select
p.unit_name, p.occured, p.tot_time, p.line# line, substr(s.text, 1,75)
text
from (select
u.unit_name, d.TOTAL_OCCUR occured, (d.TOTAL_TIME/1000000000) tot_time,
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= 1) p,user_source
s
where
p.unit_name = s.name(+) and p.line# = s.line (+)
order by
p.unit_name, p.line#;
From the output, you can
determine as to how much time oracle spends on executing each
component within your pl/sql code including time taken for populating
collections. |
