Oracle中如何更新一張大表記錄

SQL語句是一種方便的語言，同樣也是一種“迷惑性”的語言。這個主要體現在它的集合操作特性上。無論資料表資料量是1條，還是1億條，更新的語句都是完全相同。但是，實際執行結果（或者能否出現結果）卻是有很大的差異。
 
筆者在開發DBA領域的一個理念是：作為開發人員，對資料庫、對資料要有敬畏之心，一個語句發出之前，起碼要考慮兩個問題：目標資料表的總資料量是多少（投產之後）？你這個操作會涉及到多大的資料量？不同的回答，處理的方案其實是不同的。
 
更新大表資料，是我們在開發和營運，特別是在資料移轉領域經常遇到的一種情境。上面兩個問題的回答是：目標資料表整體就很大，而且修改範圍也很大。一個SQL從理論上可以處理。但是在實際中，這種方案會有很多問題。
 
本篇主要介紹幾種常見的大表處理策略，並且分析出他們的優劣。作為我們開發人員和DBA，選取的標準也是靈活的：根據你的操作類型（營運操作還是系統日常作業）、程式運行環境（硬體環境是否支援並行）和程式設計環境（是否可以完全獨佔所有資源）來綜合考量決定。
 
首先，我們需要準備出一張大表。

1、環境準備

我們選擇Oracle 11.2版本進行實驗。

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

 

 

準備一張大表。

 

 

SQL> create table t as select * from dba_objects;

Table created

 

SQL> insert into t select * from t;

72797 rows inserted

 

SQL> insert into t select * from t;

145594 rows inserted

 

（篇幅原因，中間過程略……）

SQL> commit;

Commit complete

 

 

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

BYTES/1024/1024/1024

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

 1.0673828125

 

SQL> select count(*) from t;

 

COUNT(*)

----------

 9318016

 

Executed in 14.711 seconds

 

 

資料表T作為資料來源，一共包括9百多萬條記錄，合計空間1G左右。筆者實驗環境是在虛擬機器上，一顆虛擬CPU，所以後面進行並行Parallel操作的方案就是示意性質，不具有代表性。
 
下面我們來看最簡單的一種方法，直接update。

 

2、方法1：直接Update

 

最簡單，也是最容易出問題的方法，就是“不管三七二十一”，直接update資料表。即使很多老程式員和DBA，也總是選擇出這樣的策略方法。其實，即使結果能出來，也有很大的僥倖成分在其中。
 
我們首先看筆者的實驗，之後討論其中的原因。先建立一張實驗資料表t_target。

 

 

SQL> create table t_targettablespace users as select * from t;

Table created

 

 

SQL> update t_target set owner=to_char(length(owner));

（長時間等待……）

 

 

在等待期間，筆者發現如下幾個現象：

ü  資料庫伺服器運行速度奇慢，很多串連操作速度減緩，一段時間甚至無法登陸；

ü  後台會話等待時間集中在資料讀取、log space buffer、空間分配等事件上；

ü  長期等待，作業系統層面開始出現異常。Undo資料表空間膨脹；

ü  日誌切換頻繁；

此外，選擇這樣策略的朋友還可能遇到：前台錯誤拋出異常、用戶端串連被斷開等等現象。

筆者遇到這樣的情境也是比較糾結，首先，長時間等待（甚至一夜）可能最終沒有任何結果。最要命的是也不敢輕易的撤銷操作，因為Oracle要進行update操作的復原動作。一個小時之後，筆者放棄。
 
 

 

updatet_target set owner=to_char(length(owner))

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

（接近一小時未完成）

 

 

之後就是相同時間的rollback等待，通常是事務執行過多長時間，復原進行多長時間。期間，可以通過x$ktuxe後台內部表來觀察、測算復原速度。這個過程中，我們只有“乖乖等待”。
 
 

 

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

 

  KTUXESIZ

----------

    62877

（……）

 

SQL> select KTUXESIZ from x$ktuxe where KTUXESTA<>'INACTIVE';

 

  KTUXESIZ

----------

      511

 

 

綜合這種策略的結果通常是：同業抱怨（影響了他們的作業執行）、提心弔膽（不知道執行到哪裡了）、資源耗盡（CPU、記憶體或者IO佔到滿）、勞而無功（最後還是被rollback）。如果是正式投產環境，還要承擔影響業務生產的責任。
 
我們詳細分析一下這種策略的問題：

首先，我們需要承認這種方式的優點，就是簡單和片面的高效。相對於在本文中其他介紹的方法，這種方式代碼量是最少的。而且，這種方法一次性的將所有的任務提交給資料庫SQL引擎，可以最大程度的發揮系統一個方面（CPU、IO或者記憶體）的能力。
 
如果我們的資料表比較小，經驗值在幾萬一下，這種方法是比較合適的。我們可以考慮使用。

另一方面，我們要看到Oracle Update的另一個方面，就是Undo、Redo和進程工作負載的問題。熟悉Oracle的朋友們知道，在DML操作的時候，Undo和Redo是非常重要的方面。當我們在Update和Delete資料的時候，資料區塊被修改之前的“前鏡像”就會儲存在Undo Tablespace裡面。注意：Undo Tablespace是一種特殊的資料表空間，需要儲存在磁碟上。Undo的存在主要是為了支援資料庫其他會話的“一致讀”操作。只要事務沒有被commit或者rollback，Undo資料就會一直保留在資料庫中，而且不能被“覆蓋”。
 
Redo記錄了進行DML操作的“後鏡像”，Redo產生是和我們修改的資料量相關。現實問題要修改多少條記錄，產生的Redo總量是不變的，除非我們嘗試nologging選項。Redo單個日誌成員如果比較小，Oracle應用產生Redo速度比較大。Redo Group切換頻度高，系統中就面臨著大量的日誌切換或者Log Space Buffer相關的等待事件。
 
如果我們選擇第一種方法，Undo資料表空間就是一個很大的瓶頸。大量的前鏡像資料儲存在Undo資料表空間中不能釋放，繼而不斷的引起Undo檔案膨脹。如果Undo檔案不允許膨脹（autoextend=no），Oracle DML操作會在一定時候報錯。即使允許進行膨脹，也會伴隨大量的資料檔案DBWR寫入動作。這也就是我們在進行大量update的時候，在event等待事件中能看到很多的DBWR寫入。因為，這些寫入中，不一定都是更新你的資料表，裡面很多都是Undo資料表空間寫入。
 
同時，長時間的等待操作，觸動Oracle和OS的負載上限，很多奇怪的事情也可能出現。比如進程僵死、串連被斷開。

這種方式最大的問題在於rollback動作。如果我們在長時間的事務過程中，發生一些異常報錯，通常是由於資料異常，整個資料需要復原。復原是Oracle自我保護，維持事務完整性的工具。當一個長期DML update動作發生，中斷的時候，Oracle就會進入自我的rollback階段，直至最後完成。這個過程中，系統是比較運行緩慢的。即使重啟伺服器，rollback過程也會完成。
 
所以，這種方法在處理大表的時候，一定要慎用！！起碼要評估一下風險。

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 下一頁