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 :

WITH sql_gea1
     AS (SELECT row_num r,
                curnum,
                CASE WHEN dep < dep_pre THEN CALL_BEGIN ELSE row_num END b,
                u_id,
                dep,
                dep_pre,
                RPAD (‘.’, dep, ‘.’) || ” || text text,
                sqlid,
                hv,
                (ALL_WAIT_TIME + CPU_TIME) all_response,
                (SELF_WAIT_ELA_S + SELF_CPU_TIME) self_response,
                DECODE (call_name, ‘EXEC’, 1, 0) AS is_exec
           FROM TRCEXTPROF_GEANOLGY_TEXT),
     sql_gea2
     AS (    SELECT ROWNUM row_num2, co.*, CONNECT_BY_ROOT text par
               FROM sql_gea1 co
         START WITH dep = 0
         CONNECT BY PRIOR dep = dep – 1 AND PRIOR b < r AND PRIOR r > r)
  SELECT SUM (self_response) self_response_time,
         SUM (all_response) – SUM (self_response) recursive_response_time,
         SUM (is_exec) exec_count,
         sqlid,
         u_id,
         text,
         hv,
         dep
    FROM sql_gea2
GROUP BY sqlid,
         u_id,
         text,
         hv,
         dep,
         par
ORDER BY MIN (row_num2)

This new feature can be activated using the “g” option.

@trcextprof ORCL_2_ora_7960_sqlgea5.trc -g

Extract :

Capture

That’ it enjoy 😀

DOWNLOAD : TrcExtProf

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