Oracle DBA by Example. Code samples, Scripts, Reference.: ASH Report I

===========================================
ASH Concept
===========================================

ASH was introduced in Oracle 10.

Samples of wait events are taken once per second, mostly from session level, and are written to a SGA area.

The idea is to report "what sessions are doing at this moment".

Then once every 10 seconds, the data is stored into a table, with some additional information.

===========================================
Main V$ Objects
===========================================
V$ACTIVE_SESSION_HISTORY
Samples of wait event information are taken once per second and made available using the V$ACTIVE_SESSION_HISTORY view.

The source of information is V$SESSION and V$SESSION_WAIT.

An active session is one that is waiting on CPU or any event that does not belong to the "Idle" wait class at the time of the sample.

The sample information is written to a circular buffer in the SGA, so the greater the database activity, the less time the information will remain available for.
Roughly you cannot expect to see information older than 24 hours in V$ACTIVE_SESSION_HISTORY.

DBA_HIST_ACTIVE_SESS_HISTORY
To allow for historical access to the ASH data, one in ten samples are persisted to disk and made available using the DBA_HIST_ACTIVE_SESS_HISTORY view.
Data in this view is similar to using the V$ACTIVE_SESSION_HISTORY view, with one difference.
Only 1 out of 10 samples from V$ACTIVE_SESSION_HISTORY is written into DBA_HIST_ACTIVE_SESS_HISTORY. So to get the true values, need to multiply by 10.

The data is written by AWR snapshots.
The data is wtitten into WRH$_ACTIVE_SESSION_HISTORY table and is available for querying by DBA_HIST_ACTIVE_SESS_HISTORY view.

Roughly you cannot expect to see information older than 7 days in DBA_HIST_ACTIVE_SESS_HISTORY.

===========================================
About data in V$ACTIVE_SESSION_HISTORY view.
===========================================
Imagine a case where a single session is waiting on "db file sequential read" for 5 seconds. That means we would have 5 samples, that may look like this.

EVENT SAMPLE_ID TIME_SEC
----------------------- --------- --------
db file sequential read 1 1
db file sequential read 2 2
db file sequential read 3 3
db file sequential read 4 4
db file sequential read 5 5

So to get the total amount the sessions has spent waiting on "db file sequential read", need to sum the number of entries in V$ACTIVE_SESSION_HISTORY view, rather than the sum of time_sec column.

Example
See the main activity on the database for the last 10 minutes.

SELECT NVL(ACTIVE_SESSION_HISTORY.event, 'ON CPU') AS event,
COUNT(*) AS total_wait_time
FROM V$ACTIVE_SESSION_HISTORY ACTIVE_SESSION_HISTORY
WHERE ACTIVE_SESSION_HISTORY.sample_time > SYSDATE - 10/(24*60)
AND ACTIVE_SESSION_HISTORY.session_type <> 'BACKGROUND'
GROUP BY ACTIVE_SESSION_HISTORY.event
ORDER BY ACTIVE_SESSION_HISTORY.total_wait_time DESC;

EVENT TOTAL_WAIT_TIME
------------------------------ ---------------
db file sequential read 750
log file parallel write 43
log file sync 42
db file parallel read 32
control file sequential read 22
ON CPU 21
db file parallel write 21
log file switch 8
Disk file operations I/O 1
control file parallel write 1
buffer busy waits 1

11 rows selected.

===========================================
About data in V$ACTIVE_SESSION_HISTORY view.
===========================================
Using this view is similar to using the V$ACTIVE_SESSION_HISTORY view, but remember the sample time is now 10 seconds.
So need to use (count*10) to measure time, rather than just the count.

Example.
Get last 24 hour activity info.

SELECT NVL(a.event, 'ON CPU') AS event,
COUNT(*)*10 AS total_wait_time
FROM DBA_HIST_ACTIVE_SESS_HISTORY a
WHERE a.sample_time > SYSDATE - 1
AND a.session_type <> 'BACKGROUND'
GROUP BY a.event
ORDER BY total_wait_time DESC;

EVENT TOTAL_WAIT_TIME
------------------------------ ---------------
db file sequential read 3860
ON CPU 1500
control file sequential read 990
direct path read temp 580
direct path read 560
log file parallel write 280
db file parallel write 270
Disk file operations I/O 240
log file switch completion 150
log file sync 130

db file parallel read 130

===========================================

===========================================
WAIT_TIME vs TIME_WAITED
===========================================
WAIT_TIME
– Same as V$SESSION_WAIT
– Zero means 'WAITING', and any other value means 'ON CPU'.
- This is an actual measurement value.

TIME_WAITED
– Actual total time waited for that event
– Updated later upon event completion, after the event has terminated.

DB Time
The DB Time estimate is COUNT(*) - each entry is 1 second.

Here is a graphical explanation.

===========================================
ASH Dimensions
===========================================
Here is a list of fields in V$ACTIVE_SESSION_HISTORY and in DBA_HIST_ACTIVE_SESS_HISTORY, grouped in dimensions.
Oracle Reference.

The actual fields would vary between Oracle versions.

Name Type Nullable
------------------------ ----------------- --------
SNAP_ID NUMBER Y   --Only in
DBID NUMBER Y    DBA_HIST_ACTIVE
INSTANCE_NUMBER NUMBER Y   _SESS_HISTORY
SAMPLE_ID NUMBER Y
SAMPLE_TIME TIMESTAMP(3) Y
SESSION_ID NUMBER Y   --Session info
SESSION_SERIAL# NUMBER Y
USER_ID NUMBER Y
SESSION_TYPE NUMBER Y
SESSION_STATE VARCHAR2(7 BYTE) Y
QC_SESSION_ID NUMBER Y
QC_INSTANCE_ID NUMBER Y

SQL_ID VARCHAR2(13 BYTE) Y --SQL info

SQL_CHILD_NUMBER NUMBER Y
SQL_PLAN_HASH_VALUE NUMBER Y
FORCE_MATCHING_SIGNATURE NUMBER Y
SQL_OPCODE NUMBER Y
SEQ# NUMBER Y --Wait Events
EVENT_ID NUMBER Y
P1 NUMBER Y
P2 NUMBER Y
P3     NUMBER Y
WAIT_TIME NUMBER Y
TIME_WAITED NUMBER Y
BLOCKING_SESSION NUMBER Y
BLOCKING_SESSION_SERIAL# NUMBER Y
BLOCKING_SESSION_STATUS VARCHAR2(11) Y
CURRENT_OBJ# NUMBER Y -- Objects info
CURRENT_FILE# NUMBER Y
CURRENT_BLOCK# NUMBER Y
PROGRAM VARCHAR2(48 BYTE) Y --Application
MODULE VARCHAR2(48 BYTE) Y   info
ACTION VARCHAR2(32 BYTE) Y
CLIENT_ID VARCHAR2(64 BYTE) Y

SERVICE_HASH NUMBER Y

============================
Session info
============================

SESSION_ID NUMBER Y   --Session info
-  - V$SESSION.sid
SESSION_SERIAL# NUMBER Y
- V$SESSION.serial#
USER_ID NUMBER Y
- V$SESSION.user#
SESSION_TYPE NUMBER Y
- Backgroung
- Foreground
SESSION_STATE VARCHAR2(7) Y
- 'WAITING'
- 'ON CPU'
QC_SESSION_ID NUMBER Y
- Zero for all, except for parallel query slave.
QC_INSTANCE_ID NUMBER Y

- Zero for all, except for parallel query slave.

==========================
SQL info
==========================

SQL_ID VARCHAR2(13 BYTE) Y

- SQL Identifier

SQL_CHILD_NUMBER NUMBER Y
SQL_PLAN_HASH_VALUE NUMBER Y
- Plan Identifier
FORCE_MATCHING_SIGNATURE NUMBER Y
- Related to when the CURSOR_SHARING parameter is set to FORCE.
SQL_OPCODE NUMBER Y

- V$SESSION.command. Indicates phase of query processing.

===========================
Wait Events
===========================

SEQ# NUMBER Y

EVENT_ID NUMBER Y

P1 NUMBER Y

P2 NUMBER Y

P3 NUMBER Y

WAIT_TIME NUMBER Y

- V$SESSION.wait_time.

Zero if the session was waiting at the time of sampling.

The actual value is not important.

If the value is zero,need to check SESSION_STATE.

TIME_WAITED NUMBER Y

- total time waited for that event.

BLOCKING_SESSION NUMBER Y

- V$SESSION.blocking_session

BLOCKING_SESSION_SERIAL# NUMBER Y

- Serial number of the blocking session

BLOCKING_SESSION_STATUS VARCHAR2(11) Y

- Status of the blocking session.

=========================
Application Info
=========================
PROGRAM VARCHAR2(48 BYTE) Y
MODULE VARCHAR2(48 BYTE) Y
ACTION VARCHAR2(32 BYTE) Y
CLIENT_ID VARCHAR2(64 BYTE) Y

SERVICE_HASH

===========================================
ASH main Tables
===========================================
VIEW DBA_HIST_SNAPSHOT

Name Null? Type
---------------------- -------- ------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER
SNAP_FLAG NUMBER

By default this table holds data for 7 days.
SELECT snap_id,
TO_CHAR(begin_interval_time,'DD/MM/YYYY hh24:mm') AS begin_interval,
TO_CHAR(end_interval_time,'DD/MM/YYYY hh24:mm') end_interval
FROM DBA_HIST_SNAPSHOT

ORDER BY 1 DESC

SNAP_ID BEGIN_INTERVAL END_INTERVAL
---------- ---------------- ----------------
37688 07/04/2015 17:04 07/04/2015 18:04
37687 07/04/2015 16:04 07/04/2015 17:04
37686 07/04/2015 15:04 07/04/2015 16:04

37685 07/04/2015 14:04 07/04/2015 15:04

...

37488 30/03/2015 09:03 30/03/2015 10:03

37487 30/03/2015 08:03 30/03/2015 09:03

37486 30/03/2015 07:03 30/03/2015 08:03

203 rows selected.

VIEW DBA_HIST_ACTIVE_SESS_HISTORY
Name Null? Type
---------------------- -------- ----------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
SAMPLE_ID NOT NULL NUMBER
SAMPLE_TIME NOT NULL TIMESTAMP(3)
SESSION_ID NOT NULL NUMBER
SESSION_SERIAL# NUMBER
USER_ID NUMBER
SQL_ID VARCHAR2(13)
SQL_CHILD_NUMBER NUMBER
SQL_PLAN_HASH_VALUE NUMBER
WAIT_CLASS VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_TIME NUMBER
SESSION_STATE VARCHAR2(7)
TIME_WAITED NUMBER
PROGRAM VARCHAR2(64)
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
CLIENT_ID VARCHAR2(64)

VIEW DBA_HIST_SQLTEXT
Name Null? Type
---------------------- -------- ----------------------------
DBID NOT NULL NUMBER
SQL_ID NOT NULL VARCHAR2(13)
SQL_TEXT CLOB
COMMAND_TYPE NUMBER

===========================================
ASH Data dump to file
===========================================

It is possible to dump ASH data to a file, and then import the data in another DB.

>oradebug setmypid

>oradebug dump ashdump 10

>alter session set events 'immediate trace name ashdump level 10';

10 ==> minutes of history you want to dump

Generated file can be loaded into database using supplied control file

rdbms/demo/ashldr.ctl

===========================================
Appendix
===========================================
Active Session History (ASH) Reference from Oracle-Base
YAPP (Yet Another Performance Profiling) Method - Oracle Support TechNote.
Sifting through the ASHes - Oracle TechNote on ASH
Excellent Reference for ASH

ASH By Example: http://www.go-faster.co.uk/Practical_ASH.pdf

Oracle DBA by Example. Code samples, Scripts, Reference.

Pages

Tuesday, December 16, 2014

ASH Report I - Theory

No comments:

Post a Comment