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 : 126.96.36.199.9
- OS : HP-UX V3
Test case :
CREATE OR REPLACE procedure test_me1 as
CREATE OR REPLACE procedure test_me2 as
cursor c is (select * from dba_segments);
for r in c loop
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;
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.
Usage: @TrcExtProf.sql tracefile.trc -options
TrcExtProf take this parameters as arguments :
- 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 :
You can see here the parameters that was specified and the different steps of loading the temporary tables.
PART 2 :
Nothing new in this part.
PART 3 :
We can now also check the response time calculation here.
PART 4 :
The new enhancement here is that we can check the wait event histograms in ms.
PART 5 :
This new section show the hot blocks with times expressed in s.
PART 6 :
Nothing new in this section it show the top no recursive statement.
PART 7 :
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