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的記憶體。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-660180/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- INTERVAL分割槽表鎖分割槽操作
- Oracle查詢Interval partition分割槽表內資料Oracle
- 資料庫分割槽表分割槽未分配導致的一些問題資料庫
- interval partition自動新增分割槽引起的shared pool 4031錯誤
- DATE型別INTERVAL分割槽型別
- 微軟配置錯誤導致大量敏感資料洩露，全球客戶受影響微軟
- Oracle11g INTERVAL分割槽新增分割槽策略Oracle
- 11.2.0.3 ASM例項出現ORA-4031錯誤導致資料庫歸檔失敗ASM資料庫
- hive 動態分割槽插入資料表Hive
- ASM例項出現ORA-4031錯誤導致例項崩潰ASM
- 【北亞資料恢復】誤操作分割槽損壞導致SqlServer資料庫資料丟失的資料恢復資料恢復SQLServer資料庫
- 重灌系統導致分割槽丟失的資料恢復案例資料恢復
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫
- ORA-4031導致CJQ程式出現ORA-1003錯誤
- 插入遠端資料庫資料遇到分割槽表bug資料庫
- 範圍分割槽表和INTERVAL分割槽表對於SPLIT分割槽的區別
- split分割槽操作導致的librarycachelock
- Oracle 插入大量資料Oracle
- MySQL大量資料入庫的效能比較（分割槽）MySql
- ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表Oracle
- ddl 導致分割槽表全域性索引unusable索引
- 11g解決imp匯入資料時報錯：插入資料找不到相應分割槽
- 資料庫升級導致ORA-918錯誤資料庫
- 【實驗】【PARTITION】RANGE分割槽表未指定maxvalue分割槽將無法插入相關資料
- 定期truncate 歷史間隔分割槽INTERVAL PARTITION
- Oracle Interval Partition 自動分割槽表-實驗Oracle
- 11g分割槽新特性之interval partition
- PostgreSQL 原始碼解讀（96）- 分割槽表#3（資料插入路由#3-獲取分割槽鍵值）SQL原始碼路由
- 如何解決安裝CentOS時遇到引導分割槽位於一個GPT分割槽方案的錯誤提示且分割槽後無法應用CentOSGPT
- 使用Oracle Database 11g建立Interval分割槽表OracleDatabase
- Oracle12c：建立主分割槽、子分割槽，實現自動分割槽插入效果Oracle
- PostgreSQL-亂序插入資料導致索引膨脹SQL索引
- 資料塊內事務槽不足導致資料塊鎖
- MySql資料分割槽操作之新增分割槽操作MySql
- 分割槽表入無分割槽的資料庫資料庫
- MySQL插入資料1366錯誤解決方案MySql
- informix插入資料包271，136錯誤ORM
- 多餘索引導致explain錯誤索引AI