Session History

The Session History provides a complete history of session activity. It allows you to drill down into session activity details at a specific point in time.

For Oracle 10g, you can choose to use ASH (although it's not necessary) to pre-populate Lab128 with collected data. Please note that v$active_session_history view is a part of the Oracle 10g Diagnostic Pack and requires purchase of the ODP license. Therefore, Lab128 provides additional savings by implementing ASH functions and not requiring the purchase of ODP.

How to use this window.

This window has three sections: upper - the session activity chart; middle - the tabular view of session details; and lower - specific details for the selected session.

The Active Sessions chart provides quick overview of activity and identifies spikes of activity. Select the point in time that you want to explore deeper. The tabular view shows all active sessions at that time and details about them. Here you can identify which sessions were the biggest bottlenecks during a specific point in time. The lower section shows specific details for the selected session, such as waits, locks, object access, etc.

Viewing options.

You can select sessions to view in 3 ways. Selecting a point in time on the graph, scrolling through snapshots based on previous/next snapshot time, or scrolling through snapshots based on similar criteria (such as Wait Event, SID, OS User, etc.)

Descriptions of the columns in the Session Details tabular view.

• SID - Session ID of the active session (v$session.SID);
• Serial#- Serial number of the active session (v$session.SERIAL#);
• User - Oracle user name (v$session.USERNAME);
• SQL_Text- SQL text of the cursor (reassembled $sqltext_with_newlines.sql_text - also cached by Lab128 internally on LRU basis);
• Owner- SID of the PQ coordinator (derived from v$session.OWNERID);
• State - Wait state. Values: "Waiting" or "On CPU" (derived from v$syssion_wait.STATE);
• Event- Wait event or resource for which active session was waiting (v.event_name.NAME);
• Program- Operating system program name (v$session.PROGRAM);
• Module- Name of the currently executing module (v$session.MODULE);
• OS User- Operating system client user name (v$session.OSUSER);
• Machine- Operating system machine name (v$session.MACHINE);
• Terminal- Operating system terminal name (v$session.TERMINAL);
• Action- Contains the name of the currently executing action as set by calling the DBMS_APPLICATION_INFO.SET_ACTION procedure (v$session.ACTION);
• Block Inst.- Blocking Instance ID (v$session.BLOCKING_INSTANCE);
• Block SID- SID of the blocking session (v$session.BLOCKING_SESSION);
• Blocker?- Is this session holding a blocking lock? (derived internally by Lan128);
• Obj ID- ID of object accessed by this session or locked object being waited on (v$session.ROW_WAIT_OBJ#);
• Object- Object name of accessed or locked object (looked up from dba_objects.OBJECT_NAME);
• File- File # of file containing accessed or locked object (v$session.ROW_WAIT_FILE#);
• Block- Block # accessed or locked object (v$session.ROW_WAIT_BLOCK#);
• Row- Current row being locked (v$session.ROW_WAIT_ROW#);
• Event Summary - Wait event or resource for which active session was waiting (synthesized from v$session_wait.EVENT, v$session_wait.P1, P2, and P3. When Block# is supplied, Lab128 fetches internally the segment name and type);
• P1_TEXT - Description of the first additional parameter in v$session_wait (v$session_wait.P1_TEXT);
• P1- First additional parameter in v$session_wait (v$session_wait.P1)
• P2_TEXT - Description of the second additional parameter in v$session_wait (v$session_wait.P2_TEXT);
• P2- Second additional parameter in v$session_wait (v$session_wait.P2);
• P3_TEXT- Description of the third additional parameter in v$session_wait (v$session_wait.P3_TEXT);
• P3- Third additional parameter in v$session_wait (v$session_wait.P3)
• SQL_Addr- Hexadecimal address of the cursor (v$sql.ADDRESS);
• SQL_ID- SQL ID of the cursor,10g (v$session.SQL_ID);
• SQL_Hash - SQL Hash value (v$session.SQL_HASH_VALUE);
• Plan_Hash - SQL Plan Hash value, 10g (v$active_session_history.SQL_PLAN_HASH_VALUE);
• WT- v$session_wait.WAIT_TIME;
• SW- v$session_wait.SECONDS_IN_WAIT;
• Child- Child number of the SQL statement that is currently being executed (v$session.SQL_CHILD_NUMBER);
• Entry PL/SQL- Entry procedure or function into the PL/SQL package, 10.2.0.2+ (v$session.PLSQL_ENTRY_OBJECT_ID and v$session.PLSQL_ENTRY_SUBPROGRAM_ID; names are looked up from dba_procedures);
• Curr PL/SQL- Currently executing procedure or function in the PL/SQL package (v$session.PLSQL_OBJECT_ID and v$session.PLSQL_SUBPROGRAM_ID; names are looked up from dba_procedures).