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

Conceptually:

1.spid (system process id) is a process id at the operating system level.

2.pid (process id) is an oracle-based process id that is personally understood to be a number given by oracle to its own process.

3.sid. (this is session's id) this individual understands that someone is in a conversation with oracle, and oracle assigns it a number. Long used to join other columns.

It is also associated with several dynamic performance views.

1.v$process view

This view contains all the process information of the oracle in the current system and is often used to associate the operating system process ID of the oracle or service process with the database session.

Common columns:

ADDR: process object address

PID:oracle process ID

SPID: operating system process ID

The join column ADDR in V$PROCESS is usually connected to V$SESSION PADDR.

Such as: select P. addr, P.pid, s.paddr from v$process, P.D., P.pid, etc.

Where P.ADDR=S.PADDR

2.v$session view

V$SESSION is the basic information view that is used to find the user SID or SADDR. However, it also has some columns that change dynamically and can be used to check users.

Common columns:

SID:SESSION identity, often used to join other columns.

SERIAL#: this number increases if a SID is used by another session (when one SESSION ends, another SESSION starts and uses the same SID).

AUDSID: examine the uniqueness of session ID and confirm that it is also commonly used when looking for parallel query patterns

USERNAME: the user name of the current session in oracle.

STATUS: this column is used to determine the session status is:

Achtive: executing SQL statement (waiting for/using a resource)

Inactive: wait operation (that is, wait for the SQL statement to be executed)

Killed: marked for deletion

Paddr, process addr, through this field we can view the current process related information, system process id, operating system user information and so on.

(sql_address,sql_hash_value) (prev_sql_addr,prev_hash_value) based on these two sets of fields, we can query the details of the sql statements currently being executed by session

3.v$sqltext view

This view includes the complete text of the sql statement in the shared pool (share poll), and a sql statement may be divided into multiple quick saves.

Common columns:

Hash value of HASH_VALUE:SQL statement

The address of the ADDRESS:sql statement in SGA

SQL_TEXT:SQL text.

The serial number of the PIECE:SQL statement block

Connection columns in V$SQLTEXT

Column View Joined Column (s)

HASH_VALUE, ADDRESS V$SQL, V$SESSION HASH_VALUE, ADDRESS

HASH_VALUE. ADDRESS V$SESSION SQL_HASH_VALUE, SQL_ADDRESS

Press pid to view the executing program:

Select sid,program from v$session b where paddr in (select addr from v$process where spid=$pid)

Press pid to view the executing sql statement

Select sql_text from v$sqltext where hash_value in (select sql_hash_value from v$session where

PADDR in (select addr from v "$process where spid=$pid)) order by piece

4.V$SESSION_WAIT view

This is a key view for finding performance bottlenecks. It provides what session is currently waiting for in any case in the database (if session is not currently doing anything, its last wait event is displayed). When there is a performance problem in the system, this view can be used as a starting point to indicate the direction of exploring the problem.

In V$SESSION_WAIT, each session connected to the instance corresponds to a record.

Common columns:

SID: session identity

EVENT: the event that session is currently waiting for, or the last waiting event.

WAIT_TIME: the time session waits for the event (in 1% seconds). If this column is 0, there is no wait for the current session of session.

The SEQ#: session wait event will trigger its self-increment

P1, P2, P3: details of waiting in a waiting event

P1TEXT, P2TEXT, P3TEXT: explain the p1Magol p2jinp3 event.

Note:

The 1.State field has four meanings:

Waiting:SESSION is waiting for this event.

Waited unknown time: the time information cannot be obtained because the timed_ statistics value is set to false. Indicates that a wait has occurred, but the time

Very short

Wait short time: indicates that a wait occurred, but because the time is very short and not more than one unit of time, it is not recorded.

Waited knnow time: if session waits and then gets the required resources, it will enter this state from waiting.

Wait_ time values also have four meanings:

Value > 0: the last waiting time (in 10ms), which is not currently waiting.

Value = 0:session is waiting for the current event.

Value =-1: the last waiting time is less than 1 statistical unit and is not currently waiting.

Value =-2: the time statistics status is not set to available and is not currently waiting.

The 3.Wait_time and Second_in_wait field values are related to state:

If the state value is Waiting, then the wait_time value is useless. The Second_in_ wait value is the actual wait time in seconds.

If the state value is Wait unknow time, then both the wait_ time value and the Second_in_ wait value are useless.

If the state value is Wait short time, then both the wait_ time value and the Second_in_ wait value are useless.

If the state value is Waiting known time, then the wait_ time value is the actual wait time (in seconds), and the Second_in_wait value is useless.

Connection columns in V$SESSION_WAIT

Column View Joined Colum

SID V$SESSION SID

View session wait events:

Select sid,event from v$session_wait where event not like 'rdbms%' and event not like' SQL*Net message%'

Most session are idle events such as SQL*Net message from client, pipe get, PMON timer, etc.

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.