Session Details

The Session Details window lists sessions and provides session details in a convenient format. It provides the following information: which sessions are the top resource consumers; which sessions are active; what they are doing or waiting for; which open cursors the session has; what current SQL is running; the progress of long operation; how many locks are taken by the session; how much temporary space is taken; what are the parallel query slave sessions for the active session. Like many other windows, it contains shortcuts / hot spots to get more detail. For example, to see which locks are taken, click on the LckObj column to open the Locks and Locked Objects window.

The Session Details window offers other actions related to the sessions such as obtaining the Execution Plan for the selected cursor or session administration - killing / tracing sessions (with corresponding privileges granted).

There are three tab views in this window: Sessions - described in next section; Blocking and Waiting Sessions - a tree view of blockers and waiters; Filters - user-defined filters for this window.

Viewing options.

The check boxes in the upper part of this window show selected categories of sessions. The following session categories are available: User Active; Background; Parallel Query PQ Slaves; Inactive; Killed / Sniped; Queue Jobs.

To include info about Long operations, check the "Poll Longops" box. This option is recommended for the data warehouse type of queries.

To get more details about a session, select it. The three sections to the right of the Session list provide additional details about the selected session. The upper section is a collection of fields showing the data from v$session and v$process views. The middle section shows the current waits of the selected session. The lower section lists all open cursors, with the current one (in execution) marked with 'Curr'. Use the drop-down list to browse all open cursors and the box below it will show the SQL text of the selected cursor.

The Session Details window updates itself automatically and the refresh rate can be changed. To force a refresh ahead of schedule, press the "Update" button. To stop refreshing, press the "Pause" button. The content of this window will be frozen in Pause mode. To resume automatic refresh, press the "Update" button.

Note. The information presented on this screen originates from different views: v$session, v$process, v$bgprocess, v$sesstat, v$session_wait, v$session_longops, v$lock (see more at the bottom of this help page). Lab128 queries these views independently with the rate assigned individually to each query. The "Refresh Rate" box controls the refresh rate of the v$session view only. To change the refresh rate of other queries, use the Measurement Query Editor.

It makes sense to sort the session list using criteria such as resource usage. By default, the list is sorted in descending order by the user-selected Session Statistic columns - columns that derive values from the v$sesstat performance view. There are two such columns: by default they are "File I/O" and "CPU Usage". The first one takes values from "physical reads"+"physical writes", the second one - from "CPU used by this session". To change the statistics used by those columns, right-click on the header of the column and choose "Change Statistic" in pop-up menu. For more on statistics selection, see Select Session Statistic window.

Due to the dynamic nature of the Session list, the sessions constantly connect and disconnect. Lab128 keeps a selection bar on the selected session using its SID and Serial#. If the session disconnects, another one will be randomly selected. The list of open cursors changes too. Lab128 keeps the selection on the last selected cursor. If the list is completely refreshed, the priority is given to the current cursor - the currently executing SQL statement. Therefore, switching the selection to another session refreshes the cursor list and forces the executing cursor and corresponding SQL statement to show in the Open Cursor / SQL section. To widen the SQL text box, press the "Expand" button. To open Explain Plan for SQL shown in the SQL viewer, press the "Explain Plan" button.

If you are running Parallel queries and need to see all PQ slave sessions related to the coordinator (owner) session, select the owner session. Corresponding PQ sessions will be highlighted (see picture below in Shortcuts).

In order to kill the session: select it, right-click to open the pop-up menu, and use the "Kill selected session" item.

Shortcuts.

There are following hot spot areas / shortcuts in this window:

Right-click on the session list to open the pop-up menu:

• Longops details - opens Longops window;
• Locks details - opens Lock and Locked Object Details window;
• Temp segments details - opens Temporary segments window;
• More details on selected session - opens Single Session History window in the "Current" page. This page provides the last 10 waits (10g) for the session, cumulative waits per wait class, and session statistics;
• Selected Session History - opens Single Session History window in the "History" page;
• Show in SQL Central - Opens SQL Central and fills out the Sessions template with the SID and Serial# of selected session;
• Trace On selected session - sets SQL Trace On for the selected session (also see required grants for this option) using dbms_system.set_sql_trace_in_session() function;
• 10046 Level 12 Trace On selected session - sets event 10046, level 12 causing tracing selected session. This action is implemented using dbms_system.set_ev() function (Oracle 8.1.6 and later). The call to dbms_system.set_ev() function is preceded by altering session parameters: timed_statistics=true and max_dump_file_size=2147483647;
• Trace Off selected session - sets SQL Trace Off for the selected session (also see required grants for this option);
• Kill selected session - kills the selected session;
• Copy text - copies text from the selected cell onto the clipboard;

Please note, that in RAC environment some menu items can be disabled when: Lab128 is connected to one instance, the intended operation is in the remote instance, and Oracle server version doesn't support this remote operation. For example, killing session in the remote instance is not supported in 10g and earlier versions.

Session Filters.

The list of session can be further filtered by user-defined filters. The user-defined filters are active only when "Filtered Out" box is checked. Lab128 uses a regular expression matching algorithm. Note. All searches are case-insensitive.

Here is how it works. Internally, for each row in the Session view, the following string is constructed:

username=<USERNAME>;osuser=<OSUSER>;machine=<MACHINE>;program=<PROGRAM>;module=<MODULE>;

The <> denotes values from the corresponding columns in the Sessions view. Note that the order of fields in the string is important. There is no whitespace in the string unless they are part of the column data. This string is tested against the regular expression supplied in the edit box. If a match is found, this session is not displayed.

For those who are not familiar with regular expressions, we will explain the default expression which comes with Lab128. This should give you enough information to start playing with it and customizing it to your own needs. The default expression:

program=(oms|emagent|racgimon|nmccollector)|username=;.*program=.*J\d

has two independent parts concatenated by '| ' (pipe) character: program=(oms|emagent|racgimon|nmccollector) and username=;.*program=.*J\d The pipe character means "OR"; therefore if the match found for either of these two parts, the row is not displayed.

Let's take a look at the first sub expression: program=(oms|emagent|racgimon|nmccollector). The engine will be searching for the string "program=" and then it has 4 options listed in parenthesis, delimited by the already-familiar pipe character. All these options are ORed: if any of the following 4 strings is found, the session will not be displayed:

program=oms
program=emagent
program=racgimon
program=nmccollector

If the PROGRAM column for some session was: "emagent@oraprod4.domain.com (TNS V1-V3)" the match will be found. If, for example, you also need to filter out RMAN sessions, you can change expression to: program=(oms|emagent|racgimon|nmccollector|rman)

Now let's take a look at the second sub expression: username=;.*program=.*J\d. As you may guess, the USERNAME column should be empty to match "username=;". The dot '.' stands for any character and the '*' asterisk is a modifier to the preceding character and means it can be repeated any number of times. So together ".*" means any text. The remaining parts of this expression are:

"program=" - the string;
".*" - any text;
"J" - the single-character;
"\d" - means any digit.

So this expression is going to filter out sessions that have an empty value in USERNAME and if the value in PROGRAM is, for example, like this: "oracle@oraprod4.domain.com (J000)" - because this text contains "J0".

Let's recall that the search is case-insensitive. Therefore, this expression is functionally equivalent to the default one:

Program=(OMS|emagent|racgimon|nmccollector)|Username=;.*Program=.*j\d

Note. The filter action can be reversed using "not displayed" and "displayed" radio-buttons.

Descriptions of the columns in the Sessions tabular view.

• N - Instance ID / Node number (RAC only) (gv$session.INST_ID);
• SID - Session ID (v$session.SID);
• SERIAL# - Session serial number (v$session.SERIAL#);
• USERNAME - Oracle user name (v$session.USERNAME);
• STAT1, STAT2 (the column name derived from the statistic name) - cumulative or delta (difference divided by time between two measurements) value of the chosen statistic (v$sesstat.VALUE);
• COMMAND - Decoded command in progress - last statement parsed (v$sesstat.COMMAND);
• LckObj - Number of locked objects by the session (count of v$lock.TYPE where TYPE='TM'). If the session is a blocker, the letter "B" is appended. If the session is waiting to acquire the lock, the letter "W" is appended;
• STATUS - Status of the session (v$sesstat.STATUS);
• PQs - Number of Parallel Query (PQ) slave sessions started by this session (count of v$session where OWNERID equal to the SID of this session);
• N - Instance ID / Node number of the PQ coordinator (RAC only) (derived from v$session.OWNERID);
• OWNER - SID of the PQ coordinator (derived from v$session.OWNERID);
• Completed,% - The progress of operation in percents (derived as v$session_longops.SOFAR / v$session_longops.TOTALWORK);
• Elapsed,s - The number of elapsed seconds from the start of operations (v$session_longops.ELAPSED_SECONDS);
• Remain,s - Estimate (in seconds) of time remaining for the operation to complete (v$session_longops.TIME_REMAINING);
• Temp,M - Total size of temporary segments owned by session (sum of v$sort_usage.BLOCKS multiplied by block size of this temporary tablespace);
• Event - Resource or event for which the session is waiting (v$session_wait.EVENT);
• OSUSER - Operating system client user name (v$session.OSUSER);
• MACHINE - Operating system machine name (v$session.MACHINE);
• TERMINAL - Operating system terminal name (v$session.TERMINAL);
• PROGRAM - Operating system program name (v$session.PROGRAM);
• MODULE - Name of the currently executing module (v$session.MODULE);
• TYPE - Session type (v$session.TYPE);
• BG_NAME - Name of this background process (v$bgprocess.NAME);
• SQL ID or SQL Addr - SQL ID (10g+) or SQL address of executing statement (v$session.SQL_ID or v$session.SQL_ADDRESS);
• Child or SQL Hash - SQL statement child number (10g+) or SQL hash value of executing statement (v$session.SQL_CHILD_NUMBER or v$session.SQL_HASH_VALUE);

Descriptions of the fields in the right upper info section.

• SID - Session ID (v$session.SID);
• SERIAL# - Session serial number (v$session.SERIAL#);
• USERNAME - Oracle user name (v$session.USERNAME);
• Logon Time - Time of logon (v$session.LOGON_TIME);
• PID - Oracle process identifier (v$process.PID);
• OS PID - Operating system process identifier (v$process.SPID);
• PGA, KB - PGA memory currently allocated by the process, including free PGA memory not yet released to the operating system by the server process (v$process.PGA_ALLOC_MEM);
• Last Call ET - Time in seconds since the last call started and the time since the last call finished. In other words, time since the STATUS changed last time (v$session.LAST_CALL_ET);
• SQL ID or SQL Addr - SQL ID (10g+) or SQL address of the executing statement (v$session.SQL_ID or v$session.SQL_ADDRESS);
• Child or SQL Hash - SQL statement Child number (10g+) or SQL hash value of the executing statement (v$session.SQL_CHILD_NUMBER or v$session.SQL_HASH_VALUE);

Descriptions of the fields in the "Wait info" section.

• Last Wait / Waiting for - Wait event or resource for which active sessions are 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);

Descriptions of the fields in the "Current Cursor / Open Cursor" section.

• Drop-down list - List of open cursors (v$open_cursor.SQL_TEXT);
• SQL Text - SQL text of the cursor (reassembled v$sqltext_with_newlines.sql_text - also cached by Lab128 internally on LRU basis);

Note: All joins between data from different sources are performed internally in Lab128 to offload the monitored instance from unnecessary work.