Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to understand the fields in v$process and v$session. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

V$process

The information provided in this view is all about the oracle service process, and there is no information related to the client program.

There are two types of service processes, one is background, the other is dedicate/shared server

Pid, serial#, this is the PID assigned by oracle.

Spid, this is the pid of the operating system.

Program this is the name of the operating system process corresponding to the service process

V$session:

This view mainly provides information about a database connect.

Mainly the information on the client side, such as the following fields:

Which machine is machine on?

What terminal does terminal use?

Who is the user of osuser operating system?

What client programs does program use, such as TOAD

The process number assigned to TOAD by the process operating system

What time is logon_time?

Under what oracle account does username log in?

What type of SQL command did command execute?

Sql_hash_value SQL statement information

There is some information on the server side:

Paddr is the addr of the server process in v$process

The server server is dedicate/shared

There is also some other information that can be understood as information shared by client/server, mainly for this session.

V$session

SADDR: session address

SID: session identifier, often used to join other columns.

SERIAL#: sid will be reused, but when the same sid is reused, serial# will increase and will not be repeated.

AUDSID: audit session id . You can query the sid of the current session through audsid. Select sid from v$session where audsid=userenv ('sessionid')

PADDR: process address, associated with the addr field of the v$process, which can be used to look up the id of the process of the current session corresponding to the operating system.

USER#: session's user id . Equal to the user_id in dba_users. The user# of the Oracle internal process is 0.

USERNAME: session's username . Equal to the username in dba_users. The username of the Oracle internal process is empty.

COMMAND: the SQL Id that session is executing. 1 stands for create table,3 and represents select.

TADDR: the current transaction address. Can be used to associate the addr field of the v$transaction.

LOCKWAIT: you can use this field to query information about the lock you are currently waiting for. Sid & lockwait corresponds to sid & kaddr in v$lock.

STATUS: used to determine the status of the session. Active: executing SQL statement. Inactive: wait for the operation. Killed: marked as deleted.

SERVER: server type (dedicated or shared)

SCHEMA#: schema user id . The schema# of the Oracle internal process is 0.

SCHEMANAME: schema username . The schemaname of the Oracle internal process is sys.

OSUSER: client operating system user name.

PROCESS: client process id.

MACHINE: client machine name.

TERMINAL: the terminal name executed by the client.

PROGRAM: client application. Such as ORACLE.EXE (PMON) or sqlplus.exe

TYPE: session type (background or user)

SQL_ADDRESS, SQL_HASH_VALUE, SQL_ID, SQL_CHILD_NUMBER: the sql statement being executed by session corresponds to address, hash_value, sql_id, and child_number in v$sql.

PREV_SQL_ADDR, PREV_HASH_VALUE, PREV_SQL_ID, PREV_CHILD_NUMBER: last executed sql statement.

MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO: apply some information set through DBMS_APPLICATION_INFO.

FIXED_TABLE_SEQUENCE: a number that increases when session completes a user call, that is, if session inactive, it does not increase. Therefore, the performance of session since a certain point in time can be monitored based on the change in the value of this field. For example, an hour ago, the FIXED_TABLE_SEQUENCE of a session was 10000, but now it is 20000, indicating that its user call is more frequent within an hour, so you can focus on the performance statistics of this session.

ROW_WAIT_OBJ#: is locked to the object_id of the table where the row is located. Associate with the object_id in dba_objects to get the locked table name.

The datafile id where the ROW_WAIT_FILE#: is locked. Associate with file# in v$datafile to get datafile name.

ROW_WAIT_BLOCK#: Identifier for the block containing the row specified in ROW_WAIT_ROW#

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

LOGON_TIME: session logon time

V$process

ADDR: process address . Can be associated with the paddr field of v$session.

PID: Oracle process identifier.

SPID: operating system process identifier.

USERNAME: the user name of the operating system process. Not an Oracle user name.

SERIAL#:: process serial number .

TERMINAL: operating system terminal identifier (e.g.computer name).

PROGRAM: the program that the process is executing (e.g.ORACLE.EXE (ARC0)), similar to program in v$session.

BACKGROUND: 1 stands for oracle background process,null and normal process.

View the sid and serial#: of the current user

Select sid, serial#, status from v$session where audsid=userenv ('sessionid')

View the spid of the current user:

Select spid from v$process p, v$session 's where s.audsid=userenv ('sessionid') and s.paddr=p.addr

Select spid from v$process p join v$session s on p.addr=s.paddr and s.audsid=userenv ('sessionid')

View the trace file path of the current user:

Select p.value | |'\'| | t.instance | |'_ ora_' | | ltrim (to_char (p.spidgramm99999')) | | '.trc'

From v$process p, v$session s, v$parameter p, v$thread t

Where p.addr = s.paddr and s.audsid = userenv ('sessionid') and p.name =' user_dump_dest'

With the spid known, view the statement that is currently being executed or last executed:

Select / * + ordered * / sql_text from v$sqltext sql

Where (sql.hash_value, sql.address) in (

Select decode (sql_hash_value, 0, prev_hash_value, sql_hash_value), decode (sql_hash_value, 0, prev_sql_addr, sql_address)

From v$session s where s.paddr = (select addr from v$process p where p.spid = to_number ('& pid')

Order by piece asc

Check locks and wait:

Col user_name format a10

Col owner format a10

Col object_name format a15

Col sid format 999999

Col serial# format 999999

Col spid format a6

Select / * + rule * / lpad ('', decode (l.xidusn, 0,3,0)) | | l.oracle_username user_name

O.owner, o.object_name, o.object_type, s.sid, s.serial#, p.spid

From v$locked_object l, dba_objects o, v$session s, v$process p

Where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr

Order by o.object_id, xidusn desc

So much for sharing on how to understand the fields in v$process and v$session. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.