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

realkid4發表於2012-08-26

 

在日常的運維工作中,對生產資料表進行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環境下,事情有了一些不同。

 

211g下的預設值配置

 

我們在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以超乎想象的速度完成了過程。並且注意:資料表的體積沒有發生任何變化!!但是,我們檢查資料表的時候,卻發現了對應列的預設值已經新增。

 

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

 

311g預設值處理的最佳化

 

那麼,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類進行儲存。而進入11gecol$表,這個值是使用blob型別進行儲存。

 

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

 

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

 

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

 

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

 

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

 

4、結論

 

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

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

相關文章