DBMS_PROFILER INSTALLATION

DBMS_PROFILER INSTALLATION: DBMS_PROFILER must be installed as the SYS user

If you have a database administrator, you can skip the next steps, but if you're on a new database, you'll probably have to install the profiler.

All the installation scripts you need are located in $ORACLE_HOME/RDBMS/Admin. In sqlplus, you can run this script if you simply prepend an @ in front of it.

First, you have to run the following script as SYS:

@$ORACLE_HOME/rdbms/admin/profload.SQL

Next, you have to run the following script with each user you want to use the profiler:

@$ORACLE_HOME/rdbms/admin/proftab.SQL

$ cd $ORACLE_HOME/rdbms/admin

$ Sqlplus / as sysdba

Step: 1 - /* Run profload.sql which will install the DBMS_PROFILER Package*/

SQL> @E:\app\Admin\product\11.2.0\dbhome_1\RDBMS\ADMIN\profload.sql

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.

/* Now create the user for a test of DBMS_PROFILER */

SQL> Create user Myuser identified by Myuser;

User created.

SQL> Grant Connect, Resource,DBA to Myuser;

Grant succeeded.

SQL> Conn Myuser/ Myuser

Connected.

Step: 2 - /* Once connected run Proftab.sql which will create special tables where profiler puts its results */

SQL> show user

USER is "MYUSER"

SQL> @E:\app\Admin\product\11.2.0\dbhome_1\RDBMS\ADMIN\proftab.sql

drop table plsql_profiler_data cascade constraints

* ERROR at line 1: ORA-00942: table or view does not exist

drop table plsql_profiler_units cascade constraints

*ERROR at line 1:ORA-00942: table or view does not exist

drop table plsql_profiler_runs cascade constraints

*ERROR at line 1:ORA-00942: table or view does not exist

drop sequence plsql_profiler_runnumber

*ERROR at line 1:ORA-02289: sequence does not exist

Table created.

Comment created.

Table created.

Comment created.

Table created.

Comment created.

Sequence created.

Before DBMS_PROFILER was introduced, tuning long PL/SQL programs was a great pain. Tuning PL/SQL was done by SQL TRACE and TKPROF. Code of thousands of lines was monitored via BMS_UTILITY.GET_TIME to measure time consumed by individual statements.

With the presence of DBMS_PROFILER this has become quite easy to monitor the performance of the PL/SQL programs and identifying the culprit statements consuming too much time while the run of the program.

Click here to know how DBMS_PROFILER helps in identifying Long running SQLs.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.