引言: 从10g开始,伴随ASH功能的引入,对V$SESSION视图进行了彻底改造。
从Oracle10gR1开始,Oracle在V$SESSION中增加关于等待事件的字段,实际上也就是把原来V$SESSION_WAIT视图中的所有字段全部整合到了V$SESSION视图中,开始的时候我还以为ASH是依赖联合查询来获取信息的,仔细一看才发现现在V$SESSION已经发生了变化。(如果进一步研究你会发现,实际上V$SESSION的底层查询语句及X$表已经有了变化)
这一变化使得我们的查询得以简化,但是也使得V$SESSION_WAIT开始变得多余,此外V$SESSION中还增加了BLOCKING_SESSION等字段,以前我们需要通dba_waiters等视图才能获得的信息,现在也可以直接从V$SESSION中得到了。既然这样,让我们好好研究V$SESSION视图中每个字段的含义。
首先,请看官方文档上面关于V$SESSION字段的说明:
v$session:This view has one row for every session connected to the database instance. The sessions include user sessions, as well as background processes like DBWR, LGWR, archiver.
下面为每个字段的详细说明。
Datatype |
Description |
|
|
|
Session address |
|
|
Session identifier |
|
|
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. |
|
|
Auditing session ID |
|
|
Address of the process that owns the session |
|
|
Oracle user identifier |
|
|
Oracle username |
|
|
Command in progress (last statement parsed); for a list of values, see Table 7-5. These values also appear in the |
|
|
The column contents are invalid if the value is For operations using Parallel Slaves, interpret this value as a 4-byte value. The low-order 2 bytes of which represent the session number, and the high-order bytes the instance ID of the query coordinator. |
|
|
Address of transaction state object |
|
|
Address of lock waiting for; null if none |
|
|
Status of the session: · · · · · |
|
|
Server type ( |
|
|
Schema user identifier |
|
|
Schema user name |
|
|
Operating system client user name |
|
|
Operating system client process ID |
|
|
Operating system machine name |
|
|
Operating system terminal name |
|
|
Operating system program name |
|
|
Session type |
|
|
Used with |
|
|
Used with |
|
|
SQL identifier of the SQL statement that is currently being executed |
|
|
Child number of the SQL statement that is currently being executed |
|
|
Used with |
|
|
Used with |
|
|
SQL identifier of the last SQL statement executed |
|
|
Child number of the last SQL statement executed |
|
|
Name of the currently executing module as set by calling the |
|
|
Hash value of the above |
|
|
Name of the currently executing action as set by calling the |
|
|
Hash value of the above action name |
|
|
Information set by the |
|
|
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. |
|
|
Object ID for the table containing the row specified in |
|
|
Identifier for the datafile containing the row specified in |
|
|
Identifier for the block containing the row specified in |