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 8 of the BASH installation script here:

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:

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 12.1
-- Author:        Marcus Monnig
-- Copyright:     (c) 2012, 2013 Marcus Monnig - All rights reserved.
-- Check 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 >=
--                v4: 2013-02-08 Fixed a problem on < with more than 255 arguments in case statement
--                v5: 2013-06-15 Added a nightly purge job for historic data (defaults to 93 "days to keep")
--                                 (if you already created one yourself and use the update_v4tov5.sql update
--                                  script you'll end up with two)
--                               Changed the TERMINAL column from VARCHAR2(16) to VARCHAR2(32)
--                v5: 2013-06-27 Made sure BASH works on Oracle 12c
--                               BASH is now compatible with RAC on Oracle 11.1 and higher. Each instance 
--                                 runs its own collector through a separate scheduler job.
--                                 Scheduler jobs are created and deleted when starting and stopping the 
--                                 collector through EXEC BASH.BASH.RUN;. The collector detects new 
--                                 instances when running and creates collector jobs for them.
--                                 The following public synonyms to select the collected data now exist:
--                                 BASH$ACTIVE_SESSION_HISTORY        ASH data from the current instance
--                                 BASH$HIST_ACTIVE_SESS_HISTORY      Historic ASH data from the current instance
--                                 BASHG$ACTIVE_SESSION_HISTORY       ASH data from all instances
--                                 BASHG$HIST_ACTIVE_SESS_HISTORY     Historic ASH data from all instancess
--                v6: 2013-06-27 Made sure BASH works on Oracle 12c
--                               BASH is now compatible with RAC on Oracle 11.1 and higher. Each instance 
--                                 runs its own collector through a separate scheduler job.
--                                 Scheduler jobs are created and deleted when starting and stopping the 
--                                 collector through EXEC BASH.BASH.RUN;. The collector detects new 
--                                 instances when running and create collector jobs for them.
--                                 The following public synonyms to select the collected data now exist:
--                                 BASH$ACTIVE_SESSION_HISTORY        ASH data from the current instance
--                                 BASH$HIST_ACTIVE_SESS_HISTORY      Historic ASH data from the current instance
--                                 BASHG$ACTIVE_SESSION_HISTORY       ASH data from all instances
--                                 BASHG$HIST_ACTIVE_SESS_HISTORY     Historic ASH data from all instancess
--                v7: 2013-07-13 Fixed a UTC-conversion bug around midnight, resulting in too many entries in 
--                                BASH$HIST_ACTIVE_SESS_HISTORY (Thanks to Robert Ortel)
--                               Fixed a bug leading to duplicate rows in BASH$HIST_ACTIVE_SESS_HISTORY after
--                                10 seconds with no active sessions sampled (Thanks to Robert Ortel)
--                v8: 2013-08-15 Fixed another UTC-conversion resulting in no entries in BASH$HIST_ACTIVE_SESS_HISTORY
--                               Added missing trigger on BASH.BASH$SETTINGS
--                               Renamed INST_ID to INSTANCE_NUMBER in views accessed through public synonyms
--                               Fixed a bug causing no data flushed to BASH$HIST_ACTIVE_SESS_HISTORY
-- Purpose:       It's ASH for the rest of us (no EE or no diagnostic pack license).
-- Requirements:  * Single instance Oracle 10.2 to 12.1 database or RAC database Oracle 11.1 or higher
--                * 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)
--                * RAC databases are only supported for Oracle version 11.2 or higher
-- Installation:  1.) Create a new tablespace for the BASH schema (optional, but recommended).
--                2.) Run: sqlplus sys/<sys_password>@<TNS_ALIAS> 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:     sqlplus sys/<sys_password>@<TNS_ALIAS> as sysdba @bashdrop.sql
-- 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. Called by the 
--                    data collector scheduler job, but might be usefull to call manually 
--                    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.
--                 checkfnewinst_every_n_samples NUMBER (Default: 60)
--                     How often the collector checks for new instances in a clustered database
--                     to create a collector job for the new instance.
--                 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.
--                 hist_days_to_keep NUMBER 
--                     The number of days for that historic data is kept in BASH$HIST_ACTIVE_SESS_HISTORY
--                     when the BASH.BASH.PURGE is called wthout arguments. This setting is also used by
--                     the purge job that is installed with BASH and runs every night.
--                 updated_ts TIMESTAMP 
--                     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 *** 
--                  ASH data from the current instance. Replaces V$ACTIVE_SESSION_HISTORY (1-second samples)
--                  Historic ASH data from the current instance. Replaces DBA_HIST_ACTIVE_SESS_HISTORY (10-second samples)
--                  ASH data from all instances. ASH data from the current instance. Replaces V$ACTIVE_SESSION_HISTORY 
--                  (1-second samples)
--                  Historic ASH data from all instancess. Historic ASH data from the current instance. 
--                  Replaces DBA_HIST_ACTIVE_SESS_HISTORY (10-second samples)
--                BASH$LOG
--                  Logging table (logging is off by default)
--                                 BASH$ACTIVE_SESSION_HISTORY        ASH data from the current instance
--                                 BASH$HIST_ACTIVE_SESS_HISTORY      Historic ASH data from the current instance
--                                 BASHG$ACTIVE_SESSION_HISTORY       ASH data from all instances
--                                 BASHG$HIST_ACTIVE_SESS_HISTORY     Historic ASH data from all instancess
--                Compatibilty with 3rd party products:
--                If want to use scripts or tools (e.g. "Mumbai" or "ASH Viewer") that 
--                you might want to replace the following default Oracle public synonyms with 
--                synonyms pointing to BASH$ACTIVE_SESSION_HISTORY and 
--                      FOR BASH$ACTIVE_SESSION_HISTORY;
--                      FOR BASHG$ACTIVE_SESSION_HISTORY;
--                      FOR BASHG$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 purged by a nightly
--                scheduler job based on the settings in the column HIST_DAYS_TO_KEEP in the
--                table BASH.BASH$SETTINGS.
-- 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 
--                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

32 Responses to BASH

  1. JMA says:

    Hi Marcus,
    I have a problem when compiling the bash.bash package body :
    SQL> show errors
    78/3 PL/SQL: SQL Statement ignored
    86/4 PL/SQL: ORA-00939: nombre d’arguments excessif pour la fonction
    It happens during the installation :

    … Installing packages
    Avertissement : Corps de package créé avec erreurs de compilation.
    … Installing public synonyms
    Would you like to start the BASH data collector? Enter N if you don’t want to start it now.
    Entrez une valeur pour start_bash_collector : N
    Starting BASH collector: No
    *** Successfully installed BASH. ****

    Where is the problem ?
    Thanks in advance

    Jean-michel ALZINGRE, Nemours, FRANCE

    • Which version of Oracle are you using?

      Also, if you have Mumbai or another tool installed that shows PL/SQL code, could you open the BASH.BASH package in it and check which function it is reporting “ORA-00939: too many arguments for function” for? Thanks!

      • JMA says:

        Hi Marcus,

        I am using a version.
        After a “select line,text from user_source where name=’BASH’ order by line”, I get the following lines :

        71 PROCEDURE collector
        72 IS
        73 l_sample_id NUMBER;
        74 l_sample_time TIMESTAMP(3);
        75 BEGIN
        76 select bash_seq.nextval into l_sample_id from dual;
        77 l_sample_time:=systimestamp;
        78 INSERT
        79 INTO bash.bash$session_INTERNAL
        80 (
        81 SAMPLE_ID,
        82 SAMPLE_TIME,
        83 INST_ID,
        84 SID,
        85 SERIAL#,
        86 USER#


      • Hi Jean-Michel,

        I only tested this on and Most likely there is a column missing in V$SESSION in that is available in

        Could you run

        SELECT * FROM gv$session s,
        V$TRANSACTION t,
        V$SQL sq,
        V$ACTIVE_SERVICES serv,
        v$event_name en
        WHERE ((s.status ='ACTIVE'
        AND s.state != 'WAITING')
        OR (s.status = 'ACTIVE'
        AND s.state = 'WAITING'
        AND s.wait_class != 'Idle'))
        AND t.ses_addr(+) = s.saddr
        AND sq.sql_id(+) =s.sql_id
        AND sq.child_number(+)=s.sql_child_number
        AND =s.service_name
        AND en.EVENT#(+) =s.EVENT#
        and 1=2;

        on your DB (which returns no rows) and let me know which columns you get.


  2. JMA says:

    Hi Marcus,
    I have a “No rows selected” message


  3. JMA says:

    Hi Marcus,
    I am going to check what is going wrong because I dropped the bash user then installed the v3 script and got the following ORAs while BASH installs the views.
    … Instaling views
    ERROR at line 1 :
    “ORA-00939 : too many arguments for the function.”
    ORA-06512: line 473


    • Hi Jean-michel,

      I found the problem. It seems that version < allow less arguments in CASE statements.

      Above is version 4, which I now tested on .

      Regards and thanks!

  4. JMA says:

    Thanks a lot Marcus.
    I will download it asap !

  5. Jean-michel says:

    Hi Marcus,
    BASH v4 is now installed successfully.
    I did not choose to activate the bash snapshots.
    In MUMBAI, why is “ASH” mentioned beside AWR and not BASH ?
    Do I have to activate the snapshots to see BASH mention in the GUI ?
    Thanks a lot

    • In Mumbai click the ASH button and in the next dialog you will be asked if you want to fetch data from Oracle’s ASH (V$ACTIVE_SESSION_HISTORY) or from BASH (BASH$ACTIVE_SESSION_HISTORY).

      You have to activate the BASH collector that does the actual data gathering from V$SESSION. To so so, run:


      After starting the collector, wait 2 to 5 minutes before you look at the data in Mumbai.

  6. Jean-michel says:

    Hi Marcus,
    Perfect !
    The GUI is really impressive with so many options.
    Thanks for your help

  7. Jari Hämäläinen says:


    Thanks for the great script! This has already been found very usefull :)

    If I can make some requests for the next release?
    1. DBID for the views. Lot’s of ASH script use this.
    2. sql_text from v$sql_area or similar would be great. If the SQL is already removed from shared pool it is bit difficult to find info about it.
    3. old_hash_value to be more helpful with statspack
    4. Compatibility with XPLAN_ASH (


  8. Max says:


    Thanks for the script.
    I have some performance issues on 11.2 SE RAC (two nodes). One BASH insert statement constantly takes about 90% of total database activity as reported by both ASH Viewer and Mumbai ASH. Related OS processes(PZ) keep one CPU active at all times. I tried increasing sample_every_n_centiseconds parameter from 100 to 300. It slightly reduced the load but it didn’t solve the problem.


    SELECT …

    Wait event in v$session shows this message:
    PX Deq: Execution Msg sleeptime/senderid
    PX Deq: Parse Reply sleeptime/senderid

    Stand alone instance (non RAC) does not show this issue.


    • Hi Max,

      I don’t have a RAC database to test it with but looking at the code again, I have to admit that BASH currently doesn’t work for RAC databases. Even without the performance problem that you see, I am joining GV$SESSION with other V$ (not GV$) views.

      I will definitely make BASH RAC compatible, but don’t know when I have the time.


  9. Max says:

    Hi Marcus,

    Thank you very much for your help. Keep up the good work.


  10. Jari Hämäläinen says:


    I found a issue with the table BASH.BASH$SESSION_INTERNAL column TERMINAL.
    In v$session it is VARCHAR2(30)
    And in BASH table it is VARCHAR2(16)

    I was errors in alert.log:
    ORA-12899: value too large for column “BASH”.”BASH$SESSION_INTERNAL”.”TERMINAL” (actual: 22, maximum: 16).

    I changed both internal tables to varchar2(30) and now it seems to work.


    • For the and databases that I have access to, it’s actually VARCHAR2(16). Which database version are you using?

      It’s actually in the Oracle docs as VARCHAR2(30), so I’ll change it for the next version.

  11. Robert says:

    Hi Marcus,

    I have installed BASH into 5 Standard Edition One databases. However, I found that the 512 MB of tablespace for BASH have been consumed quite quickly even though there is very little load on those databases (AAS is typical <0,2).

    After a little searching I found that the historic ASH data is kind of flooded with entries each night from 0 AM until 2 AM:

    select sample_time, count(*) from BASH$HIST_ACTIVE_SESS_HISTORY group by sample_time order by sample_time;
    07.07.13 23:54:30,787 1
    07.07.13 23:56:40,809 1
    08.07.13 00:00:00,839 3958
    08.07.13 00:03:30,872 3958
    08.07.13 00:14:00,974 7916
    08.07.13 00:17:20,007 3958
    08.07.13 00:49:50,318 7916
    08.07.13 00:54:50,361 3958
    08.07.13 01:15:20,560 3958
    08.07.13 01:22:40,625 3958
    08.07.13 02:03:20,013 1
    08.07.13 02:06:50,047 2
    – and from another database
    06.07.13 23:04:00,013 1
    06.07.13 23:33:40,242 1
    06.07.13 23:44:40,326 1
    07.07.13 00:00:20,446 7920
    07.07.13 00:00:30,449 7920
    07.07.13 00:00:40,454 7920
    07.07.13 00:01:10,452 7920
    07.07.13 00:01:20,455 7920
    07.07.13 00:01:30,459 7920
    07.07.13 00:02:30,465 7920
    07.07.13 00:02:40,468 7920
    07.07.13 00:03:00,468 7920
    07.07.13 00:03:10,467 7920
    07.07.13 00:03:30,469 7920
    07.07.13 00:03:40,470 7920
    07.07.13 00:04:40,481 7920
    07.07.13 00:05:00,482 7920
    07.07.13 00:05:20,487 7920
    07.07.13 00:06:40,502 7920
    07.07.13 00:07:20,504 7920
    07.07.13 00:12:50,540 7920
    07.07.13 00:13:00,551 23760
    07.07.13 00:16:00,571 7920
    07.07.13 00:54:10,867 7920
    07.07.13 01:00:00,904 7920
    07.07.13 01:02:00,924 7920
    07.07.13 01:41:50,230 7921
    07.07.13 02:00:00,369 1
    07.07.13 02:13:00,469 2
    07.07.13 02:13:50,483 2
    07.07.13 02:14:00,478 2

    You have any idea whats causing this? Those massive rows are the very same row again and again, but different in each timeslot.


    • Looks like a bug. What kind of entries are collected during these times?

      Can you email some sample data to (you need to delete all x characters from this email address)?

      You could run this query in Mumbai and right click on the data to Export to Excel:

      select * from BASH$HIST_ACTIVE_SESS_HISTORY where sample_time=’08.07.13 00:54:50,361′ order by sample_time;

  12. Robert says:

    Hi Marcus,

    looks like BASH is not fully compatible with ASH, as BASH$HIST_ACTIVE_SESS_HISTORY has a column INSTANCE_ID while in the original view DBA_HIST_ACTIVE_SESS_HISTORY the same column is named INSTANCE_NUMBER (in Is there any reason for this difference?


  13. Robert says:

    The same is with BASH$ACTIVE_SESSION_HISTORY.INST_ID and V$ACTIVE_SESSION_HISTORY as well as the cluster views (GV$ACT… and BASHG$ACT).

    • Well, V$ACTIVE_SESSION_HISTORY doesn’t have an INSTANCE_NUMBER column, so there’s no mismatch, but just an additional column in BASH$ACTIVE_SESSION_HISTORY, but I’ll rename all columns to INSTANCE_NUMBER anyway.

      • Robert says:

        Well … right. Just thinking before writing ;-).

        Another issue of your version 7. See this:


        —————- —————–
        30000 10

        SQL> select count(*) from BASH$HIST_ACTIVE_SESS_HISTORY ;


        SQL> select count(*) from BASH$ACTIVE_SESSION_HISTORY;



        ————————————————————————— —————————————————————————
        24.07.13 05:20:30,231 25.07.13 15:54:42,204


        ==> It is set to keep ash for 10 days, but no flushing to BASH$HIST_ACTIVE_SESS_HISTORY takes place.

        I have this issue since your last V7 update. If you need the logs, just post here or mail me.


      • What does

        SELECT min(sample_id),max(sample_id) FROM bash.BASH$SESSION_INTERNAL;


        select count(*)
        FROM bash.bash$session_internal
        WHERE sample_id > bash.GET_TS_ID;


  14. Robert says:

    SQL> SELECT min(sample_id),max(sample_id) FROM bash.BASH$SESSION_INTERNAL;

    ————– ————–
    430030784 430391308

    SQL> select count(*) FROM bash.bash$session_internal WHERE sample_id > bash.GET_TS_ID;




  15. Robert says:

    Hi Marcus,

    looks like you are on vacation? Enjoy! Please note that hte issue above does always cause Mumbai to fail when trying to visualize BASH data.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s