PLSQL中慎用CLOB型別

在老熊《在儲存過程中的clob資料型別》（）中，
分析了PLSQL中使用clob欄位，雖然clob型別便於使用（不用擔心欄位長度超過varchar2的限制），但也帶來了效能問題。

透過對比分析發現PLSQL中對clob欄位的操作，引發以下問題並給出了相應建議：
*大量邏輯讀（一致性讀(consistent gets)和當前模式讀(db block gets)）
*臨時段，只有在會話退出時才會清除

為方便參考,複製原文如下:
---------------------------------------
在儲存過程中的clob資料型別Oracle效能最佳化performance9月 28th, 2011
客戶新上線的一套重要生產系統，某個儲存過程每小時呼叫約11萬次，每次呼叫的邏輯讀超過了10000，消耗的CPU佔資料庫CPU Time的25-30%。很顯然，這樣一個儲存過程是值得最佳化的。

不幸的是，這個儲存過程的業務邏輯很複雜，光是引數就有9個。而儲存過程所在的包其程式碼超過了1萬行。透過複查程式碼的方式，耗時耗力，還不一定能夠找出問題。

診斷效能問題，首要的是瞭解這個儲存過程所大概要完成的業務功能，然後透過trace或instrument收集足夠詳細的效能資料。從客戶和開發商那裡瞭解到，存在效能問題的儲存過程，主要用於系統之間的資料查詢介面，根據不同的引數輸入查詢不同的資料，那麼，對於這種小資料量的儲存過程，可以考慮使用10046事件來分析是哪些SQL產生了這麼多的邏輯讀。

但是透過10046事件，發現儲存過程中實際執行的SQL並不多，同時並沒有邏輯讀高的SQL語句。也許問題並沒有出現在SQL語句中，而是出現在儲存過程中其他非SQL部分。雖然用10046沒有找到SQL語句，但還是有重大發現，在過程執行時，大量的邏輯讀來自於current方式的讀，這顯示不是通常的SELECT語句所產生的。接下來我們用dbms_profiler來分析儲存過程：

select dbms_profiler.start_profiler from dual;
exec intf.CRM_SERVICE_INTF.QueryService(.....);
exec dbms_profiler.stop_profiler;

然後使用來自MOS文件“Implementing and Using the PL/SQL Profiler [ID 243755.1]”中的profiler.sql指令碼，生成一個profiler的結果檔案，格式為html。下面是部分的內容：

點選其顯示的程式碼行(line)，跳轉到相應的原始碼，發現大量的字串拼接程式碼，很明顯是用於拼接成XML格式。這很容易理解，因為現在系統之間的文字資料互動，xml幾乎成了標準。從上面的截圖中也可以看到很多型別於xml:=xml || ‘xxxx’ 這樣的程式碼。是這樣的程式碼引起的問題嗎？

檢查程式碼發現，這裡用於拼接字串的變數xml，被定義為clob型別，這引起了我極大的關注。在oracle的標準資料型別中，lob型別由於其能夠儲存大資料的本質，導致其內部格式和操作是最複雜的。有理由懷疑是clob的大量拼接引起的問題。

在有懷疑物件後，我們可以構造下面的測試來進行驗證：

首先建立下面3個不同的儲存過程，但是實現的功能是一致的：

create or replace procedure p1 ( v_out out clob)
is
  v_lob clob;
begin
  v_lob:='';
  for rec in (select object_name from dba_objects where rownum< =1000) loop
      v_lob:=v_lob || rec.object_name;
  end loop;
  v_out:=v_lob;
end;
/

     
create or replace procedure p2 ( v_out out clob)
is
  v_lob varchar2(32767);
begin
  v_lob:='';
  for rec in (select object_name from dba_objects where rownum<=1000) loop
      v_lob:=v_lob || rec.object_name;
  end loop;
  v_out:=v_lob;
end;
/

create or replace procedure p3 ( v_out out clob)
is
  v_lob clob;
  v_str varchar2(32767);
  v_cnt number;
begin
  v_lob:='';
  v_cnt:=0;
  v_str:='';
  for rec in (select object_name from dba_objects where rownum<=1000) loop
        v_str:=v_str || rec.object_name;
        v_cnt:=v_cnt+1;
        if v_cnt = 50 then
           v_cnt:=0;
           v_lob:=v_lob || v_str;
           v_str:='';
        end if;  
  end loop;
  if v_cnt <>0 then
     v_lob:=v_lob || v_str;
  end if;  
  v_out:=v_lob;
end;
/

第1個儲存過程，P1，完全使用clob型別來拼接字串；第2個儲存過程，P2，使用varchar2型別拼接字串，只有在過程最後將varchar2轉成clob；第3個儲存過程，P3，先使用varchar2型別拼接成較長的字串，然後再用較長的字串用clob型別來拼接。

下面看看P1和P2的差距：

SQL> var v_lob1 clob;
SQL> var v_lob2 clob;
SQL> begin
  2    sys.runstats_pkg.rs_start;
  3    p1(:v_lob1);
  4    sys.runstats_pkg.rs_middle;
  5    p2(:v_lob2);
  6    sys.runstats_pkg.rs_stop;
  7  end;
  8  /
Run1 ran in 5 hsecs
Run2 ran in 0 hsecs
run 1 ran in 50000000% of the time

Name                                  Run1        Run2        Diff
STAT...lob reads                       999           0        -999
LATCH.cache buffers lru chain        1,000           0      -1,000
STAT...free buffer requested         1,012           9      -1,003
STAT...lob writes                    2,000           1      -1,999
STAT...lob writes unaligned          2,000           1      -1,999
LATCH.object queue header oper       2,024           9      -2,015
STAT...consistent gets               3,581         580      -3,001
STAT...consistent gets from ca       3,581         580      -3,001
STAT...consistent changes            5,051          42      -5,009
STAT...db block changes              5,061          52      -5,009
STAT...calls to get snapshot s       7,008          10      -6,998
STAT...db block gets from cach      14,075          57     -14,018
STAT...db block gets                14,075          57     -14,018
STAT...session logical reads        17,656         637     -17,019
LATCH.cache buffers chains          46,151       1,083     -45,068
STAT...session uga memory          138,608      15,072    -123,536
STAT...session uga memory max      315,052     131,024    -184,028
STAT...session pga memory max      393,216     131,072    -262,144
STAT...session pga memory          393,216      65,536    -327,680

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
49,789       1,674     -48,115  2,974.25%

PL/SQL 過程已成功完成。

從上面的資料來看，差距是巨大的，執行P1時產生的一致性讀(consistent gets)和當前模式讀(db block gets)，都遠遠大於執行P2時產生的一致讀和當前模式讀。特別是大量的db block gets驗證了之前使用10046事件跟蹤儲存過程執行得到的結果。上面的資料中，甚至是會話消耗的記憶體都有很明顯的差異。同時，還有其他對效能顯著影響的地方，cache buffers chains latch，cache buffers lru chain latch，object queue header operation latch，這3種latch的獲取次數是相當多的，在大併發時無疑將引起爭用。實際上，在客戶的這套系統中，這3種latch的爭用經常出現，只是目前還沒形成特別嚴重的後果。

再看看P1和P3的差異：

SQL> var v_lob1 clob;  
SQL> var v_lob2 clob;  
SQL> begin 
  2    sys.runstats_pkg.rs_start;  
  3    p1(:v_lob1);  
  4    sys.runstats_pkg.rs_middle;  
  5    p3(:v_lob2);  
  6    sys.runstats_pkg.rs_stop;  
  7  end;  
  8  /  
Run1 ran in 7 hsecs  
Run2 ran in 2 hsecs  
run 1 ran in 350% of the time 
 
Name                                                      Run1        Run2        Diff  
STAT...lob reads                       999          19        -980  
LATCH.cache buffers lru chain        1,002          20        -982  
STAT...free buffer requested         1,012          29        -983  
STAT...lob writes                    2,000          40      -1,960  
STAT...lob writes unaligned          2,000          40      -1,960  
LATCH.object queue header oper       2,016          49      -1,967  
STAT...consistent gets from ca       3,579         639      -2,940  
STAT...consistent gets               3,579         639      -2,940  
STAT...consistent changes            5,051         143      -4,908  
STAT...db block changes              5,061         153      -4,908  
STAT...calls to get snapshot s       7,007         146      -6,861  
STAT...db block gets                14,075         346     -13,729  
STAT...db block gets from cach      14,075         346     -13,729  
STAT...session logical reads        17,654         985     -16,669  
LATCH.cache buffers chains          46,147       1,999     -44,148  
STAT...session uga memory          189,012      65,464    -123,548  
STAT...session pga memory          196,608      65,536    -131,072  
STAT...session pga memory max      262,144      65,536    -196,608  
STAT...session uga memory max      315,052      65,560    -249,492  
 
Run1 latches total versus runs -- difference and pct  
Run1        Run2        Diff       Pct  
49,380       2,264     -47,116  2,181.10% 

二者的差異同樣很明顯。

不僅如此，在儲存過程中，執行P1過程之後，甚至在臨時表空間中產生了臨時段，而這個臨時段是不會自動清除的，經測試，即使沒有用於返回結果的clob引數，在過程內部生成的clob所佔的臨時段也不會自動清除，只有會話退出才會清除掉。如果更深入跟蹤，也許可以發現大量的邏輯讀來源於這個臨時段。

SQL> select tablespace,contents,segtype,blocks from v$sort_usage;  
 
TABLESPACE                      CONTENTS  SEGTYPE       BLOCKS  
------------------------------- --------- --------- ----------  
TEMP                            TEMPORARY LOB_DATA         128 

透過驗證，證明clob型別的資料的確是引起客戶系統中儲存過程大量邏輯讀和Latch爭用的原因，找到了這個原因，最佳化就相對簡單了。

在PL/SQL儲存過程中，clob是相當方便的一種資料型別，由於其能夠儲存超長字元資料的特性，使得在這種用於資料交換的儲存過程中用得較多。然而，從上面的資料中，不難發現，如果大量使用clob運算，將普通的字串拼接成clob，其CPU消耗、邏輯讀、甚至是latch的獲取都是非常高的，對效能影響非常大。實際上在10g中，儲存過程中的varchar2型別，其長度最大可以達到32767，所以如果返回的結果確保不超過這個長度，完全可以使用varchar2型別，只是在返回時再轉換為clob，正如上面的儲存過程P2所做的那樣。如果其長度超過了32767，也可以如儲存過程P3一樣，先將短小的字串拼接成較大的字串，然後將較大的字串拼接到clob中。