Oracle Dynamic Performance Table V$session & v$process each field description __oracle

v$session

Reference:

V$session &v$process The description of each field "reproduced"

v$session of Oracle official documents

The object described by v$session is a session .

The object described by v$session is a currently existing session (not including a session currently connected to the database, this is a session of the database foreground server process, and a session of the database daemon, which is not connected).

The following is a group description of the fields in v$session :

Used to identify a unique session:SID:serial# or saddr

SADDR: Session Address

Or

SID: Session identifier, which is commonly used to connect to other columns.

serial#: SIDs are reused, but when the same SID is reused, serial# increases and does not repeat.

Comments:

Audsid: Audit session ID.

You can query the SID of the current session through AUDSID. Select Sid from V$session where Audsid=userenv (' SessionID ');

So the value of both SID and Audsid is one by one corresponding.

Audsid use is different with SID.

Self-related properties of a session :

Command:session the SQL Id being executed. 1 represents the Create table,3 representative select.

TADDR: The current transaction address. The addr field that can be used to associate v$transaction.

Status: Used to determine session status. Active: Executing SQL statement. Inactive: Wait for operation. Killed: is marked for deletion.

Server:server type (dedicated or shared)

Server Type:

Dedicated

SHARED

PSEUDO (. ）

Pooled (. ）

NONE Comment:

when the server field in V$session is none.

type:session TYPE (background or user)

user#: Session ' s user ID. equal to the user_id in Dba_users. The user# of the Oracle internal process (that is, the background process) is 0.

 

Username:session ' s USERNAME, which indicates which database user (in the server-side database) is using the session instead of the server-side operating system's user. Equal to the username in Dba_users. The username of the Oracle internal process (that is, the background process) is empty.

schema#: Schema User ID. the schema# of Oracle internal processes is 0.

Schemaname:schema username. the SchemaName of the Oracle internal process is sys. （?? is not empty. Corresponds to username. )

The process in which a session is located:

paddr: Process address (address of the process this owns session), associating v$process addr fields, This field can be used to investigate the ID of the process for the current session's corresponding operating system.

when the Type field is background , programis the Oracle background process in the server-side database, and theType field is User . program is a client (for example, Sqlplus.exe) that is used to connect to a server-side database . The following fields except program are used to describe the related properties of The program:

Program: The name of a client application. Here is a point, we connect to the remote server through the client, and then for example, the client's Sqlplus window on the use of v$session to view, then v$session view of the program field in the value of such as ORACLE.EXE (Pmon) This is the background process that represents the instance of the server side, then the value in the Process field in the V$session view is represented as the process ID number of the operating system level of the process in the server-side operating system, and when the v$ The values in the Program field in the session view, such as Sqlplus.exe or Sqldeveloperw.exe, are the names of the programs that represent the processes from the client (that is, the replication of an application (static)), and then v$ The value in the Process field in the session view is expressed as the process ID number of the operating system level in the client operating system for these processes from the client; in summary, when theType field is background , Program is the client's user process for the Oracle background process on the server side, and theType field for user.

osuser: Client operating system user name.

 

process: The process ID of a client application at its operating system level.

 

MACHINE: Client MACHINE name. (Represents the IP address.) ) is typically the host name of the host on which the client resides.

 

TERMINAL: TERMINAL name executed by the client. That is, typically the host name of the host on which the client resides. It is not clear that the difference between the machine and the ...

Additional:

When MACHINE= computer name,TERMINAL=unkown when MACHINE= workgroup/Computer name,TERMINAL= The computer name .

Comments:

The background process for these instances like ORACLE.EXE (Pmon) is not connected to the client.

If the value in the program field in the V$session view represents a process from the server side, then the v$session view:

osuser: Service-side operating system user name.

 

process: The process ID of the server at the operating system level .

 

MACHINE: Service-side MACHINE name.

TERMINAL: TERMINAL name executed by the server. Represents the host name of the host on which the server is located.

The above several fields represent the meaning of the operating system on the server side.

If the value in the program field in the V$session view represents a process from the client, the above fields represent the meaning of the client's operating system.

Information about the SQL statements executed in the session:

Command:session the SQL Id being executed. 1 represents the Create table,3 representative select.

sql_address, Sql_hash_value, sql_id, Sql_child_number: Session executing SQL statement, and V$sql in address, hash_ Value, sql_id, and Child_number correspond.

prev_sql_addr, Prev_hash_value, prev_sql_id, prev_child_number: Last executed SQL statement.

Session properties for SQL statement tracking:

MODULE, Module_hash, ACTION, Action_hash, client_info: Some information is applied through Dbms_application_info settings.

fixed_table_sequence: When the session completes a user call, a value is added, that is, it does not increase if it is inactive. Therefore, you can monitor the performance of the session since a point in time based on the value changes of this field. For example, one hours ago, a session of the fixed_table_sequence is 10000, and now is 20000, it means that one hours of user call more frequent, you can focus on this session of the performance Statistics

lockwait: This field allows you to query for information about the locks that are currently waiting. Sid & Lockwait correspond to SIDs & KADDR in V$lock.

row_wait_obj#: The object_id of the table where the row is locked. and the OBJECT_ID Association in Dba_objects can get the locked table name.

row_wait_file#: The datafile ID in which the row is locked. The file# association with V$datafile can get datafile name.

row_wait_block#: Identifier for the "block containing" ROW specified in row_wait_row#

row_wait_row#: The locked row that the session is currently waiting for.

Logon_time: Session LOGON Time

Description

1, the generation principle and meaning of audsid field in V$session

There is a column of Audsid in Oracle's v$session view, never knowing its meaning, and seeing an article on Metalink Sessions get their audsid [ID 122230.1], The principle and meaning of audsid are described in detail, which is summarized as follows: Audsid is produced by sys.audses$ this sequence, when a new session is produced, its audsid value is the sequence of this nextval. Prior to Oracle 10g, the AUDSID value was 0 when the session was ' CONNECT INTERNAL ' or the Oracle process was the Oracle background process . 'connect INTERNAL ' means a session connected by as SYSDBA and as Sysoper. 10g and later, Audsid at ' Connect INTERNAL ' as Ub4max (4294967295), when the Oracle background process is connected to 0. Specifically as follows:

If Audsid=0, then is a session generated by the Oracle background process.

If audsid=4294967295, then description is a session generated by the SYS user.

The If 0<audsid<4294967295,then description is a session generated by a normal user.

Note: ub4=unsigned INT, Value range is 0-(2^32-1=4294967295)

As you can see, unlike the SIDs in V$session view, Audsid is primarily used to differentiate between general user connections and internal/background connections in Oracle Audsid. so for the only session to determine an application user (that is, a general user ), you can use Audsid to make the difference, in addition to using the combination of SID and Serial.

Comments:

The AUDSID is generated by the sys.audses$ this sequence (that is, the sequence, the database object), by Desc SYS. audses$ hints that he is a sequence (i.e. sequence, this database object).

2, to see a session, we first want to distinguish between the type of the session, that is, when you view the V$session view, you first see the Type field, because the field program, PROCESS, osuser, MACHINE,TERMINAL have different meanings when the type field is of different values:

when the type field is background, the above several fields represent the meaning of the operating system on the server side;

when the type field is user , the above fields represent the meaning of the client's operating system.

3. Select Type,program from V$session where type = ' strong> Background '; orSelect Audsid,program from V$session Strong>where strong> audsid =0 ; The results obtained are as follows:

Sql> select    type,program from V$session where strong> type = ' strong> background ';

strong> type                    program strong>

strong> -------                  -------------

background Oracle.exe (LGWR)

or

sql>Selec