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.
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..