Wednesday, 23 November 2011

Actual use of Active Session History (ASH)

Active Session History (ASH) was introduced in Oracle 10g. It samples the activity of each active1 database session every second. The data is held in a buffer in memory in the database. 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. But yes if you want inactive sessions also to be sampled you can set the parameter _ash_enable_all = TRUE.

When an AWR snapshot is taken, 1 row in 10 from the ASH buffer is copied down into the AWR repository. It can also be flushed to disk between snapshots when the buffer reaches 66% full, so there is no missed data.The data is stored in WRH$_ACTIVE_SESSION_HISTORY and it is exposed via dba_hist_active_sess_history.

The main components of the ASH are
- 2 background procresses, MMON and MMNL,
- a rolling buffer, which sits in the SGA fixed area and holds the historical statistical information of active sessions

ASH is enabled by default, but before you rush off to use it, be aware that it is a licenced feature. It is part of the Diagnostic Pack, so you have to pay for it. I don’t like that either, but that’s how it is.

The ASH will auto-configure itself to try and hold at least one-hour worth of instance activity without wasting too much value SGA space and will never be bigger than 5% of SGA_TARGET or 5% of SHARED_POOL (if AutoSGA is not used)". ASH memory represents 2MB of memory per CPU, ASH cannot exceed 5% of shared pool and 5% of SGA_TARGET.

Comparison of ASH with SQL TRACE

ASH and SQL*Trace are not the same thing, but both are valuable tools for finding out about where processes spend time. SQL*Trace (or event 10046 as we used to call it) has been my weapon of choice for solving performance issues for a very long time, and it is extremely effective, and there is still a place for it.

There are difficulties with using SQL trace, especially in a production environment.

Firstly, it does have a run time overhead. You could afford to trace a single process, but you certainly couldn’t trace the entire database.

1) You have to work with trace in a reactive way. You will probably not already be tracing a process when you experience a performance problem, so you need to run the process again and reproduce the poor performance with trace.

2) Trace will tell you if a session is blocked waiting on a lock. However, it will not tell you who is blocking you. ASH will do this (although there are limitations).

3) A trace file records everything that happens in a session, whereas ASH data samples the session every seconds. Short-lived events will be missed, so the data has to be handled statistically.

4) There are problems with both approaches if you have the kind of application where you have lots of different SQL statements because the application uses literal values rather than bind variables (and cursor sharing is EXACT).

5) Oracle’s TKPROF trace file profiler cannot aggregate these statements, but I have found another called ORASRP that can. With ASH, you will see different SQL_IDs, but it can be effective to group statements with the same execution plan.

6) You may have trouble finding the SQL text in the SGA (or via the DBMS_XPLAN package) because it has already been aged out of the library cache. You may have similar problems with historical ASH data because the statement had been aged out when the AWR snapshot was taken.

7) A trace file, with STATISTICS_LEVEL set to ALL, will give you timings for each operation in the execution plan. So, you can see where in the execution plan the time was spent. ASH will only tell you how long the whole statement takes to execute, and how long was spent on which wait event.

Application Instrumentation

Oracle has provided a package called DBMS_APPLICATION_INFO since at least Oracle 8. This allows you to set two attributes; MODULE and ACTION for a session. That value then appears in v$session, and can be very useful to help you identify what database sessions relate to what part of an application. These values are then also captured by ASH. I cannot over-emphasise the importantance of this instrumentation when analysing performance issues. Without sensible values in these columns all you have is the program
name. You will probably struggle to identify ASH data for the sessions which are of interest. These values are not set by default. Instead DBAs are dependent on developers to include them in their code. For example, Oracle E-Business Suite has built this into the application.

Following query fetchs top sqls spent more on cpu/wait/io. (Thanks to Kyle Hailey for this script):

ash.SQL_ID ,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#

No comments:

Post a Comment