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

What a Junior DBA will do, When he Faces “Slowness in the Database” ?

Many a times, a DBA gets a call from Client side regarding the slowness of the Database.

In these case, fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit.

There are few checks a Junior DBA can perform to detect & Troubleshoot Slowness

1. Taking user inputs:

·        Is application is slow or any particular batch processing is slow?

·        Slowness is observed through out the system or only few or one user

·        Is it happening in some particular timing ?

By collecting these information we will get an outline of what needs to be checked.

2. Check for any lock contention

You can use the below query for this.

SQL> select count(*) from v$lock where block=1;

·        If count is greater than one, lock is there in database.

·        Check with application team and release the blocking sessions

3. Locking is not only the cause to effects the performance. Disk I/O contention is another case.

·        When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as "db file sequential read" (for index scan) or "db file scattered read" (for full table scan).When you see the event, you know that the session is waiting for I/O from the disk to complete.

·        To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.

·        Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.

4. Check the resource utilization:

You can check the following Areas :

·        CPU utilisation

·        Load

·        Memory utilization

ü  Run TOP command in Linux to check CPU usage.

ü  Check  any single process is holding the CPU for long time -- note that process ID.

            Press 'c' in top command, it will give you the time and process which is   consuming more CPU.

ü  Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.

5. Check the alert log:

Check the alert log. You can check how many log switches are happening in one hour.

If you have more than 5 archives per hour we can say you may need to increase the redo log size. Troubleshoot the errors if it is critical or related to performance.

6. Server side checks

Check for the Memory, Paging, I/O utilisation from server side.

Paging and memory can be checked by ‘top’ command and ‘iostat’ will do the I/O statistics.

Contact the concern team for any abnormality if you see in this.

Advance performance tuning is not the scope of this blog, this is for junior DBA. Hope it helps.

Expecting your comments...

Source : ArunSankar Blog

Find more about Tuning Click Here

AWR - Automatic Workload Repository :: Beginners Guide

FREE Sign-Up with Gmail and Receive Pdf's and Updates via Mail -Click Here