BASH

BASH – It’s ASH for the rest of us

If you, like me, work with Standard Edition, Standard Edition One or don’t have a license for Oracle’s Diagnostic Pack, you probably miss Oracle’s ASH (Active Session History) badly. While Statspack, in my opinion, still can fill most of the gap the unavailable AWR leaves on SE/SE1, at least I miss ASH very much.

Like lots of DBAs and consultants, I am a huge fan of Tanel Poder’s snapper script (which has an ASH mode) and I used it almost on a daily basis. However, one of its main feature is that nothing has to be installed, which on the other hand means that there is no historic information available. You have to know when it makes sense to start snapper.

BASH on the other hands replicates what ASH does. It samples V$SESSION every second, makes the samples available through the view BASH$ACTIVE_SESSION_HISTORY and persists the samples from every 10 seconds to BASH$HIST_ACTIVE_SESS_HISTORY, so it is meant a drop-in replacement for ASH.

You can download the current version 4 of the BASH installation script here: bashcreate_v4.zip

If you want to update from a previous version, update scripts for each new version are included in the archive above.

BASH needs Oracle 10g or 11g, a positive job_queue_processes parameter, but no Diagnostic Pack License.

EDIT: I learned that Kyle Hailey wrote S-ASH, which is similiar to BASH, a few years ago already. You might want to look into it: http://dboptimizer.com/ash-masters-2/s-ash/

Here is the header from the bashcreate.sql script with further information about BASH:

------------------------------------------------------------------------------------
--
-- Name:          bashcreate.sql - BASH Installation script for Oracle 10.2 to 11.2
--
-- Author:        Marcus Monnig
-- Copyright:     (c) 2012, 2013 Marcus Monnig - All rights reserved.
--
-- Check http://marcusmonnig.wordpress.com/bash/ for new versions.
--
-- Disclaimer:    No guarantees. Use at your own risk. 
--
-- Changelog:     v1: 2012-12-28 First public release 
--                v2: 2013-01-15 Added not null constraints to tables	 	
--                v3: 2013-02-07 Rewrote the install script so that it dynamically checks for 
--                               columns available in V$SESSION and generates an appropiate view. 
--                               This should make it compatible at least to all Oracle 
--                               versions >= 10.2.0.1.
--
--                v4: 2013-02-08 Fixed a problem on <10.2.0.5 with more than 255 arguments in case statement
--
-- Purpose:       It's ASH for the rest of us (no EE or no diagnostic pack license).
--
-- Requirements:  * Oracle 10.2 to 11.2 (tested with 10.2 and 11.2) 
--                * SE1, SE or EE - Diagnostic Pack NOT needed
--                * Parameter job_queue_processes > 0 
--                   (since the bash data collector permanently runs as a scheduler 
--                    job, you might want to consider raising the job_queue_processes 
--                    parameter by one)
--
-- Installation:  1.) Create a new tablespace for the BASH schema (optional, but recommended).
--                2.) Run: sqlplus sys/@ as sysdba @bashcreate.sql
--                3.) When asked, enter the password for the BASH user to be created and the 
--                    names for the permanent and temporary tablespace for the BASH user.
--                4.) When asked, enter "N" if you don't want to start the data 
--                    collector job right away. 
--
-- Uninstall:     1.) Execute bash.bash.stop;
--                2.) drop user bash cascade;
--
--
-- Usage:         *** CONTROLLING THE DATA COLLECTION *** 
--
--                The package BASH.BASH has the following procedures that let you
--                control the data gathering:
--
--                procedure run;
--                    Creates and start the bash data collector scheduler job.
--
--                procedure stop;
--                    Stops the bash data collector scheduler job.
--
--                procedure purge (days_to_keep NUMBER);
--                    Purges the data in BASH$HIST_ACTIVE_SESS_HISTORY
--
--                procedure runner;  
--                    Blocking procedure that collects the bash data. Might be
--                    usefull e.g. when scheduler jobs are not available and the
--                    data collector can not be run from a job session.
--
--
--                *** SETTINGS ***
--
--                The table BASH.BASH$SETTINGS has the following columns that let
--                you control how the BASH data is gathered:
--
--                 sample_every_n_centiseconds NUMBER (Default: 100 = 1 second)
--                     Number of centiseconds V$SESSION is sampled
--
--                 max_entries_kept NUMBER (Default: 30000)
--                     How many entries are kept in BASH$ACTIVE_SESSION_HISTORY
--
--                 cleanup_every_n_samples NUMBER (Default: 100)
--                     How often the data in BASH$ACTIVE_SESSION_HISTORY is purged  
--
--                 persist_every_n_samples NUMBER (Default: 10 )
--                     How many of the samples are persisted to BASH$HIST_ACTIVE_SESS_HISTORY
--
--                 logging_enabled NUMBER (Default: 0)
--                     If logging to BASH$LOG is enabled 
--
--                 keep_log_entries_n_days NUMBER (Default: 1)
--                     How many days log entries in BASH$LOG are kept
--
--                 updated NUMBER 
--                     An internally used column that tracks changes in the settings table
--                     through a trigger
--
--                 version NUMBER 
--                     The version number of BASH. Might be used with future update scripts.
--                     Do not change.
--
--                If you change a setting in the BASH.BASH$SETTINGS table and commit,
--                the updated setting will be used by the data collector the next time
--                it persists data to DBA_HIST_ACTIVE_SESS_HISTORY (default: every 10 seconds) 
--
--                The default values for sample_every_n_centiseconds and 
--                persist_every_n_samples replicate the ASH behaviour. 
--                
--
--                *** QUERYING THE COLLECTED BASH DATA *** 
--
--                BASH$ACTIVE_SESSION_HISTORY
--                  Replaces V$ACTIVE_SESSION_HISTORY (1-second samples)
--
--                BASH$HIST_ACTIVE_SESS_HISTORY
--                  Replaces DBA_HIST_ACTIVE_SESS_HISTORY (10-second samples)
--
--                BASH$LOG
--                  Logging table (logging is off by default)
--
--
--                If want to use scripts or tools (e.g. "Mumbai" or "ASH Viewer") that 
--                select from V$ACTIVE_SESSION_HISTORY or DBA_HIST_ACTIVE_SESS_HISTORY,
--                you might want to replace the following public synonyms with synonyms 
--                pointing to BASH$ACTIVE_SESSION_HISTORY and 
--                BASH$HIST_ACTIVE_SESS_HISTORY:
--                
--                  CREATE OR REPLACE PUBLIC SYNONYM "V$ACTIVE_SESSION_HISTORY" 
--                      FOR BASH$ACTIVE_SESSION_HISTORY;
--
--                  CREATE OR REPLACE PUBLIC SYNONYM "DBA_HIST_ACTIVE_SESS_HISTORY" 
--                      FOR BASH$HIST_ACTIVE_SESS_HISTORY;
--                
--                Note that these synonyms will not work for user SYS, so selecting from 
--                V$ACTIVE_SESSION_HISTORY as user sys will still return the Oracle ASH data,
--                not BASH data.
--                
--                Also note that you are still not allowed to use Oracle Enterprise Manager,
--                Oracle Database Console or the Oracle supplied ASH scripts in RDBMS/ADMIN
--                against BASH data without a valid Diagnostic Pack license.
--
--
--                *** CLEANUP AND PURGING *** 
--
--                The data collected for BASH$ACTIVE_SESSION_HISTORY is automatically purged,
--                based on the max_entries_kept setting.
--
--                The data collected for BASH$HIST_ACTIVE_SESS_HISTORY is not purged 
--                automatically. You need to execute BASH.BASH.PURGE() to purge
--                the data. You might want to create a scheduler job to do this on a
--                regular basis.
--
-- Background:    *** Performance impact of the BASH data collector ***
--
--                Oracle's own ASH uses a circular buffer in the SGA, which is something
--                a user process like the BASH data collector can not. After trying a few 
--                setups (global temporary tables, communications though DBMS_PIPE, etc.), I
--                decided to implement BASH as simple as possible using standard heap tables.
--                (The buffer cache is probably the closest thing to a separate memory area 
--                that can be used from a user session.)
--
--                I tested BASH on ten productive databases with quite different loads, both
--                on the OLTP and OLAP side. Since the load from BASH is not recorded by BASH 
--                (when sampling the sampler has to be ignored) I used Tanel Poder's snapper 
--                and latchprof scripts to check for load and excessive latch gets by the bash
--                data collector. The load was usually 0,01 AAS (usually on CPU), on some 
--                database with a large number of active session it sometimes was 0,02 AAS. 
--                The latchprof script showed only very low numbers of latch gets from the 
--                bash data collector.
--                
--                While the ASH setup with a circular buffer in the SGA and its latch-free 
--                access is definetly the superior architecture, I can not see any serious
--                side-effects with the down-to-earth BASH architecture.
--                
--                If you worry about the additonal 1-2% AAS load, you probably need BASH 
--                badly, to fix a few performance problems...  
--                
--                
--                *** Columns in BASH$ACTIVE_SESSION_HISTORY ***
--                
--                For compatibilty reasons with 3rd party tools that select from 
--                V$ACTIVE_SESSION_HISTORY (but actually BASH$ACTIVE_SESSION_HISTORY if
--                you decide to replace the V$ACTIVE_SESSION_HISTORY public synonym), I made
--                all columns from V$ACTIVE_SESSION_HISTORY available in 
--                BASH$ACTIVE_SESSION_HISTORY, however some columns are not really filled
--                with data and always NULL: qc_session_id, qc_instance_id
--                and blocking_session_serial# from the 10.2 version of 
--                V$ACTIVE_SESSION_HISTORY and a whole series of columns from the 11.2
--                version of V$ACTIVE_SESSION_HISTORY (see comments in PL/SQL code).
--                
--                On the other hand, there are three columns in BASH$ACTIVE_SESSION_HISTORY
--                orginating from V$SESSION that are not available in V$ACTIVE_SESSION_HISTORY, 
--                because I think they are useful: OSUSER, TERMINAL, USERNAME
--                
------------------------------------------------------------------------------------