Oracle数据库维护SQL语句.docx
《Oracle数据库维护SQL语句.docx》由会员分享,可在线阅读,更多相关《Oracle数据库维护SQL语句.docx(8页珍藏版)》请在冰豆网上搜索。
![Oracle数据库维护SQL语句.docx](https://file1.bdocx.com/fileroot1/2023-2/9/f7b4751c-89b6-4011-b5fa-857872308028/f7b4751c-89b6-4011-b5fa-8578723080281.gif)
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');