Oracle 12c 新特性之临时Undo--temp_undo_enabled

最新推荐文章于 2021-04-03 05:02:55 发布

coco3600

最新推荐文章于 2021-04-03 05:02:55 发布

阅读量425

点赞数

文章标签：数据库

Oracle 12c 新特性之临时Undo--temp_undo_enabled

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/managing-undo.html#GUID-B159A41D-C28C-4E6E-BAAF-FE5ACE2C18B7

每个 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

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.

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:

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

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2212794/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2212794/

coco3600

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle 12c 新特性之临时Undo--temp_undo_enabled

Oracle 12c 新特性之临时Undo--temp_undo_enabledhttps://docs.oracle.com/en/database/oracle/ora...
复制链接

扫一扫