Active Session History
In Oracle Database 10 g , the current session activity can be obtained from the V$SESSION or V$SESSION_WAIT views. The view V$SESSION_WAIT_HISTORY provides information about the last 10 wait events that the session encountered . However, for troubleshooting a performance issue, these views do not provide enough historical information about what the session did. Oracle Database 10 g solves this problem with the introduction of Active Session History (ASH).
As the name suggests, Oracle provides historical information on active sessions. It samples all active sessions every second to track their state and stores this information in a buffer in the SGA.
The historical information is displayed by the V$ACTIVE_SESSION_HISTORY view. This view is similar to a join between V$SESSION and V$SESSION_WAIT views with historical data for active sessions.
The other important difference between the V$ACTIVE_SESSION_HISTORY and V$SESSION_WAIT_HISTORY is the archiving of the data. ASH contents are written to the database tables by AWR. This view can roughly be called the ‚“flashback session ‚½ view as it helps perform spot analysis of the session when diagnosing problems that may have occurred in the immediate past. However, this information is not guaranteed to be available in this view at all times. In a very active database with numerous active sessions, the internal buffer can get full faster. Oracle will sample available information and write to an AWR table that can be viewed via the DBA_HIST_ACTIVE_SESSION_HISTORY view. The view V$ACTIVE_SESSION_HISTORY acts as a single point of reference for various pieces of information such as waits events, accessed objects, time spent on CPU, details about the SQL statement such hash value, and the SQL execution plan.
What Is an Active Session?
We mentioned in the previous section that ASH samples active sessions. But what is an active session? The status ACTIVE is not to be confused with V$SESSION.STATE, which has a binary value of ACTIVE or INACTIVE. ASH considers a session ACTIVE if the user call to the RDBMS kernel falls in any of the following categories and collects the session activity data:
-
PARSE or EXECUTE or FETCH operations
-
Waiting for the I/O to complete
-
Waiting for the message or buffer from remote instance (in RAC)
-
On CPU
-
Not waiting for recursive session
-
If it is a parallel slave, not waiting for PX_IDLE event
-
Any other wait that does not fall in the Idle wait class
Components of ASH
ASH does not require any initialization parameter setting or any installation script be run. It is enabled by default after creating or upgrading to an Oracle Database 10 g .
The new background process, MMNL (the lightweight version of MMON), is responsible for writing the sampled data to the in-memory circular buffer in the fixed area of the SGA. The buffer contents are further sampled and written to the AWR table, WRH$_ACTIVE_SESSION_HISTORY, on every AWR flush every hour by default. The MMNL process will also flush the buffer contents to the AWR tables whenever the buffer gets full. This process does not request any latches to update the buffer contents and can keep up with database activity without any problems.
The ASH in-memory buffer, by default, has an upper limit of 30MB for its size. The minimum size is 1MB. The size of the ASH in-memory buffer depends on factors such as the number of CPUs, the size of shared pool, the value set for SGA_TARGET, and some arbitrary rounding of numbers . In Oracle Database 10 g Release 1, the following formula derives the buffer size; however, it may change in the future releases:
max (min (#of CPUs * 2MB, 5% of SHARED_POOL_SIZE, 30MB), 1MB)
The view V$ACTIVE_SESSION_HISTORY is based on the X$KEWASH and X$ASH structures. The X$ASH structure contains the sampled details of every active session. The X$KEWASH structure contains the details about the number of samples taken in the instance.
There are a few hidden initialization parameters that change the default behavior of ASH. Do not rush to start using those. Always get approval from Oracle Support before using such parameters.
The dynamic parameter, _ASH_ENABLE, when set to FALSE will disable ASH functionality, and the view V$ACTIVE_SESSION_HISTORY will not be populated anymore. The _ASH_DISK_WRITE_ENABLE defaults to TRUE to flush the in-memory ASH data to disk. Setting it to FALSE will disable writing this data to disk. So, if for some reason you do not want to store ASH data to AWR but want to keep it in the memory, you can set this parameter to FALSE. You can also increase the buffer size by setting the _ASH_SIZE parameter to a larger value than 30MB.
V$ACTIVE_SESSION_HISTORY View
Table 9-3 describes the columns in the V$ACTIVE_SESSION_HISTORY and where appropriate relates those to columns already available in other V$ views.
| Column Name | Type | Description |
|---|---|---|
| SAMPLE_ID | NUMBER | ID of the sample snapshot. |
| SAMPLE_TIME | TIMESTAMP(3) | Time at which the sample was taken. |
| SESSION_ID | NUMBER | Session identifier, maps to V$SESSION.SID. |
| SESSION_SERIAL# | NUMBER | Session serial number, maps to V$SESSION.SERIAL#. |
| USER_ID | NUMBER | Oracle user identifier, maps to V$SESSION.USER#. |
| SQL_ID | VARCHAR2(13) | SQL identifier of the SQL statement. |
| SQL_CHILD_NUMBER | NUMBER | Child number of the SQL statement. |
| SQL_PLAN_HASH_VALUE | NUMBER | Hash value of the SQL plan, maps to V$SQL.PLAN_HASH_VALUE. |
| SQL_OPCODE | NUMBER | SQL operation code, maps to V$SESSION.COMMAND. |
| SERVICE_HASH | NUMBER | Service hash, maps to V$ACTIVE_SERVICES.NAME_HASH. |
| SESSION_TYPE | VARCHAR2(10) | FOREGROUND or BACKGROUND. |
| SESSION_STATE | VARCHAR2(7) | State: WAITING or ON CPU. |
| QC_SESSION_ID | NUMBER | Query coordinator ID for parallel query. |
| QC_INSTANCE_ID | NUMBER | Query coordinator instance ID. |
| EVENT | VARCHAR2(64) | If SESSION_STATE = WAITING, the event for which the session was waiting for at the time of sampling. |
| EVENT_ID | NUMBER | Identifier of the resource or event, maps to V$EVENT_NAME.EVENT_ID. |
| EVENT# | NUMBER | Number of the resource, maps to V$EVENT_NAME.EVENT#. |
| SEQ# | NUMBER | Sequence number, uniquely identifies the wait, maps to V$SESSION.SEQ#. |
| P1 | NUMBER | First additional wait parameter. |
| P2 | NUMBER | Second additional wait parameter. |
| P3 | NUMBER | Third additional wait parameter. |
| WAIT_TIME | NUMBER | It is 0 if the session was waiting, maps to V$SESSION.WAIT_TIME. |
| TIME_WAITED | NUMBER | If SESSION_STATE = WAITING, the time that the session actually spent waiting for that EVENT will be 0 until it finishes waiting. |
| CURRENT_OBJ# | NUMBER | Object ID of the object if the session is waiting for some I/O- related events or for some enqueue waits, maps to V$SESSION.ROW_WAIT_OBJ#. |
| CURRENT_FILE# | NUMBER | File number of the file if the session was waiting for some I/O-related events or for some enqueue waits, maps to V$SESSION.ROW_WAIT_FILE#. |
| CURRENT_BLOCK# | NUMBER | ID of the block if the session was waiting for I/O-related events or for some enqueue waits, maps to V$SESSION.ROW_WAIT_BLOCK#. |
| PROGRAM | VARCHAR2(48) | Name of the operating system program, maps to V$SESSION.PROGRAM. |
| MODULE | VARCHAR2(48) | Name of the executing module when sampled, as set by the procedure DBMS_APPLICATION_INFO.SET_MODULE. |
| ACTION | VARCHAR2(32) | Name of the executing module when sampled, as set by the procedure DBMS_APPLICATION_INFO.SET_ACTION. |
| CLIENT_ID | VARCHAR2(64) | Client identifier of the session; maps to V$SESSION.CLIENT_ID. |
The V$ACTIVE_SESSION_HISTORY view can be considered a fact table in a data warehouse with its columns as the dimensions of the fact table. The contents are in the memory; so accessing those by these columns is very fast. You can find out almost anything about any sessions ‚ activity from this view. You can quickly answer questions such as: how many sessions waited on a particular wait event in the last five minutes and for how long? What objects sessions are waiting on the most and what for? It is important to note that this information is based on sampled data that is captured every second. As such, it will be very close to being accurate and sufficient for your analysis.
Such online analysis is possible because of the ASH in-memory buffer. The following example shows how to find what sessions waited in the last five minutes, for what wait events, for how long, and how many times they waited:
select session_id, event, count(*), sum(time_waited)
from v$active_session_history
where session_state = 'WAITING'
and time_waited > 0
and sample_time >= (sysdate - &HowLongAgo/(24*60))
group by session_id, event;
Enter value for howlongago: 5
old 5: and sample_time >= (sysdate - &HowLongAgo/(24*60))
new 5: and sample_time >= (sysdate - 5/(24*60))
SESSION_ID EVENT COUNT(*) SUM(TIME_WAITED)
---------- ------------------------------ ---------- ----------------
126 db file scattered read 276 16958032
131 db file scattered read 270 17728709
131 log file switch completion 1 418071
133 class slave wait 1 5125049
133 db file sequential read 4 151610
138 db file scattered read 5 354926
138 db file sequential read 20 974258
138 log file switch completion 1 418261
138 control file sequential read 1 27706
153 null event 1 45580
153 db file sequential read 26 6900220
153 control file sequential read 4 202271
166 control file parallel write 8 1896634
166 control file sequential read 1 55883
167 log file parallel write 8 359185
167 control file single write 1 30063
168 db file parallel write 9 362689
17 rows selected.
When querying the V$ACTIVE_SESSION_HISTORY view, Oracle has to acquire all the usual latches for statement parsing, accessing the buffers, etc. If there is a parse latch related problem in a hung database, you may not be able to access the wealth of information in the V$ACTIVE_SESSION_HISTORY view that can help you diagnose the problem. However, there is another way to access this in-memory ASH information, and that is what we will discuss in the next section.
The ASHDUMP: Dumping ASH Circular Buffer Contents to Trace File
The contents of the ASH buffer can be dumped to a trace file using the event ASHDUMP. These contents can then be loaded into a database table. The structure of this table resembles the V$ACTIVE_SESSION_HISTORY view.
You can produce the ASHDUMP trace file using the following oradebug command sequence after connecting as sysdba :
oradebug setmypid
oradebug unlimit
oradebug dump ashdump 10
oradebug tracefile_name
You can also use the ALTER SESSION command to produce an immediate dump of the ASH buffer as shown next:
alter session set events immediate trace name ashdump, level 10;
The trace file will be in your UDUMP directory. The contents of the trace file can be loaded into a table in other database. In the first few lines, the trace file lists all the column names for the data. The trace data is displayed as comma-separated values for the respective columns. This information can be used to employ SQL*Loader to load the rest of the contents of the trace file to the table for further analysis.
The following example shows the contents of the trace file from an ASHDUMP. The header information, typically found in trace files, is removed for clarity.
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
DBID, INSTANCE_NUMBER, SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, SQL_ID, SQL_CHILD_NUMBER, SQL_PLAN_HASH_VALUE, SERVICE_HASH, SESSION_TYPE, SQL_OPCODE, QC_SESSION_ID, QC_INSTANCE_ID, CURRENT_OBJ#, CURRENT_FILE#, CURRENT_BLOCK#, EVENT_ID, SEQ#, P1, P2, P3, WAIT_TIME, TIME_ WAITED, PROGRAM, MODULE, ACTION, CLIENT_ID
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN>>>
2847681843,1,6033130,"04-13-2004 07:40:58.006572000",41,597,5,"2xbhdwsp8a0zd",0,0,3427055676,1,62,0,0,16314, 1,27521,2652584166,135,1,27709,1,121,0,"sqlplus@hptest (TNS V1- V3)","SQL*Plus","",""
2847681843,1,6033129,"04-13-2004 07:40:56.976572000",41,597,5,"2xbhdwsp8a0zd",0,0,3427055676,1,62,0,0,16314, 1,27521,2652584166,135,1,27709,1,121,0,"sqlplus@hptest (TNS V1- V3)","SQL*Plus","",""
. . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . .
2847681843,1,6032838,"04-13-2004 07:35:57.196572000",49,3,0,"6q766vsk5290x",0,0,165959219,2,47,0,0,429496729 5,0,0,866018717,189,300,0,0,3007426,0,"oracle@hptest (MMON)","","",""
2847681843,1,6032837,"04-13-2004 07:35:56.166572000",49,3,0,"6q766vsk5290x",0,0,165959219,2,47,0,0,429496729 5,0,0,866018717,189,300,0,0,3007426,0,"oracle@hptest (MMON)","","",""
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP END>>>
Although this process of dumping the ASH buffer, loading the trace file data into a table, and then troubleshooting the cause of the problem may sound a bit cumbersome, it may occasionally be useful when you have a hung system. You don ‚ t need to wait for the disaster to strike. You can experiment by producing the ASHDUMP trace file using the ALTER SESSION command and keeping those scripts ready to load the data to your own ASH table!
In the preceding sections we discussed how Oracle Database 10 g captures performance data using AWR and ASH snapshots, how to manage the data capture process, and how you can view the data. Behind the scenes, Oracle Database 10 g is doing a lot more with this data, and that is the topic of the next section.
EAN: 2147483647
Pages: 114