又一个WordPress站点

标签归档：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.

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.”

There is an interesting case in this week. A query run over hours, it take millions of buffer gets. The query is to verify if there is any data in the past 3 month for a customer, which usually completes in a minute. The case can’t be reproduced in production clone DB. The same execution path return around 10 seconds, doing 3829 buffer gets. The execution plan is as expected as below, the index skip scan will return hundreds of rows, zero or several rows return after nested loop join. 继续阅读 →