Tony’s Oracle Tips

June 1, 2010

Querying dba_hist_active_sess_history

Filed under: Uncategorized — tonyhasler @ 12:14 pm

I finally couldn’t stand it anymore and investigated why querying this view is slow. A typical query might look like this:
WITH x
       AS (SELECT   *
             FROM      dba_hist_active_sess_history
                    JOIN
                       dba_hist_snapshot
                    USING (snap_id, dbid, instance_number)
            WHERE   begin_interval_time BETWEEN TO_DATE(
                                                   '28-MAY-2010 21:30'
                                                  ,'DD-MON-YYYY HH24:MI'
                                                )
                                            AND  TO_DATE(
                                                    '28-MAY-2010 23:59'
                                                   ,'DD-MON-YYYY HH24:MI'
                                                 ))
  SELECT   TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value
          ,session_id, event, COUNT( * )
    FROM   x
GROUP BY   TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value
          ,session_id, event;
Of course, you may not use ANSI SQL or factored subqueries but that is by the by. Examination of the execution plan will reveal that all partitions of the session history are probed. This is because the snap_id column returned from the view is from that table and not the main snapshot table so the predicate is not applied there. It is massively faster to query the snapshot table and then plug the returned snap_ids into the query by hand:
SELECT   snap_id
  FROM   dba_hist_snapshot
WHERE   begin_interval_time BETWEEN TO_DATE('28-MAY-2010 21:30'
                                            ,'DD-MON-YYYY HH24:MI')
                                 AND  TO_DATE('28-MAY-2010 23:59'
                                             ,'DD-MON-YYYY HH24:MI')
And then
WITH y
       AS (SELECT   *
             FROM   dba_hist_active_sess_history
            WHERE  snap_id IN (17823, 17822)
                    )
  SELECT   TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value
          ,session_id, event, COUNT( * )
    FROM   y
GROUP BY   TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value
          ,session_id, event;
This works because transitive closure only applies to predicates on literals: sn.snap_id = ash.snap_id and ash.snap_id in (17822,17823) => sn.snap_id in (17822,17823).

Of course, if you do this a lot from a suitably privileged account you could create an “improved” version of the view (that is unsupported and likely to break in future releases) that selects “sn.snap_id” rather than “ash.snap_id” or you could create a table function that automates the creation of the dynamic SQL.  Readers are invited to post their implementations!

Advertisements

1 Comment »

  1. Interesting… I’d always wondered about why that was so slow as well. There was me thinking it was due to it sending a report to Oracle because it’s an extra cost option view!

    Comment by Paul — June 1, 2010 @ 2:31 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: