Oracle12c中效能最佳化&功能增強新特性之臨時undo

臨時表最有意思的特點之一是undo段也儲存在常規undo表空間中，而它們的undo反過來被redo保護，這會導致一些問題。
1)  寫undo表空間需要資料庫以讀寫模式開啟，因此，只讀資料庫和物理備庫中不能用臨時表。
2)  臨時表中儲存的是臨時資料，恢復資料庫時不需要恢復它們，因此，浪費redo來保護它們沒必要。
3)  臨時表的undo佔用滿足undo保留時間的表空間。
Oracle12c中，允許將臨時表的undo資料儲存在臨時表空間中，這使得臨時表可以用於物理備庫和只讀庫中，因為臨時表沒必要產生redo。
1.   啟用/禁用臨時undo
Oracle12c中，臨時表的undo段預設還是透過常規undo表空間管理。臨時undo透過TEMP_UNDO_ENABLED引數在會話或系統級別啟用或禁用。
-- 會話級別
ALTER SESSION SETTEMP_UNDO_ENABLED = TRUE;
ALTER SESSION SETTEMP_UNDO_ENABLED = FALSE;
 
-- 系統級別
CONN sys@pdb1 AS SYSDBA
 
ALTER SYSTEM SETTEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SETTEMP_UNDO_ENABLED = FALSE;
以下為臨時undo的注意事項。
1)  臨時undo功能僅在 COMPATIBLE引數設定為12.0.0或更高時才可用。
2)  一旦會話建立了一些臨時undo，無論用常規還是臨時undo，怎麼設定TEMP_UNDO_ENABLED引數也是無濟於事的，除非退出會話，期間，也不會有什麼報錯。
3)  臨時undo在備庫上是預設的設定，因此，無論在備庫上怎麼設定TEMP_UNDO_ENABLED引數，都沒什麼作用。
2.   監控臨時undo
常規undo活動透過V$UNDOSTAT檢視監控。Oracle12c中，增加了V$TEMPUNDOSTAT檢視來監控臨時undo活動。下面透過建立一個臨時表來測試。
CONN test/test@pdb1
 
DROP TABLE g_t_tab PURGE;
 
CREATE GLOBAL TEMPORARY TABLEg_t_tab (
  c1          NUMBER,
  c2 VARCHAR2(20)
)
ON COMMIT DELETE ROWS;
確信使用者可以訪問V$TEMPUNDOSTAT檢視。
CONN sys@pdb1 AS SYSDBA
 
GRANT SELECT ONv_$tempundostat TO test;
下面的測試建立一個會話，往臨時表裡載入一些資料，然後，檢查redo和undo的使用變化。下例中沒有用到臨時undo。
CONN test/test@pdb1
SET AUTOTRACE ON STATISTICS;
 
--載入資料
INSERT INTO g_t_tab
WITH data AS (
  SELECT 1 AS c1
  FROM  dual
  CONNECT BY level < 10000
)
SELECT rownum,TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;
 
1000000 rows created.
 
Statistics
----------------------------------------------------------
         46 recursive calls
      15346 db block gets
       2379 consistent gets
         16 physical reads
    2944564 redo size
        855 bytes sent via SQL*Net to client
        986 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
    1000000 rows processed
 
SQL>
 
SET AUTOTRACE OFF
 
-- 檢查該事務產生的undo
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
---------- ----------
       302      6238
 
SQL>
我們可以看到該操作產生了302個undo塊，這就是統計資訊顯式產生了2.9M redo的原因，是為了保護這些undo塊。
下例建立一個會話，開啟臨時undo，往臨時表中載入一些資料，再檢查redo和undo使用變化，即透過V$TEMPUNDOSTAT檢視來檢查產生的undo。
CONN test/test@pdb1
 
-- 開啟臨時undo
ALTER SESSION SETTEMP_UNDO_ENABLED = TRUE;
 
SET AUTOTRACE ON STATISTICS;
 
-- 載入資料
INSERT INTO g_t_tab
WITH data AS (
  SELECT 1 AS c1
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum,TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;
 
1000000 rows created.
 
Statistics
----------------------------------------------------------
         25 recursive calls
      15369 db block gets
       2348 consistent gets
         16 physical reads
       1004 redo size
        853 bytes sent via SQL*Net to client
        986 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
    1000000 rows processed
 
SQL>
 
SET AUTOTRACE OFF
 
-- 檢查該事務產生的undo
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
 
SQL>
 
--檢查產生的臨時undo
SET LINESIZE 200
ALTER SESSION SETNLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
 
SELECT *
FROM   v$tempundostat
WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;
 
BEGIN_TIME           END_TIME                UNDOTSN   TXNCOUNT MAXCONCURRENCY MAXQUERYLENMAXQUERYID    UNDOBLKCNT     EXTCNT   USCOUNT SSOLDERRCNT NOSPACEERRCNT   CON_ID
---------------------------------------- ---------- ---------- -------------- ------------------------ ---------- ---------- ---------- ----------- -----------------------
24-NOV-2014 15:11:0923-NOV-2014 15:17:30          3          2              0           0                      321          4         1           0           0           0
 
SQL>
該例中，看到僅產生了少量的常規undo，導致產生了1K的redo。V$TEMPUNDOSTAT檢視顯示產生了321個臨時undo塊。
 
臨時表最有意思的特點之一是undo段也儲存在常規undo表空間中，而它們的undo反過來被redo保護，這會導致一些問題。
1)  寫undo表空間需要資料庫以讀寫模式開啟，因此，只讀資料庫和物理備庫中不能用臨時表。
2)  臨時表中儲存的是臨時資料，恢復資料庫時不需要恢復它們，因此，浪費redo來保護它們沒必要。
3)  臨時表的undo佔用滿足undo保留時間的表空間。
Oracle12c中，允許將臨時表的undo資料儲存在臨時表空間中，這使得臨時表可以用於物理備庫和只讀庫中，因為臨時表沒必要產生redo。
1.   啟用/禁用臨時undo
Oracle12c中，臨時表的undo段預設還是透過常規undo表空間管理。臨時undo透過TEMP_UNDO_ENABLED引數在會話或系統級別啟用或禁用。
-- 會話級別
ALTER SESSION SETTEMP_UNDO_ENABLED = TRUE;
ALTER SESSION SETTEMP_UNDO_ENABLED = FALSE;
 
-- 系統級別
CONN sys@pdb1 AS SYSDBA
 
ALTER SYSTEM SETTEMP_UNDO_ENABLED = TRUE;
ALTER SYSTEM SETTEMP_UNDO_ENABLED = FALSE;
以下為臨時undo的注意事項。
1)  臨時undo功能僅在 COMPATIBLE引數設定為12.0.0或更高時才可用。
2)  一旦會話建立了一些臨時undo，無論用常規還是臨時undo，怎麼設定TEMP_UNDO_ENABLED引數也是無濟於事的，除非退出會話，期間，也不會有什麼報錯。
3)  臨時undo在備庫上是預設的設定，因此，無論在備庫上怎麼設定TEMP_UNDO_ENABLED引數，都沒什麼作用。
2.   監控臨時undo
常規undo活動透過V$UNDOSTAT檢視監控。Oracle12c中，增加了V$TEMPUNDOSTAT檢視來監控臨時undo活動。下面透過建立一個臨時表來測試。
CONN test/test@pdb1
 
DROP TABLE g_t_tab PURGE;
 
CREATE GLOBAL TEMPORARY TABLEg_t_tab (
  c1          NUMBER,
  c2 VARCHAR2(20)
)
ON COMMIT DELETE ROWS;
確信使用者可以訪問V$TEMPUNDOSTAT檢視。
CONN sys@pdb1 AS SYSDBA
 
GRANT SELECT ONv_$tempundostat TO test;
下面的測試建立一個會話，往臨時表裡載入一些資料，然後，檢查redo和undo的使用變化。下例中沒有用到臨時undo。
CONN test/test@pdb1
SET AUTOTRACE ON STATISTICS;
 
--載入資料
INSERT INTO g_t_tab
WITH data AS (
  SELECT 1 AS c1
  FROM  dual
  CONNECT BY level < 10000
)
SELECT rownum,TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;
 
1000000 rows created.
 
Statistics
----------------------------------------------------------
         46 recursive calls
      15346 db block gets
       2379 consistent gets
         16 physical reads
    2944564 redo size
        855 bytes sent via SQL*Net to client
        986 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          5 sorts (memory)
          0 sorts (disk)
    1000000 rows processed
 
SQL>
 
SET AUTOTRACE OFF
 
-- 檢查該事務產生的undo
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
---------- ----------
       302      6238
 
SQL>
我們可以看到該操作產生了302個undo塊，這就是統計資訊顯式產生了2.9M redo的原因，是為了保護這些undo塊。
下例建立一個會話，開啟臨時undo，往臨時表中載入一些資料，再檢查redo和undo使用變化，即透過V$TEMPUNDOSTAT檢視來檢查產生的undo。
CONN test/test@pdb1
 
-- 開啟臨時undo
ALTER SESSION SETTEMP_UNDO_ENABLED = TRUE;
 
SET AUTOTRACE ON STATISTICS;
 
-- 載入資料
INSERT INTO g_t_tab
WITH data AS (
  SELECT 1 AS c1
  FROM   dual
  CONNECT BY level < 10000
)
SELECT rownum,TO_CHAR(rownum)
FROM   data a, data b
WHERE  rownum <= 1000000;
 
1000000 rows created.
 
Statistics
----------------------------------------------------------
         25 recursive calls
      15369 db block gets
       2348 consistent gets
         16 physical reads
       1004 redo size
        853 bytes sent via SQL*Net to client
        986 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          3 sorts (memory)
          0 sorts (disk)
    1000000 rows processed
 
SQL>
 
SET AUTOTRACE OFF
 
-- 檢查該事務產生的undo
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
 
SQL>
 
--檢查產生的臨時undo
SET LINESIZE 200
ALTER SESSION SETNLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
 
SELECT *
FROM   v$tempundostat
WHERE  end_time >= SYSDATE - INTERVAL '1' MINUTE;
 
BEGIN_TIME           END_TIME                UNDOTSN   TXNCOUNT MAXCONCURRENCY MAXQUERYLENMAXQUERYID    UNDOBLKCNT     EXTCNT   USCOUNT SSOLDERRCNT NOSPACEERRCNT   CON_ID
---------------------------------------- ---------- ---------- -------------- ------------------------ ---------- ---------- ---------- ----------- -----------------------
24-NOV-2014 15:11:0923-NOV-2014 15:17:30          3          2              0           0                      321          4         1           0           0           0
 
SQL>
該例中，看到僅產生了少量的常規undo，導致產生了1K的redo。V$TEMPUNDOSTAT檢視顯示產生了321個臨時undo塊。