oracel查询锁和文件IO问题

1 查询数据库锁相关

锁相关视图
$locke{d}_{o}bjectv$session v$processv$transaction
v$session$session+v$sessio{n}_{w}ait[当前状态]v$session_wait_history[最近状态]
v$session_event[启动至今]

查询锁的表

select   p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name
   from   v$process   p,v$session   a,   v$locked_object   b,all_objects   c   
   where   p.addr=a.paddr   and   a.process=b.process   and   c.object_id=b.object_id ; 

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID ;

查询锁的SQL

select b.sid, b.username, b.serial#, a.spid, b.paddr, c.sql_text, b.machine
from v$process a, v$session b, v$sqlarea c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value;

 select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

杀掉死锁进程

alter   system     kill   session   'sid,serial#';

查询当前会话sid，serial#

select userenv('SID') From DUAL;
select userenv('SESSIONID') From DUAL;

select sid,serial# from v$session where audsid = (select userenv('SESSIONID') From DUAL);
select sid,serial# from v$session where audsid=userenv('sessionid');

2 查询文件IO相关

主要视图 v$filestat
字段注释
FILE#：文件序号；
PHYRDS：已完成的物理读次数；
PHYBLKRD：块读取数；
PHYWRTS：DBWR完成的物理写次数；
PHYBLKWRT：写入磁盘的块数；

文件IO 查询

select df.tablespace_name name,df.file_name "ile",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
f.phyblkwrt pbw 
from v$filestat f,dba_data_files df
where f.file#=df.file_id;

监控表空间的I/O比例：

select df.tablespace_name name,df.file_name "ile",f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,
f.phyblkwrt pbw 
from v$filestat f,dba_data_files df
where f.file#=df.file_id;

监空文件系统的I/O比例：

select substr(a.file#,1,2) "#",substr(a.name,1,30) "name",a.status,a.bytes,
b.phyrds,b.phywrts
from v$datafile a,v$filestat b
where a.file#=b.file#;