Tuesday, January 16, 2018

Temporary Undo

You might be in a situation where you are administering a database that supports an OLTP workload. An application regularly creates global temporary tables and a large number of transactions are performed on them. You notice that performance is degraded because of excessive generation of undo due to a large number of transactions on the global temporary tables.

Oracle database 12c Release 1 (12.1) introduced the concept of temporary undo, with this the undo segments for global temporary tables can be stored in the temporary tablespace.
By default we used to store undo segments of global temporary tables in regular undo tablespace, which is protected by REDO. 
This can cause multiple issues :-
  • Database should be in read-write mode to writing into undo tablespace. So global temporary tables can't be used in read-only databases and physical standby databases. 
  • Transient data ( contained by global temporary tables) is not required in database recovery scenarios. So protecting them with redo represents an unnecessary additional load on the system. 
  • The undo associated with global temporary tables adds to the total space needed to meet the undo retention period.

Key Points :-
  • When temporary undo is enabled, it might be necessary to increase the size of the temporary tablespaces to account for the undo records. 
  • 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. 
  • The temporary undo functionality is only available if the COMPATIBLE parameter is set to 12.0.0 or higher. 
  • Temporary undo is the default for standby databases, so any alteration to the TEMP_UNDO_ENABLED parameter is ignored on standby databases. 
  • Temporary undo is enabled by default for a physical standby database with the Oracle Active Data Guard option. 
  • 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 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.

Enable/Disable Temporary Undo :-
In Oracle 12c, undo segments for global temporary tables are still managed using the conventional undo tablespace by default. Temporary undo is enabled/disabled using the TEMP_UNDO_ENABLED parameter at session or system level.
--Session Level
ALTER SESSION SET TEMP_UNDO_ENABLED=TRUE/FALSE;
--System Level
ALTER SYSTEM SET TEMP_UNDO_ENABLED=TRUE/FALSE

Monitor Temporary Undo :-
  • V$UNDOSTAT -Regular undo activity
  • V$TEMPUNDOSTAT -temporary undo activity
Hands-On :-
Step 1 : Create Temporary Table
Create a global temporary table for testing.
SYS@racdb1> CREATE GLOBAL TEMPORARY TABLE test_table (
  id           NUMBER,
  details        VARCHAR2(10)
)
ON COMMIT DELETE ROWS;
Table created.
Make sure the user can access the V$TEMPUNDOSTAT view.Here I'm using SYS else you need to execute below grant statement
GRANT SELECT ON v_$tempundostat TO <user>;

Step 2 : Load data into GTT

Load some data into the GTT, then checks the redo and undo usage. This example is not using temporary undo.
SYS@racdb1> SET AUTOTRACE ON STATISTICS;
SYS@racdb1> INSERT INTO test_table
WITH data AS (
  SELECT 10 AS id
  FROM   dual
  CONNECT BY level < 20000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 2000000; 
2000000 rows created.
Statistics
----------------------------------------------------------
         58  recursive calls
      32706  db block gets
       5012  consistent gets
         32  physical reads
    5930248  redo size
        855  bytes sent via SQL*Net to client
        984  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    2000000  rows processed

Step 3 : Verify UNDO generated by transaction
SYS@racdb1> SET AUTOTRACE OFF
SYS@racdb1> -- Check undo used by transaction.
SYS@racdb1> SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 
 USED_UBLK  USED_UREC
---------- ----------
       614      12767
So this insert produces 614 blocks of undo, which is why the statistics show a redo size of 5.9 million bytes of redo, generated to protect the undo.

Now we'll enable temporary undo,checks the redo and undo usage.
SYS@racdb1> ALTER SESSION SET TEMP_UNDO_ENABLED = TRUE;
Session altered.
SYS@racdb1> SET AUTOTRACE ON STATISTICS;
SYS@racdb1> INSERT INTO test_table
WITH data AS (
  SELECT 10 AS id
  FROM   dual
  CONNECT BY level < 20000
)
SELECT rownum, TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 2000000;   
2000000 rows created.
Statistics
----------------------------------------------------------
         48  recursive calls
      32760  db block gets
       4979  consistent gets
         31  physical reads
        980  redo size
        845  bytes sent via SQL*Net to client
        984  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    2000000  rows processed
SYS@racdb1> SET AUTOTRACE OFF
SYS@racdb1> -- Check undo used by transaction.
SYS@racdb1> SELECT t.used_ublk,
       t.used_urec
FROM   v$transaction t,
       v$session s
WHERE  s.saddr = t.ses_addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID'); 
 USED_UBLK  USED_UREC
---------- ----------
         1          1
SYS@racdb1> -- Check temporary undo usage.
SYS@racdb1> SET LINES 1000
SYS@racdb1> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.
SYS@racdb1> SELECT *
FROM   v$tempundostat
WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;  2    3
BEGIN_TIME           END_TIME                UNDOBLKCNT   
-------------------- -------------------- ---------- ---------- --------------
16-JAN-2018 19:35:54 16-JAN-2018 19:45:57         648  

Here we can see only a single byte of conventional undo is generated, which results in approximately 980 bytes of redo. The UNDOBLKCNT of the V$TEMPUNDOSTAT view shows us 648 blocks of temporary undo have been used.

So here with help of "Temporary Undo" we can address the issue causing by excessive generation of undo due to a large number of transactions on the global temporary tables.

No comments:

Post a Comment