如何進(jìn)行session和v$session說(shuō)明

這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)?lái)有關(guān)如何進(jìn)行session和v$session說(shuō)明，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

專注于為中小企業(yè)提供成都做網(wǎng)站、成都網(wǎng)站制作服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)泗縣免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了近1000家企業(yè)的穩(wěn)健成長(zhǎng)，幫助中小企業(yè)通過(guò)網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。

1、前言

v$session和v$process兩個(gè)視圖作為數(shù)據(jù)庫(kù)管理了解系統(tǒng)性能、分析系統(tǒng)原因最常用的視圖，因此作為一名非菜鳥的DBA管理員，就需要對(duì)這兩個(gè)視圖有充分的了解；

2、概念

首先必須了解什么是session：通俗來(lái)講，session 是通信雙方從開始通信到通信結(jié)束期間的一個(gè)上下文(context)。這個(gè)上下文是一段位于服務(wù)器端的內(nèi)存：記錄了本次連接的客戶端機(jī)器、通過(guò)哪個(gè)應(yīng)用程序、哪個(gè)用戶在登錄等信息。session 是和connection同時(shí)建立的，兩者是對(duì)同一件事情不同層次的描述。簡(jiǎn)單講，connection是物理上的客戶機(jī)同服務(wù)器段的通信鏈路，session是邏輯上的用戶同服務(wù)器的通信交互。

oracle中一個(gè)用戶登錄oracle服務(wù)器的前提，就是該用戶具有oracle的 “create session”權(quán)限。oracle允許同一個(gè)用戶在同一個(gè)客戶機(jī)上建立多個(gè)同服務(wù)器的連接，這一點(diǎn)從oracle的視圖V$session中可以看到[select * from v$session;]。每個(gè)session都代表了用戶與服務(wù)器的一個(gè)交互。就像兩個(gè)國(guó)家之間可以同時(shí)開展很多談判，經(jīng)濟(jì)的，環(huán)境的等等。關(guān)閉了有關(guān)經(jīng)濟(jì)的談判，不會(huì)影響到環(huán)境談判的進(jìn)行。后臺(tái)進(jìn)程PMON會(huì)每隔一段時(shí)間，就會(huì)測(cè)試用戶連接狀況，如果連接已斷開，PMON會(huì)清理現(xiàn)場(chǎng)，釋放相關(guān)的資源。

1. sqlplus 登錄 oracle

這種場(chǎng)景比較容易理解，一個(gè)連接對(duì)應(yīng)一個(gè)session。

2. 其他客戶端工具登錄oracle

比如：pl/sql developer 登錄oracle。pl/sql developer 可以設(shè)置是否每個(gè)窗口共用同一個(gè)session. 如果想在調(diào)試窗口調(diào)試存儲(chǔ)過(guò)程或函數(shù)，則必須設(shè)置為共享session。如果設(shè)置為非共享，則每次打開一個(gè)操作窗口，pl/sql developer 會(huì)利用最初輸入的帳戶和口令建立新的connection 和 session.

3. IIS 用程序登錄oracle

這種情況下，其實(shí)是IIS在登錄oracle。connection 和 session 的建立情況和iis機(jī)制相關(guān)。

“對(duì)于Oracle來(lái)說(shuō)，安全的Sessions數(shù)應(yīng)該為Sessions = (IIS process number) * (min pool size)。”

IIS進(jìn)程：在IIS6.0中，采用了新的進(jìn)程隔離模式來(lái)響應(yīng)用戶的請(qǐng)求，在IIS管理器中，可以設(shè)置應(yīng)用程序池的最大進(jìn)程數(shù)。對(duì)于新的WEB應(yīng)用請(qǐng)求，IIS進(jìn)程管理器會(huì)啟動(dòng)多個(gè)W3wp.exe進(jìn)行響應(yīng)。

4、java配置連接池

通過(guò)在應(yīng)用配置java的腳本的時(shí)候，可以配置相應(yīng)的session的數(shù)量，這樣應(yīng)用連接過(guò)來(lái)就默認(rèn)分配了相應(yīng)的session，而且這些session不管有沒(méi)有連接都不會(huì)被PMON清除掉，減少了數(shù)據(jù)庫(kù)的開銷。

3、v$session視圖的說(shuō)明

數(shù)據(jù)庫(kù)的所有session提供了相應(yīng)的視圖v$session，為所有的dba提供了一個(gè)查詢和管理的窗口，那么就有必要了解下v$session的相關(guān)信息；

Column

Datatype

Description

說(shuō)明

SADDR

RAW(4 | 8)

Session address

SID

NUMBER

Session identifier

通過(guò)這兩個(gè)值來(lái)確定唯一的一個(gè)session

SERIAL#

NUMBER

Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID.

AUDSID

NUMBER

Auditing session ID

If AUDSID=0, then it is an internally generated SYS session.

If AUDSID=UB4MAX(4294967295), then it is a direct SYS session.

PADDR

RAW(4 | 8)

Address of the process that owns the session

process address,關(guān)聯(lián)v$process的addr字段

USER#

NUMBER

Oracle user identifier

同dba_users關(guān)聯(lián)

USERNAME

VARCHAR2(30)

Oracle username

同dba_users

COMMAND

NUMBER

Command in progress (last statement parsed).

session正在執(zhí)行的sql id，1代表create table,3代表select

You can find the command name for any value n returned in thisCOMMAND column by running this SQL query:

SELECT command_name

FROM v$sqlcommand

WHERE command_type = n;

A value of 0 in this COMMAND column means the command is not recorded in V$SESSION.

OWNERID

NUMBER

Identifier of the user who owns the migratable session; the column contents are invalid if the value is 2147483644

For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes represent the session number and the high-order bytes represent the instance ID of the query coordinator.

TADDR

VARCHAR2(8)

Address of the transaction state object

當(dāng)前的transaction address?？梢杂脕?lái)關(guān)聯(lián)v$transaction中的addr字段

LOCKWAIT

VARCHAR2(8)

Address of the lock the session is waiting for; NULL if none

可以通過(guò)這個(gè)字段查詢出當(dāng)前正在等待的鎖的相關(guān)信息。sid + lockwait與v$loc中的sid + kaddr相對(duì)應(yīng)。

STATUS

VARCHAR2(8)

Status of the session:

用來(lái)判斷session狀態(tài)。Active:正執(zhí)行SQL語(yǔ)句。inactive:等待操作。killed:被標(biāo)注為殺死

ACTIVE - Session currently executing SQL

INACTIVE - Session which is inactive and either has no configured limits or has not yet exceeded the configured limits

KILLED - Session marked to be killed

CACHED - Session temporarily cached for use by Oracle*XA

SNIPED - An inactive session that has exceeded some configured limits (for example, resource limits specified for the resource manager consumer group or idle_time specified in the user's profile). Such sessions will not be allowed to become active again.

SERVER

VARCHAR2(9)

Server type:

服務(wù)類型（一般專用類型）

DEDICATED

SHARED

PSEUDO

POOLED

NONE

SCHEMA#

NUMBER

Schema user identifier

跟USER#一致

SCHEMANAME

VARCHAR2(30)

Schema user name

跟USERNAME一致

OSUSER

VARCHAR2(30)

Operating system client user name

客戶端操作系統(tǒng)用戶名

PROCESS

VARCHAR2(24)

Operating system client process ID

客戶端process id

MACHINE

VARCHAR2(64)

Operating system machine name

客戶端machine name

PORT

NUMBER

Client port number

客戶端的端口號(hào)

TERMINAL

VARCHAR2(30)

Operating system terminal name

客戶端執(zhí)行的terminal name

PROGRAM

VARCHAR2(48)

Operating system program name

客戶端應(yīng)用程序

TYPE

VARCHAR2(10)

Session type

用戶進(jìn)程還是后臺(tái)進(jìn)程，后臺(tái)進(jìn)程一般不能KILL

SQL_ADDRESS

RAW(4 | 8)

Used with SQL_HASH_VALUE to identify the SQL statement that is currently being executed

SQL_HASH_VALUE

NUMBER

Used with SQL_ADDRESS to identify the SQL statement that is currently being executed

SQL_ID

VARCHAR2(13)

SQL identifier of the SQL statement that is currently being executed

當(dāng)前被執(zhí)行的SQL語(yǔ)句，跟v$sql關(guān)聯(lián)

SQL_CHILD_NUMBER

NUMBER

Child number of the SQL statement that is currently being executed

SQL_EXEC_START

DATE

Time when the execution of the SQL currently executed by this session started; NULL if SQL_ID is NULL

當(dāng)前sql的開始運(yùn)行時(shí)間

SQL_EXEC_ID

NUMBER

SQL execution identifier; NULL if SQL_ID is NULL or if the execution of that SQL has not yet started (see V$SQL_MONITOR)

PREV_SQL_ADDR

RAW(4 | 8)

Used with PREV_HASH_VALUE to identify the last SQL statement executed

PREV_HASH_VALUE

NUMBER

Used with SQL_HASH_VALUE to identify the last SQL statement executed

PREV_SQL_ID

VARCHAR2(13)

SQL identifier of the last SQL statement executed

剛被執(zhí)行的SQL語(yǔ)句，從v$sql查詢相應(yīng)的SQL語(yǔ)句

PREV_CHILD_NUMBER

NUMBER

Child number of the last SQL statement executed

PREV_EXEC_START

DATE

SQL execution start of the last executed SQL statement

PREV_EXEC_ID

NUMBER

SQL execution identifier of the last executed SQL statement

PLSQL_ENTRY_OBJECT_ID

NUMBER

Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack

PLSQL_ENTRY_SUBPROGRAM_ID

NUMBER

Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack

PLSQL_OBJECT_ID

NUMBER

Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL

PLSQL_SUBPROGRAM_ID

NUMBER

Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL

MODULE

VARCHAR2(48)

Name of the currently executing module as set by calling theDBMS_APPLICATION_INFO.SET_MODULE procedure

MODULE_HASH

NUMBER

Hash value of the MODULE column

ACTIONFootref 1

VARCHAR2(32)

Name of the currently executing action as set by calling theDBMS_APPLICATION_INFO.SET_ACTION procedure

ACTION_HASH

NUMBER

Hash value of the ACTION column

CLIENT_INFO

VARCHAR2(64)

Information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFOprocedure

FIXED_TABLE_SEQUENCE

NUMBER

This contains a number that increases every time the session completes a call to the database and there has been an intervening select from a dynamic performance table. This column can be used by performance monitors to monitor statistics in the database. Each time the performance monitor looks at the database, it only needs to look at sessions that are currently active or have a higher value in this column than the highest value that the performance monitor saw the last time. All the other sessions have been idle since the last time the performance monitor looked at the database.

ROW_WAIT_OBJ#

NUMBER

Object ID for the table containing the row specified in ROW_WAIT_ROW#

ROW_WAIT_FILE#

NUMBER

Identifier for the datafile containing the row specified inROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not -1.

ROW_WAIT_BLOCK#

NUMBER

Identifier for the block containing the row specified in ROW_WAIT_ROW#. This column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.

ROW_WAIT_ROW#

NUMBER

Current row being locked. This column is valid only if the session is currently waiting for another transaction to commit and the value ofROW_WAIT_OBJ# is not -1.

TOP_LEVEL_CALL#

NUMBER

Oracle top level call number

LOGON_TIME

DATE

Time of logon

LAST_CALL_ET

NUMBER

If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.

If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.

PDML_ENABLED

VARCHAR2(3)