大量的Oracle知識點都是需要在實際中不斷發現實驗獲得的。本篇中介紹兩個實際中發現的問題，權當記錄。

1、慎用for update大規模資料

Select for update是我們有時候會使用的一種語句型別。它的特點優勢是能夠將篩選出的資料進行獨佔鎖定。通常情況下的Oracle select操作，是不會對資料表進行鎖定，同時也不會被其他操作所阻塞。

在實際應用中，我們的確是經常使用for update進行資料鎖定。常用的場景是，確定符合條件的資料是否存在，如果存在就立即鎖定該條記錄獨佔。之後對獨佔的資料進行修改。在存在多會話併發訪問的時候，for update可以起到資源/訊號量搶奪的作用。

但是，for update的使用是有相應的代價的。在之前的系列文章中，我們探討過for update要消耗更多的時間，主要在於需要在每個資料行進行加鎖操作。在Oracle11g中，CBO的Query Optimizer採用了一些最佳化技術進行效能提升。但是，總的來說，for update是要消耗更多的資源的。

除了上面提到的方面，在redo size上for update也是有獨特之處。通常我們繼續DDL和更改型別操作的時候，才會生成redo log片段。普通的select是不會生成redo log記錄的。如果使用for update，我們是可以發現select會生成redo size。

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

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

使用v$mystat可以看到當前會話累計的各種指標統計量。

SQL> select a.statistic#, name, value from v$mystat a left join v$statname b on a.STATISTIC#=b.STATISTIC# where b.NAME = 'redo size';

STATISTIC# NAME VALUE

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

169 redo size 0

當前，redo size生成的消耗量是0。

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

BYTES BLOCKS

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

9437184 1152

SQL> select count(*) from t;

COUNT(*)

----------

72349

此時，我們使用一個for update語句。

SQL> select * from t where wner='SCOTT' for update;

（結果集合省略……）

此時，redo size的消耗可以查詢到。

SQL> select a.statistic#, name, value from v$mystat a left join v$statname b on a.STATISTIC#=b.STATISTIC# where b.NAME = 'redo size';

STATISTIC# NAME VALUE

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

169 redo size 0

SQL> commit;

SQL> select a.statistic#, name, value from v$mystat a left join v$statname b on a.STATISTIC#=b.STATISTIC# where b.NAME = 'redo size';

STATISTIC# NAME VALUE

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

169 redo size 2628

一個一般的for update之後，進行commit，沒有發生本質上的資料變化。但是之後，我們發現了redo size指標增加到了2628。一般的select操作，就不會產生redo size消耗。

SQL> select * from t where wner='SCOTT';

（結果集合省略……

SQL> select a.statistic#, name, value from v$mystat a left join v$statname b on a.STATISTIC#=b.STATISTIC# where b.NAME = 'redo size';

STATISTIC# NAME VALUE

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

169 redo size 2628

SQL> commit;

SQL> select a.statistic#, name, value from v$mystat a left join v$statname b on a.STATISTIC#=b.STATISTIC# where b.NAME = 'redo size';

STATISTIC# NAME VALUE

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

169 redo size 2628

可見，一般的select的操作是不會產生redo記錄的。這個問題我們也可以使用autotrace工具來檢視到。

SQL> set autotrace traceonly;

SQL> select * from t for update;

已選擇72349行。

執行計劃

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

Plan hash value: 250223868

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

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

| 0 | SELECT STATEMENT | | 72349 | 6853K| 282 (1)| 00:00:04 |

| 1 | FOR UPDATE | | | | | |

| 2 | BUFFER SORT | | | | | |

| 3 | TABLE ACCESS FULL| T | 72349 | 6853K| 282 (1)| 00:00:04 |

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

統計資訊

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

46 recursive calls

73649 db block gets

1066 consistent gets

1026 physical reads

15086508 redo size

5649675 bytes sent via SQL*Net to client

53429 bytes received via SQL*Net from client

4825 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

72349 rows processed

SQL> select * from t;

已選擇72349行。

執行計劃

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

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | 72349 | 6853K| 282 (1)| 00:00:04 |

| 1 | TABLE ACCESS FULL| T | 72349 | 6853K| 282 (1)| 00:00:04 |

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

統計資訊

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

1 recursive calls

0 db block gets

5788 consistent gets

0 physical reads

0 redo size

3286309 bytes sent via SQL*Net to client

53429 bytes received via SQL*Net from client

4825 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

72349 rows processed

從統計量資訊看，使用for update在處理相同資料行資料的時候，要產生相當資料量的redo size和巢狀SQL。

分析原因，從Oracle角度看，實現資料行的鎖定依靠Select語句是不能做到的。所以，在for update操作的時候，Oracle會啟動一個事務過程，將篩選出的資料行作出修改動作。

在這個過程中，Oracle會在條件記錄對應的資料塊上的事務槽作出標記，表示對應的資料行已經進行修改。這樣才可能以獨佔的方式獲取資料行。在Oracle中，只要對資料塊進行了修改，無論這個修改是否最終被commit/rollback，都會生成出redo log資訊，寫入到online redo log中。

資料表越大，涉及的資料越多，for update的損耗和生成redo就越大。所以，如果資料表很大，同時select出的資料集合也很大，頻繁的select for update就引起Oracle產生出大量的redo blocks。在歸檔模式下，過於頻繁的對大資料進行for update操作，會帶來額外的歸檔日誌儲存要求。如果沒有預先的規劃，容易引起資料庫hange住。

2、資料列預設值：先加後加，大不相同

資料列預設值，也是我們經常使用的一種資料庫物件技術。透過資料列預設值的設定，當插入資料的時候，如果不對資料列進行指定，那麼預設值就會加入其中。

如果對一個已經存在資料的資料表加列，同時確定預設值和之後設定預設值有什麼差別呢？

SQL> create table t (id number, name varchar2(10));

Table created

SQL> select * from t;

ID NAME

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

SQL> insert into t select object_id, null from dba_objects where rownum<10;

9 rows inserted

SQL> commit;

Commit complete

SQL> select * from t;

ID NAME

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

20

46

28

15

9 rows selected

如果我們首先建立資料列和定義預設值在一起，會如何呢？

SQL> set timing on;

SQL> alter table t add vname varchar2(10) default 'X';

Table altered

Executed in 0.39 seconds

SQL> select * from t;

ID NAME VNAME

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

20 X

46 X

28 X

15 X

29 X

3 X

25 X

41 X

54 X

9 rows selected

Executed in 0.093 seconds

如果我們在新新增列的時候，指定default value。效果就是將資料列加入其中，並且使用預設值填滿已經為null的記錄專案。Default value的含義在於補全，將不完全的資料加以不全。

不過，也要注意這個操作的時候消耗為0.39s，時間消耗略大。下面，如果我們只是指定資料列，不同時進行預設值指定的情況呢？

--新新增列

SQL> alter table t add nname varchar2(10);

Table altered

Executed in 0.062 seconds

--後設定預設值；

SQL> alter table t modify nname default 'M';

Table altered

Executed in 0.062 seconds

SQL> select * from t;

ID NAME VNAME NNAME

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

20 X

46 X

28 X

15 X

29 X

3 X

25 X

41 X

54 X

9 rows selected

Executed in 0.094 seconds

第一個區別，如果後設定預設值，資料列中原來為空的取值，是不會如同時設定一樣補齊資料的。

SQL> insert into t (id) values (100);

1 row inserted

Executed in 0 seconds

SQL> commit;

Commit complete

Executed in 0.015 seconds

SQL> select * from t;

ID NAME VNAME NNAME

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

41 X

54 X

100 X M

10 rows selected

Executed in 0.094 seconds

從實驗結果上看，對新插入的資料，後設定default value是其效果的。同時，從時間上看，一次性的設定新列和預設值消耗的時間巨大。這對於生產環境是一個潛在的風險。

我們對高消耗的操作通常的處理是均衡化負載，儘可能將一個操作劃分為若干個可干預、可控制的操作。所以，對一個大表新增有預設值資料列，可以先加一個無預設值資料列，之後修改預設值，最後使用dml進行資料更新。

3、結論

本篇闡述介紹的兩個知識點，都是一些非常零碎的經驗積累。只有將這些知識透過實驗進行驗證，歸納為自己的所得，不斷的總結提高，才能達到我們質變的目的。

Oracle兩個使用細節知識點

相關文章