学习动态视图(二) v$session

动态视图学习计划列表

今天仔仔细细看了document关于v$session的描述，有点泄气，觉得都不用整理了，oracle已经整理的相当完整清晰了。在document的基础上稍微整理了一下，加了一些常用的SQL。说真的，看文档就足够了。

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> desc v$session
 Name             Type
 ----------------------- ----------------------------
 SADDR                   RAW(8)                        session address
 
 SID                           NUMBER      
 SERIAL#                 NUMBER
 sid和serial#用于唯一标识一个session。serial#主要是用于保证命令被正确的session所执行，因为存在这样的情况，一个session结束了，后一个session使用了相同的id登录。
 
 AUDSID                  NUMBER
 PADDR                   RAW(8)                        Address of the process that owns the session
 USER#                   NUMBER
 USERNAME          VARCHAR2(30)
 COMMAND            NUMBER
 OWNERID             NUMBER
 TADDR                   VARCHAR2(16)         Address of transaction state object
 LOCKWAIT            VARCHAR2(16)         Address of lock waiting for; null if none
 STATUS                 VARCHAR2(8)
 SERVER                VARCHAR2(9)
 SCHEMA#             NUMBER
 SCHEMANAME    VARCHAR2(30)
 
 以下为Os相关信息：
 OSUSER                  VARCHAR2(30)
 PROCESS                 VARCHAR2(12)
 MACHINE                 VARCHAR2(64)
 TERMINAL                VARCHAR2(30)
 PROGRAM                 VARCHAR2(48)
 
 TYPE                    VARCHAR2(10)
 SQL_ADDRESS             RAW(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
 PREV_SQL_ADDR           RAW(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
 
Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules.
Registering the application allows system administrators and performance tuning specialists to track performance by module. System administrators can also use this information to track resource use by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views.
 MODULE                          VARCHAR2(48)
 MODULE_HASH             NUMBER
 ACTION                            VARCHAR2(32)
 ACTION_HASH               NUMBER
 CLIENT_INFO                 VARCHAR2(64)
 
 FIXED_TABLE_SEQUENCE    NUMBER
 ROW_WAIT_OBJ#           NUMBER
 ROW_WAIT_FILE#          NUMBER
 ROW_WAIT_BLOCK#      NUMBER
 ROW_WAIT_ROW#          NUMBER
 LOGON_TIME                    DATE
 LAST_CALL_ET                 NUMBER                        last_call_et 该session idle的时间, 每3秒中更新一次
 PDML_ENABLED              VARCHAR2(3)
 FAILOVER_TYPE               VARCHAR2(13)
 FAILOVER_METHOD        VARCHAR2(10)
 FAILED_OVER                   VARCHAR2(3)
 RESOURCE_CONSUMER_GROUP VARCHAR2(32)
 PDML_STATUS                 VARCHAR2(8)
 PDDL_STATUS                 VARCHAR2(8)
 PQ_STATUS                       VARCHAR2(8)
 CURRENT_QUEUE_DURATION  NUMBER
 CLIENT_IDENTIFIER       VARCHAR2(64)

1. 通过sid我们可以查询与这个session相关的各种统计信息,处理信息.
a. select * from v$sesstat where sid = :sid;
查询用户相关的各种统计信息.
select a.sid,a.statistic#,b.name,a.value
from v$sesstat a,v$statname b
where a.statistic# = b.statistic#
and a.sid = :sid;
b. 查询用户相关的各种io统计信息
select * from v$sess_io where sid = :sid;
c. 查询用户想在正在打开着的游标变量.
select * from v$open_cursor where sid = :sid;
d. 查询用户当前的等待信息. 以查看当前的语句为什么这么慢/在等待什么资源.
select * from v$session_wait where sid = :sid ;
e. 查询用户在一段时间内所等待的各种事件的信息. 以了解这个session所遇到的瓶颈^_^
select * from v$session_event where sid = :sid;
f. 还有, 就是当我们想kill当前session的时候可以通过sid,serial#来处理.
alter system kill session ':sid,:serail#';
被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.
一般情况下这个时候资源并没有被释放，只有杀掉系统进程后资源才会被释放。

2. paddr.字段, process addr, 通过这个字段我们可以查看当前进程的相关信息, 系统进程id,操作系统用户信息等等.
select a.pid,a.spid,b.name,b.description,a.latchwait,a.latchspin,a.pga_used_mem,a.pga_alloc_mem,a.pga_freeable_mem,a.pga_max_mem
from v$process a,v$bgprocess b
where a.addr = b.paddr(+)
and a.addr = :paddr

3. command 字段, 表明当前session正在执行的语句的类型.请参考reference.

4. taddr 当前事务的地址,可以通过这个字段查看当前session正在执行的事务信息, 使用的回滚段信息等^_^
select b.name rollname,a.*
from v$transaction a,v$rollname b
where a.xidusn = b.usn
and a.addr = 'C00000019460F500';

5. lockwait字段, 可以通过这个字段查询出当前正在等待的锁的相关信息.
select *
from v$lock
where (id1,id2) = (
        select id1,id2 from v$lock where kaddr = '57C68C48'
)

6. (sql_address,sql_hash_value) (prev_sql_addr,prev_hash_value) 根据这两组字段, 我们可以查询到当前session正在执行的sql语句的详细信息.

select * from v$sqltext where address = :sql_address and hash_value = :sql_hash_value;

7.ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
可以通过这几个字段查询现在正在被锁的表的相关信息.^_^
a. 首先得到被锁的的信息
select * from dba_objects where object_id = :row_wait_obj#;
b. 根据row_wait_file#可以找出对应的文件的信息.
select * from v$datafile where file# = :row_wait_file#.
c. 在根据以上四个字段构造出被锁的字段的rowid信息.
select dbms_rowid.ROWID_CREATE(1,:row_wait_obj#,:row_wait_file#,:row_wait_block#,:row_wait_row#) from dual;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/55472/viewspace-366455/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/55472/viewspace-366455/