S-ASH
Contents
S-ASH
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!
- Download
- How to Install
- hostview.sql - SASH collects data for multiple databases. This sql script will change which database the SASH views filter data for.
- Data mining SASH
- If using ASHMON on this version of SASH you need to run two scripts
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 repository but still having data purging using “poor man’s partitioning”, ie having separate 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.
| 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_waitgroups2.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
actall.sql - detailed view on all targets
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
red |
| 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 | a |
| SESSION_TYPE | TYPE | TYPE | a | 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 | a | |
| SQL_CHILD_NUMBER | SQL_CHILD_NUMBER | a | decode(s.ksusesch, 65535,
to_number(null), s.ksusesch) | |
| MODULE | MODULE | MODULE | a | a |
| ACTION | ACTION | ACTION | a | a |
| CURRENT_OBJ# | ROW_WAIT_OBJ# | a | a | s.ksuseobj |
| CURRENT_FILE# | ROW_WAIT_FILE# | a | a | s.ksusefil |
| CURRENT_BLOCK# | ROW_WAIT_BLOCK# | a | a | s.ksuseblk |
| CLIENT_ID | a | a | a | a |
| BLOCKING_SESSION | BLOCKING_SESSION | a | a | 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 |
See V$SESSION and V$SESSION_WAIT
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