淺析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

在分析的結果中，我們發現很多的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/，如需轉載，請註明出處，否則將追究法律責任。

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

相關文章