如何進(jìn)行session和v$session說明
這期內(nèi)容當(dāng)中小編將會(huì)給大家?guī)碛嘘P(guān)如何進(jìn)行session和v$session說明，文章內(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)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
1、前言
v$session和v$process兩個(gè)視圖作為數(shù)據(jù)庫管理了解系統(tǒng)性能、分析系統(tǒng)原因最常用的視圖，因此作為一名非菜鳥的DBA管理員，就需要對(duì)這兩個(gè)視圖有充分的了解；
2、概念
首先必須了解什么是session：通俗來講，session 是通信雙方從開始通信到通信結(jié)束期間的一個(gè)上下文(context)。這個(gè)上下文是一段位于服務(wù)器端的內(nèi)存：記錄了本次連接的客戶端機(jī)器、通過哪個(gè)應(yīng)用程序、哪個(gè)用戶在登錄等信息。session 是和connection同時(shí)建立的，兩者是對(duì)同一件事情不同層次的描述。簡單講，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è)國家之間可以同時(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ì)測試用戶連接狀況，如果連接已斷開，PMON會(huì)清理現(xiàn)場，釋放相關(guān)的資源。
1. sqlplus 登錄 oracle
這種場景比較容易理解，一個(gè)連接對(duì)應(yīng)一個(gè)session。
2. 其他客戶端工具登錄oracle
比如：pl/sql developer 登錄oracle。pl/sql developer 可以設(shè)置是否每個(gè)窗口共用同一個(gè)session. 如果想在調(diào)試窗口調(diào)試存儲(chǔ)過程或函數(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來說，安全的Sessions數(shù)應(yīng)該為Sessions = (IIS process number) * (min pool size)?！?/p>
IIS進(jìn)程：在IIS6.0中，采用了新的進(jìn)程隔離模式來響應(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配置連接池
通過在應(yīng)用配置java的腳本的時(shí)候，可以配置相應(yīng)的session的數(shù)量，這樣應(yīng)用連接過來就默認(rèn)分配了相應(yīng)的session，而且這些session不管有沒有連接都不會(huì)被PMON清除掉，減少了數(shù)據(jù)庫的開銷。
3、v$session視圖的說明
數(shù)據(jù)庫的所有session提供了相應(yīng)的視圖v$session，為所有的dba提供了一個(gè)查詢和管理的窗口，那么就有必要了解下v$session的相關(guān)信息；
Column | Datatype | Description | 說明 |
SADDR | RAW(4 | 8) | Session address |
|
SID | NUMBER | Session identifier | 通過這兩個(gè)值來確定唯一的一個(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. |
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。可以用來關(guān)聯(lián)v$transaction中的addr字段 |
LOCKWAIT | VARCHAR2(8) | Address of the lock the session is waiting for; NULL if none | 可以通過這個(gè)字段查詢出當(dāng)前正在等待的鎖的相關(guān)信息。sid + lockwait與v$loc中的sid + kaddr相對(duì)應(yīng)。 |
STATUS | VARCHAR2(8) | Status of the session: | 用來判斷session狀態(tài)。Active:正執(zhí)行SQL語句。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語句，跟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語句，從v$sql查詢相應(yīng)的SQL語句 |
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) | This column has been replaced by the PDML_STATUS column |
|
FAILOVER_TYPE | VARCHAR2(13) | Indicates whether and to what extent transparent application failover (TAF) is enabled for the session: |
|
|
| ||
NONE - Failover is disabled for this session |
| ||
SESSION - Client is able to fail over its session following a disconnect |
| ||
SELECT - Client is able to fail over queries in progress as well |
| ||
|
| ||
See Also: |
| ||
|
| ||
Oracle Database Concepts for more information on TAF |
| ||
Oracle Database Net Services Administrator's Guide for information on configuring TAF |
| ||
|
| ||
FAILOVER_METHOD | VARCHAR2(10) | Indicates the transparent application failover method for the session: |
|
|
| ||
NONE - Failover is disabled for this session |
| ||
BASIC - Client itself reconnects following a disconnect |
| ||
PRECONNECT - Backup instance can support all connections from every instance for which it is backed up |
| ||
|
| ||
FAILED_OVER | VARCHAR2(3) | Indicates whether the session is running in failover mode and failover has occurred (YES) or not (NO) |
|
RESOURCE_CONSUMER_GROUP | VARCHAR2(32) | Name of the session's current resource consumer group |
|
PDML_STATUS | VARCHAR2(8) | If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED,PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML. |
|
PDDL_STATUS | VARCHAR2(8) | If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED,PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL. |
|
PQ_STATUS | VARCHAR2(8) | If ENABLED, the session is in a PARALLEL QUERY enabled mode. IfDISABLED, PARALLEL QUERY enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLELQUERY. |
|
CURRENT_QUEUE_DURATION | NUMBER | If queued (1), the current amount of time the session has been queued. If not currently queued, the value is 0. |
|
CLIENT_IDENTIFIER | VARCHAR2(64) | Client identifier of the session |
|
BLOCKING_SESSION_STATUS | VARCHAR2(11) | This column provides details on whether there is a blocking session: |
|
|
| ||
VALID - there is a blocking session, and it is identified in theBLOCKING_INSTANCE and BLOCKING_SESSION columns |
| ||
NO HOLDER - there is no session blocking this session |
| ||
NOT IN WAIT - this session is not in a wait |
| ||
UNKNOWN - the blocking session is unknown |
| ||
|
| ||
BLOCKING_INSTANCE | NUMBER | Instance identifier of the blocking session. This column is valid only ifBLOCKING_SESSION_STATUS has the value VALID. |
|
BLOCKING_SESSION | NUMBER | Session identifier of the blocking session. This column is valid only ifBLOCKING_SESSION_STATUS has the value VALID. |
|
FINAL_BLOCKING_SESSION_STATUS | VARCHAR2(11) | The final blocking session is the final element in the wait chain constructed by following the sessions that are blocked by one another starting with this session. In the case of a cyclical wait chain, one of the sessions in the wait chain will be chosen as the final blocker. |
|
This column provides details on whether there is a final blocking session: |
| ||
|
| ||
VALID - there is a final blocking session and it is identified in theFINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION columns |
| ||
NO HOLDER - there is no session blocking this session |
| ||
NOT IN WAIT - this session is not in a wait |
| ||
UNKNOWN - the final blocking session is unknown |
| ||
|
| ||
FINAL_BLOCKING_INSTANCE | NUMBER | Instance identifier of the final blocking session. This column is valid only ifFINAL_BLOCKING_SESSION_STATUS has the value VALID. |
|
FINAL_BLOCKING_SESSION | NUMBER | Session identifier of the blocking session. This column is valid only ifFINAL_BLOCKING_SESSION_STATUS has the value VALID. |
|
SEQ# | NUMBER | A number that uniquely identifies the current or last wait (incremented for each wait) |
|
EVENT# | NUMBER | Event number | 等待事件的編號(hào)跟v$session_wait關(guān)聯(lián) |
EVENT | VARCHAR2(64) | Resource or event for which the session is waiting | 等待事件的解釋跟v$session_wait關(guān)聯(lián) |
See Also: Appendix C, "Oracle Wait Events" | |||
P1TEXT | VARCHAR2(64) | Description of the first wait event parameter | 對(duì)應(yīng)DBA_EXTENTS的FILE_ID,BLOCK_ID,BLOCKS |
P1 | NUMBER | First wait event parameter (in decimal) | |
P1RAW | RAW(8) | First wait event parameter (in hexadecimal)Foot 2 | |
P2TEXT | VARCHAR2(64) | Description of the second wait event parameter | |
P2 | NUMBER | Second wait event parameter (in decimal) | |
P2RAW | RAW(8) | Second wait event parameter (in hexadecimal)Footref 2 | |
P3TEXT | VARCHAR2(64) | Description of the third wait event parameter | |
P3 | NUMBER | Third wait event parameter (in decimal) | |
P3RAW | RAW(8) | Third wait event parameter (in hexadecimal)Footref 2 |
|
WAIT_CLASS_ID | NUMBER | Identifier of the class of the wait event |
|
WAIT_CLASS# | NUMBER | Number of the class of the wait event |
|
WAIT_CLASS | VARCHAR2(64) | Name of the class of the wait event |
|
WAIT_TIME | NUMBER | If the session is currently waiting, then the value is 0. If the session is not in a wait, then the value is as follows: |
|
|
| ||
> 0 - Value is the duration of the last wait in hundredths of a second |
標(biāo)題名稱：如何進(jìn)行session和v$session說明
分享鏈接：http://jinyejixie.com/article48/jjjsep.html 成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營銷推廣、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站排名、網(wǎng)站設(shè)計(jì)、網(wǎng)站設(shè)計(jì)公司、定制開發(fā) 聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián) 猜你還喜歡下面的內(nèi)容 
|