Wait Event Docs‎ > ‎

SASH

ASH is only available in Oracle 10g and higher and also requires the diagnostic package license. If you are are an older version of Oracle or don’t have the diagnostic package license then you have the option of simulating ASH (S-ASH) yourself. Your mileage may vary on the scripts below. No guarantees on them working correctly on your systems. Make sure you test and understand them.

For the scripts below it is highly recommended to put the repository on a different database than the database being monitored. I’ve only tested resource consumption for gathering data (less than 1% of 1 CPU) and not the resource usage of actually storing the data.

——————————————–

V1 Unix install,  uses partitioning and sub-partitioning

I’ve  road tested these to some degree on Solaris and Linux systems and the seem to work alright, but I’m sure there is always more to be fixed and added. For example, I don’t collect RAC instance ids and I want to. This is a single shell script that is run both on the repository database and on the target database. The repository has to be installed on Enterprise Edition because it uses partitioning. If you want to use Standard Edition use Version 2 below. Also this script is a shell script and won't run on Windows not even with Cygwin. For Windows installs use V2. In fact V2 is the only version I've used in the last 2 years!

———————————————

V2 Installs on Windows or Unix on Standard Edition Oracle

The scripts below are newer so there will probably be some hickups in them. A couple of the collection procedures aren't fully implemented but the main ASH collection works well.  Please comment on the blog on any issues and/or solutions you find. I wrote these scripts as both an option for installing on Windows (since the first version was a shell script) and for allowing Standard Edition as a repostitory but still having data purging using “poor man’s partitioning”, ie having separte tables for each day of the week, and using view with union all of the 7 tables in the data mining scripts. I find the following scripts easier to follow, read, understand and change than the single script above.

Repository 

 script run as required description
 repo_0_user.sql  SYSTEM NO creates SASH user 
 You can install SASH but I recommend creating a dedicated SCHEMA because SASH create table   script also creates some views with the same name as DBA_ views and V$ views.  

 repo_1_tables.sql  SASH YES Installs schema on repository database.
 WARNING - don’t run as SYS or SYSTEM because it recreates a couple dba_ views and V$ views
 repo_2_pkg.sql SASH NO optionally create an automatic purge procedure on repository machine
 repo_3_jobs.sql SASH NO optionally start a job on repository machine to purge oldest day of data
 repo_4_waitgroups.sql   SASH depends required or queries that use the WAIT_CLASS field. This script populates WAIT CLASS.
 repo_5_curdb.sql SASH depends(same as hostview.sql on V1)  this query changes the database id that the SASH view v$active_session_session filters for. Because SASH can collect data from multiple databases into the same repository it requires a database ID filter for when running queries on the fake "V$ACTIVE_SESSION_HISTORY". This script will list the databases collected in the SASH repository and prompt you for the one you want the view V$ACTIVE_SESSION_HISTORY to filter for.


Monitored Databases 

The SASH collection is a push system, ie all the targerts push their data into the repository which is more scalable than the repository pulling the data from the targets. The only limit to the number of targets you can monitor will be the power of your repository database.

 script run as required description
 targ_1_pkg.sql  SYS YES  install collection package on each database to be monitored
 targ_2_jobs.sql  SYS YES  start up collection in a job on each database to be monitored


(possible to run as SYSTEM but would have to change script not to use x$ which is very easy to do. The only loss would be getting PLAN_HASH on Oracle 9i. )

Data Mining S-ASH

S-ASH creates a view that look just like V$ACTIVE_SESSION_HISTORY. The reason for this is so that queries written on the real ASH are compatible with SASH and vice versa. The SASH install creates a few other views that look like DBA views but under the SASH schema (therefore don't run the SASH schema creation script as SYSTEM or SYS or you will corrupt several important views). These extra DBA_ views are used in some of the S-ASH data mining scripts.

For ASH compatible queries to run on SASH two things are required:

  1. create wait groups: repo_4_waitgroups.sql
  2. set the database ID to filter for: repo_5_curdb.sql

S-ASH queries

One of the coolest things that differentiates SASH from ASH besides the fact that it is free is that you can run ASH type reports across all the targets you collect SASH from from the central repository:

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
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                         

TM                 NPTS  AVEACT GRAPH                   CPU WAITS
---------------- ------ ------- ---------------------- ---- -----
06-AUG  13:00:00    270     .33 +-        2              29    59
06-AUG  14:00:00   1040    2.24 ++--------2---          341  1984
06-AUG  15:00:00    623    6.67 ++++------2----------   438  3718
06-AUG  16:00:00   1088    2.59 ++--------2----         335  2486
06-AUG  17:00:00   1104    1.26 ++-----   2             349  1043
06-AUG  18:00:00   1093    1.38 +++----   2             663   842
06-AUG  19:00:00   1012    1.74 ++------- 2             373  1388
06-AUG  20:00:00   1131     .99 +----     2             304   820
06-AUG  21:00:00   1111    1.22 ++-----   2             344  1012
06-AUG  22:00:00   1010    1.66 ++------  2             414  1259
06-AUG  23:00:00   1120    1.08 +----     2             298   913
07-AUG  00:00:00   1024     .83 +---      2             273   576
07-AUG  01:00:00   1006    1.74 ++------- 2             319  1428
07-AUG  02:00:00   1090    2.47 ++--------2----         347  2345
07-AUG  03:00:00    687    6.59 +++-------2----------   382  4142
07-AUG  04:00:00   1004    1.95 ++++++--- 2            1299   659
07-AUG  05:00:00   1104    3.08 +++++-----2------      1170  2226
07-AUG  06:00:00   1122    1.91 +++++++-- 2            1582   558
07-AUG  07:00:00   1115    1.06 +++---    2             559   618
07-AUG  08:00:00   1140     .81 ++--      2             403   519
07-AUG  09:00:00   1128     .88 ++---     2             386   601

   "+" - 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 (AAS)
aveactn.sql
   TO_CHAR(STA SAMPLES FAAS FIRST           SAAS 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
   AAS - Active Session Load
   FASL - first (top) event measuerd in AAS
   SASL - second most event measuerd in AAS
SQL_ID         PLAN_HASH TYPE              CPU       WAIT         IO      TOTAL
------------- ---------- ---------- ---------- ---------- ---------- ----------
4gd6b1r53yt88          0 UNKNOWN             7          6          1         14
7dstgpw2mkg59 1599603377 INSERT              3          0          0          3
0hpqdzxxgscxn 1968181591 INSERT              1          2          0          3
g4y6nw3tts7cc          0 PL/SQL EXE          1          0          0          1

STATUS         SID NAME         PROGRAM                     CPU    WAITING    IO  TOTAL
------------ ----- ------------ ------------------------- ----- ---------- ----- ------
CONNECTED      165 SYS          ORACLE.EXE (CKPT)           232        173     0    405
DISCONNECTED   158 SYS          ORACLE.EXE (J003)            43          6   303    352
DISCONNECTED   141 SYS          ORACLE.EXE (J002)            13          3   333    349
CONNECTED      162 SYS          ORACLE.EXE (CJQ0)           149         14     2    165
CONNECTED      167 SYS          ORACLE.EXE (DBW0)            26        116     0    142
CONNECTED      166 SYS          ORACLE.EXE (LGWR)            46         94     0    140
CONNECTED      161 SYS          ORACLE.EXE (MMON)            34         13    16     63
CONNECTED      170 SYS          ORACLE.EXE (PMON)            59          0     0     59
DISCONNECTED   147 SYS          ORACLE.EXE (m000)             0         24    12     36

Appendix


v$active_session_history and v$session share many of the same fields.
The fields in red or the same value, just a different field name

v$active_session_history v$session
SESSION_ID               SID                                            
SESSION_SERIAL#          SERIAL#                                        
USER_ID                  USER#                                          
SQL_OPCODE               COMMAND                                        
PROGRAM                  PROGRAM                                        
SESSION_TYPE             TYPE                                           
SQL_ID                   SQL_ID                          
SQL_CHILD_NUMBER         SQL_CHILD_NUMBER                               
MODULE                   MODULE                                         
ACTION                   ACTION                                         
CURRENT_OBJ#             ROW_WAIT_OBJ#                                  
CURRENT_FILE#            ROW_WAIT_FILE#                                 
CURRENT_BLOCK#           ROW_WAIT_BLOCK#                                
CLIENT_ID                CLIENT_IDENTIFIER                              
BLOCKING_SESSION         BLOCKING_SESSION                        
BLOCKING_SESSION_STATUS  BLOCKING_SESSION_STATUS                              
SEQ#                     SEQ#                                           
EVENT#                   EVENT#                                         
EVENT                    EVENT                                          
P1TEXT                   P1TEXT                                         
P1                       P1                                             
P2TEXT                   P2TEXT                                         
P2                       P2                                             
P3TEXT                   P3TEXT                                         
P3                       P3                                             
WAIT_CLASS_ID            WAIT_CLASS_ID                                  
WAIT_CLASS               WAIT_CLASS                                     
WAIT_TIME                WAIT_TIME          
                           

Chart under construction:

 v$active_session_history     v$session    v$session  (pre 10g)  v$session_waits    x$ksuse  s
 x$ksled   e
 SESSION_ID  SID    SID   SID  s.indx 
 SESSION_SERIAL#   SERIAL#   SERIAL#  a s.ksuseser 
 USER_ID    USER#    USER#  a s.ksuudlui 
 SQL_OPCODE   COMMAND    COMMAND  a a
 PROGRAM PROGRAM  PROGRAM a 
 SESSION_TYPE  TYPE    TYPE  a 
  SQL_ADDRESS  SQL_ADDRESS  s.ksusesql 
  SQL_HASH_VALUE  SQL_HASH_VALUE  s.ksusesqh  
  SQL_PLAN_HASH_VALUE   SQL_PLAN_HASH_VALUE  s.ksusesph  
 SQL_ID SQL_ID    a 
 SQL_CHILD_NUMBER SQL_CHILD_NUMBER

decode(s.ksusesch, 65535, to_number(null), s.ksusesch) 
 MODULE MODULE MODULE a 
 ACTION ACTION ACTION a 
 CURRENT_OBJ# ROW_WAIT_OBJ#  s.ksuseobj  
 CURRENT_FILE# ROW_WAIT_FILE# s.ksusefil 
 CURRENT_BLOCK# ROW_WAIT_BLOCK#  s.ksuseblk 
 CLIENT_ID   a  a 
 BLOCKING_SESSION  BLOCKING_SESSION a 
 BLOCKING_SESSION_STATUS  BLOCKING_SESSION_STATUS   
 SEQ#     SEQ#    s.ksuseseq
 EVENT# EVENT#    s.ksuseopc
 EVENT  EVENT   EVENT  e.kslednam
 P1 P1  P1 s.ksusep1
 P1TEXT P1TEXT  P1TEXT e.ksledp1
 P2  P2   P2 s.ksusep2 
 P2TEXT P2TEXT  P2TEXT e.ksledp2
 P3 P3  P3 s.ksusep3 
 P3TEXT P3TEXT  P3TEXT e.ksledp3
 WAIT_CLASS_ID  WAIT_CLASS_ID    
 WAIT_CLASS  WAIT_CLASS    
 WAIT_TIME WAIT_TIME   s.ksusetim 
  PROGRAM   s.ksusepnm 
  MODULE_HASH   s.ksuseaph  
  ACTION_HASH   s.ksuseach 
  FIXED_TABLE_SEQUENCE   s.ksusefix
  TIME_WAITED   s.ksusewtm 


session_state = decode(WAIT_TIME, 0,'WAITING', 'ON CPU')
v$session where

  ON CPU : status='ACTIVE'   and  wait_time > 0 

     or

  Waiting   : wait_class != 'Idle‘  


select       sysdate sample_time,
                 decode(s.WAIT_TIME, 0,'WAITING','ON CPU')  "SESSION_STATE“
                 /* plus other fields */
       from   v$session 
       where sid != ( select distinct sid from v$mystat  where rownum < 2 )
           and ( ( s.status='ACTIVE' and wait_time > 0   ) 
                        or s.wait_class!='Idle' ) ;

            NOTE: 9i – join v$session and v$session_wait

V$SESSION missing these fields

Field                    query to get value joining to v$session
FORCE_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE 
                         v$sql.sql_id = sql_id
QC_SESSION_ID            QCSID   
                         v$px_session.sid=sid , serial#
QC_INSTANCE_ID           QCINST_ID 
                         v$px_session.sid=sid , serial#
SQL_PLAN_HASH_VALUE      PLAN_HASH_VALUE 
                         v$sql.sql_id = sql_id and 
                         v$sql.child_number=sql_child_number
                         also 
                         (PLAN_HASH_VALUE = x$ksuse.KSUSESPH , 
                          ie select from x$ksuse instead of v$session)
XID                      XID 
                         v$transaction.ADDR  = taddr 

These fields aren't in ASH but are easy to collect in SASH
FIXED_TABLE_SEQUENCE     - find sql executions                      
ROW_WAIT_ROW# 
LAST_CALL_ET             - how long in current state          
   
The following fields have been added to ASH since 10.1. The fields with "*" are available in v$session. The other fields are not, and for the time being I'm not sure how to collect them or if it's possible. The first place to start, would be to look at x$ksuse and see if these fields are there. 

10.2
 *   BLOCKING_SESSION
 *   BLOCKING_SESSION_STATUS
 *   BLOCKING_SESSION_SERIAL#

10.2.0.3 
 *   PLSQL_ENTRY_OBJECT_ID
 *   PLSQL_ENTRY_SUBPROGRAM_ID
 *   PLSQL_OBJECT_ID
 *   PLSQL_SUBPROGRAM_ID

11.1
 *   TOP_LEVEL_SQL_ID
     TOP_LEVEL_SQL_OPCODE
     SQL_PLAN_LINE_ID
     SQL_PLAN_OPERATION
     SQL_PLAN_OPTIONS
 *   SQL_EXEC_ID
 *   SQL_EXEC_START
     IN_CONNECTION_MGMT
     IN_PARSE
     IN_HARD_PARSE
     IN_SQL_EXECUTION
     IN_PLSQL_EXECUTION
     IN_PLSQL_RPC
     IN_PLSQL_COMPILATION
     IN_JAVA_EXECUTION
     IN_BIND
     IN_CURSOR_CLOSE
11.2
     TM_DELTA_TIME
     TM_DELTA_CPU_TIME
     TM_DELTA_DB_TIME
     DELTA_TIME
     DELTA_READ_IO_REQUESTS
     DELTA_WRITE_IO_REQUESTS
     DELTA_READ_IO_BYTES
     DELTA_WRITE_IO_BYTES
     DELTA_INTERCONNECT_IO_BYTES
     PGA_ALLOCATED
     TEMP_SPACE_ALLOCATE





Subpages (1): Oracle: V$SESSION
Comments