Oracle数据库维护SQL语句.docx

1、Oracle数据库维护SQL语句Oracle数据库维护常用SQL语句集合1、捕捉运行很久的SQLcolumn username format a12column opname format a16column progress format a8SELECT Username, Sid, Opname,Round(Sofar * 100 / Totalwork, 0) | % AS Progress, Time_Remaining,Sql_TextFROM V$session_Longops, V$sqlWHERE Time_Remaining 0AND Sql_Address = Addre

2、ssAND Sql_Hash_Value = Hash_Value;2、求DISK READ较多的SQLSELECT St.Sql_TextFROM V$sql s, V$sqltext StWHERE s.Address = St.AddressAND s.Hash_Value = St.Hash_ValueAND s.Disk_Reads 300;3、求DISK SORT严重的SQLSELECT Sess.Username, SQL.Sql_Text, Sort1.BlocksFROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1WHE

3、RE Sess.Serial# = Sort1.Session_NumAND Sort1.Sqladdr = SQL.AddressAND Sort1.Sqlhash = SQL.Hash_ValueAND Sort1.Blocks 200;4、监控索引是否使用alter index &index_name monitoring usage;alter index &index_name nomonitoring usage;select * from v$object_usage where index_name = &index_name;5、求数据文件的I/O分布SELECT Df.NA

4、ME, Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim,WritetimFROM V$filestat Fs, V$dbfile DfWHERE Fs.File# = Df.File#ORDER BY Df.NAME;6、查看还没提交的事务select * from v$locked_object;select * from v$transaction;7、回滚段查看SELECT Rownum, Sys.Dba_Rollback_Segs.Segment_Name NAME,V$rollstat.Extents Exten

5、ts, V$rollstat.Rssize Size_In_Bytes,V$rollstat.Xacts Xacts, V$rollstat.Gets Gets, V$rollstat.Waits Waits,V$rollstat.Writes Writes, Sys.Dba_Rollback_Segs.Status StatusFROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollnameWHERE V$rollname.NAME(+) = Sys.Dba_Rollback_Segs.Segment_NameAND V$rollstat.Usn(+) =

6、 V$rollname.UsnORDER BY Rownum8、查看系统请求情况SELECT Decode(NAME, summed dirty write queue length, VALUE) /Decode(NAME, write requests, VALUE) Write Request LengthFROM V$sysstatWHERE NAME IN (summed dirty queue length, write requests)AND VALUE 0;9、计算data buffer 命中率SELECT a.VALUE + b.VALUE logical_reads, c

7、.VALUE phys_reads,Round(100 * (a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE + b.VALUE) BUFFER HIT RATIOFROM V$sysstat a, V$sysstat b, V$sysstat cWHERE a.Statistic# = 40AND b.Statistic# = 41AND c.Statistic# = 42;SELECT NAME,(1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets) * 100 h_RatioFROM V$buff

8、er_Pool_Statistics;10、查看内存使用情况SELECT Least(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) / (1

9、024 * 1024) / (MAX(b.VALUE) / (1024 * 1024) * 100 Avail_Pool_PctFROM V$sgastat a, V$parameter bWHERE (a.Pool = shared pool AND a.NAME NOT IN (free memory)AND b.NAME = shared_pool_size;11、查看用户使用内存情况SELECT Username, SUM(Sharable_Mem), SUM(Persistent_Mem), SUM(Runtime_Mem)FROM Sys.v_$sqlarea a, Dba_Use

10、rs bWHERE a.Parsing_User_Id = b.User_IdGROUP BY Username;12、查看对象的缓存情况SELECT Owner, Namespace, TYPE, NAME, Sharable_Mem, Loads, Executions, Locks,Pins, KeptFROM V$db_Object_CacheWHERE TYPE NOT IN(NOT LOADED, NON-EXISTENT, VIEW, TABLE, SEQUENCE)AND Executions 0AND Loads 1AND Kept = NOORDER BY Owner, N

11、amespace, TYPE, Executions DESC;SELECT TYPE, COUNT(*)FROM V$db_Object_CacheGROUP BY TYPE;13、查看库缓存命中率SELECT Namespace, Gets, Gethitratio * 100 Gethitratio, Pins,Pinhitratio * 100 Pinhitratio, Reloads, InvalidationsFROM V$librarycache14、查看某些用户的hashSELECT a.Username, COUNT(b.Hash_Value) Total_Hash,COUN

12、T(b.Hash_Value) - COUNT(UNIQUE(b.Hash_Value) Same_Hash,(COUNT(UNIQUE(b.Hash_Value) / COUNT(b.Hash_Value) * 100 u_Hash_RatioFROM Dba_Users a, V$sqlarea bWHERE a.User_Id = b.Parsing_User_IdGROUP BY a.Username;15、查看字典命中率SELECT (SUM(Getmisses) / SUM(Gets) RatioFROM V$rowcache;16、查看undo段的使用情况SELECT d.Seg

13、ment_Name, Extents, Optsize, Shrinks, Aveshrink, Aveactive,d.StatusFROM V$rollname n, V$rollstat s, Dba_Rollback_Segs dWHERE d.Segment_Id = n.Usn(+)AND d.Segment_Id = s.Usn(+);17、求归档日志的切换频率(生产系统可能时间会很长)SELECT Start_Recid, Start_Time, End_Recid, End_Time, MinutesFROM (SELECT Test.*, Rownum AS RnFROM

14、(SELECT b.Recid Start_Recid,To_Char(b.First_Time, yyyy-mm-dd hh24:mi:ss) Start_Time,a.Recid End_Recid,To_Char(a.First_Time, yyyy-mm-dd hh24:mi:ss) End_Time,Round(a.First_Time - b.First_Time) * 24) * 60, 2) MinutesFROM V$log_History a, V$log_History bWHERE a.Recid = b.Recid + 1AND b.First_Time SYSDAT

15、E - 1ORDER BY a.First_Time DESC) Test) yWHERE y.Rn (SELECT To_Number(VALUE) * 0.05FROM V$parameterWHERE NAME = db_block_buffers);21、求buffer cache中的块信息SELECT o.Object_Type, Substr(o.Object_Name, 1, 10) Objname, b.Objd, b.Status,COUNT(b.Objd)FROM V$bh b, Dba_Objects oWHERE b.Objd = o.Data_Object_IdAND

16、 o.Owner = &ownerGROUP BY o.Object_Type, o.Object_Name, b.Objd, b.Status;22、求日志文件的空间使用SELECT Le.Leseq Current_Log_Sequence#,100 * Cp.Cpodr_Bno / Le.Lesiz Percentage_FullFROM X$kcccp Cp, X$kccle LeWHERE Le.Leseq = Cp.Cpodr_Seq;23、求等待中的对象SELECT /*+rule */s.Sid, s.Username, w.Event, o.Owner, o.Segment_

17、Name, o.Segment_Type,o.Partition_Name, w.Seconds_In_Wait Seconds, w.StateFROM V$session_Wait w, V$session s, Dba_Extents oWHERE w.Event IN (SELECT NAMEFROM V$event_NameWHERE Parameter1 = file#AND Parameter2 = block#AND NAME NOT LIKE control%)AND o.Owner sysAND w.Sid = s.SidAND w.P1 = o.File_IdAND w.

18、P2 = o.Block_IdAND w.P2 o.Block_Id + o.Blocks24、求当前事务的重做尺寸SELECT V$statname.NAME,VALUEFROM V$mystat, V$statnameWHERE V$mystat.Statistic# = V$statname.Statistic#AND V$statname.NAME = redo size;25、唤醒smon去清除临时段column pid new_value Smonset termout offSELECT p.PidFROM Sys.v_$bgprocess b, Sys.v_$process p

19、WHERE b.NAME = SMONAND p.Addr = b.Paddr;/SET Termout ON Oradebug Wakeup &Smon Undefine Smon26、求回退率SELECT b.VALUE / (a.VALUE + b.VALUE), a.VALUE, b.VALUEFROM V$sysstat a, V$sysstat bWHERE a.Statistic# = 4AND b.Statistic# = 5;27、求free memorySELECT *FROM V$sgastatWHERE NAME = free memory;SELECT a.NAME,

20、 SUM(b.VALUE)FROM V$statname a, V$sesstat bWHERE a.Statistic# = b.Statistic#GROUP BY a.NAME;查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行就看看能否kill它，等等, 查看当前正在使用的回滚段的用户信息和回滚段信息:set linesize 121SELECT r.NAME ROLLBACK SEGMENT NAME , l.Sid ORACLE PID,p.Spid SYSTEM PID , s.

21、Username ORACLE USERNAMEFROM V$lock l, V$process p, V$rollname r, V$session sWHERE l.Sid = p.Pid(+)AND s.Sid = l.SidAND Trunc(l.Id1(+) / 65536) = r.UsnAND l.TYPE(+) = TXAND l.Lmode(+) = 6ORDER BY r.NAME;28、查看用户的回滚段的信息SELECT s.Username, Rn.NAMEFROM V$session s, V$transaction t, V$rollstat r, V$rollna

22、me RnWHERE s.Saddr = t.Ses_AddrAND t.Xidusn = r.UsnAND r.Usn = Rn.Usn29、查看内存中存的使用SELECT Decode(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) Not Dirty,SUM(Decode(Bitand(Flag, 1), 1, 1, 0) Dirty,SUM(Dirty_Queue) On Dirty, COUNT(*) TotalFROM X$bhGROUP BY Decode(Greatest(CLASS, 10),10,Decode(CLASS, 1, Data, 2, Sort, 4, Header, To_Char(CLASS), Rollback);