Wednesday, 18 April 2012

Using Hierarchical Profiler in SQL Developer

One of the features exposed since SQL Developer 1.5 is the hierarchical profiler.  There have been several blogs and things about this but none I think that really get into the detail of what you are seeing and how to do it.

The hierarchical profiler allows you to see what happens when your piece of PL/SQL is running.  More specifically, it allows you to see where it is spending most of its time, which means you can concentrate on hammering that down, rather than wondering where to start.

For today, I'm using a really basic reference table with a few rows in it to allow us to do something.  I have also created two procedures, one of which calls the other so we have some nested dependencies.

drop table hier_demo;
create table hier_demo (id number, name varchar2(200));
insert into hier_demo values (1, 'Barry');
insert into hier_demo values (2, 'Lisa');
insert into hier_demo values (3, 'Rebecca');
insert into hier_demo values (4, 'Katie-Ellen');

CREATE OR REPLACE
PROCEDURE PRINTER(
    NAME IN VARCHAR2 )
AS
BEGIN
  dbms_output.put_line(NAME);
END PRINTER;
/
CREATE OR REPLACE
PROCEDURE SHOW_PEEPS
AS
  CURSOR hiercur
  IS
    SELECT * FROM hier_demo;
  -- hierrec hiercur%type;
  -- type  hiertab is table of hierrec%TYPE;
BEGIN
  FOR myrec IN hiercur
  LOOP
    dbms_output.put_line(myrec.name);
  END LOOP;
END;
/

Running the main procedure normally gives us some nice and simple out put.


When we click on the profile button in the plsql editor, SQL Developer will check that you have the proper permissions and the associated table to use the profiler.

When you hit the profiler button , it first comes up with the run dialog to set the parameters for the stored procedure.  Hitting ok on this diualog will run the stored procedure and any issues it has will also pop up while you are profiling.  


As this happens, the profiler  first checks to see if the there is a profiler log directory.  and if there is not one, it will prompt you to create one and get the appropriate permissions.






Hitting ok on this makes the tool then set up the directory for the profile.  To do this, it needs to run some SQL as sys to do it.


If the user agrees with all this, then he is prompted for SYS passwd to actually create the tables for the profiler statistics in the local user, in this case, hrdemo.
Finally, when they agree, the tool asks if it can setup a local set of tables for the profiler,  We'll agree to this too and make sure the profile is captured.

 Now, when we look at the profile tab of the PLSQL editor, we should have a new editor with the results of the profile.


This shows us a breakdown of the how the procedure actually executed all the way down to the actual fetch which returned the rows.  A very slight change to the procedure, in this case adding another procedure as a dependency which we also described above, we can show the nesting in the profile going further down.
create or replace
PROCEDURE SHOW_PEEPS
AS
  CURSOR hiercur
  IS
    SELECT * FROM hier_demo;
  -- hierrec hiercur%type;
  -- type  hiertab is table of hierrec%TYPE;
Begin
  FOR myrec IN hiercur
  Loop
    PRINTER(myrec.name);
  END LOOP;
END;

This now shows us that we have another profile in the set and clicking on it gives us the hierarchy of calls in the stored procedures execution.  The main point here is that we can now see the further level of indirection through the printer procedure.


So thats all of this profiler for now,  If you want to see how to do this with your own tables, the easiest thing to do is to download the Oracle Developer Days VM from OTN.  This particular blog will make an appearance as part of a bigger set later which we will discuss Tuning in general..

2 comments:

Lisa said...

Good idea to post your whole submission! very helpful and appreciated!Hierarchy Structure

djeday84 said...

can't reproduce this example in sqldeveloper 3.1.07 Build MAIN-07.42 =(