13-1. Installing DBMS_PROFILER
Problem
You want to analyze and diagnose your code to find bottlenecks and areas where excess execution time is being spent, but the DBMS_PROFILER package is not installed.
Solution
Install the DBMS_PROFILER packages, and then create the tables and the Oracle sequence object they need in order to run. Once installed, you can use the DBMS_PROFILER package to help diagnose application performance issues.
Installing the Packages
To install the DBMS_PROFILER packages, follow these steps:
The packages are owned by the
SYSaccount; therefore, it requires DBA login access. Start by opening a SQL Plus connect with theconnect syscommand. If the operation is successful, the system will respond with the message “Connected.”connect sys/sys_pwd as sysdba
Connected.Once connected, run the
profload.sqlscript that can be found within theRDBMS/ADMINdirectory contained in your Oracle Database home. The system will respond with a series of messages like those shown next.
@[Oracle_Home]/RDBMS/ADMIN/profload.sql
You should see the following output after executing the script:
Package created.
Grant succeeded.
Synonym created.
Library created.
Package body created.
Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.
PL/SQL procedure successfully completed.
Finally, enter the
grant executecommand to ensure that all schemas within the database have access to theDBMS_PROFILERpackage.
grant execute on DBMS_PROFILER to PUBLIC;
Grant succeeded.
Creating the Profiler Tables and Sequence Object
Create the tables and Oracle sequence object you need for the profiler to run. Log into the account that wants to use the profiler, and enter the following. The system will respond as follows:
@[Oracle_Home]/RDBMS/ADMIN/proftab.sql
How It Works
The first step creates the packages and makes them available for public access. The second creates the required tables in the schema that wants to use the profiler. There are alternatives to this installation method based on needs and preferences.
The DBA may, for example, want to grant execution privileges to specific users instead of everyone. Step 2 must be repeated for every user who wants to use the profiling tools. An alternative is for the DBA to create public synonyms for the tables and sequence created, thereby having only one copy of the profiler table, in which case the solution changes as in the following example. In the following recipe, replace [Oracle_Home] with the exact path used to install the database software on your system.
connect sys/sys_pwd as sysdba
@[Oracle_Home]/RDBMS/ADMIN/profload.sql
grant execute on DBMS_PROFILER to USER1, USER2, USER3;
@[Oracle_Home]/RDBMS/ADMIN/proftab.sql
CREATE PUBLIC SYNONYM plsql_profiler_data FOR plsql_profiler_data;
CREATE PUBLIC SYNONYM plsql_profiler_units FOR plsql_profiler_units;
CREATE PUBLIC SYNONYM plsql_profiler_runs FOR plsql_profiler_runs;
CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR plsql_profiler_runnumber;