| 導購 | 订阅 | 在线投稿
分享
 
 
當前位置: 王朝網路 >> oracle >> Oracle數據庫維護常用SQL語句集合(3)
 

Oracle數據庫維護常用SQL語句集合(3)

2008-07-04 06:34:30  編輯來源:互聯網  简体版  手機版  評論  字體: ||
 
 
  性能相關內容

  1、捕捉運行很久的SQL

  column username format a12

  column opname format a16

  column progress format a8

  SELECT Username, Sid, Opname,

  Round(Sofar * 100 / Totalwork, 0) || '%' AS Progress, Time_Remaining,

  Sql_Text

  FROM V$session_Longops, V$sql

  WHERE Time_Remaining <> 0

  AND Sql_Address = Address

  AND Sql_Hash_Value = Hash_Value;

  2、求DISK READ較多的SQL

  SELECT St.Sql_Text

  FROM V$sql s, V$sqltext St

  WHERE s.Address = St.Address

  AND s.Hash_Value = St.Hash_Value

  AND s.Disk_Reads > 300;

  3、求DISK SORT嚴重的SQL

  SELECT Sess.Username, SQL.Sql_Text, Sort1.Blocks

  FROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1

  WHERE Sess.Serial# = Sort1.Session_Num

  AND Sort1.Sqladdr = SQL.Address

  AND Sort1.Sqlhash = SQL.Hash_Value

  AND 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.NAME, Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim,

  Writetim

  FROM V$filestat Fs, V$dbfile Df

  WHERE 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 Extents, 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 Status

  FROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollname

  WHERE V$rollname.NAME(+) = Sys.Dba_Rollback_Segs.Segment_Name

  AND V$rollstat.Usn(+) = V$rollname.Usn

  ORDER BY Rownum

  8、查看系統請求情況

  SELECT Decode(NAME, 'summed dirty write queue length', VALUE) /

  Decode(NAME, 'write requests', VALUE) "Write Request Length"

  FROM V$sysstat

  WHERE NAME IN ('summed dirty queue length', 'write requests')

  AND VALUE > 0;

  9、計算data buffer 命中率

  SELECT a.VALUE + b.VALUE "logical_reads", c.VALUE "phys_reads",

  Round(100 * ((a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE + b.VALUE)) "BUFFER HIT RATIO"

  FROM V$sysstat a, V$sysstat b, V$sysstat c

  WHERE a.Statistic# = 40

  AND b.Statistic# = 41

  AND c.Statistic# = 42;

  SELECT NAME,

  (1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets))) * 100 h_Ratio

  FROM V$buffer_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) / (1024 * 1024)) / (MAX(b.VALUE) / (1024 * 1024))) * 100 Avail_Pool_Pct

  FROM V$sgastat a, V$parameter b

  WHERE (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_Users b

  WHERE a.Parsing_User_Id = b.User_Id

  GROUP BY Username;

  12、查看對象的緩存情況

  SELECT Owner, Namespace, TYPE, NAME, Sharable_Mem, Loads, Executions, Locks,

  Pins, Kept

  FROM V$db_Object_Cache

  WHERE TYPE NOT IN

  ('NOT LOADED', 'NON-EXISTENT', 'VIEW', 'TABLE', 'SEQUENCE')

  AND Executions > 0

  AND Loads > 1

  AND Kept = 'NO'

  ORDER BY Owner, Namespace, TYPE, Executions DESC;

  SELECT TYPE, COUNT(*)

  FROM V$db_Object_Cache

  GROUP BY TYPE;

  13、查看庫緩存命中率

  SELECT Namespace, Gets, Gethitratio * 100 Gethitratio, Pins,

  Pinhitratio * 100 Pinhitratio, Reloads, Invalidations

  FROM V$librarycache

  14、查看某些用戶的hash

  SELECT a.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)) * 100 u_Hash_Ratio

  FROM Dba_Users a, V$sqlarea b

  WHERE a.User_Id = b.Parsing_User_Id

  GROUP BY a.Username;

  15、查看字典命中率

  SELECT (SUM(Getmisses) / SUM(Gets)) Ratio

  FROM V$rowcache;

  16、查看undo段的使用情況

  SELECT d.Segment_Name, Extents, Optsize, Shrinks, Aveshrink, Aveactive,

  d.Status

  FROM V$rollname n, V$rollstat s, Dba_Rollback_Segs d

  WHERE d.Segment_Id = n.Usn(+)

  AND d.Segment_Id = s.Usn(+);

  

  17、求歸檔日志的切換頻率(生産系統可能時間會很長)

  SELECT Start_Recid, Start_Time, End_Recid, End_Time, Minutes

  FROM (SELECT Test.*, Rownum AS Rn

  FROM (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) Minutes

  FROM V$log_History a, V$log_History b

  WHERE a.Recid = b.Recid + 1

  AND b.First_Time > SYSDATE - 1

  ORDER BY a.First_Time DESC) Test) y

  WHERE y.Rn < 30

  18、求回滾段正在處理的事務

  SELECT a.NAME, b.Xacts, c.Sid, c.Serial#, d.Sql_Text

  FROM V$rollname a, V$rollstat b, V$session c, V$sqltext d, V$transaction e

  WHERE a.Usn = b.Usn

  AND b.Usn = e.Xidusn

  AND c.Taddr = e.Addr

  AND c.Sql_Address = d.Address

  AND c.Sql_Hash_Value = d.Hash_Value

  ORDER BY a.NAME, c.Sid, d.Piece;

  19、求某個事務的重做信息(bytes)

  SELECT s.NAME, m.VALUE

  FROM V$mystat m, V$statname s

  WHERE m.Statistic# = s.Statistic#

  AND s.NAME LIKE '%redo size%';

  20、求cache中緩存超過其5%的對象

  SELECT o.Owner, o.Object_Type, o.Object_Name, COUNT(b.Objd)

  FROM V$bh b, Dba_Objects o

  WHERE b.Objd = o.Object_Id

  GROUP BY o.Owner, o.Object_Type, o.Object_Name

  HAVING COUNT(b.Objd) > (SELECT To_Number(VALUE) * 0.05

  FROM V$parameter

  WHERE 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 o

  WHERE b.Objd = o.Data_Object_Id

  AND o.Owner = '&owner'

  GROUP 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_Full

  FROM X$kcccp Cp, X$kccle Le

  WHERE Le.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_Wait Seconds, w.State

  FROM V$session_Wait w, V$session s, Dba_Extents o

  WHERE w.Event IN (SELECT NAME

  FROM V$event_Name

  WHERE Parameter1 = 'file#'

  AND Parameter2 = 'block#'

  AND NAME NOT LIKE 'control%')

  AND o.Owner <> 'sys'

  AND w.Sid = s.Sid

  AND w.P1 = o.File_Id

  AND w.P2 >= o.Block_Id

  AND w.P2 < o.Block_Id + o.Blocks

  24、求當前事務的重做尺寸

  SELECT V$statname.NAME,VALUE

  FROM V$mystat, V$statname

  WHERE V$mystat.Statistic# = V$statname.Statistic#

  AND V$statname.NAME = 'redo size';

  25、喚醒smon去清除臨時段

  column pid new_value Smon

  set termout off

  SELECT p.Pid

  FROM Sys.v_$bgprocess b, Sys.v_$process p

  WHERE b.NAME = 'SMON'

  AND p.Addr = b.Paddr;

  /

  SET Termout ON Oradebug Wakeup &Smon Undefine Smon

  26、求回退率

  SELECT b.VALUE / (a.VALUE + b.VALUE), a.VALUE, b.VALUE

  FROM V$sysstat a, V$sysstat b

  WHERE a.Statistic# = 4

  AND b.Statistic# = 5;

  27、求free memory

  SELECT *

  FROM V$sgastat

  WHERE NAME = 'free memory';

  SELECT a.NAME, SUM(b.VALUE)

  FROM V$statname a, V$sesstat b

  WHERE a.Statistic# = b.Statistic#

  GROUP BY a.NAME;

  查看一下誰在使用那個可以得回滾段,或者查看一下某個可以得用戶在使用回滾段,

  找出領回滾段不斷增長的事務,再看看如何處理它,是否可以將它commit,再不行

  就看看能否kill它,等等, 查看當前正在使用的回滾段的用戶信息和回滾段信息:

  set linesize 121

  SELECT r.NAME "ROLLBACK SEGMENT NAME ", l.Sid "ORACLE PID",

  p.Spid "SYSTEM PID ", s.Username "ORACLE USERNAME"

  FROM V$lock l, V$process p, V$rollname r, V$session s

  WHERE l.Sid = p.Pid(+)

  AND s.Sid = l.Sid

  AND Trunc(l.Id1(+) / 65536) = r.Usn

  AND l.TYPE(+) = 'TX'

  AND l.Lmode(+) = 6

  ORDER BY r.NAME;

  28、查看用戶的回滾段的信息

  SELECT s.Username, Rn.NAME

  FROM V$session s, V$transaction t, V$rollstat r, V$rollname Rn

  WHERE s.Saddr = t.Ses_Addr

  AND t.Xidusn = r.Usn

  AND r.Usn = Rn.Usn

  29、查看內存中存的使用

  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(*) "Total"

  FROM X$bh

  GROUP BY Decode(Greatest(CLASS, 10),

  10,

  Decode(CLASS, 1, 'Data', 2, 'Sort', 4, 'Header', To_Char(CLASS)), 'Rollback');
 
 
 
上一篇《深入了解Oracle數據恢複/挖掘工具FySafe》
下一篇《解決Oracle 10g library內存分配告警問題》
 
 
 
日版寵物情人插曲《Winding Road》歌詞

日版寵物情人2017的插曲,很帶節奏感,日語的,女生唱的。 最後聽見是在第8集的時候女主手割傷了,然後男主用嘴幫她吸了一下,插曲就出來了。 歌手:Def...

兄弟共妻,我成了他們夜裏的美食

老鍾家的兩個兒子很特別,就是跟其他的人不太一樣,魔一般的執著。兄弟倆都到了要結婚的年齡了,不管自家老爹怎麽磨破嘴皮子,兄弟倆說不娶就不娶,老父母爲兄弟兩操碎了心...

如何磨出破洞牛仔褲?牛仔褲怎麽剪破洞?

把牛仔褲磨出有線的破洞 1、具體工具就是磨腳石,下面墊一個硬物,然後用磨腳石一直磨一直磨,到把那塊磨薄了,用手撕開就好了。出來的洞啊很自然的。需要貓須的話調幾...

我就是掃描下圖得到了敬業福和愛國福

先來看下敬業福和愛國福 今年春節,支付寶再次推出了“五福紅包”活動,表示要“把欠大家的敬業福都還給大家”。 今天該活動正式啓動,和去年一樣,需要收集“五福”...

冰箱異味産生的原因和臭味去除的方法

有時候我們打開冰箱就會聞到一股異味,冰箱裏的這種異味是因爲一些物質發出的氣味的混合體,聞起來讓人惡心。 産生這些異味的主要原因有以下幾點。 1、很多人有這種習...

《極品家丁》1-31集大結局分集劇情介紹

簡介 《極品家丁》講述了現代白領林晚榮無意回到古代金陵,並追隨蕭二小姐化名“林三”進入蕭府,不料卻陰差陽錯上演了一出低級家丁拼搏上位的“林三升職記”。...

李溪芮《極品家丁》片尾曲《你就是我最愛的寶寶》歌詞

你就是我最愛的寶寶 - 李溪芮 (電視劇《極品家丁》片尾曲) 作詞:常馨內 作曲:常馨內 你的眉 又鬼馬的挑 你的嘴 又壞壞的笑 上一秒吵鬧 下...

烏梅的功效與作用以及烏梅的食用禁忌有哪些?

烏梅,又稱春梅,中醫認爲,烏梅味酸,性溫,無毒,具有安心、除熱、下氣、祛痰、止渴調中、殺蟲的功效,治肢體痛、肺痨病。烏梅泡水喝能治傷寒煩熱、止吐瀉,與幹姜一起制...

什麽是脂肪粒?如何消除臉部脂肪粒?

什麽是脂肪粒 在我們的臉上總會長一個個像脂肪的小顆粒,弄也弄不掉,而且顔色還是白白的。它既不是粉刺也不是其他的任何痘痘,它就是脂肪粒。 脂肪粒雖然也是由油脂...

網絡安全治理:國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰受害者

來源:中國青年報 新的攻擊方法不斷湧現,黑客幾乎永遠占據網絡攻擊的上風,我們不可能通過技術手段杜絕網絡攻擊。國家安全保障的主要方向是打擊犯罪,而不是處置和懲罰...

 
 
 
性能相關內容 1、捕捉運行很久的SQL column username format a12 column opname format a16 column progress format a8 SELECT Username, Sid, Opname, Round(Sofar * 100 / Totalwork, 0) || '%' AS Progress, Time_Remaining, Sql_Text FROM V$session_Longops, V$sql WHERE Time_Remaining <> 0 AND Sql_Address = Address AND Sql_Hash_Value = Hash_Value; 2、求DISK READ較多的SQL SELECT St.Sql_Text FROM V$sql s, V$sqltext St WHERE s.Address = St.Address AND s.Hash_Value = St.Hash_Value AND s.Disk_Reads > 300; 3、求DISK SORT嚴重的SQL SELECT Sess.Username, SQL.Sql_Text, Sort1.Blocks FROM V$session Sess, V$sqlarea SQL, V$sort_Usage Sort1 WHERE Sess.Serial# = Sort1.Session_Num AND Sort1.Sqladdr = SQL.Address AND Sort1.Sqlhash = SQL.Hash_Value AND 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.NAME, Phyrds, Phywrts, Phyblkrd, Phyblkwrt, Singleblkrds, Readtim, Writetim FROM V$filestat Fs, V$dbfile Df WHERE 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 Extents, 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 Status FROM V$rollstat, Sys.Dba_Rollback_Segs, V$rollname WHERE V$rollname.NAME(+) = Sys.Dba_Rollback_Segs.Segment_Name AND V$rollstat.Usn(+) = V$rollname.Usn ORDER BY Rownum 8、查看系統請求情況 SELECT Decode(NAME, 'summed dirty write queue length', VALUE) / Decode(NAME, 'write requests', VALUE) "Write Request Length" FROM V$sysstat WHERE NAME IN ('summed dirty queue length', 'write requests') AND VALUE > 0; 9、計算data buffer 命中率 SELECT a.VALUE + b.VALUE "logical_reads", c.VALUE "phys_reads", Round(100 * ((a.VALUE + b.VALUE) - c.VALUE) / (a.VALUE + b.VALUE)) "BUFFER HIT RATIO" FROM V$sysstat a, V$sysstat b, V$sysstat c WHERE a.Statistic# = 40 AND b.Statistic# = 41 AND c.Statistic# = 42; SELECT NAME, (1 - (Physical_Reads / (Db_Block_Gets + Consistent_Gets))) * 100 h_Ratio FROM V$buffer_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) / (1024 * 1024)) / (MAX(b.VALUE) / (1024 * 1024))) * 100 Avail_Pool_Pct FROM V$sgastat a, V$parameter b WHERE (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_Users b WHERE a.Parsing_User_Id = b.User_Id GROUP BY Username; 12、查看對象的緩存情況 SELECT Owner, Namespace, TYPE, NAME, Sharable_Mem, Loads, Executions, Locks, Pins, Kept FROM V$db_Object_Cache WHERE TYPE NOT IN ('NOT LOADED', 'NON-EXISTENT', 'VIEW', 'TABLE', 'SEQUENCE') AND Executions > 0 AND Loads > 1 AND Kept = 'NO' ORDER BY Owner, Namespace, TYPE, Executions DESC; SELECT TYPE, COUNT(*) FROM V$db_Object_Cache GROUP BY TYPE; 13、查看庫緩存命中率 SELECT Namespace, Gets, Gethitratio * 100 Gethitratio, Pins, Pinhitratio * 100 Pinhitratio, Reloads, Invalidations FROM V$librarycache 14、查看某些用戶的hash SELECT a.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)) * 100 u_Hash_Ratio FROM Dba_Users a, V$sqlarea b WHERE a.User_Id = b.Parsing_User_Id GROUP BY a.Username; 15、查看字典命中率 SELECT (SUM(Getmisses) / SUM(Gets)) Ratio FROM V$rowcache; 16、查看undo段的使用情況 SELECT d.Segment_Name, Extents, Optsize, Shrinks, Aveshrink, Aveactive, d.Status FROM V$rollname n, V$rollstat s, Dba_Rollback_Segs d WHERE d.Segment_Id = n.Usn(+) AND d.Segment_Id = s.Usn(+); 17、求歸檔日志的切換頻率(生産系統可能時間會很長) SELECT Start_Recid, Start_Time, End_Recid, End_Time, Minutes FROM (SELECT Test.*, Rownum AS Rn FROM (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) Minutes FROM V$log_History a, V$log_History b WHERE a.Recid = b.Recid + 1 AND b.First_Time > SYSDATE - 1 ORDER BY a.First_Time DESC) Test) y WHERE y.Rn < 30 18、求回滾段正在處理的事務 SELECT a.NAME, b.Xacts, c.Sid, c.Serial#, d.Sql_Text FROM V$rollname a, V$rollstat b, V$session c, V$sqltext d, V$transaction e WHERE a.Usn = b.Usn AND b.Usn = e.Xidusn AND c.Taddr = e.Addr AND c.Sql_Address = d.Address AND c.Sql_Hash_Value = d.Hash_Value ORDER BY a.NAME, c.Sid, d.Piece; 19、求某個事務的重做信息(bytes) SELECT s.NAME, m.VALUE FROM V$mystat m, V$statname s WHERE m.Statistic# = s.Statistic# AND s.NAME LIKE '%redo size%'; 20、求cache中緩存超過其5%的對象 SELECT o.Owner, o.Object_Type, o.Object_Name, COUNT(b.Objd) FROM V$bh b, Dba_Objects o WHERE b.Objd = o.Object_Id GROUP BY o.Owner, o.Object_Type, o.Object_Name HAVING COUNT(b.Objd) > (SELECT To_Number(VALUE) * 0.05 FROM V$parameter WHERE 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 o WHERE b.Objd = o.Data_Object_Id AND o.Owner = '&owner' GROUP 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_Full FROM X$kcccp Cp, X$kccle Le WHERE Le.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_Wait Seconds, w.State FROM V$session_Wait w, V$session s, Dba_Extents o WHERE w.Event IN (SELECT NAME FROM V$event_Name WHERE Parameter1 = 'file#' AND Parameter2 = 'block#' AND NAME NOT LIKE 'control%') AND o.Owner <> 'sys' AND w.Sid = s.Sid AND w.P1 = o.File_Id AND w.P2 >= o.Block_Id AND w.P2 < o.Block_Id + o.Blocks 24、求當前事務的重做尺寸 SELECT V$statname.NAME,VALUE FROM V$mystat, V$statname WHERE V$mystat.Statistic# = V$statname.Statistic# AND V$statname.NAME = 'redo size'; 25、喚醒smon去清除臨時段 column pid new_value Smon set termout off SELECT p.Pid FROM Sys.v_$bgprocess b, Sys.v_$process p WHERE b.NAME = 'SMON' AND p.Addr = b.Paddr; / SET Termout ON Oradebug Wakeup &Smon Undefine Smon 26、求回退率 SELECT b.VALUE / (a.VALUE + b.VALUE), a.VALUE, b.VALUE FROM V$sysstat a, V$sysstat b WHERE a.Statistic# = 4 AND b.Statistic# = 5; 27、求free memory SELECT * FROM V$sgastat WHERE NAME = 'free memory'; SELECT a.NAME, SUM(b.VALUE) FROM V$statname a, V$sesstat b WHERE a.Statistic# = b.Statistic# GROUP BY a.NAME; 查看一下誰在使用那個可以得回滾段,或者查看一下某個可以得用戶在使用回滾段, 找出領回滾段不斷增長的事務,再看看如何處理它,是否可以將它commit,再不行 就看看能否kill它,等等, 查看當前正在使用的回滾段的用戶信息和回滾段信息: set linesize 121 SELECT r.NAME "ROLLBACK SEGMENT NAME ", l.Sid "ORACLE PID", p.Spid "SYSTEM PID ", s.Username "ORACLE USERNAME" FROM V$lock l, V$process p, V$rollname r, V$session s WHERE l.Sid = p.Pid(+) AND s.Sid = l.Sid AND Trunc(l.Id1(+) / 65536) = r.Usn AND l.TYPE(+) = 'TX' AND l.Lmode(+) = 6 ORDER BY r.NAME; 28、查看用戶的回滾段的信息 SELECT s.Username, Rn.NAME FROM V$session s, V$transaction t, V$rollstat r, V$rollname Rn WHERE s.Saddr = t.Ses_Addr AND t.Xidusn = r.Usn AND r.Usn = Rn.Usn 29、查看內存中存的使用 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(*) "Total" FROM X$bh GROUP BY Decode(Greatest(CLASS, 10), 10, Decode(CLASS, 1, 'Data', 2, 'Sort', 4, 'Header', To_Char(CLASS)), 'Rollback');
󰈣󰈤
 
 
 
  免責聲明:本文僅代表作者個人觀點,與王朝網路無關。王朝網路登載此文出於傳遞更多信息之目的,並不意味著贊同其觀點或證實其描述,其原創性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容、文字的真實性、完整性、及時性本站不作任何保證或承諾,請讀者僅作參考,並請自行核實相關內容。
 
 
竹林裏的甜美養眼美女
MM尖尖的下巴
靓麗的草根美女
超靓迷人的大眼睛美女
海南(二)
霧繞三清山
Oldtime
美麗婺源
 
>>返回首頁<<
 
 熱帖排行
 
 
 
 
© 2005- 王朝網路 版權所有