标签归档:redo

Oracle 12c new feature: Temporary undo

This is quick blog to demostrate the tiny new feature in 12C: temporary undo. Temporary undo is introdued in 12 and well documented.

By default, undo records for temporary tables are stored in the undo tablespace and are logged in the redo, which is the same way undo is managed for persistent tables. However, you can use the TEMP_UNDO_ENABLED initialization parameter to separate undo for temporary tables from undo for persistent tables. When this parameter is set to TRUE, the undo for temporary tables is called temporary undo.

Temporary undo is controlled by The Parameter TEMP_UNDO_ENABLED. With TEMP_UNDO_ENABLED enable, significantly less Redo is only generated for the very first DML(insert/update/delete) on the global temporary table, there is no redo for the following DML on the temporary tables within the same transaction totally. This may be a great benifit for the ETL processes, which take temporary table as the staging table, load the huge data into temporary table for heavy process before move the data into the final fact table. With less redo, both the sql performance on global temporary table and system loading may be improved a lot.

Perform the same insert/update/delete on the same global temporary table(see the script below), from below session statistics comparision, we can see: 1) With TEMP_UNDO_ENABLED disabled, the total redo size for the insert/update/delete is 4MB, while enabled, only 280 bytes redo size for the first insert statment, and the following update and delete produce zero redo; 2) With TEMP_UNDO_ENABLED disable, The temporary space requirement is 2MB, while enabled, it’s 7MB.

Here is the script for the demo:

1. Prepare the table.

drop table t1 purge;
create global temporary table t1
(
  id number,
  padding varchar2(100)
)
on commit preserve rows;

2. Run insert/update/delete statement with TEMP_UNDO_ENABLED disabled and enabled.

alter session set temp_undo_enabled = FALSE;
--alter session set temp_undo_enabled = TRUE;
show parameter temp_undo_enabled;

exec snap_stats.start_snap;
insert into t1 select rownum, lpad(rownum, 100, ' ') from dual connect by level exec snap_stats.end_snap;
select count(*) from t1;

exec snap_stats.start_snap;
update t1 set padding = padding;
exec snap_stats.end_snap;

exec snap_stats.start_snap;
delete from t1;
exec snap_stats.end_snap;
commit;

P.S. The TEMP_UNDO_ENABLED can’t be changed dynamically within a session: “When a session uses temporary objects for the first time, the current value of the TEMP_UNDO_ENABLED initialization parameter is set for the rest of the session.”

ORA-01555和延迟块清除

01555, 00000, "snapshot too old: rollback segment number %s
with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read
//         are overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase
//	undo_retention setting. Otherwise, use larger rollback segments

出现ORA-01555直接的原因是一致读所需的undo records被覆盖, 一致读失败有两种情况:
1. 数据块中ITL结构对应的undo block被覆盖, 无法构造一致读.
2. transaction table in undo segment header: 延迟块清除发生时, 如果Oracle需要回滚对应的transaction table, 找到事务确切提交的时间. 而且所需的undo record被覆盖, ORA-01555也会发生.
继续阅读

False User Commits And User Rollbacks

If a session is doing change, such insert/update/delete, and find there are critical change between consistent read and current read, the session will rollback the statement and restart again; or fail if the transaction isolation is set to SERIALIZABLE. Critical change means the value of the columns either in the predicates or referenced in the before for each row trigger has changed. Tom Kyte demonstrate the restart feature in his book “Export Oracle Database Architecture”. 继续阅读

reading of this two week

Oracle automatic segment space management: ASSM internal structures
http://www.dba-oracle.com/art_builder_assm.htm

Not KEEPing
http://jonathanlewis.wordpress.com/2010/03/20/not-keeping/

Row Directory
http://jonathanlewis.wordpress.com/2009/05/21/row-directory/

Index too big
http://jonathanlewis.wordpress.com/2010/03/25/index-too-big/

heap block compress
http://jonathanlewis.wordpress.com/2010/03/30/heap-block-compress/

Failed Login
http://jonathanlewis.wordpress.com/2010/04/05/failed-login/

Daylight Saving Time Causes Performance Issues!
http://prodlife.wordpress.com/2010/04/02/daylight-saving-time-causes-performance-issues/

Block cleanout – fast or delayed.
http://www.jlcomp.demon.co.uk/cleanout.html

Snapshot too old and delayed block cleanout
http://www.ixora.com.au/q+a/0103/24224451.htm

Parse Calls
http://jonathanlewis.wordpress.com/2007/07/03/parse-calls/

Nutshell – 1
http://jonathanlewis.wordpress.com/2009/10/14/nutshell-1/