How to use the DBMS_Profiler in PL/SQL ?

In my previous article I have given the multiple examples of SQL performance tuning with multiple examples. In this article I would like to give you more information about how to use the DBMS_Profiler in PL/SQL and tuning the PL/SQL code accordingly. The key question in everyone’s mind is how you can tune the PL/SQL code and where to start it exactly. Here is the answer of this question. We need to start with DBMS_Profiler to tune the PL/SQL performance.

What you will find in this article?

1. What is DBMS_Profiler and Steps to use it?
2. How to use the DBMS_Profiler step by step
3. Which collections we require to use for DBMS_Profiler.

What is DBMS_profiler and How to use the DBMS_Profiler ?

The DBMS_Profiler is nothing but the oracle in built package which is used to find out the performance bottleneck for PL SQL code. This package will capture the information about the code and it will also capture the line by line execution of the code.

Steps to use DBMS_Profiler :

1. Environment set up for DBMS_Profiler
2. Profiler Execution
3. Analyse the data
4. Optimize the PL SQL

Step 1 : Environment set up for DBMS_Profiler

The DBMS_Profiler package is the part of SYS schema. We require to check the privileges to use that package. If we dont have privileges to use that package need to give grants to that package.

Then we require to create the profiler tables in the schema. The question here is how to create a table. There is standard procedure to create the profiler tables.

Execute the Progtabs.SQL statements which you will find in oracle installation directory.

The proftab script will create the 3 tables :

1. PLSQL_PROFILER_DATA
2. PLSQL_PROFILER_UNITS
3. PLSQL_PROFILER_RUNS

This is all about the one time environment set up.

Step 2 : Profiler Execution

In this step we require to execute the profiler. There are standard steps to execute the profiler.

Step 1 : execute procedure dbms_profiler.start_profiler(“Produedure_name”);

Step 2 : excute procedure_to_be_Tuned

Step 3 : dbms_profiler.stop_profiler(“Produedure_name”);

Step 3 : Collect and Analyse the data

Step 1 : execute @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\proftab.sql

Step 2 : Check the profiler tables

Select * from user_tables where table_name like ‘%PROF%’;

Step 3 :

EXEC dbms_profiler.start_profiler(“Test_procedure_tuning”);

P_MONEYTRANS

EXEC dbms_profiler.stop_profiler();

Step 4 :

Check the data in following tables and analyse it. You will get the statements with high cost.

select * from PLSQL_PROFILER_RUNS;
select * from plsql_profiler_data;
select * from plsql_profiler_units;

You can also analyse the data with joining the above tables and getting the required data to analyse. (Runid and unit_number are keys to join it)

Step 4 : Apply the PL SQL and SQL tuning techniques

By analysing the data you will get information about the line which is taking longer time. We require to check that line and you can do the tuning for that line rather than analysing whole code.

These are few techniques to use the performance tuning in PL SQL :

1. Use different Hints
2. Use of Bulk collections
3. Use global temporary table
4. Whenever possible use the SQL rather than PLSQL
5. Use Global temporary tables.

These are multiple steps to use DBMS_PROFILER in detail. If you like this article or if you have issues with the same kindly comment in comments section.