OCP Blog
Super DBA
DBMS_PROFILER - Identify performance bottleneck
by Vazha Mantua Monday, August 22, 2011 3:13 PM

The dbms_profiler  package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.

The basic idea behind profiling with dbms_profiler is for the developer to understand where their code is spending the most time, so they can detect and optimize it.

Let see a example:

In Scott schema you have a procedure named create_client_information. Your goal is find which lines of source code of procedure is potential bottleneck .

Once you have run the profiler, Oracle will place the results inside the dbms_profiler tables.

First of all you should build appropriate tables and procedures for using dbms_profiler.

Step By Step:

 

1. Create necessary metadata for using dbms_profiler

   connect  as sysdba
run scripts @$ORACLE_HOME/rdbms/admin/profload.sql

    connect  as Scott 
run scripts @$ORACLE_HOME/rdbms/admin/proftab.sql

 

2. Delete unused data and start profiler for collecting information

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;
commit;

declare
  success binary_integer;
begin
  success := dbms_profiler.start_profiler(run_comment => 'test_run');

  if success <> 0 then
    dbms_output.put_line('could not start profiler');
  else
    dbms_output.put_line('Profiler started!');
  end if;
end;

3. Run procedure which you are testing now, for our example

begin  create_client_information(); end;

4. Stopping profiler

 select decode(
  dbms_profiler.stop_profiler,
  '0', 'Profiler Stopped',
  'Could not stop profiler')
from
  dual;

5. Get A results

 

First of all you should find which runid  is your case, you can get this information from statement:

select runid from plsql_profiler_runs
 
then you can get information with select

SELECT *
FROM PLSQL_PROFILER_DATA A,
     PLSQL_PROFILER_UNITS B
WHERE A.UNIT_NUMBER = B.UNIT_NUMBER
   AND A.RUNID = &ID

 

 With dbms_profiler you can find in which line of procedure you have a performance problem, but only find, it will not help you optimize code itself.

Tags: ,

Filter by APML

Calendar

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

View posts in large calendar

TextBox