溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這期內容當中小編將會給大家帶來有關如何進行session和v$session說明，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。
1、前言
v$session和v$process兩個視圖作為數據庫管理了解系統性能、分析系統原因最常用的視圖，因此作為一名非菜鳥的DBA管理員，就需要對這兩個視圖有充分的了解；
2、概念
首先必須了解什么是session：通俗來講，session 是通信雙方從開始通信到通信結束期間的一個上下文(context)。這個上下文是一段位于服務器端的內存：記錄了本次連接的客戶端機器、通過哪個應用程序、哪個用戶在登錄等信息。session 是和connection同時建立的，兩者是對同一件事情不同層次的描述。簡單講，connection是物理上的客戶機同服務器段的通信鏈路，session是邏輯上的用戶同服務器的通信交互。
oracle中一個用戶登錄oracle服務器的前提，就是該用戶具有oracle的 “create session”權限。oracle允許同一個用戶在同一個客戶機上建立多個同服務器的連接，這一點從oracle的視圖V$session中可以看到[select * from v$session;]。每個session都代表了用戶與服務器的一個交互。就像兩個國家之間可以同時開展很多談判，經濟的，環境的等等。關閉了有關經濟的談判，不會影響到環境談判的進行。后臺進程PMON會每隔一段時間，就會測試用戶連接狀況，如果連接已斷開，PMON會清理現場，釋放相關的資源。
1. sqlplus 登錄 oracle
這種場景比較容易理解，一個連接對應一個session。
2. 其他客戶端工具登錄oracle
比如：pl/sql developer 登錄oracle。pl/sql developer 可以設置是否每個窗口共用同一個session. 如果想在調試窗口調試存儲過程或函數，則必須設置為共享session。如果設置為非共享，則每次打開一個操作窗口，pl/sql developer 會利用最初輸入的帳戶和口令建立新的connection 和 session.
3. IIS 用程序登錄oracle
這種情況下，其實是IIS在登錄oracle。connection 和 session 的建立情況和iis機制相關。
“對于Oracle來說，安全的Sessions數應該為Sessions = (IIS process number) * (min pool size)。”
IIS進程：在IIS6.0中，采用了新的進程隔離模式來響應用戶的請求，在IIS管理器中，可以設置應用程序池的最大進程數。對于新的WEB應用請求，IIS進程管理器會啟動多個W3wp.exe進行響應。
4、java配置連接池
通過在應用配置java的腳本的時候，可以配置相應的session的數量，這樣應用連接過來就默認分配了相應的session，而且這些session不管有沒有連接都不會被PMON清除掉，減少了數據庫的開銷。
3、v$session視圖的說明
數據庫的所有session提供了相應的視圖v$session，為所有的dba提供了一個查詢和管理的窗口，那么就有必要了解下v$session的相關信息；
Column | Datatype | Description | 說明 |
SADDR | RAW(4 | 8) | Session address |
|
SID | NUMBER | Session identifier | 通過這兩個值來確定唯一的一個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,關聯v$process的addr字段 |
USER# | NUMBER | Oracle user identifier | 同dba_users關聯 |
USERNAME | VARCHAR2(30) | Oracle username | 同dba_users |
COMMAND | NUMBER | Command in progress (last statement parsed). | session正在執行的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 | 當前的transaction address。可以用來關聯v$transaction中的addr字段 |
LOCKWAIT | VARCHAR2(8) | Address of the lock the session is waiting for; NULL if none | 可以通過這個字段查詢出當前正在等待的鎖的相關信息。sid + lockwait與v$loc中的sid + kaddr相對應。 |
STATUS | VARCHAR2(8) | Status of the session: | 用來判斷session狀態。Active:正執行SQL語句。inactive:等待操作。killed:被標注為殺死 |
| |||
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: | 服務類型（一般專用類型） |
| |||
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 | 客戶端操作系統用戶名 |
PROCESS | VARCHAR2(24) | Operating system client process ID | 客戶端process id |
MACHINE | VARCHAR2(64) | Operating system machine name | 客戶端machine name |
PORT | NUMBER | Client port number | 客戶端的端口號 |
TERMINAL | VARCHAR2(30) | Operating system terminal name | 客戶端執行的terminal name |
PROGRAM | VARCHAR2(48) | Operating system program name | 客戶端應用程序 |
TYPE | VARCHAR2(10) | Session type | 用戶進程還是后臺進程，后臺進程一般不能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 | 當前被執行的SQL語句，跟v$sql關聯 |
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 | 當前sql的開始運行時間 |
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 | 剛被執行的SQL語句，從v$sql查詢相應的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 | 等待事件的編號跟v$session_wait關聯 |
EVENT | VARCHAR2(64) | Resource or event for which the session is waiting | 等待事件的解釋跟v$session_wait關聯 |
See Also: Appendix C, "Oracle Wait Events" | |||
P1TEXT | VARCHAR2(64) | Description of the first wait event parameter | 對應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 |
| ||
-1 - Duration of the last wait was less than a hundredth of a second |
| ||
-2 - Parameter TIMED_STATISTICS was set to false |
| ||
|
| ||
This column has been deprecated in favor of the columnsWAIT_TIME_MICRO and STATE. |
| ||
SECONDS_IN_WAIT | NUMBER | If the session is currently waiting, then the value is the amount of time waited for the current wait. If the session is not in a wait, then the value is the amount of time since the start of the last wait. |
|
This column has been deprecated in favor of the columnsWAIT_TIME_MICRO and TIME_SINCE_LAST_WAIT_MICRO. |
| ||
STATE | VARCHAR2(19) | Wait state: |
|
|
| ||
WAITING - Session is currently waiting | 當前session處于等待 | ||
WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false |
| ||
WAITED SHORT TIME - Last wait was less than a hundredth of a second |
| ||
WAITED KNOWN TIME - Duration of the last wait is specified in theWAIT_TIME column |
| ||
|
| ||
WAIT_TIME_MICRO | NUMBER | Amount of time waited (in microseconds). If the session is currently waiting, then the value is the time spent in the current wait. If the session is currently not in a wait, then the value is the amount of time waited in the last wait. |
|
TIME_REMAINING_MICRO | NUMBER | Value is interpreted as follows: |
|
|
| ||
> 0 - Amount of time remaining for the current wait (in microseconds) |
| ||
0 - Current wait has timed out |
| ||
-1 - Session can indefinitely wait in the current wait |
| ||
NULL - Session is not currently waiting |
| ||
|
| ||
TIME_SINCE_LAST_WAIT_MICRO | NUMBER | Time elapsed since the end of the last wait (in microseconds). If the session is currently in a wait, then the value is 0. |
|
SERVICE_NAME | VARCHAR2(64) | Service name of the session |
|
SQL_TRACE | VARCHAR2(8) | Indicates whether SQL tracing is enabled (ENABLED) or disabled (DISABLED) | 是否打開跟蹤 |
SQL_TRACE_WAITS | VARCHAR2(5) | Indicates whether wait tracing is enabled (TRUE) or not (FALSE) |
|
SQL_TRACE_BINDS | VARCHAR2(5) | Indicates whether bind tracing is enabled (TRUE) or not (FALSE) |
|
SQL_TRACE_PLAN_STATS | VARCHAR2(10) | Frequency at which row source statistics are dumped in the trace files for each cursor: |
|
|
| ||
never |
| ||
first_execution |
| ||
all_executions |
| ||
|
| ||
SESSION_EDITION_ID | NUMBER | Shows the value that, in the session, would be reported bysys_context('USERENV', 'SESSION_EDITION_ID') |
|
CREATOR_ADDR | RAW(4 | 8) | Address of the creating process or circuit |
|
CREATOR_SERIAL# | NUMBER | Serial number of the creating process or circuit |
|
ECID | VARCHAR2(64) | Execution context identifier (sent by Application Server) |
|
以上只是標注了常用的字段的注釋，如果漏掉的也請各位高手補充，后續隨著工作的深入也會相應的補充；
4、v$session在實際工作中的作用
4.1查詢鎖住對象的會話信息：
SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL#
FROM GV$LOCKED_OBJECT L, DBA_OBJECTS O, GV$SESSION S
WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID;
4.2查看當前會話所執行的語句以及會話相關信息：
SELECT A.SID, A.SERIAL#, A.USERNAME, A.TERMINAL, A.PROGRAM, S.SQL_TEXT
FROM V$SESSION A, V$SQLAREA S WHERE A.SQL_ADDRESS = S.ADDRESS(+)
AND A.SQL_HASH_VALUE = S.HASH_VALUE(+) ORDER BY A.USERNAME, A.SID;
4.3殺掉會話：
ALTER SYSTEM KILL SESSION 'SID, #SERIAL'; （需要注意當前的session tpye不能是background)
上述就是小編為大家分享的如何進行session和v$session說明了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
