程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle中IMU技術和redoprivatestrand技術

Oracle中IMU技術和redoprivatestrand技術

編輯:Oracle教程

Oracle中IMU技術和redoprivatestrand技術


oracle030

oracle030
Oracle中IMU技術和redo private strand技術



3、圖解Oracle IMU機制 \
select * from v$sysstat where name like "%IMU%'; STATISTIC#, NAME, CLASS, VALUE, STAT_ID
312 IMU commits 128 393 1914489094
313 IMU Flushes 128 88 2099506212
314 IMU contention 128 1 2909373607
315 IMU recursive-transaction flush 128 2 2591100633
316 IMU undo retention flush 128 0 2087226422
317 IMU ktichg flush 128 0 1206609541
318 IMU bind flushes 128 0 2756376339
319 IMU mbu flush 128 0 3723686946
320 IMU pool not allocated 128 0 659017805
321 IMU CR rollbacks 128 114 2225124543
322 IMU undo allocation size 128 1867712 244193920
323 IMU Redo allocation size 128 161408 3945654623
324 IMU- failed to get a private strand 128 0 2412863545
4、圖解Oracle private redo strands機制 \
查看回滾段的使用情況,哪個用戶正在使用回滾段的資源
select s.username, u.name from v$transaction t,v$rollstat r,
v$rollname u,v$session s where s.taddr=t.addr and
t.xidusn=r.usn and r.usn=u.usn order by s.username;

檢查UNDO Segment狀態
select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
from v$rollstat order by rssize;



確定當前例程正在使用的UNDO表空間
Show parameter undo_tablespace

顯示數據庫的所有UNDO表空間
SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO';

顯示UNDO表空間統計信息
SELECT TO_CHAR(BEGIN_TIME,'HH24:MI:SS') BEGIN_TIME,
TO_CHAR(END_TIME,'HH24:MI:SS') END_TIME,UNDOBLKS
FROM V$UNDOSTAT;

顯示UNDO段統計信息
SELECT a.name, b.xacts, b.writes, b.extents
FROM v$rollname a, v$rollstat b
WHERE a.usn=b.usn;

顯示活動事務信息
Col username format a10
Col name format a10
SELECT a.username, b.name, c.used_ublk
FROM v$session a, v$rollname b, v$transaction c
WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn
AND a.username='HR';







V$ROLLSTAT中的常用列

USN Rollback segment number
EXTENTS Number of extents in the rollback segment
RSSIZE Size (in bytes) of the rollback segment.
WRITES Number of bytes written to the rollback segment
XACTS Number of active transactions
GETS Number of header gets
WAITS Number of header waits
OPTSIZE Optimal size of the rollback segment
HWMSIZE High-watermark of rollback segment size
SHRINKS Number of times the size of a rollback segment decreases
WRAPS Number of times rollback segment is wrapped
EXTENDS Number of times rollback segment size is extended
AVESHRINK Average shrink size
AVEACTIVE Current size of active extents, averaged over time.
STATUS Rollback segment status:
ONLINE
PENDING OFFLINE
OFFLINE
FULL
CUREXT Current extent
CURBLK Current block

5、讀一致性
ORA-01555錯誤

6、undo advisor
EM

redo的中讀一致性(ORA-01555錯誤機制問題)問題undo表空間的大小設置
\
\

解決這樣問題:1. 確保undo表空間數據的保留時間至少大於最長sql語句的時間 2. 增大undo表空間的大小 就是根據時間找大小。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved