Oracle 12c 新特性之臨時Undo--temp_undo_enabled

Oracle 12c 新特性之臨時Undo--temp_undo_enabled

每個 Oracle 資料庫包含一組與系統相關的表空間，例如 SYSTEM ， SYSAUX ， UNDO&TEMP ，並且它們在 Oracle 資料庫中每個都用於不同的目的。 在 Oracle 12c 之前，臨時表生成的 Undo 記錄是儲存在 Undo 表空間 和 Redo 日誌檔案中 的，通用表和持久表的 undo 記錄也是 儲存在 Undo 表空間中的，從 Oracle 12c 開始 ，臨時 Undo 記錄可以儲存在 臨時表空間中，且不再記錄到 Redo 日誌檔案中， 這樣做的主要好處在於： 減少 Undo 表空間 的使用 ， 且 減少了 Redo 資料的生成 。可以在會話級別或者資料庫級別來啟用臨時 Undo 選項。

Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called temporary undo segments .You can enable or disable temporary undo for a session or for the system. To do so, set the TEMP_UNDO_ENABLED initialization parameter.

 

 

系統會廣泛使用臨時表作為暫存區來存放中間結果。這是因為更改這些表的速度遠遠超過更改非臨時表的速度。效能之所以會改進，主要是因為不會為臨時表中的更改直接生成重做條目。但是，對臨時表（和索引）的操作的還原資料仍會記錄在重做日誌中。

臨時表的還原資料對於在臨時物件生存期中實現讀取一致性和事務處理回退非常有用。除此之外，不需要該還原資料。因此，它無需儲存在重做流中。例如，事務處理恢復將放棄臨時物件的還原資料。

從 Oracle Database 12c 開始，可以將由臨時表的事務處理生成的還原資料直接儲存在臨時表空間的一個單獨還原流中，以避免將該還原資料記錄在重做流中。這種新模式稱為臨時還原。

注：臨時還原段是會話專用的。它將儲存對屬於相應會話的臨時表（一般為臨時物件）所做的更改的還原資料。

 

臨時 Undo ：優點和設定

• 臨時 Undo 可減少 Undo 表空間中儲存的 Undo 資料量。

• 臨時 Undo 可減小重做日誌的大小。

• 臨時 Undo 支援在具有 Oracle Active Data Guard 選件的物理備用資料庫中對臨時表執行 DML 操作。

SQL> ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;

SQL> ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;

• 會話首次使用臨時物件時會選擇臨時還原模式。

 

啟用臨時還原具有以下優點：

• 臨時還原可減少還原表空間中儲存的還原資料量。還原表空間中的還原資料越少，還原記錄所需要的還原保留期越實際。

• 臨時還原可減小重做日誌的大小。由於寫入重做日誌的資料較少，因此效能會有所提高，並且由於要進行語法分析的重做資料較少，因此，用於對重做日誌記錄進行語法分析的元件（如 LogMiner ）效能也會有所提高。

• 臨時還原支援在具有 Oracle Active Data Guard 選件的物理備用資料庫中對臨時表執行資料操縱語言 (DML) 操作。但是，必須在主資料庫上發出建立臨時表的資料操縱語言 (DDL) 操作。

可以為特定會話或整個系統啟用臨時還原。為某個會話啟用臨時還原時，該會話將建立臨時還原，而不影響其他會話。當會話首次使用臨時物件時，系統將為該會話的其餘部分設定 TEMP_UNDO_ENABLED 初始化引數的當前值。因此，如果為某個會話啟用了臨時還原，並且該會話使用臨時物件，則無法為該會話禁用臨時還原。同樣，如果為某個會話禁用了臨時還原，並且該會話使用臨時物件，則無法為該會話啟用臨時還原。為系統啟用臨時還原時，所有現有會話和新會話都將建立臨時還原。

 

（一） 啟用臨時 undo 功能

要使用這一新功能，需要做以下設定：

ü  相容性引數必須設定為 12.0.0 或更高

ü  啟用 TEMP_UNDO_ENABLED 初始化引數

ü  由於臨時 undo 記錄現在是儲存在一個臨時表空間中的，你需要有足夠的空間來建立這一臨時表空間

ü  對於會話級，你可以使用： ALTER SYSTEM SET TEMP_UNDO_ENABLE=TRUE;

（二） 查詢臨時 undo 資訊

以下所列的字典檢視是用來檢視或查詢臨時 undo 資料相關統計資訊的：

ü  V$TEMPUNDOSTAT

ü  DBA_HIST_UNDOSTAT

ü  V$UNDOSTAT

要禁用此功能，你只需做以下設定：

SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;

 

SELECT to_char(BEGIN_TIME,'dd/mm/yy hh24:mi'),

TXNCOUNT,MAXCONCURRENCY,UNDOBLKCNT,USCOUNT,

NOSPACEERRCNT

FROM V$TEMPUNDOSTAT;

 

 

V$TEMPUNDOSTAT 顯示了與此資料庫例項的臨時還原日誌相關的各種統計資訊。它將顯示統計資料直方圖，以展示系統的執行情況。該檢視的每一行會儲存從例項中收集的 10 分鐘間隔統計資訊。各個行按 BEGIN_TIME 列值降序排列。此檢視總共包含 576 行，跨越一個四天週期。此檢視與 V$UNDOSTAT 檢視類似。該示例顯示了 V$TEMPUNDOSTAT 檢視中的一些重要列：

•BEGIN_TIME ：確定時間間隔的開始。

•TXNCOUNT ：在相應的時間間隔內繫結到臨時還原段的事務處理總數。

•MAXCONCURRENCY ：並行執行的最多事務處理數，這些事務處理會在相應的時間間隔內修改臨時物件。

•UNDOBLKCNT ：在相應的時間間隔內佔用的臨時還原塊總數。

•USCOUNT ：在相應的時間間隔內建立的臨時還原段。

•NOSPACEERRCNT ：在相應的時間間隔內引發錯誤 “no space left for temporary   undo （沒有用於臨時還原的剩餘空間） ” 的總次數。

--------測試指令碼
ALTER SYSTEM SET TEMP_UNDO_ENABLED=TRUE;
 
事務臨時表：
CREATE GLOBAL TEMPORARY TABLE CGTT_DELETE_LHR AS SELECT * FROM SCOTT.EMP WHERE 1=2;
INSERT INTO CGTT_DELETE_LHR SELECT * FROM SCOTT.EMP WHERE ROWNUM<=5;
 
會話臨時表：
CREATE GLOBAL TEMPORARY TABLE CGTT_PRESERVE_LHR ON COMMIT PRESERVE ROWS AS SELECT * FROM SCOTT.EMP WHERE 1=2;
INSERT INTO CGTT_PRESERVE_LHR SELECT * FROM SCOTT.EMP WHERE ROWNUM<=5;
SELECT * FROM CGTT_PRESERVE_LHR;
 
 
檢視一張表是否臨時表，可以從DBA_TABLES檢視的DURATION列來查詢：
SELECT UT.TABLE_NAME,
       UT.TABLESPACE_NAME,
       DECODE(UT.DURATION,'SYS$SESSION','會話級','SYS$TRANSACTION','事務級') T_TYPE
  FROM DBA_TABLES UT
 WHERE UT.TEMPORARY = 'Y'
   AND UT.TABLE_NAME LIKE '%CGTT%';
 
SELECT to_char(BEGIN_TIME, 'yyyy-mm-dd hh24:mi') BEGIN_TIME,
       TXNCOUNT,
       MAXCONCURRENCY,
       UNDOBLKCNT,
       USCOUNT,
       NOSPACEERRCNT
  FROM V$TEMPUNDOSTAT;

Oracle 12c R1 之前，臨時表生成的undo記錄是儲存在undo表空間裡的，通用表和持久表的undo記錄也是類似的。而在 12c R12 的臨時 undo 功能中，臨時 undo 記錄可以儲存在一個臨時表空間中，而無需再儲存在 undo 表空間內。臨時表的UNDO資訊通常用於讀一致性和事務回滾，在事務完成之後，無需進行恢復，所以也就不必永久儲存。這個特性完全無損Oracle的事務一致性。這樣做的主要好處在於：減少 undo 表空間，由於資訊不會被記錄在 redo 日誌中，所以減少了 redo 資料的生成。你可以在會話級別或者資料庫級別來啟用臨時 undo 選項。

啟用 temp undo 好處：
1.減少 undo 表空間
2.減少 redo 資料
3.允許在ADG中對臨時表進行DML操作

啟用 temp undo 要求：
1.相容性引數必須設定為 12.0.0 或更高
2.啟用 c##andy.temp_undo_ENABLED 初始化引數
3.足夠的空間來建立臨時表空間

開啟或者禁用 temp undo:
SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=TRUE;
SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;

16.7  Managing Temporary Undo

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 .

• About Managing Temporary Undo
  Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called  temporary undo segments .

• Enabling and Disabling Temporary Undo
  You can enable or disable temporary undo for a session or for the system. To do so, set the  TEMP_UNDO_ENABLED initialization parameter.

16.7.1  About Managing Temporary Undo

Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called  temporary undo segments .

When temporary undo is enabled, it might be necessary to increase the size of the temporary tablespaces to account for the undo records.

Enabling temporary undo provides the following benefits:

• Temporary undo reduces the amount of undo stored in the undo tablespaces.

  Less undo in the undo tablespaces can result in more realistic undo retention period requirements for undo records.

• Temporary undo reduces the size of the redo log.

  Performance is improved because less data is written to the redo log, and components that parse redo log records, such as LogMiner, perform better because there is less redo data to parse.

• Temporary undo enables data manipulation language (DML) operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database.

You can enable temporary undo for a specific session or for the whole system. When you enable temporary undo for a session using an  ALTER   SESSION  statement, the session creates temporary undo without affecting other sessions. When you enable temporary undo for the system using an  ALTER   SYSTEM  statement, all existing sessions and new sessions create temporary undo.

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. Therefore, if temporary undo is enabled for a session and the session uses temporary objects, then temporary undo cannot be disabled for the session. Similarly, if temporary undo is disabled for a session and the session uses temporary objects, then temporary undo cannot be enabled for the session.

Temporary undo is enabled by default for a physical standby database with the Oracle Active Data Guard option. The  TEMP_UNDO_ENABLED  initialization parameter has no effect on a physical standby database with Active Data Guard option because of the default setting.

Note:

Temporary undo can be enabled only if the compatibility level of the database is 12.0.0 or higher.

See Also:

• " Creating a Temporary Table "

• " About the Undo Retention Period "

• Oracle Database Reference  for more information about the  TEMP_UNDO_ENABLED  initialization parameter

• Oracle Data Guard Concepts and Administration

• Oracle Database Concepts  for more information about temporary undo segments

16.7.2  Enabling and Disabling Temporary Undo

You can enable or disable temporary undo for a session or for the system. To do so, set the  TEMP_UNDO_ENABLED  initialization parameter.

To enable or disable temporary undo:

1. In SQL*Plus, connect to the database.

   If you are enabling or disabling temporary undo for a session, then start the session in SQL*Plus.

   If you are enabling or disabling temporary undo for the system, then connect as an administrative user with the  ALTER SYSTEM  system privilege in SQL*Plus.

   See  " Connecting to the Database with SQL*Plus " .

2. Set the  TEMP_UNDO_ENABLED  initialization parameter:

• To enable temporary undo for a session, run the following SQL statement:

  ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
  

• To disable temporary undo for a session, run the following SQL statement:

  ALTER SESSION SET TEMP_UNDO_ENABLED = FALSE;
  

• To enable temporary undo for the system, run the following SQL statement:

  ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
  

  After temporary undo is enabled for the system, a session can disable temporary undo using the  ALTER   SESSION statement.

• To disable temporary undo for the system, run the following SQL statement:

  ALTER SYSTEM SET TEMP_UNDO_ENABLED = FALSE;
  

  After temporary undo is disabled for the system, a session can enable temporary undo using the  ALTER   SESSION statement.

You can also enable temporary undo for the system by setting  TEMP_UNDO_ENABLED  to  TRUE  in a server parameter file or a text initialization parameter file. In this case, all new sessions create temporary undo unless temporary undo is disabled for the system by an  ALTER   SYSTEM  statement or for a session by an  ALTER   SESSION  statement.

See Also:

• Oracle Database Reference  for more information about the  TEMP_UNDO_ENABLED  initialization parameter

• Oracle Data Guard Concepts and Administration  for information about enabling and disabling temporary undo in an Oracle Data Guard environment

TEMP_UNDO_ENABLED

TEMP_UNDO_ENABLED  determines whether transactions within a particular session can have a temporary undo log.

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	ALTER SESSION ,  ALTER SYSTEM
Modifiable in a PDB	Yes
Range of values	true | false
Basic	No
Oracle RAC	Each session of each instance can have its own value or not set any value at all

The default choice for database transactions has been to have a single undo log per transaction. This parameter, at the session level / system level scope, lets a transaction split its undo log into temporary undo log (for changes on temporary objects) and permanent undo log (for changes on persistent objects).

By splitting the undo stream of a transaction into two streams (temporary and permanent), a database can provide separate storage and retention model for these. This results in overall reduction in the size of undo log and redo log in the database

If database applications make use of temporary objects (using global temporary tables or temporary table transformations), it is advisable to set this parameter's value to  true .

When  TEMP_UNDO_ENABLED  is set to  true  and the COMPATIBLE initialization parameter is set to  12.0.0 , this feature is enabled. The temporary undo feature is enabled for the session in which it is set. Setting it across the system will affect all existing and upcoming sessions. If the value is set in the  init.ora  file, all upcoming sessions will inherit this value unless overwritten by an explicit ALTER SESSION or ALTER SYSTEM statement. All undo for operations on temporary objects is deemed temporary.

If  TEMP_UNDO_ENABLED  is not set to  true , existing applications that make use of temporary objects run as is without any change.

Once the value of the parameter is set, it cannot be changed for the lifetime of the session. If the session has temporary objects using temporary undo, the parameter cannot be disabled for the session. Similarly, if the session already has temporary objects using regular undo, setting this parameter will have no effect.

This parameter is only applicable for the primary database. For a standby database, this parameter is ignored because temporary undo is enabled by default on the standby database.