淺析Oracle 11g中對資料列預設值變化的優化

 轉載---求道的路上

在日常的運維工作中，對生產資料表進行DDL操作是一件需要謹慎對待的事情。運維DBA們在進行資料DDL操作的時候，通常要全域性考慮，諸如對生產影響、執行時間長度和影響儲存資料等等。

 

資料列預設值的新增，是DBA們經常頭疼的一個問題。傳統的執行語句，消耗時間長、資源使用量大，對生產環境影響程度高。採用其他的一些變通方法，又存在操作步驟繁瑣的問題。如何快速的新增一個有預設值的資料列，同時對現有生產環境影響最小，是我們希望達到的一個目標。

 

本文從操作入手，探討新增default資料列的問題點，最後介紹Oracle 11g中對其進行的“革命性”優化。

 

1、從10g的資料列新增談起

 

為了實現對比效果，我們首選選擇10g版本的Oracle進行試驗，構造一個相對較大的資料表。

 

 

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE   10.2.0.1.0     Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> create table t as select object_id from dba_objects;

表已建立。

 

SQL> select count(*) from t;

 COUNT(*)

----------

  3220352

 

 

資料表t只包括一個資料列，但是資料量大約為320萬條。我們從體積上進行評估如下：

 

 

SQL> set timing on;

SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';

 

BYTES/1024/1024    BLOCKS

--------------- ----------

            39      4992

 

已用時間: 00: 00: 00.03

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

PL/SQL過程已成功完成。

 

已用時間: 00: 00: 00.35

 

SQL> select blocks from dba_tables where wner='SYS' and table_name='T';

 

   BLOCKS

----------

     4883

 

已用時間: 00: 00: 00.01

 

 

Oracle分配給這個段segment的中空間為4992個資料塊，高水位線HWM下的格式化過資料塊為4883。總體積約40M。

 

下面進行兩種方式的新增資料表預設值列方法，一起觀察一下變化情況。首先是允許為空預設值列的操作。

 

 

SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT';

 

表已更改。

 

已用時間: 00: 34: 37.15

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

 

PL/SQL過程已成功完成。

 

已用時間: 00: 00: 03.86

 

 

SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segment_name='T';

 

BYTES/1024/1024    BLOCKS

--------------- ----------

           208     26624

 

已用時間: 00: 00: 00.06

SQL> select blocks from dba_tables where wner='SYS' and table_name='T';

 

   BLOCKS

----------

    25864

 

已用時間: 00: 00: 00.01

 

 

果然是一個費時的操作，新增一個資料列預設值，總共消耗了近30分鐘時間。原有資料表的體積也發生的膨脹，從原來的不到40M，上升到了208M。

 

這個現象告訴我們，當我們新增一個有default值的資料列，並且是直接新增的時候，一些資料被插入到了資料塊中，引起空間膨脹。

 

在原有的結構下，資料新增到資料塊上是必需的，只有這樣才能將資料列default新增到裡面去。

 

除了這個字句，我們是還可以提供資料列的not null選項，也是可以實現相同的功能的。

 

 

SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' not null;

 

表已更改。

 

已用時間: 00: 15: 58.85

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

 

PL/SQL過程已成功完成。

 

已用時間: 00: 00: 36.87

 

SQL> select bytes/1024/1024,blocks from dba_segments where wner='SYS' and segme

nt_name='T';

 

BYTES/1024/1024    BLOCKS

--------------- ----------

           256     32768

 

已用時間: 00: 00: 00.14

SQL> select blocks from dba_tables where wner='SYS' and table_name='T';

 

   BLOCKS

----------

    32448

 

已用時間: 00: 00: 00.04

 

 

也是消耗了15分鐘，空間發生了很大程度變化。新空間分配，同時資料行數沒有發生變化，潛在的行遷移（Row Migration）和行連結（Row Chaining）是嚴重惡化的！

 

綜合分析Oracle 10g下的操作：為了新增上資料欄位的預設值，Oracle會去訪問每個資料塊上的每個資料行進行資料列擴充工作，這個過程中還伴隨著新空間分配和多餘資料行復制。

 

這型別操作對於生產環境是恐怖的，在整個作業過程中，資料表結構被鎖定，相關業務處理操作阻塞或者緩慢。所以，運維DBA都是選擇在維護視窗或者變通的方法進行處理。

 

在Oracle 11g環境下，事情有了一些不同。

 

2、11g下的預設值配置

 

我們在11g上進行相似操作。

 

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE       11.2.0.1.0        Production

 

 

構建相似規模的資料表。

 

 

SQL> set timing on;

SQL> create table t as select object_id from dba_objects;

Table created

 

SQL> select count(*) from t;

 COUNT(*)

----------

  3323167

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';

 

SEGMENT_NA BYTES/1024/1024   EXTENTS    BLOCKS

---------- --------------- ---------- ----------

T                      40        55      5120

 

SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';

 

 NUM_ROWS    BLOCKS

---------- ----------

  3323167      5041

 

 

11g下我們準備了約330萬資料，進行新增非空帶預設值的資料列。

 

 

SQL> alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT' ;

 

alter table t add vc2 varchar2(100) default 'TTTTTTTTTTTT'

 

ORA-01013:使用者請求取消當前的操作

 

 

在新增defalut列，不指定not null的時候，資料持續時間超過了我們的想象。筆者主動將其斷開了。下面試試新增not null時候。

 

--1s不到完成操作；

SQL> alter table t add vc varchar2(100) default 'TTTTTTTTTTTT' not null;

Table altered

 

Executed in 0.047 seconds

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

 

SQL> select NUM_ROWS, BLOCKS from dba_tables where wner='SCOTT' and table_name='T';

 NUM_ROWS    BLOCKS

---------- ----------

  3323167      5041

 

Executed in 0 seconds

 

SQL> select segment_name, bytes/1024/1024, extents,blocks from user_segments where segment_name='T';

 

SEGMENT_NA BYTES/1024/1024   EXTENTS    BLOCKS

---------- --------------- ---------- ----------

T                      40        55      5120

 

SQL> select * from t where rownum<10;

 

 OBJECT_ID VC

---------- --------------------------------------------------------------------------------

       20 TTTTTTTTTTTT

       46 TTTTTTTTTTTT

       28 TTTTTTTTTTTT

       15 TTTTTTTTTTTT

（篇幅原因，有省略……）

9 rows selected

 

 

我們發現，當執行not null的時候，Oracle以超乎想象的速度完成了過程。並且注意：資料表的體積沒有發生任何變化！！但是，我們檢查資料表的時候，卻發現了對應列的預設值已經新增。

 

這個事情是比較奇怪的，有一個道理必然是可以說通：就是這個預設值在執行過程中，是絕對沒有真正新增到資料塊中的，因為只有這樣才不會影響資料段的體積。

 

3、11g預設值處理的優化

 

那麼，11g這個過程中是如何處理的呢？而且為什麼只有新增Not null的時候才會有這個特點。我們從select資料行的trace進行入手。

 

我們選擇10046跟蹤一下select的全過程，看看顯示出來的預設值從哪裡來。

 

 

SQL> select value from v$diag_info where name='Default Trace File';

 

VALUE

-----------------------------------------------------------------------

/u01/diag/rdbms/wilson/wilson/trace/wilson_ora_6177.trc

 

 

SQL> alter session set events '10046 trace name context forever, level 12';

會話已更改。

 

SQL> select * from t where rownum<10;

 

 OBJECT_ID

----------

VC

-----------------------------------------------------------------------------

 

SQL> alter session set events '10046 trace name context off';

會話已更改。

 

 

對生成的trace檔案進行處理，獲取到tkprof結果。

 

 

D:\des>tkprof wilson_ora_6177.trc

output = res.txt

 

TKPROF: Release 10.2.0.1.0 - Production on星期五8月24 22:07:10 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.

 

 

在分析的結果中，我們發現很多的recursive語句，也就是Oracle為了執行這個SQL，連帶執行了很多的語句，其中我們發現了一個“可疑”物件。

 

 

***********************************************************************

 

select binaryDefVal, length(binaryDefVal)

from

 ecol$          where tabobj# = :1 and colnum = :2

 

 

call    count      cpu   elapsed      disk     query   current       rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse       1     0.00      0.00         0         0         0          0

Execute     1     0.00      0.00         0         0         0          0

Fetch       1     0.00      0.00         2         2         0          1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total       3     0.00      0.00         2         2         0          1

 

 

 

ecol$是sys使用者下的一個新新增的字典基表，其中內容如下：

 

 

SQL> desc ecol$;

Name        Type  Nullable Default Comments

------------ ------ -------- ------- --------

TABOBJ#     NUMBER Y                        

COLNUM      NUMBER Y                        

BINARYDEFVAL BLOB  Y                        

 

SQL> select * from ecol$;

 

  TABOBJ#    COLNUM BINARYDEFVAL

---------- ---------- ------------

    76046         2

Executed in 0.031 seconds

 

SQL> col owner for a10;

SQL> col object_name for a10;

SQL> select owner, object_name, object_id from dba_objects where object_id in (76046);

 

OWNER     OBJECT_NAM OBJECT_ID

---------- ---------- ----------

SCOTT     T              76046

 

Executed in 0 seconds

 

 

從ecol$資料表中，我們發現了對資料表T物件第二列（column=2）的一個物件引用，引用的值binarydefval是一個blob型別。從直觀上，我們已經可以猜出這個就是記錄了資料表vc列的預設值。

 

此處，我們說一個問題，在Oracle中，預設值都是通過大物件型別進行儲存。在資料字典col$中，預設值是通過long類進行儲存。而進入11g的ecol$表，這個值是使用blob型別進行儲存。

 

另一個需要注意的，就是這個資料表中只有一個資料行，也就是隻有我們建立資料表T的預設值。這說明什麼呢？

 

此時，我們已經可以猜出Oracle的良苦用心。首先，Oracle注意到了在生產online的時候，新增帶預設值列資料的困難。但是，從現有的體系結構和儲存結構下，將預設值逐行插入、從而引起行遷移的情況是不能避免的。所以，Oracle採用了一種“障眼法”。

 

如果我們在建立資料表的時候就指定了資料列的預設值、或者沒有要求將所有資料空值一次性全都變成預設值的時候，Oracle還是按照原有的儲存策略進行管理。如果出現了要求新增資料列，並且一次性將所有預設值列都加入的情況，Oracle索性就不進行插入資料和挪行的操作，而是將這個預設值儲存在ecol$中。

 

接下來，如果要進行檢索資料，首先oracle會利用recursive call的方法，儲存提取出預設值。在檢索資料的過程中，如果遇到預設值列為空的情況（沒有插值），就將取出的預設值輸出到介面上進行顯示。其實，資料行對應的預設值列是沒有這個值的。

 

這就解釋了為什麼只有在新增not null預設值列的時候，才會有這個優化。因為Oracle需要確認這個列不會有空值，才會將出現的空值全都進行“障眼法”匹配。

 

4、結論

 

藉助了11g這個特性，我們說在online生產環境下，臨時加入預設值列就不是一件恐怖的工作了。不過，處於謹慎的考慮，還是希望有條件的時候，將該資料表進行重構。這種特性屬於應急環境下考慮使用。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-744240/，如需轉載，請註明出處，否則將追究法律責任。