The Power of Proactive Monitoring. Part 5

Something worth mentioning while talking about Oracle Hierarchical Profiler is that its scope is a database session. So, if your middle-tier logic has multiple database calls within established connection, you can benefit from the profiler data. To be fair, it has a number of restrictions as I show further down, but still it can be useful.

Here is a basic example that shows "both sides of the coin":

String sql = 
  "begin dbms_hprof.start_profiling(location=>'IO',filename=>'Case1a.txt'); end;";
CallableStatement stmt = conn.prepareCall(sql);
stmt.execute();

PreparedStatement stmt =
conn.prepareStatement("select listagg(owner_tx,',') within group(order by 1) r1 \n" +
  "from (select distinct  scott.f_change_tx(owner) owner_tx\n" +
  "    from scott.test_tab) A ");
stmt.execute();

stmt =
conn.prepareStatement("select listagg(owner_tx,',') within group(order by 1) \n" +
   "from (select distinct  scott.f_change_tx(owner) owner_tx\n" +
   "    from scott.test_tab) B ");
stmt.execute();

sql = "begin dbms_hprof.stop_profiling; end;";
stmt = conn.prepareCall(sql);
stmt.execute();

As you can see, the code is running between start and stop functionally same queries with a slight modification (so SQL IDs wouldn't match). Both of those SQL statements would cause 50k of function calls. And that scenario is more probable in the real life - reasonably few development environments have established PL/SQL APIs as their communication mechanisms between layers (to my huge sorrow, I am a big fan of "smart DB" approach). 

But would they be shown correctly? As I spoilered above - not exactly:

Since there is no "root" PL/SQL entry point, HProf gets confused and merges both SQL calls into a single one - you see 100k function calls - i.e. you can get at least some stats on what is going on after the root object is being called, but no SQL ID, no clear info about the root element.

What is interesting, the same behavior can be replicated via SQL*Plus:

SQL> exec dbms_hprof.start_profiling 

  2          (location=>'IO',filename=>'Case1b_SQLPlus.txt');

SQL> select listagg(owner_tx,',') within group (order by 1) r1

  2  from (select distinct  scott.f_change_tx(owner) owner_tx

  3      from scott.test_tab a);

...

SQL> select listagg(owner_tx,',') within group (order by 1)

  2  from (select distinct  scott.f_change_tx(owner) owner_tx

  3      from scott.test_tab b);

...

SQL> exec dbms_hprof.stop_profiling;

As you can see, no SQL ID, and all function calls are being merged.

Summary of this part: unless you have PL/SQL APIs you may not enjoy the whole power of HProf, but still you can benefit from some of its provided information