今天突然收到告警，一台11.2.0.4的数据库报错ORA-04031。
1 2 SYS@> show sga ORA-04031: 无法分配 32 字节的共享内存 ("shared pool","SELECT DECODE(null,'','Total...","SQLA","tmp")
根据错误代码可以判断是共享池使用出了问题
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 [oracle@xxx trace]$ oerr ora 4031 04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")" // *Cause: More shared memory is needed than was allocated in the shared // pool or Streams pool. // *Action: If the shared pool is out of memory, either use the // DBMS_SHARED_POOL package to pin large packages, // reduce your use of shared memory, or increase the amount of // available shared memory by increasing the value of the // initialization parameters SHARED_POOL_RESERVED_SIZE and // SHARED_POOL_SIZE. // If the large pool is out of memory, increase the initialization // parameter LARGE_POOL_SIZE. // If the error is issued from an Oracle Streams or XStream process, // increase the initialization parameter STREAMS_POOL_SIZE or increase // the capture or apply parameter MAX_SGA_SIZE.
从smon日志中可以看到大量的sga组件等待扩展的信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Session Wait History: elapsed time of 0.261076 sec since last wait 0: waited for 'SGA: allocation forcing component growth' =0x0, =0x0, =0x0 wait_id=2337078 seq_num=43657 snap_id=101 wait times: snap=0.000000 sec, exc=5.069536 sec, total=13.674583 sec wait times: max=infinite wait counts: calls=100 os=100 occurred after 0.000000 sec of elapsed time 1: waited for 'SGA: allocation forcing component growth' =0x0, =0x0, =0x0 wait_id=2337178 seq_num=43656 snap_id=1 wait times: snap=0.003531 sec, exc=0.003531 sec, total=0.003531 sec wait times: max=infinite wait counts: calls=2 os=2 occurred after 0.000000 sec of elapsed time 2: waited for 'SGA: allocation forcing component growth' =0x0, =0x0, =0x0 wait_id=2337078 seq_num=43655 snap_id=100 wait times: snap=0.050112 sec, exc=5.069536 sec, total=13.671052 sec wait times: max=infinite wait counts: calls=100 os=100 occurred after 0.000000 sec of elapsed time ...省略
表示sga所分配的内存已经使用完，不足以支撑组件的扩展。这个库使用的sga_target,所以sga的各个组件都能自适应分配内存大小
1 2 3 PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------------------------------ sga_target big integer 12G
在sga中有6个子池，每个子池中大部分内存都分配给了”KGH: NO ACCESS”,都在1G左右。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1 ---------------------------------------------- "KGH: NO ACCESS " 1118 MB 67% "free memory " 205 MB 12% "KGLH0 " 86 MB 5% "SQLA " 70 MB 4% "KGLS " 21 MB 1% "KGLHD " 15 MB 1% "VIRTUAL CIRCUITS " 15 MB 1% "db_block_hash_buckets " 13 MB 1% "kglsim object batch " 10 MB 1% "private strands " 9443 KB 1% ----------------------------------------- free memory 205 MB memory alloc. 1459 MB Sub total 1664 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2 ---------------------------------------------- "KGH: NO ACCESS " 1277 MB 77% "free memory " 260 MB 16% "db_block_hash_buckets " 13 MB 1% "kglsim object batch " 11 MB 1% "ASH buffers " 10 MB 1% "private strands " 9443 KB 1% "event statistics per sess " 8335 KB 0% "ksunfy : SSO free list " 7929 KB 0% "dbktb: trace buffer " 6848 KB 0% "kglsim heap " 6444 KB 0% ----------------------------------------- free memory 260 MB memory alloc. 1404 MB Sub total 1664 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3 ---------------------------------------------- "KGH: NO ACCESS " 1086 MB 68% "free memory " 192 MB 12% "KGLH0 " 94 MB 6% "SQLA " 39 MB 2% "FileOpenBlock " 30 MB 2% "KGLHD " 16 MB 1% "db_block_hash_buckets " 13 MB 1% "enqueue " 12 MB 1% "kglsim object batch " 11 MB 1% "KGLS " 10 MB 1% ----------------------------------------- free memory 192 MB memory alloc. 1408 MB Sub total 1600 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4 ---------------------------------------------- "KGH: NO ACCESS " 1182 MB 67% "free memory " 243 MB 14% "KGLH0 " 81 MB 5% "SQLA " 48 MB 3% "KQR M PO " 38 MB 2% "KGLS " 17 MB 1% "db_block_hash_buckets " 13 MB 1% "KGLHD " 12 MB 1% "kglsim object batch " 10 MB 1% "ASH buffers " 10 MB 1% ----------------------------------------- free memory 243 MB memory alloc. 1517 MB Sub total 1760 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 5 ---------------------------------------------- "KGH: NO ACCESS " 1271 MB 74% "free memory " 221 MB 13% "SQLA " 51 MB 3% "KGLH0 " 45 MB 3% "db_block_hash_buckets " 13 MB 1% "ASH buffers " 10 MB 1% "kglsim object batch " 9622 KB 1% "private strands " 9443 KB 1% "event statistics per sess " 8335 KB 0% "ksunfy : SSO free list " 7942 KB 0% ----------------------------------------- free memory 221 MB memory alloc. 1507 MB Sub total 1728 MB ============================================== TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 6 ---------------------------------------------- "KGH: NO ACCESS " 1182 MB 70% "free memory " 232 MB 14% "KGLH0 " 83 MB 5% "SQLA " 48 MB 3% "KGLHD " 13 MB 1% "db_block_hash_buckets " 13 MB 1% "KGLS " 13 MB 1% "kglsim object batch " 12 MB 1% "private strands " 9576 KB 1% "event statistics per sess " 8335 KB 0% ----------------------------------------- free memory 232 MB memory alloc. 1464 MB Sub total 1696 MB TOTALS --------------------------------------- Total free memory 1571 MB Total memory alloc. 10 GB Grand total 11 GB ==============================================
在每个子池中，会有4个持续时间部分（durations），分别是”instance”, “session”, “cursor”, 和 “execution”。 主要目的是为了根据不同的事务种类对共享池申请的内存大小和方式的不同，而将会造成大量碎片、或者能重用的任务区分开来，它们之间互相独立、互不干扰。比如cursor所需内存来源于duration2，execution所需内存来源于duration3等等。
12c以前的版本
12c以后的版本
为了能使shared pool里的内存得到充分利用，我们通过隐含参数来去掉durations的限制。
1 2 3 4 NUM N_HEX NAME VALUE DESCRIPTION ---------- ----- ------------------------------------------------------ ------------------------------ --------------------------------------------- 111 6F _enable_shared_pool_durations TRUE temporary to disable/enable kgh policy
最后解决办法
1 2 SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile; - restart the database
设置"_enable_shared_pool_durations = false"的主要好处在于所有的durations会合并成一个池，所以就不会出现其中一个duration内存不足而其他duration还有空余的情况。
这个问题将在12c版本以后解决，由于体系的改变导致可以允许子池减少对duration的需求。
