ASH - Active Session History

Hmm .. .so one more topic in Performance tuning other than AWR is ASH

So, What is ASH ?

·        The data which is held in Buffer cache, It samples (Items selected at random from a population and used to test hypotheses about the population) the activity of each active database session every second.

·        The design goal is to keep about an hour (your mileage will vary).

·         If a session is not active it will not be sampled. The in-memory buffer is exposed via a view called v$active_session_history.

·        Active Session History (ASH) was introduced in Oracle 10g.

Note :

You can disable ash by setting the underscore parameter

SQL > alter system set “_ash_enable”=False;

Earlier I said that, if the session is not active it will not be sampled. In this regards, You can actually set a parameter as below

 SQL > alter system set “_ash_enable_all” = TRUE

to force all sessions, including idle sessions, to be sampled.

How To Generate ASH Report

Oracle Provide a standard script just like AWR to analyze the ASM data

I will explain how to generate ASH(Active Session History) reports from SQLPLUS. The same can be done using Enterprise Manager also.

Script Location is same as other standard script $ORACLE_HOME/rdbms/admin/

Example :

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name  Inst Num Instance

----------- ------------ -------- ------------

 4174341635 F1                               1 f1

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name          Instance     Host

------------ -------- ------------ ------------ ------------

* 4174341635               1 F1               f1                  oracleasm1.l

                                                                        ocaldomain

Defaults to current database

Using database id: 4174341635

Enter instance numbers. Enter 'ALL' for all instances in a

RAC cluster or explicitly specify list of instances (e.g., 1,2,3).

Defaults to current instance.

Using instance number(s): 1

ASH Samples in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  16-Nov-16 12:34:10   [  71385 mins in the past]

Latest ASH sample available:  05-Jan-17 02:19:18   [     0 mins in the past]

Specify the timeframe to generate the ASH report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter begin time for report:

--    Valid input formats:

--         To specify absolute begin time:

--           [MM/DD[/YY]] HH24:MI[:SS]

--           Examples: 02/23/03 14:30:15

--                         02/23 14:30:15

--                         14:30:15

--                         14:30

--         To specify relative begin time: (start with '-' sign)

--           -[HH24:]MI

--           Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)

--                         -25    (SYSDATE - 25 Mins)

Defaults to -15 mins

Enter value for begin_time: 01/01/17 12:34:10

Report begin time specified: 01/01/17 12:34:10

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time

Press Enter to analyze till current time

Enter value for duration: 10

Report duration specified:   10

Using 01-Jan-17 12:34:10 as report begin time

Using 01-Jan-17 12:44:10 as report end time

Summary of All User Input

-------------------------

Format                  : HTML

DB Id         : 4174341635

Inst num       : 1

Begin time     : 01-Jan-17 12:34:10

End time       : 01-Jan-17 12:44:10

Slot width     : Default

Report targets : 0

Report name    : nk