The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 244 other followers

Temporary undo

Posted by Harald van Breederode on July 28, 2013

Back in Oracle 8i the Global Temporary Table was introduced to reduce the overhead of storing temporary data inside the database. A Global Temporary Table stores its data in a Temporary Tablespace where the data is retained for the duration of either a single transaction or the lifetime of a session. Performing DML on a Global Temporary Table doesn’t generate redo, because the data is stored in a temporary tablespace, but it does generate undo which in turn will generate redo since the undo is stored in a permanent tablespace. Because this redo isn’t required for recovery purposes, wouldn’t it be nice if it wasn’t generated in the first place?

Oracle 12c makes it possible to store the undo generated by performing DML on global temporary tables in a temporary tablespace. This feature is called Temporary Undo and is controlled by the temp_undo_enabled database parameter which is modifiable at the system or session level. There are two reasons for enabling this new feature:

  1. To reduce the amount of redo caused by DML on global temporary tables.
  2. To allow DML on global temporary tables in a physical standby database.

Using temporary undo to reduce the amount of redo

One reason for using temporary undo is to reduce the amount of redo caused by DML on global temporary tables. All we need to do to demonstrate this is insert a bunch of rows in a global temporary table. So, let’s get started by creating a global temporary table in an Oracle 12c database:

SQL> connect sh/sh
Connected.
SQL> create global temporary table gtt(c1 number(8), c2 char(100));

Table created.

By default the temp_undo_enabled parameter is set to false, so inserting rows in a global temporary table will generate redo as shown below:

SQL> connect sh/sh
Connected.
SQL> show parameter temp_undo_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE

SQL> select name, value from v$mystat natural join v$statname
  2  where name = 'redo size' or name = 'redo entries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                              2
redo size                                                               720

SQL> insert into gtt select level, level from dual connect by level <= 10000;

10000 rows created.

SQL> select name, value from v$mystat natural join v$statname
  2  where name = 'redo size' or name = 'redo entries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                            287
redo size                                                             63064

Based on this output we can derive that inserting 10,000 rows generated 285 redo entries with a total of 62 KBytes of redo.

Let’s repeat this test with temp_undo_enabled set to true and see what happens:

SQL> connect sh/sh
Connected.
SQL> alter session set temp_undo_enabled = true;

Session altered.

SQL> select name, value from v$mystat natural join v$statname
  2  where name = 'redo size' or name = 'redo entries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                              2
redo size                                                               720

SQL> insert into gtt select level, level from dual connect by level <= 10000;

10000 rows created.

SQL> select name, value from v$mystat natural join v$statname
  2  where name = 'redo size' or name = 'redo entries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                              3
redo size                                                               992

This time only one redo entry containing 272 bytes of redo was generated by inserting 10,000 rows. Hopefully this is what you expected ;-)

Temporary undo and a physical standby database

Another reason for using temporary undo is to allow DML on global temporary tables stored in a read-only opened physical standby database. To demonstrate this we need an Oracle 12c Data Guard environment as shown below:

DGMGRL> show configuration;

Configuration - Animals

  Protection Mode: MaxPerformance
  Databases:
  dogs  - Primary database
    sheep - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database sheep 

Database - sheep

  Role:              PHYSICAL STANDBY
  Intended State:    APPLY-ON
  Transport Lag:     0 seconds (computed 1 second ago)
  Apply Lag:         0 seconds (computed 1 second ago)
  Apply Rate:        19.00 KByte/s
  Real Time Query:   ON
  Instance(s):
    v1210

Database Status:
SUCCESS

Temporary undo is automatically enabled when connected to a physical standby database, so there is no need to set the temp_undo_enabled parameter. Let’s see what happens if we insert rows in a global temporary table in a read-only opened physical standby database:

SQL> connect sh/sh@sheep
Connected.
SQL> select name, value from v$mystat natural join v$statname
  2  where name = 'redo size' or name = 'redo entries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                              0
redo size                                                                 0

SQL> insert into gtt select level, level from dual connect by level <= 10000;

10000 rows created.

SQL> select name, value from v$mystat natural join v$statname
  2  where name = 'redo size' or name = 'redo entries';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                              0
redo size                                                                 0 

Wow, we can insert data in a read-only opened database without generating redo! This used to be impossible on earlier releases of Oracle. The output below was captured on an Oracle 11g Data Guard environment:

SQL> connect sh/sh@sheep11g
Connected.

SQL> insert into gtt select level, level from dual connect by level <= 10000;
insert into gtt select level, level from dual connect by level <= 10000
            *
ERROR at line 1:
ORA-16000: database open for read-only access

In conclusion

Temporary undo stores undo caused by performing DML on global temporary tables in a temporary tablespace thereby eliminating the need to generate redo. This makes it also possible to perform DML on global temporary tables stored in a read-only opened physical standby database.

Temporary undo is good for performance in general and Data Guard performance in particular because DML on global temporary tables in the physical standby database do not generate redo and DML on global temporary tables in the primary database generate less redo to be transmitted over the network to the standby database(s).
-Harald

About these ads

3 Responses to “Temporary undo”

  1. Bruno Fabietti said

    Thanks Harald,
    Clear example of this new feature.
    If I understand this correctly, you could now build a complete , temporary. subsystem in your standby database, populate it with a point-in-time subset of the fixed data, run a special application, then throw the tables away and do it again as needed.
    Pretty powerful.

    • Harald van Breederode said

      Hi Bruno,

      Thanx for your comment. I am not sure if you understand it correctly ;-)
      Remember that you cannot create global temporary tables in a physical standby database, you can only use them to load and manipulate data. If you need a new one, you can only create it on the primary database.
      The best solution for your requirement is to make use of a snapshot standby database.
      Hope this answers your question?
      -Harald

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 244 other followers