TrcExtProf.sql the raw trace file (10046) profiler V1.1 (Wait event histo,Hot I/O blocks,etc)

I have finally got some time to work on TrcExtProf :D, so here is the new version including many bugs fixes,faster and with enhanced functionality. If you don’t know  what it’s TrcExtProf please check my previous post.

TrcExtProf is an sql trace file (10046) profiler based essentially on the use of SQL/analytic SQL, PL/SQL is used only for formatting purpose.So YES SQL can do it also 😀

The first version of TrcExtProf was based only on external tables and regexp,i designed it this way to have the smallest footprint possible on the database.Sadly the cost of this design was a very poor response time for large sql trace files. So i used a new approach on this version, i begin by eliminating the use of regexp and replacing it with “substr” and “like” because regexp function are more CPU intensive then i redesigned the script to be based on the use of temporary tables for storing intermediate result.The response time was far better with expense of more footprint.

Enough talk let’s do some test :

  • Database version : 11.2.0.3.9
  • OS : HP-UX V3

Test case :

CREATE OR REPLACE procedure test_me1 as
begin
test_me2;
end;
/

CREATE OR REPLACE procedure test_me2 as
cursor c  is (select * from dba_segments);
begin
for r in c  loop
dbms_output.put_line(r.segment_name);
end loop;
end;
/

exec dbms_monitor.session_trace_enable(binds => true);
exec HATEM.test_me1 ;
exec HATEM.test_me1 ;
var object_type varchar2(100);
exec :object_type :=  ‘SYNONYM’;
select * from dba_objects where object_type = :object_type;
exec dbms_monitor.session_trace_disable;

Now let’s run the analysis :

Important : Before running TrcExtProf please take the time to create the tables indicated in the header of the script.

Capture

Usage:  @TrcExtProf.sql tracefile.trc -options

TrcExtProf take this parameters as arguments :

Options :

  • t(threshold) : Statement which contribute less than this threshold to the total response time will not be diplayed in the TOP SQL section . Default : 10
  • r(threshold) : Statement which contribute less than this threshold to the parent statement response time will not be displayed in the RECURSIVE STATEMENT section. Default : 20
  • w : Display wait event histograms.
  • b : Display bind variables.
  • d : Display I/O stats.

UPDATE 30/09/2015 : TrcExtProf can now also display the sql genealogy when used with the “g” option for more info here.

PART 1 :

lance trace

You can see here the parameters that was specified and the different steps of loading the temporary tables.

PART 2 :

Sans titre 2

Nothing new in this part.

PART 3 :

Capture 3

We can now also check the response time calculation here.

PART 4 :

Capture 4

The new enhancement here is that we can check the wait event histograms in ms.

PART 5 :

Capture 10

This new section show the hot blocks with times expressed in s.

PART 6 :

Capture 6

Nothing new in this section it show the top no recursive statement.

PART 7 :

Capture 7

Capture 8

Here we can see the top statement including the contributing recursive statement.

That’s it :D.

Here is the list of the important temporary table created to store intermediate result you can use them to extend the functionality of the script  :

  • TRCEXTPROF_WAITS : Contain all the wait events.
  • TRCEXTPROF_GEANOLGY_TEXT : Contain the genealogy of dbcall and different stats like self_cr and cr.One important column is CALL_BEGIN it indicate when the dbcall begin.
  • TRCEXTPROF_BASE_CURSOR : Contain the list of cursor.

Stay tuned 😀

DOWNLOAD : TrcExtProf

2 thoughts on “TrcExtProf.sql the raw trace file (10046) profiler V1.1 (Wait event histo,Hot I/O blocks,etc)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s