Extracting sql genealogy from sql trace file (10046) with response time [TrcExtProf V1.1]

Sometimes when analyzing a performance problem it’s useful to know from where a particular sql statement was called and how much time does it consume (recursive and self). Fortunately this information is present in SQL trace file but how to format it in a readable way.In my last post i have presented the new version of TrcExtProf profiler and some of the important table that every one can work on to extend the functionality of the script.So here i will add a new section to TrcExtProf based on the “TRCEXTPROF_GEANOLGY_TEXT”  table to display the SQL genealogy.

Here is the query used :

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 :