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: ,

Comments (11) -

8/23/2011 1:44:49 PM #

Hosting Provider India

Hi everybody else !!! The blog was totally fantastic! you all do such a fantastic job at such Concepts..., for one appreciate all you do!

Hosting Provider India United States

9/21/2011 9:55:37 PM #

usb splitter

Just letting you know that I enjoy your site, and I come here every day for the valuable information you provide. Keep up the great work!

usb splitter United States

9/29/2011 4:03:36 PM #

poker tricks

Extraordinary posting. Your posting is probably authored about pretty an extraordinary style but it involves very much beneficial guidelines with good luck.

poker tricks United States

10/2/2011 11:55:54 PM #

pva glue

Greetings!! This is a real cool blog!

pva glue United States

10/28/2011 10:52:03 AM #

casino en ligne

It’s Essential tips for all. I will highly recommend my local freinds in order to read the examples below certainly. Thanks considerably for your complete the task.

casino en ligne United States

11/21/2011 5:18:00 PM #

home security

I enjoyed your blog. It’s easy to read, the content is good, and you’re an educated writer unlike most of the blogs I come across when searching on this topic. I will check back in the future and see if you have more articles. Thanks for posting this, I appreciate the information and the effort you put into your site.

home security United States

11/26/2011 3:09:16 PM #

flowers to france

Great post! your website is fantastic! I just can’t stop reading this. Its so fresh, so filled with updates that I just didn’t know.

flowers to france United States

5/15/2012 1:31:13 PM #

Cheapest logo design

I just read through the entire article of yours and it was quite good. This is a great article thanks for sharing this information. I will visit your blog regularly for some latest post.

Cheapest logo design United States

5/21/2012 3:46:03 PM #

seo services uk

This blog is a good one! Its original, thanks for the info!

seo services uk United States

6/20/2012 2:32:04 PM #

Diminished Value

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.

Diminished Value United Kingdom

8/19/2012 7:07:17 AM #

Free Run 2

Several beer bottles on the table, I am slow to take it over, the boss got the message, took a candle, insert into the front of a little bright, but the outside is dark.

Free Run 2 United States

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Filter by APML

Calendar

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

View posts in large calendar

TextBox