Oracle数据库维护SQL语句.docx
《Oracle数据库维护SQL语句.docx》由会员分享，可在线阅读，更多相关《Oracle数据库维护SQL语句.docx（8页珍藏版）》请在冰豆网上搜索。
Oracle数据库维护SQL语句
Oracle数据库维护常用SQL语句集合
1、捕捉运行很久的SQL
columnusernameformata12
columnopnameformata16
columnprogressformata8
SELECTUsername,Sid,Opname,
Round（Sofar*100/Totalwork,0）||'%'ASProgress,Time_Remaining,
Sql_Text
FROMV$session_Longops,V$sql
WHERETime_Remaining<>0
ANDSql_Address=Address
ANDSql_Hash_Value=Hash_Value;
2、求DISKREAD较多的SQL
SELECTSt.Sql_Text
FROMV$sqls,V$sqltextSt
WHEREs.Address=St.Address
ANDs.Hash_Value=St.Hash_Value
ANDs.Disk_Reads>300;
3、求DISKSORT严重的SQL
SELECTSess.Username,SQL.Sql_Text,Sort1.Blocks
FROMV$sessionSess,V$sqlareaSQL,V$sort_UsageSort1
WHERESess.Serial#=Sort1.Session_Num
ANDSort1.Sqladdr=SQL.Address
ANDSort1.Sqlhash=SQL.Hash_Value
ANDSort1.Blocks>200;
4、监控索引是否使用
alterindex&index_namemonitoringusage;
alterindex&index_namenomonitoringusage;
select*fromv$object_usagewhereindex_name=&index_name;
5、求数据文件的I/O分布
SELECTDf.NAME,Phyrds,Phywrts,Phyblkrd,Phyblkwrt,Singleblkrds,Readtim,
Writetim
FROMV$filestatFs,V$dbfileDf
WHEREFs.File#=Df.File#
ORDERBYDf.NAME;
6、查看还没提交的事务
select*fromv$locked_object;
select*fromv$transaction;
7、回滚段查看
SELECTRownum,Sys.Dba_Rollback_Segs.Segment_NameNAME,
V$rollstat.ExtentsExtents,V$rollstat.RssizeSize_In_Bytes,
V$rollstat.XactsXacts,V$rollstat.GetsGets,V$rollstat.WaitsWaits,
V$rollstat.WritesWrites,Sys.Dba_Rollback_Segs.StatusStatus
FROMV$rollstat,Sys.Dba_Rollback_Segs,V$rollname
WHEREV$rollname.NAME（+）=Sys.Dba_Rollback_Segs.Segment_Name
ANDV$rollstat.Usn（+）=V$rollname.Usn
ORDERBYRownum
8、查看系统请求情况
SELECTDecode（NAME,'summeddirtywritequeuelength',VALUE）/
Decode（NAME,'writerequests',VALUE）"WriteRequestLength"
FROMV$sysstat
WHERENAMEIN（'summeddirtyqueuelength','writerequests'）
ANDVALUE>0;
9、计算databuffer命中率
SELECTa.VALUE+b.VALUE"logical_reads",c.VALUE"phys_reads",
Round（100*（（a.VALUE+b.VALUE）-c.VALUE）/（a.VALUE+b.VALUE））"BUFFERHITRATIO"
FROMV$sysstata,V$sysstatb,V$sysstatc
WHEREa.Statistic#=40
ANDb.Statistic#=41
ANDc.Statistic#=42;
SELECTNAME,
（1-（Physical_Reads/（Db_Block_Gets+Consistent_Gets）））*100h_Ratio
FROMV$buffer_Pool_Statistics;
10、查看内存使用情况
SELECTLeast（MAX（b.VALUE）/（1024*1024）,SUM（a.Bytes）/（1024*1024））Shared_Pool_Used,
MAX（b.VALUE）/（1024*1024）Shared_Pool_Size,
Greatest（MAX（b.VALUE）/（1024*1024）,SUM（a.Bytes）/（1024*1024））-
（SUM（a.Bytes）/（1024*1024））Shared_Pool_Avail,
（（SUM（a.Bytes）/（1024*1024））/（MAX（b.VALUE）/（1024*1024）））*100Avail_Pool_Pct
FROMV$sgastata,V$parameterb
WHERE（a.Pool='sharedpool'ANDa.NAMENOTIN（'freememory'））
ANDb.NAME='shared_pool_size';
11、查看用户使用内存情况
SELECTUsername,SUM（Sharable_Mem）,SUM（Persistent_Mem）,SUM（Runtime_Mem）
FROMSys.v_$sqlareaa,Dba_Usersb
WHEREa.Parsing_User_Id=b.User_Id
GROUPBYUsername;
12、查看对象的缓存情况
SELECTOwner,Namespace,TYPE,NAME,Sharable_Mem,Loads,Executions,Locks,
Pins,Kept
FROMV$db_Object_Cache
WHERETYPENOTIN
（'NOTLOADED','NON-EXISTENT','VIEW','TABLE','SEQUENCE'）
ANDExecutions>0
ANDLoads>1
ANDKept='NO'
ORDERBYOwner,Namespace,TYPE,ExecutionsDESC;
SELECTTYPE,COUNT（*）
FROMV$db_Object_Cache
GROUPBYTYPE;
13、查看库缓存命中率
SELECTNamespace,Gets,Gethitratio*100Gethitratio,Pins,
Pinhitratio*100Pinhitratio,Reloads,Invalidations
FROMV$librarycache
14、查看某些用户的hash
SELECTa.Username,COUNT（b.Hash_Value）Total_Hash,
COUNT（b.Hash_Value）-COUNT（UNIQUE（b.Hash_Value））Same_Hash,
（COUNT（UNIQUE（b.Hash_Value））/COUNT（b.Hash_Value））*100u_Hash_Ratio
FROMDba_Usersa,V$sqlareab
WHEREa.User_Id=b.Parsing_User_Id
GROUPBYa.Username;
15、查看字典命中率
SELECT（SUM（Getmisses）/SUM（Gets））Ratio
FROMV$rowcache;
16、查看undo段的使用情况
SELECTd.Segment_Name,Extents,Optsize,Shrinks,Aveshrink,Aveactive,
d.Status
FROMV$rollnamen,V$rollstats,Dba_Rollback_Segsd
WHEREd.Segment_Id=n.Usn（+）
ANDd.Segment_Id=s.Usn（+）;
17、求归档日志的切换频率（生产系统可能时间会很长）
SELECTStart_Recid,Start_Time,End_Recid,End_Time,Minutes
FROM（SELECTTest.*,RownumASRn
FROM（SELECTb.RecidStart_Recid,
To_Char（b.First_Time,'yyyy-mm-ddhh24:
mi:
ss'）Start_Time,
a.RecidEnd_Recid,
To_Char（a.First_Time,'yyyy-mm-ddhh24:
mi:
ss'）End_Time,
Round（（（a.First_Time-b.First_Time）*24）*60,2）Minutes
FROMV$log_Historya,V$log_Historyb
WHEREa.Recid=b.Recid+1
ANDb.First_Time>SYSDATE-1
ORDERBYa.First_TimeDESC）Test）y
WHEREy.Rn<30
18、求回滚段正在处理的事务
SELECTa.NAME,b.Xacts,c.Sid,c.Serial#,d.Sql_Text
FROMV$rollnamea,V$rollstatb,V$sessionc,V$sqltextd,V$transactione
WHEREa.Usn=b.Usn
ANDb.Usn=e.Xidusn
ANDc.Taddr=e.Addr
ANDc.Sql_Address=d.Address
ANDc.Sql_Hash_Value=d.Hash_Value
ORDERBYa.NAME,c.Sid,d.Piece;
19、求某个事务的重做信息（bytes）
SELECTs.NAME,m.VALUE
FROMV$mystatm,V$statnames
WHEREm.Statistic#=s.Statistic#
ANDs.NAMELIKE'%redosize%';
20、求cache中缓存超过其5%的对象
SELECTo.Owner,o.Object_Type,o.Object_Name,COUNT（b.Objd）
FROMV$bhb,Dba_Objectso
WHEREb.Objd=o.Object_Id
GROUPBYo.Owner,o.Object_Type,o.Object_Name
HAVINGCOUNT（b.Objd）>（SELECTTo_Number（VALUE）*0.05
FROMV$parameter
WHERENAME='db_block_buffers'）;
21、求buffercache中的块信息
SELECTo.Object_Type,Substr（o.Object_Name,1,10）Objname,b.Objd,b.Status,
COUNT（b.Objd）
FROMV$bhb,Dba_Objectso
WHEREb.Objd=o.Data_Object_Id
ANDo.Owner='&owner'
GROUPBYo.Object_Type,o.Object_Name,b.Objd,b.Status;
22、求日志文件的空间使用
SELECTLe.LeseqCurrent_Log_Sequence#,
100*Cp.Cpodr_Bno/Le.LesizPercentage_Full
FROMX$kcccpCp,X$kccleLe
WHERELe.Leseq=Cp.Cpodr_Seq;
23、求等待中的对象
SELECT/*+rule*/
s.Sid,s.Username,w.Event,o.Owner,o.Segment_Name,o.Segment_Type,
o.Partition_Name,w.Seconds_In_WaitSeconds,w.State
FROMV$session_Waitw,V$sessions,Dba_Extentso
WHEREw.EventIN（SELECTNAME
FROMV$event_Name
WHEREParameter1='file#'
ANDParameter2='block#'
ANDNAMENOTLIKE'control%'）
ANDo.Owner<>'sys'
ANDw.Sid=s.Sid
ANDw.P1=o.File_Id
ANDw.P2>=o.Block_Id
ANDw.P224、求当前事务的重做尺寸
SELECTV$statname.NAME,VALUE
FROMV$mystat,V$statname
WHEREV$mystat.Statistic#=V$statname.Statistic#
ANDV$statname.NAME='redosize';
25、唤醒smon去清除临时段
columnpidnew_valueSmon
settermoutoff
SELECTp.Pid
FROMSys.v_$bgprocessb,Sys.v_$processp
WHEREb.NAME='SMON'
ANDp.Addr=b.Paddr;
/
SETTermoutONOradebugWakeup&SmonUndefineSmon
26、求回退率
SELECTb.VALUE/（a.VALUE+b.VALUE）,a.VALUE,b.VALUE
FROMV$sysstata,V$sysstatb
WHEREa.Statistic#=4
ANDb.Statistic#=5;
27、求freememory
SELECT*
FROMV$sgastat
WHERENAME='freememory';
SELECTa.NAME,SUM（b.VALUE）
FROMV$statnamea,V$sesstatb
WHEREa.Statistic#=b.Statistic#
GROUPBYa.NAME;
查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，
找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行
就看看能否kill它，等等,查看当前正在使用的回滚段的用户信息和回滚段信息:
setlinesize121
SELECTr.NAME"ROLLBACKSEGMENTNAME",l.Sid"ORACLEPID",
p.Spid"SYSTEMPID",s.Username"ORACLEUSERNAME"
FROMV$lockl,V$processp,V$rollnamer,V$sessions
WHEREl.Sid=p.Pid（+）
ANDs.Sid=l.Sid
ANDTrunc（l.Id1（+）/65536）=r.Usn
ANDl.TYPE（+）='TX'
ANDl.Lmode（+）=6
ORDERBYr.NAME;
28、查看用户的回滚段的信息
SELECTs.Username,Rn.NAME
FROMV$sessions,V$transactiont,V$rollstatr,V$rollnameRn
WHEREs.Saddr=t.Ses_Addr
ANDt.Xidusn=r.Usn
ANDr.Usn=Rn.Usn
29、查看内存中存的使用
SELECTDecode（Greatest（CLASS,10）,
10,
Decode（CLASS,1,'Data',2,'Sort',4,'Header',To_Char（CLASS））,'Rollback'）"Class",
SUM（Decode（Bitand（Flag,1）,1,0,1））"NotDirty",
SUM（Decode（Bitand（Flag,1）,1,1,0））"Dirty",
SUM（Dirty_Queue）"OnDirty",COUNT（*）"Total"
FROMX$bh
GROUPBYDecode（Greatest（CLASS,10）,
10,
Decode（CLASS,1,'Data',2,'Sort',4,'Header',To_Char（CLASS））,'Rollback'）;
升级会员 