-- (c) Kyle Hailey 2007


All the ahs data is stored in the table

    v$ash_all

Here is a query that looks at all the databases:

actallsum.sql - condensed overview of all targets over last hour

    HOST                   ASL GRAPH
   --------------- ---------- ------------------------------
    # of cpus                 ----1----2----3----4----5----6
   bsn08                 1.83 +++------2-
   control1              1.82 ++-------2-
   cont01                1.28 +-----   2
   bsn02                  .68 ++--               4
   tsukuba10              .53 --       2
   limerock               .13          2
   bsn03                  .06                    4
   devnode                .02     1

   HOST                   ASL GRAPH
   --------------- ---------- ------------------------------
    # of cpus                 ----1----2----3----4----5----6
   control1              2.83 +++++++--2-------
   cont01                 1.3 ++-----  2
   bsn08                  .44 +-       2
   tsukuba10              .42 --       2
   bsn03                  .33 +-                 4
   bsn02                  .28 +-                 4
   limerock               .15          2
   devnode                .02     1



   Explanation
       "1" or "2" or "4" - represents the # of CPU's on the machine
       "+" - # pluses represent avg user load on CPU
       "-" - # minuses represent avg user load Waiting

actall.sql - detailed view on all targets
    DB                   AVE_ACT_SESS WAIT_EVENT                            
    -------------------- ------------ ---------------------------------------- 
    bsn02.cdb                      .2 enqueue                                 
    control2.cdb                  .14 log file sync                          
                                  .24 direct path read                      
                                  .25 db file sequential read              
                                  .55 direct path write                   
                                 1.34 db file scattered read             
                                 2.32 enqueue                           
                                 5.98 ON CPU                           
    bsn03.cdb                     .17 enqueue                         

In general I want to  look at just one database at a time.
(for example if one is having performance problems)
I also want to keep the queries somewhat compatible 
with Oracle 10g's V$ACTIVE_SESSION_HISTORY (which most
should work with just by "create view v$ash as
select * from V$ACTIVE_SESSION_HISTORY" but I haven't
tried this yet)
Because the repository can contain data from many
targets and I didn't want to include
   
    where dbid = :dbid

in all the queries, I instead create views that filter by dbid

    drop view v$ash;
    create view v$ash as
    select  * from v$ash_all
    where dbid = &v_dbid ;
    
and use

    v$ash

in my queries from then on out.
If I want to change target, I run

change_target.sql

This could probably be done more effciently by just
inserting the current target into a single row table
and then basing the views on a join with this table
but I haven't tried that yet)

Here are data mining query  examples:

 act.sql
 Like "top 5 timed events" in statspack, but it also gives
 the average active sessions which is more impotant.
 The script defaults to the last 15 minutes if you hit return
 otherwise you can give it a start time in measured in minutes ago
 and a duration window in minutes.
    WAIT_EVENT                                  CNT   % Active Ave_Act_Sess
    ---------------------------------------- ------ ---------- ------------
    latch free                                    2        .12          .01
    buffer busy waits                             9        .53          .04
    SQL*Net more data from client                15        .88          .07
    log file sync                                50       2.92          .23
    db file sequential read                      51       2.98          .24
    db file scattered read                      210      12.27          .97
    enqueue                                     262       15.3         1.21
    ON CPU                                     1111      64.89         5.12

 aveact.sql - 06/06/07
 aveact.sql - 08/02/07 made time buckets on the hour
 This shows activity in 15 minute intervals, added GRAPH (before it ws just #s)
 aveact.sql - 08/03/07 - fixed anomolie with 
 subtracting out BACKGROUND waits from total. I don't care about background waits
 but I do care about displaying BACKGROUND CPU
 Also rearanged the columns. I include NPTS (ie samples) to show accuracy. In a perfect
 world we would have 300 points (samples) per 15 minute bucket. This doesn't work
 out for a couple reasons. One sampling doesn't happen every 3 seconds as requested
 and two, if there is no data collected, then there is no insert. I'll probably change
 the collection to include place holders for no data so this isn't a problem.

 This is like and ASCII version of OEM 10g's performance page

   TM                 NPTS  AVEACT GRAPH                                 CPU      WAITS
   ---------------- ------ ------- ------------------------------ ---------- ----------
   02-AUG  16:45:00    261     .03           2                             2          5
   02-AUG  17:00:00    285     .16 -         2                             6         41
   02-AUG  17:15:00    283     .36 +-        2                            31         72
   02-AUG  17:30:00    290    1.12 +----     2                            70        254
   02-AUG  17:45:00    248     .12 -         2                             4         26
   02-AUG  18:00:00    211     .27 -         2                             9         47
   02-AUG  18:15:00    196     .34 +-        2                            24         42
   02-AUG  18:30:00    276     .76 +---      2                            54        156
   02-AUG  18:45:00    268    1.22 +-----    2                            78        248
   02-AUG  19:00:00    186    1.54 +-------  2                            36        251
   02-AUG  19:15:00    205     .09           2                             7         11
   02-AUG  19:30:00    262     .69 +--       2                            65        115
   02-AUG  20:45:00    219    1.81 ++------- 2                            86        311

   "+" - represent CPU usage
   "-" - represent wait time
   "2" in the GRAPH is the number of CPUS on this machine
   GRAPH is a graphical representation of AVEACT (ASL)

 aveactn.sql same as above but with top events
   TO_CHAR(STA SAMPLES FASL FIRST           SASL SECOND          GRAPH
   ----------- ------- ---- --------------- ---- --------------- ---------------
   31 02:45:00      14  .71 db file sequent  .50 CPU             +++-----  2
   31 03:00:00     258  .48 db file sequent  .31 log file sync   +-------  2
   31 03:15:00     214  .17 CPU              .15 log file sync   +--       2
   31 03:30:00     268  .30 db file sequent  .21 log file sync   +----     2
   31 03:45:00     284  .55 db file sequent  .27 CPU             +------   2
   31 04:00:00     222  .55 db file sequent  .47 log file sync   +-------- 2
   31 04:15:00     194  .30 CPU              .26 log file sync   +----     2
   31 04:30:00     251  .29 db file sequent  .22 CPU             +---      2
   31 04:45:00     280  .57 db file sequent  .29 CPU             +-----    2
   31 05:00:00     212  .93 db file sequent  .38 log file sync   +---------2--
   31 05:15:00     210  .44 log file sync    .36 db file sequent +------   2
   31 05:30:00     273  .26 db file sequent  .23 log file sync   +---      2
   31 05:45:00     276  .57 db file sequent  .28 CPU             +------   2
   31 06:00:00     234 1.09 db file sequent  .50 db file scatter ++--------2----
 
   "+" - represent CPU usage
   "-" - represent wait time
   "2" in the GRAPH is the number of CPUS on this machine
 
   "+" - represent CPU usage
   "-" - represent wait time
   "2" in the GRAPH is the number of CPUS on this machine
   ASL - Active Session Load
   FASL - first (top) event measuerd in ASL
   SASL - second most event measuerd in ASL

 longsql.sql
 This shows the longest running queries. The higher the count the longer
 they have been running (you can't do this with 10g ASH)
   START_TIME     COUNT(*)     SQL_ID FIXED_TABLE_SEQUENCE       TOPN
   ------------ ---------- ---------- -------------------- ----------
   JUN-01 02:00         29  795857215                34254          1
   JUN-01 02:00         29  795857215                34547          1
   JUN-01 02:00         29  986013188                34305          1
   JUN-01 02:15        105  452165714                35961          1
   JUN-01 02:15        102  866152581                35999          2
   JUN-01 02:30        119 2586830956                39017          1
   JUN-01 02:30        109  452165714                39130          2

 topsqln.sql
 This is really cool. It shows the top sql and then breaks their time down
 into the top 3 events
 
        SQL_ID TOT  FPCT FIRST            SPCT SECOND       TPCT THIRD
    ---------- --- ----- -------------- ---- -------------- ---- -------------
     288152548   1 1.00 db file sequent
    3417348745   4  .75 enqueue         .25 db file sequent
    2641200096   6 1.00 ON CPU
    2053128062  13  .92 ON CPU          .08 db file sequent
    3186842843  18  .89 log file sync   .11 ON CPU
    3003979045 101  .59 enqueue         .39 ON CPU          .02 db file sequent
     795857215 248  .50 db file scatter .46 ON CPU          .02 buffer busy wai
     986013188 248  .50 ON CPU          .45 db file scatter .02 buffer busy wai
     452165714 253  .97 ON CPU          .03 db file scatter
     866152581 253 1.00 ON CPU
    2586830956 253  .99 ON CPU          .01 db file scatter .00 db file sequent
    2710468782 335  .49 ON CPU          .35 enqueue         .10 db file sequent

 ash_waiters.sql
 just a formated dump from v$ash 
 also translates enqueues into type and mode 
 
    SAMPLE_TIME     USERNAME   SID   SERIAL   HASH_VALUE STATUS
    --------------- ---------- ----- -------- ---------- --------------------
    JUN-01 02:28:02 SIMULATOR  27    5         866152581 direct path read
                    SIMULATOR  30    2         986013188 ON CPU
                    SIMULATOR  32    1         452165714 direct path read
                    SIMULATOR  36    489      2586830956 ON CPU
                    SIMULATOR  38    543       795857215 ON CPU
    JUN-01 02:28:05 SIMULATOR  27    5         866152581 direct path write
                    SIMULATOR  30    2         986013188 db file sequential r
                    SIMULATOR  32    1         452165714 direct path read
                    SIMULATOR  36    489      2586830956 direct path read
                    SIMULATOR  38    543       795857215 db file scattered re
    JUN-01 02:28:08 SIMULATOR  27    5         866152581 direct path read
                    SIMULATOR  30    2         986013188 db file sequential r
                    SIMULATOR  32    1         452165714 direct path write
                    SIMULATOR  36    489      2586830956 direct path read
                    SIMULATOR  38    543       795857215 db file scattered re
    JUN-01 02:28:11 SIMULATOR  27    5         866152581 direct path read
                    SIMULATOR  30    2         986013188 db file sequential r
                    SIMULATOR  32    1         452165714 direct path read
                    SIMULATOR  36    489      2586830956 direct path read
                    SIMULATOR  38    543       795857215 db file scattered re
    
 topenq.sql
 shows the top locks
    /* some object names are missing, some block#s seem to be 0 */
    OBJECT_NAME                     OBJ#      FILE#     BLOCK#   COUNT(*) TY M
    ------------------------- ---------- ---------- ---------- ---------- -- -
                                  115628         13     117610         98 TX 4
    BX_CMTS_QOS_P_C               104667         10          0         98 TX 4
                                  115628          4     453191         99 TX 4
                                  115379         12     554559        100 TX 4
    BX_CMTS_CPU_HOUR_FACTS_CM     104651         11          0        100 TX 4
    BX_VA_CM_HOUR_CM              115842         13     124725        105 TX 4
                                  115379         12    1306554        107 TX 4
 
 topobj.sql
 shows the top objects and associated events
    OBJECT_NAME               CURRENT_OBJ#   COUNT(*) WAIT_NAME
    ------------------------- ------------ ---------- ------------------------------
    CM_HOUR_FACTS                   115393      23515 db file scattered read
    CM_HOUR_FACTS                   115147      21765 db file scattered read
    CM_HOUR_FACTS                   115639      21428 db file scattered read
    CM_HOUR_FACTS                   116131      12331 db file scattered read
    BX_VA_CM_HOUR_CM                115380       7525 enqueue
    BX_VA_CM_HOUR_CM                115629       6932 enqueue
    CM_HOUR_FACTS                   116652       5761 db file scattered read
    BX_VA_CM_HOUR_CM                115110       5578 enqueue
    CM_HOUR_FACTS                   115885       5350 db file scattered read
    CM_HOUR_FACTS                   114847       4613 db file scattered read
    BX_VA_CM_HOUR_CM                116130       4108 enqueue

 topsesl.sql
  top sessions 
      SID NAME           PROGRAM                         CPU   WAIT  TOTAL
    ------ -------------- ---------------------------- ------ ------ ------
        26 SIMULATOR                                     2138   1832   3970
        57 SIMULATOR                                     2644   1576   4220
        35 SIMULATOR                                     2176   2346   4522
        15 SIMULATOR                                     2151   2412   4563
        38 SIMULATOR                                     2038   2533   4571
        64 SIMULATOR                                     3060   1543   4603
        70 SIMULATOR                                     3086   1797   4883
  
 aveactcpu.sql
  shows the average active session load and then graphs
  that where the | represent maximum CPU
  sort of a ascci version of the OEM 10g performance page

     START_TIME    AVEACT        CPU      WAITS GRAPH
     ------------ ------- ---------- ---------- ------------------------------
     JUN 12 11:20    4.00          8          4 ++++|++---
     JUN 12 11:20     .38         46         59     |
     JUN 12 11:36     .06          1         16     |
     JUN 12 11:51     .21         19         32     |
     JUN 12 12:06     .72        115         62 +-  |
     JUN 12 12:20     .66         95         62 +-  |
     JUN 12 12:36     .05          4         10     |
     JUN 12 12:51     .15         14         16     |
     JUN 12 13:06     .95        137         93 +-  |
     JUN 12 13:20     .43         57         57     |
     JUN 12 13:36     .08          1         22     |
     JUN 12 13:51     .16         17         15     |
     JUN 12 14:06     .64         84         76 +-  |
     JUN 12 14:20     .85        107         90 +-  |
     JUN 12 14:36     .13          0         22     |
     JUN 12 15:23     .21          3         47     |
     JUN 12 15:36     .14          2         38     |
     JUN 12 15:51     .19         26         13     |
     JUN 12 16:06     .59         80         54 +   |
     JUN 12 16:20     .95        126        102 +-  |
     JUN 12 16:36     .08          0         20     |
     JUN 12 16:51     .24         29         18     |
     
 sqlstats.sql
 get statistics for a particular SQL statement
     Enter value for hash_value: 418113968
     old  10: where  hash_value=&hash_value
     new  10: where  hash_value=418113968

     TO_CHAR(SAMPLE_ EXECUTIONS ELAPSED_TIME ROWS_PROCESSED
     --------------- ---------- ------------ --------------
     13-JUN-07 12:46
     13-JUN-07 13:00          0            0              0
     13-JUN-07 14:00          1      9520780          75416
     13-JUN-07 15:00          1      9036535          75416
     13-JUN-07 16:00          1     10230288          75416
     13-JUN-07 17:00          1     10933131          75416


Get the explain plan for a particular statement
(you need to get the address which the above queries don't do, yet,
but you can just add "address" in the return values of any of the
above sql related queries)

find the address for the sqlstatement and get the explain plan
SELECT * FROM TABLE(
dbms_xplan.display('V$ASH_SQLPLANS','540940D0')
);

-----------------------------------------------------------------------------------------
| Id  | Operation                         |  Name          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |       |       |            |
|   1 |  SORT ORDER BY                    |                |   939K|   337M| 75891   (1)|
|*  2 |   FILTER                          |                |       |       |            |
|   3 |    SORT GROUP BY                  |                |   939K|   337M| 75891   (1)|
|   4 |     VIEW                          |                |   939K|   337M|            |
|   5 |      UNION-ALL                    |                |       |       |            |
|*  6 |       HASH JOIN                   |                |  6183 |   603K|   309   (0)|
|*  7 |        TABLE ACCESS FULL          | TOPOLOGY_LINK  |  6137 |   113K|    51   (0)|
|*  8 |        HASH JOIN                  |                |  6144 |   486K|   249   (0)|
|*  9 |         TABLE ACCESS FULL         | TOPOLOGY_LINK  |  6137 |   113K|    51   (0)|
|* 10 |         HASH JOIN                 |                |  6140 |   371K|   190   (0)|
|* 11 |          TABLE ACCESS FULL        | TOPOLOGY_LINK  |  6137 |   113K|    51   (0)|
|* 12 |          HASH JOIN                |                |  6137 |   257K|   133   (0)|
|* 13 |           TABLE ACCESS FULL       | TOPOLOGY_LINK  |  6137 |   113K|    51   (0)|
|* 14 |           TABLE ACCESS FULL       | TOPOLOGY_NODE  | 37660 |   882K|    66   (0)|
|* 15 |       TABLE ACCESS FULL           | CM_BYTES       |   132K|  2971K|   370   (0)|
|* 16 |       TABLE ACCESS BY INDEX ROWID | CM_EVENTS      |     1 |     8 |     2  (50)|
|* 17 |        INDEX SKIP SCAN            | PK_CM_EVENTS   |     1 |       |            |
|* 18 |       TABLE ACCESS FULL           | CM_POWER_2     |   168K|  3464K|   467   (0)|
|* 19 |       TABLE ACCESS FULL           | CM_POWER_1     |   170K|  2825K|   396   (0)|
|* 20 |       TABLE ACCESS FULL           | CM_ERRORS      |   168K|  4759K|   538   (0)|
|* 21 |       TABLE ACCESS FULL           | CM_QOS_PROF    |   172K|  3202K|   496   (0)|
|  22 |       INLIST ITERATOR             |                |       |       |            |
|  23 |        TABLE ACCESS BY INDEX ROWID| CM_VA          |   121K|    10M|     2  (50)|
|* 24 |         INDEX RANGE SCAN          | PK_CM_VA       |     1 |       |     2   (0)|
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NVL(SUM("BYTES_UP"),0)+NVL(SUM("BYTES_DOWN"),0)+NVL(SUM("RESET_COUNT"),0)+N
              VL(AVG("TXPOWER_UP"),0)+NVL(AVG("RXPOWER_DOWN"),0)+NVL(AVG("RXPOWER_UP"),0)+NVL(MAX("CER_
              DOWN"),0)+NVL(MAX("CCER_DOWN"),0)+NVL(MIN("SNR_DOWN"),0)+NVL(MAX("NODE_PROFILE_ID"),0)<>0
               AND MAX("CM_ID") IS NOT NULL AND MAX("UP_ID") IS NOT NULL AND MAX("DOWN_ID") IS NOT
              NULL AND MAX("MAC_ID") IS NOT NULL AND MAX("CMTS_ID") IS NOT NULL)
   6 - access("PARENTID"="TOPOLOGYID")
   7 - filter("TOPOLOGYID_NODETYPEID"=128 AND "STATEID"=1)
   8 - access("PARENTID"="TOPOLOGYID")
   9 - filter("TOPOLOGYID_NODETYPEID"=127 AND "STATEID"=1)
  10 - access("PARENTID"="TOPOLOGYID")
  11 - filter("TOPOLOGYID_NODETYPEID"=129 AND "STATEID"=1)
  12 - access("TOPOLOGYID"="TOPOLOGYID")
  13 - filter("TOPOLOGYID_NODETYPEID"=2002 AND "STATEID"=1)
  14 - filter("STATEID"=1)
  15 - filter("SECONDID"=1181696520 OR "SECONDID"=1181696700 OR "SECONDID"=1181697600 OR
              "SECONDID"=1181698500 OR "SECONDID"=1181699400)
  16 - filter(TRUNC("TSTAMP",'fmhh24')=TRUNC(SYSDATE@!-.041666666666666666666666666666666
              6666667,'fmhh24'))
  17 - access("EVENTID"=3)
       filter("EVENTID"=3)
  18 - filter("SECONDID"=1181696520 OR "SECONDID"=1181696700 OR "SECONDID"=1181697600 OR
              "SECONDID"=1181698500 OR "SECONDID"=1181699400)
  19 - filter("SECONDID"=1181696520 OR "SECONDID"=1181696700 OR "SECONDID"=1181697600 OR
              "SECONDID"=1181698500 OR "SECONDID"=1181699400)
  20 - filter("SECONDID"=1181696520 OR "SECONDID"=1181696700 OR "SECONDID"=1181697600 OR
              "SECONDID"=1181698500 OR "SECONDID"=1181699400)
  21 - filter("SECONDID"=1181696520 OR "SECONDID"=1181696700 OR "SECONDID"=1181697600 OR
              "SECONDID"=1181698500 OR "SECONDID"=1181699400)
  24 - access("SECONDID"=1181696520 OR "SECONDID"=1181696700 OR "SECONDID"=1181697600 OR
              "SECONDID"=1181698500 OR "SECONDID"=1181699400)
     
 avewaits.sql
START_TIME      TOT    FPCT FIRST              SPCT SECOND             TPCT THIRD
------------ ------ ------- --------------- ------- --------------- ------- ---------------
JUL 16 16:35      4     .40 log file sync       .20 ON CPU              .20 control file pa
JUL 16 16:40     13    1.00 control file pa
JUL 16 16:50      5     .60 log file sync       .40 ON CPU
JUL 16 16:56      7    1.00 control file pa
JUL 16 17:00     19     .63 direct path wri     .26 ON CPU              .11 SQL*Net more da
JUL 16 17:10    105     .74 ON CPU              .10 db file paralle     .10 log buffer spac
JUL 16 17:19      8     .50 direct path wri     .38 log file sync       .13 db file scatter
JUL 16 17:22      2    1.00 rdbms ipc reply
JUL 16 17:24     61     .67 ON CPU              .16 log buffer spac     .07 db file paralle