学习笔记

--查看正在运行的占I/O较大的Session
select se.sid,se.serial#,pr.spid,si.physical_reads,
si.block_changes,se.username,se.status,se.terminal,se.program,
se.module,se.sql_address,st.event,st.p1text
from v$session se,v$session_wait st,v$sess_io si,v$process pr
where st.sid=se.sid and st.sid=si.sid and se.paddr=pr.addr
and se.sid>6 and st.wait_time=0 and st.event not like '%SQL%'
order by physical_reads desc

--查看使用CPU较多的用户Session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal ,osuser,
value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11   --11 表示CPU资源
and c.sid=a.sid and a.paddr=b.addr order by value desc

--查看正在使用回滚段的会话
select r.name rollback_segment,s.sid,s.serial#,s.username,
t.status,t.cr_get,t.phy_io,t.used_ublk,t.noundo,
substr(s.program,1,78) operating_program
from sys.v_$session s ,sys.v_$transaction t, sys.v_$rollname r
where t.addr=s.taddr and t.xidusn=r.usn order by t.cr_get,t.phy_io

--查看正在使用的临时段的会话
select se.username,sid,serial#,sql_address,machine,program,tablespace,
segtype,contents
from v$session se,v$sort_usage su
where se.saddr=su.session_addr

--查看所有进程属性可用V$process视图该视图包含当前系统Oracle运行的所有进程信息。常用于将Oracle或服务进程的操作系统进程ID与数据库Session之间建立联系。addr列是进程对像地址，PId为Oracle进程ID，Spid为操作系统进程ID。

--assm(auto segments space management)自动段空间管理
--HWM(high water mark) 高水位标记 top limit
--oltp(on line transaction process)联机事务处理
--ops(oracle parallel server)oracle并行服务器
--free list 表空间空闲链

--查看表空间的数据文件和容量信息
select b.file_id fileID, b.tablespace_name tab_space_name,
b.file_name fileName,b.bytes/(1024*1024) totalMB,
(b.bytes-sum(nvl(a.bytes,0))) /(1024*1024) used,
sum(nvl(a.bytes,0)) /(1024*1024) free,
round(sum(nvl(a.bytes,0))/b.bytes * 100,2) freeratio
from dba_free_space a,dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name desc

--查看一个表占用表空间信息
select s.* ,bytes/1024/1024  from user_segments s

--自由空间碎片索引(free space fragmentation index)
--fsfi=sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))))
--查看表空间碎片 如果碎片超过30时就要做整理了 可采用自动方法
alter tablespace tablespace_name coalesce
select tablespace_name,to_char(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))),'999.99') fsfi from dba_free_space group by tablespace_name

--查看数据文件信息
select d.name,d.file#,d.status,h.status
from v$datafile d inner join v$datafile_header h
on d.file#=h.file#

--查看表空间数据文件读写性能
select name,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm
from v$filestat,v$datafile
where v$filestat.file#=v$datafile.file#;

http://yangtingkun.itpub.net/category/468/1253

http://hi.baidu.com/dba_james/blog/category/Oracle

http://hi.baidu.com/dbaeyes/blog/category/Oracle%20Pl%20Sql

http://warehouse.itpub.net/category/777/56406

http://www.blogjava.net/gddg/archive/2009/05/20/172851.html

http://www.blogjava.net/gddg/category/13093.html

http://www.cnblogs.com/cxd4321/category/123517.html