INTERVAL分割槽插入大量資料導致ORA-4031錯誤

對INTERVAL分割槽插入大量資料，使得INTERVAL分割槽不停產生新的分割槽，最終導致ORA-4031錯誤的產生。

本想測試一下分割槽表中最多可以擁有多少個分割槽，由於INTERVAL分割槽是最方便的，只需要插入資料就可以了，不需要透過程式執行大量的DDL語句，沒想到資料導致了ORA-4031的錯誤。

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit 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_PART_INTER
  2  (ID NUMBER)
  3  PARTITION BY RANGE (ID)
  4  INTERVAL (1)
  5  (PARTITION P1 VALUES LESS THAN (2));

表已建立。

SQL> INSERT INTO T_PART_INTER
  2  SELECT ROWNUM
  3  FROM ALL_OBJECTS;
INSERT INTO T_PART_INTER
*
第 1 行出現錯誤:
ORA-04031: 無法分配 2080 位元組的共享記憶體 ("shared pool","T_PART_INTER","PRTMV^f36d7b3","slab alloc in kksga")

SQL> SELECT COUNT(*)
  2  FROM USER_TAB_PARTITIONS
  3  WHERE TABLE_NAME = 'T_PART_INTER';

  COUNT(*)
----------
      9736

下面和ALTER TABLE ADD PARTITION語句進行對比。

為了測試，首先清空共享池：

SQL> CONN / AS SYSDBA
已連線。
SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系統已更改。

下面建立一個範圍分割槽表，透過增加分割槽的方式，看看是否會造成ORA-4031錯誤：

SQL> CONN TEST/TEST
已連線。
SQL> CREATE TABLE T_PART_RANGE
  2  (ID NUMBER)
  3  PARTITION BY RANGE (ID)
  4  (PARTITION P1 VALUES LESS THAN (2));

表已建立。

SQL> BEGIN
  2  FOR I IN 2..20000 LOOP
  3     EXECUTE IMMEDIATE 'ALTER TABLE T_PART_RANGE ADD PARTITION P'
  4             || I || ' VALUES LESS THAN (' || (I + 1) || ')';
  5  END LOOP;
  6  END;
  7  /

PL/SQL 過程已成功完成。

使用ALTER TABLE ADD PARTITION的方式，即使將分割槽的數量增大到20000，也不會出現ORA-4031錯誤。

INTERVAL分割槽確實使得範圍分割槽的管理工作量大大降低，分割槽的建立不再需要DBA手工提前建立，也不需要在JOB中定時建立，而是等到新分割槽的資料進入到資料庫中時，由Oracle自動建立。這種方式不但減輕了維護的工作量，而且提高的空間利用率。如果分割槽沒有對應的資料，則分割槽不會被建立。

看上起確實是一舉兩得，但是INTERVAL分割槽的建立也是有代價的，在資料插入的時候建立分割槽，就意味著當分割槽需要建立時，使用者的INSERT或UPDATE語句就會導致大量的遞迴呼叫來建立新的分割槽。對於每個分割槽都很大，分割槽建立不是很頻繁的情況，這點代價不算什麼，但是如果分割槽建立很頻繁，就會導致效能的問題，上面就是一個極端的例子，每條INSERT都會導致Oracle的遞迴呼叫，進行空間分配，分割槽資訊的維護，不僅造成效能底下，最後甚至共享池都全部耗盡了。

下面看一下共享池中哪部分佔用了這些記憶體資源：

SQL> CONN / AS SYSDBA
已連線。
SQL> SELECT POOL, SUM(BYTES)/1024/1024
  2  FROM V$SGASTAT
  3  GROUP BY POOL;

POOL         SUM(BYTES)/1024/1024
------------ --------------------
                       98.8458252
java pool                      64
streams pool                  128
shared pool            6144.00457
large pool                     64

SQL> SHOW SGA

Total System Global Area 6814535680 bytes
Fixed Size                  2214016 bytes
Variable Size            6710888320 bytes
Database Buffers           67108864 bytes
Redo Buffers               34324480 bytes

一共6G的記憶體，幾乎全部分配給了共享池。

下面重啟一下資料庫，再次執行一下上面的過程：

SQL> SHUTDOWN IMMEDIATE
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> STARTUP
ORACLE 例程已經啟動。

Total System Global Area 6814535680 bytes
Fixed Size                  2214016 bytes
Variable Size            4026533760 bytes
Database Buffers         2751463424 bytes
Redo Buffers               34324480 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> SHOW SGA

Total System Global Area 6814535680 bytes
Fixed Size                  2214016 bytes
Variable Size            4093642624 bytes
Database Buffers         2684354560 bytes
Redo Buffers               34324480 bytes
SQL> SELECT POOL, SUM(BYTES)/1024/1024
  2  FROM V$SGASTAT
  3  GROUP BY POOL;

POOL         SUM(BYTES)/1024/1024
------------ --------------------
                       2594.84583
java pool                      64
streams pool                  128
shared pool            1024.00457
large pool                     64

可以看到，正常啟動後，共享池只分配了1G的空間：

SQL> SELECT DISTINCT POOL,
  2  FIRST_VALUE(NAME) OVER(PARTITION BY POOL ORDER BY BYTES DESC) NAME,
  3  FIRST_VALUE(BYTES/1024/1024) OVER(PARTITION BY POOL ORDER BY BYTES DESC) M
  4  FROM V$SGASTAT;

POOL         NAME                                M
------------ -------------------------- ----------
large pool   free memory                   56.1875
streams pool free memory                       128
             buffer_cache                     2560
java pool    free memory                        64
shared pool  free memory                864.393021

可以看到，每個池中佔用空間最大的部分都是空閒記憶體，其中共享池中有864M的空閒空間，下面在一個會話執行INTERVAL分割槽表的資料插入過程：

SQL> INSERT INTO T_PART_INTER
  2  SELECT ROWNUM
  3  FROM ALL_OBJECTS;
INSERT INTO T_PART_INTER
*
第 1 行出現錯誤:
ORA-04031: 無法分配 72 位元組的共享記憶體 ("shared pool","T_PART_INTER","PRTMV^f36d7b3","kggec.c.kggfa")

在這個插入執行過程中，Oracle尚未報錯的時候，在立國那一個會話查詢V$SGASTAT檢視，檢查共享池中哪個部分佔用的空間最大：

SQL> SELECT MAX(BYTES)/1024/1024 FROM V$SGASTAT
  2  WHERE POOL = 'shared pool';
SELECT MAX(BYTES)/1024/1024 FROM V$SGASTAT
                                 *
第 1 行出現錯誤:
ORA-04031: 無法分配 264 位元組的共享記憶體 ("shared pool","unknown object","KGLH0^ae4fab53","kglob")
ORA-04031: 無法分配 264 位元組的共享記憶體 ("shared pool","unknown object","KGLH0^75c1a12f","kglob")

SQL> SELECT DISTINCT POOL,
  2  FIRST_VALUE(NAME) OVER(PARTITION BY POOL ORDER BY BYTES DESC) NAME,
  3  FIRST_VALUE(BYTES/1024/1024) OVER(PARTITION BY POOL ORDER BY BYTES DESC) M
  4  FROM V$SGASTAT;

POOL         NAME                                M
------------ -------------------------- ----------
large pool   free memory                   56.1875
shared pool  PRTMV                      4994.42047
streams pool free memory                       128
java pool    free memory                        64
             buffer_cache                       64

SQL> SELECT DISTINCT POOL,
  2  FIRST_VALUE(NAME) OVER(PARTITION BY POOL ORDER BY BYTES DESC) NAME,
  3  FIRST_VALUE(BYTES/1024/1024) OVER(PARTITION BY POOL ORDER BY BYTES DESC) M
  4  FROM V$SGASTAT;
FROM V$SGASTAT
     *
第 4 行出現錯誤:
ORA-04031: 無法分配 264 位元組的共享記憶體 ("shared pool","unknown object","KGLH0^ae4fab53","kglob")
ORA-04031: 無法分配 264 位元組的共享記憶體 ("shared pool","unknown object","KGLH0^75c1a12f","kglob")

查詢很多時候是返回ORA-4031錯誤，但是有時能返回結果，在上面的查詢結果中，PRTMV佔用了共享池中將近5G的空間，而DB_CACHE已經縮小到了64M。

SQL> SELECT DISTINCT POOL,
  2  FIRST_VALUE(NAME) OVER(PARTITION BY POOL ORDER BY BYTES DESC) NAME,
  3  FIRST_VALUE(BYTES/1024/1024) OVER(PARTITION BY POOL ORDER BY BYTES DESC) M
  4  FROM V$SGASTAT;

POOL         NAME                                M
------------ -------------------------- ----------
large pool   free memory                   56.1875
streams pool free memory                       128
java pool    free memory                        64
             buffer_cache                       64
shared pool  PRTMV                      4993.20631

SQL> SELECT POOL, SUM(BYTES)/1024/1024
  2  FROM V$SGASTAT
  3  GROUP BY POOL;

POOL         SUM(BYTES)/1024/1024
------------ --------------------
                       98.8458252
java pool                      64
streams pool                  128
shared pool            6144.00457
large pool                     64

等到插入語句返回ORA-4031錯誤後，再次執行查詢語句，可以看到，6G的共享池中5G的空間被PRTMV佔用。查詢了一下，沒有發現關於這個記憶體區域的說明，懷疑是11g中為了支援INTERVAL分割槽而新增的記憶體區。

雖然每個分割槽一條記錄的設計確實不合理，但是Oracle的INTERVAL分割槽記憶體使用也存在一定的問題。不然不會僅不到10000個分割槽，就耗盡了6G的記憶體。