Queries

Queries have been broken down into 3 sections or levels.

Level 1 - queries to give and overview of database activity
Level 2 - given a time frame, aggregate the data into “Top X”, ie “Top SQL”, “Top Session” etc
Level 3 - given a time frame and an sql statement or wait event, filter and aggregate the data for example display the top sql *and* block type for buffer busy waits

 Here are queries to provide help mining performance data in ASH. ASH is a multidimensional  data source, comprised of some 30-60 fields depending on version, and  spanning the axis of

    time frame
    consumer
    SQL
    state

The states could be running on CPU or waiting and waiting can be broken down into I/O, locks, latches or any one of several hundred wait states.
The SQL can be grouped by sql_id, plan hash, child number, opcode etc.
The consumer can be broken down or grouped by session_id (along with serial#), user, module, action, package, procedure, service or client.

All this to say that queries on ASH offer an overwhelming choice of dimensions, filters and groups that we can use in sql queries when mining data from ASH, thus the ASH queries pages are here to provide examples of useful queries on ASH and be a forum where people can add queries as well as discuss, correct and modify queries already presented.

2 Responses to “Queries”  

Feed for this Entry Trackback Address
  1. 1 Shevin May 30th, 2008 at 2:19 am

    Hi,

    Just wondering how retention policy is configured for ASH.
    For example I can see that active session history has only 7 days worth of data while AWR was setup for 14 days and some historical data such as execution plan history has 2 weeks of data.

    Thanks

  2. 2 Graham Jun 2nd, 2008 at 3:47 pm

    Hi Shevin.

    The ASH data that is written to AWR has the same retention as the rest
    of AWR, as you thought. If that is not the case it’s a bug.

    Cheers, Graham

Leave a Reply

You must log in to post a comment.