Andrew Fraser DBA

TKProf by ASH

Oracle session trace + tkprof is still the best tool for performance diagnosis. But:

• Need access to database server user_dump_dest directory.
• Has to be switched on in advance
• Has a performance impact, especially if tracing multiple sessions or tracing for a long time.

And ASH is a good enough alternative much of the time.

The below scripts mimic tkprof output using only ASH data.

With these, use dba_hist_active_sess_history (10s sample) in place of v$active_session_history (1s sample) for less recent activity.
For RAC, use gv$active_session_history if want cross-instance information, and specify dba_hist_active_sess_history.instance_number= if not wanting cross-instance information.

The below mimics tkprof for a specific session, but wider queries on username , program , module , action , client , machine , sql_id work also.

Tkprof by ash: 1) Summary total, equivalent to the bottom of tkprof report.

SELECT CASE WHEN a.event IS NULL AND a.in_parse = 'Y' THEN 'CPU Parse'
            WHEN a.event IS NULL AND a.in_parse = 'N' THEN 'CPU Execute/Fetch'
            ELSE a.event
            END AS event
     , COUNT(*)
     , ROUND ( 100 * RATIO_TO_REPORT ( COUNT(*) ) OVER() ) AS percentage
  FROM v$active_session_history a
  LEFT OUTER JOIN dba_users u ON u.user_id = a.user_id
 WHERE a.sample_time BETWEEN TO_TIMESTAMP ( '01-APR-2017 13:00:00' , 'DD-Mon-YYYY HH24:MI:SS' ) AND TO_TIMESTAMP ( '01-APR-2017 16:00:00' , 'DD-Mon-YYYY HH24:MI:SS' )
   AND u.username = 'MYUSER'
   AND a.session_id = 99
   AND a.session_serial# = 99
 GROUP BY CASE WHEN a.event IS NULL AND a.in_parse = 'Y' THEN 'CPU Parse'
               WHEN a.event IS NULL AND a.in_parse = 'N' THEN 'CPU Execute/Fetch'
               ELSE a.event
               END
 ORDER BY COUNT(*) DESC
;

Tkprof by ash: 2) List of sql statements ranked in order. Sql_text is given double substr’s because of limitations working with clobs.

SELECT a.sql_id
     , COUNT(*)
     , ROUND ( 100 * RATIO_TO_REPORT ( COUNT(*) ) OVER() ) AS percentage
     , LOWER ( SUBSTR ( REGEXP_REPLACE ( TO_CHAR ( SUBSTR ( s.sql_text , 1 , 600 ) ) , '[[:space:]]+' , ' ' ) , 1 , 100 ) ) AS sql_text
     , CASE WHEN MIN ( a.sql_plan_hash_value ) = MAX ( a.sql_plan_hash_value)
                    THEN TO_CHAR ( MIN ( a.sql_plan_hash_value ) )
                    ELSE TO_CHAR ( MIN ( a.sql_plan_hash_value ) ) || ' ' || TO_CHAR ( MAX ( a.sql_plan_hash_value ) )
                    END AS plan_hash_values
  FROM v$active_session_history a
  LEFT OUTER JOIN dba_users u ON u.user_id = a.user_id
  LEFT OUTER JOIN dba_hist_sqltext s ON s.sql_id = a.sql_id -- AND s.dbid = a.dbid
 WHERE a.sample_time BETWEEN TO_TIMESTAMP ( '01-APR-2017 13:00:00' , 'DD-Mon-YYYY HH24:MI:SS' ) AND TO_TIMESTAMP ( '01-APR-2017 16:00:00' , 'DD-Mon-YYYY HH24:MI:SS' )
   AND u.username = 'MYUSER'
   AND a.session_id = 99
   AND a.session_serial# = 99
 GROUP BY a.sql_id
     , TO_CHAR ( SUBSTR ( s.sql_text , 1 , 600 ) )
 ORDER BY COUNT(*) DESC , a.sql_id
;

Tkprof by ash: 3) Details for a particular sql id.

SELECT a.sql_plan_line_id
     , COUNT(*)
     , ROUND ( 100 * RATIO_TO_REPORT ( COUNT(*) ) OVER() ) AS percentage
     , a.sql_plan_operation || ' ' || a.sql_plan_options || ' ' || LOWER ( o.owner || '.' || o.object_name ) || ' ' || o.object_type AS plan_operation
     , a.sql_plan_hash_value
  FROM v$active_session_history a
  LEFT OUTER JOIN dba_users u ON u.user_id = a.user_id
  LEFT OUTER JOIN dba_objects o on o.object_id = a.current_obj#
 WHERE a.sample_time BETWEEN TO_TIMESTAMP ( '01-APR-2017 13:00:00' , 'DD-Mon-YYYY HH24:MI:SS' ) AND TO_TIMESTAMP ( '01-APR-2017 16:00:00' , 'DD-Mon-YYYY HH24:MI:SS' )
   AND u.username = 'MYUSER'
   AND a.session_id = 99
   AND a.session_serial# = 99
   AND a.sql_id = 'mysqlid'
 GROUP BY a.sql_plan_line_id
     , a.sql_plan_operation
     , a.sql_plan_options
     , a.sql_plan_hash_value
     , o.owner
     , o.object_name
     , o.object_type
 ORDER BY a.sql_plan_hash_value , COUNT(*) DESC , a.sql_plan_line_id
;

If there is only one plan hash value (or if you want to see all plan hash’s) then the plan_hash_value parameter is not required:

SELECT * FROM TABLE ( DBMS_XPLAN.display_awr ( sql_id => 'mysqlid' , plan_hash_value => 99 ) ) ;
SELECT * FROM TABLE ( DBMS_XPLAN.display_awr ( sql_id => 'mysqlid' ) ) ;