Learning Dynamic Performance Table 5-V $ session

Learning Dynamic Performance Table

Article 5-- V $ session

In this view, each session connected to the database instance has a record. Including user sessions and background processes such as dbwr, lgwr, and arcchiver.

V $ sessionCommon columns in

 

V $ session is the basic information view used to find the user Sid or saddr. However, it also has some columns that dynamically change and can be used to check users. Example:

SQL _hash_value, SQL _address: These two columns are used to identify the SQL statements executed by the default session. If it is null or 0, it indicates that this session has not executed any SQL statements. The prev_hash_value and prev_address columns are used to identify the previous statement executed by the session.

Note: When using SQL * Plus for selection, make sure that the column width you re-defined is not less than 11 to see the complete value.

Status: This column is used to determine the session Status:

L achtive: SQL statement being executed (waiting for/using a resource)

L inactive: waiting for the operation (that is, waiting for the SQL statement to be executed)

L killed: marked as deleted

The following columns provide session information, which can be used to locate a session when one or more combination operations are unknown.

SessionInformation

L Sid: Session ID, which is often used to connect other columns

L serial #: If a Sid is used by another session, the value is automatically increased (when one session ends, the other session starts and uses the same SID ).

L audsid: Reviews session ID uniqueness and confirms that it is usually used when looking for a parallel query mode

L Username: the username of the current session in Oracle.

ClientInformation

The database session is started by a client process running on the database server or connecting to the database from the intermediate server or even the desktop through SQL * Net. The following lists the client information.

L osuser: User Name of the client operating system

L machine: The machine executed by the client

L terminal: terminal running on the client

L process: ID of the client process

L program: Client Program executed by the client

To display the terminal and osuser of the connected pc, you need to set the keyword terminal and username in the Oracle. ini or windows of the PC.

ApplicationInformation

Call the dbms_application_info package to set some information to distinguish users. This will show the following columns.

L client_info: Set in dbms_application_info

L action: Set in dbms_application_info

L module: Set in dbms_application_info

The following v $ session columns may also be used:

L row_wait_obj #

L row_wait_file #

L row_wait_block #

L row_wait_row #

V $ sessionConnection column in

Column view joined column (s)

Sid v $ session_wait, V $ sesstat, V $ lock, V $ session_event, V $ open_cursor Sid

(SQL _hash_value, SQL _address) V $ sqltext, V $ sqlarea, V $ SQL (hash_value, address)

(Prev_hash_value, prev_ SQL _address) V $ sqltext, V $ sqlarea, V $ SQL (hash_value, address)

Taddr v $ transaction ADDR

Paddr v $ process ADDR

Example:

1. Find your session information

Select Sid, osuser, username, machine, Process

From v $ session where audsid = userenv ('sessionid ');

2. Search for sessions when the machine is known

Select Sid, osuser, username, machine, Terminal

From v $ session

Where Terminal = 'pts/TL 'and machine = 'rgmdbs1 ';

3. Find the SQL statement currently running by a specified session. Assume that sessionid is 100

Select B. SQL _text

From v $ session A, V $ sqlarea B

Where a. SQL _hash_value = B. hash_valueand A. Sid = 100

Searching for the SQL statement executed by the specified session is a public requirement. If the session is the main cause of the bottleneck, you can view what the session is doing based on the statement currently being executed.