Oracle動態效能檢視學習筆記(3)_v$undostat

參考文件<>Chapter 24

##################################################################
1 Overview
##################################################################
V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction
concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns null values if the system is in manual undo management mode. Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 1008 rows, spanning a 7 day cycle.

(這個檢視可以統計undo表空間的使用情況，事務的併發度，查詢語句的執行時間。透過這個檢視可以估算undo表空間的適合大小。
預設情況下每隔10Min取樣一次資料，共保留7 days)

##################################################################
2 Useful Columns for v$undostat
##################################################################
1) End_time: End time for each ten minute interval

2) UNDOBLKS: The total number of undo blocks consumed.

3) MAXCONCURRENCY: Maximum number of transactions executed concurrently.
(最大的併發度)

4) TXNCOUNT: Total number of transactions executed within the interval.
(間隔期間內總共的事務數)

5) MAXQUERYLEN: Maximum length of queries, in seconds executed in the instance
(最長的查詢時間)

6) EXPSTEALCNT: Number of times an undo extent must be transferred from one undo segment to another within the interval.
(undo extent出現Segment遷移的次數，什麼時候會出現這種情況呢？)

7) UNDOTSN: undo tablespaces in service during each time period
undo表空間的ts#號

8) SSOLDERRCNT:Identifies the number of times the error ORA-01555 occurred.
出現ORA-01555錯誤的次數

##################################################################
3 示例
##################################################################
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select begin_time, end_time, undoblks, MAXCONCURRENCY, txncount,MAXQUERYLEN, EXPSTEALCNT, SSOLDERRCNT, undotsn from v$undostat;

SQL> show parameter retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     10800

SQL> select 10800/3600 from dual;

10800/3600
----------
         3

UNDO表空間的監測:
正在使用的undo block.返回的每一行代表一個Extent,每個Extent包含的Blocks數可能為1024,128.64,....
select tablespace_name,segment_name, blocks, bytes/1024, status,BLOCK_ID,BLOCK_ID+blocks-1 from dba_undo_extents where status='ACTIVE';
or
select tablespace_name, sum(bytes/1024/1024) from dba_undo_extents where status='ACTIVE' group by tablespace_name;

select begin_time, end_time, UNDOBLKS ten, ((undoblks*6*3)*8*1.5)/1024/1024 undo_size, TXNCOUNT,MAXQUERYLEN,MAXCONCURRENCY
from v$undostat
order by undoblks desc ;

6 -- 一小時 6 * 10
3 --- 為undo_retention時間．
8 --- blocksize=8k
1.5 --為預估1.5倍空間

從這裡可以預估undo表空間size 大小．

Oracle動態效能檢視學習筆記(3)_v$undostat

相關文章