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博客 ” ，链接：https://blog.itpub.net/4227/viewspace-660180/ ，如需转载，请注明出处，否则将追究法律责任。
