[PL/SQL] Performance tuning — DBMS_PROFILER

How to start with PL/SQL performance tuning ?

1. Manual inspection of code
2. Execute the code with multiple log statements and analyze the statement that more time
3. Tune the SQL code & PL/SQL code separately

In this article, we’ll talk about “dbms_profiler”, this is a tool to indentify performance bottleneck in plsql code. For example, we have a long process, then we can’t analyze for each single queries, then we could use “dbms_profiler” to profile existing PL/SQL applications.

1. Environment Setup

• We just need to do it 1 time only to create 3 profile tables. Here we’ll compile proftab.sql in sql*plus

SQL> @D:\oracle\product\10.2.0\db_2\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.
SQL>

• Then check if the 3 tables are created successfully via dictionary table “user_tables”

2. Profiler Execution

• Let’s make a demo with 3 procedures : proc_a() calls proc_b() calls proc_c()

https://gist.github.com/binh12A3/d59f0b01c407227fa8fb9183800a78ca

• Depends on the PL/SQL code, this step may take lot of time…So wait until the PL/SQL code execution gets completed.

BEGIN
  dbms_output.put_line('###### STARTING ######');
  dbms_profiler.start_profiler('MY_DEMO_1');
  proc_a();
  dbms_profiler.stop_profiler();
  dbms_output.put_line('######## END ########');
END;
/
###### STARTING ######
######## END ########

3. Analyze profiler data

• As we can see the line number “5” in “proc_c” has been executed for “125000” times, and the taken total_time is mentioned here. So we could compute the percent of time here 61.61% of the time is spent in 1 particular line “5”.

SELECT r.run_date, r.run_comment, u.unit_type, u.unit_name, d.line#, d.total_occur, d.total_time, d.min_time, d.max_time
      ,ROUND(d.total_time/1000000000) total_time_in_sec
      ,TRUNC(((d.total_time)/(SUM(d.total_time) OVER()))*100, 2) pct_of_time_taken
  FROM plsql_profiler_data d, plsql_profiler_runs r, plsql_profiler_units u
 WHERE d.runid = r.runid
   AND u.runid = r.runid
   AND u.unit_number = d.unit_number
   AND d.total_time > 0
 ORDER BY d.total_time DESC;

Sometime, the total_occur will be very less, while the pct_of_time_taken will be very high.

• After finishing analyzing, we could clear profiler tables

TRUNCATE TABLE plsql_profiler_data;
DELETE FROM plsql_profiler_units;
DELETE FROM plsql_profiler_runs;

4. Optimize the PL/SQL

• When we come to optimization, there will be many ways to optimization. Here we’ll just highlight some points

• This step might be repeated several times until we get an expected timing result. For each run, you could capture the profiler data into excel file in order to compare the result with the next runs. Then finally we could check how much performance we actually improved.