oracle_dba常用sql脚本.doc

PAGE-56-

OracleSQL脚本命令
监控SQL
1.监控事例的等待：
selectevent,sum(decode(wait_time,0,0,1))prev,sum(decode(wait_time,0,1,0))curr,count(*)
fromv$session_wait
groupbyeventorderby4;
2.回滚段的争用情况：
selectname,waits,gets,waits/getsratiofromv$rollstata,v$rollnamebwherea.usn=b.usn;
3.监控表空间的I/O比例：
selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr,f.phyblkrdpbr,f.phywrtspyw,
f.phyblkwrtpbw
fromv$filestatf,dba_data_filesdf
wheref.file#=df.file_id
4.监空文件系统的I/O比例：
selectsubstr(a.file#,1,2)"#",substr(a.name,1,30)"name",a.status,a.bytes,
b.phyrds,b.phywrts
fromv$datafilea,v$filestatb
wherea.file#=b.file#
5.在某个用户下找所有的索引：
selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_name
fromuser_ind_columns,user_indexes
whereuser_ind_columns.index_name=user_indexes.index_name
anduser_ind_columns.table_name=user_indexes.table_name
orderbyuser_indexes.table_type,user_indexes.table_name,
user_indexes.index_name,column_position;
6.进程监控：
selectdistinctp.spidunix_process,
s.terminal,
to_char(s.logon_time,'YYYY/MON/DDHH24:MI')Logon_Time,
s.username
fromv$processp,v$sessions
wherep.addr=s.paddrorderby2

7.监控SGA中字典缓冲区的命中率
selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100"missratio",
(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"
fromv$rowcache
wheregets+getmisses<>0
groupbyparameter,gets,getmisses;
8.监控SGA中共享缓存区的命中率，应该小于1%
selectsum(pins)"TotalPins",sum(reloads)"TotalReloads",
sum(reloads)/sum(pins)*100libcache
fromv$librarycache;
selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"
fromv$librarycache;
9.显示所有数据库对象的类别和大小
selectcount(name)num_instances,type,sum(source_size)source_size,
sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size)error_size,
sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size)size_required
fromdba_object_size
groupbytypeorderby2;
10.监控SGA中重做日志缓存区的命中率，应该小于1%
SELECTname,gets,misses,immediate_gets,immediate_misses,
Decode(gets,0,0,misses/gets*100)ratio1,
Decode(immedi