PL/SQL Profiler

 1) verify if package DBMS_PROFILER is installed


SYS@DB> desc sys.dbms_profiler

if it’s not yet installed, connect as sysdba and run script


SYS@DB> @?/rdbms/admin/profload.sql

2) verify if profiler repository tables are created

connect as application user (the one who runs pl/sql for analysis)


USER@DB> desc PLSQL_PROFILER_RUNS
USER@DB> desc PLSQL_PROFILER_UNITS
USER@DB> desc PLSQL_PROFILER_DATA

if they are not installed yet, install them by running script


USER@DB> @proftab.sql

the script can be downloaded as PROF.zip from metalink note: ID 243755.1

3. profile a pl/sql

To profile a PL/SQL Library (package, procedure, function or trigger), include in its body
the two calls to actually start and complete the profiling.  Use the example below on any
PL/SQL Library to profile.


USER@DB>
BEGIN
DBMS_PROFILER.START_PROFILER('pl-sql profiling run1');

...    -- PL/SQL goes here

DBMS_PROFILER.STOP_PROFILER;
EXCEPTION -- this line may exist in your code
...
END;
/

4. profiler.sql –  Reporting PL/SQL Profiler data generated by DBMS_PROFILER

USER@DB> @profiler.sql <runid>

the script profiler.sql can be found in PROF.zip from metalink note: ID 243755.1

it requires one input ‘runid’, we get the last pl/sql profiler run id by


USER@DB> select runid from plsql_profiler_runs where run_comment = 'pl-sql profiling run1';

RUNID
--------
1
USER@DB> @profiler.sql 1

it will generate a report in html format.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: