052-187(新增70题2018)

What advantage would you get if you increase the length of time during which the undo information would be stored in the database?

A. roll back of large transactions

B. roll forward to redo a transaction

C. data concurrency for large updates

D. read consistency for long-running transactions

Answer: D

在Oracle数据库中，undo的作用是什么？
undo

主要作用有三个：提供一致性读（Consistent Read）

                              回滚事务（RollbackTransaction）

                              实例恢复（Instance Recovery）

这里只增加保存时间，对应的对长时间事务的一致性读有帮助。

undo_retention之确定最优的撤销保留时间

使用下面的公式来计算undo_retention参数的值：
undo_retention=undo size/(db_block_size * undo_block_per_sec)

SQL> select sum(d.bytes/1024/1024) "undo_mb"
  2    from v$datafile d, v$tablespace t, dba_tablespaces s
  3   where s.contents = 'UNDO'
  4     and s.status = 'ONLINE'
  5     and t.name = s.tablespace_name
  6     and d.ts# = t.ts#;
   undo_mb
----------
       325

SQL> select max(undoblks / ((end_time - begin_time) * 3600 * 24)) "UNDO_BLOCK_PER_SEC"  FROM v$undostat;  
UNDO_BLOCK_PER_SEC
------------------
  3.75833333333333

SQL> show parameter db_block_size 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> select 325*1024*1024/(3.75833333333333*8192) from dual;
325*1024*1024/(3.7583333333333
------------------------------
              11068.7361419069