SHARED_POOL_SIZE ora-4031处理思路
一、概念
- SHARED_POOL_SIZE --大小
- SHARED_POOL_RESERVED_SIZE --保留区大小
- _SHARED_POOL_RESERVED_MIN_ALLOC --请求的内存是否放入保留池的阈值，默认4400,如果在共享池空闲列表中找不到足够大小的内存块，则大于此值的内存分配可以从保留区中分配空间
1.1、查看该参数默认值
SELECT nam.ksppinm NAME,val.ksppstvl VALUE FROM x$ksppi nam,x$ksppsv val WHERE nam.indx = val.indx AND nam.ksppinm LIKE '%shared%' ORDER BY 1;
NAME VALUE
---------------------------------------- ------------------------------------------------------------
_shared_io_pool_debug_trc 0
_shared_io_pool_size 134217728
_shared_io_set_value FALSE
_shared_iop_max_size 134217728
_shared_pool_max_size 0
_shared_pool_max_sz 0
_shared_pool_minsize_on FALSE
_shared_pool_reserved_min_alloc 4400
_shared_pool_reserved_pct 5
_shared_server_load_balance 0
_shared_server_num_queues 2
1.2、查看sga中各组件大小
select * from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE CON_ID
------------------------------ ------------ ---------- ---------- ------------------- ---------- -------------------------- ------------------ ------------------- ------------ ----------
shared pool 1761607680 1409286144 1761607680 0 5 GROW DEFERRED 2022-04-12 22:01:44 16777216 0
large pool 33554432 33554432 33554432 0 0 STATIC 16777216 0
java pool 0 0 0 0 0 STATIC 16777216 0
streams pool 0 0 0 0 0 STATIC 16777216 0
unified pga pool 0 0 0 0 0 STATIC 16777216 0
memoptimize buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT buffer cache 4966055936 4966055936 5318377472 0 5 SHRINK DEFERRED 2022-04-12 22:01:44 16777216 0
KEEP buffer cache 0 0 0 0 0 STATIC 16777216 0
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 16777216 0
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE CON_ID
------------------------------ ------------ ---------- ---------- ------------------- ---------- -------------------------- ------------------ ------------------- ------------ ----------
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 16777216 0
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 16777216 0
Shared IO Pool 134217728 134217728 134217728 134217728 0 STATIC 16777216 0
Data Transfer Cache 0 0 0 0 0 STATIC 16777216 0
In-Memory Area 0 0 0 0 0 STATIC 16777216 0
In Memory RW Extension Area 0 0 0 0 0 STATIC 16777216 0
In Memory RO Extension Area 0 0 0 0 0 STATIC 16777216 0
ASM Buffer Cache 0 0 0 0 0 STATIC 16777216 0
1.3、查看内存组件大小变动
select * from V$SGA_RESIZE_OPS;
COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME CON_ID
------------------------------ -------------------------- ------------------ ------------------------------ ------------ ----------- ---------- ------------------ ------------------- ------------------- ----------
DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
In Memory RW Extension Area STATIC _inmemory_ext_rwarea 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
In-Memory Area STATIC inmemory_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
Data Transfer Cache STATIC data_transfer_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
Shared IO Pool STATIC _shared_io_pool_size 0 134217728 134217728 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
large pool STATIC large_pool_size 0 33554432 33554432 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
java pool STATIC java_pool_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
streams pool STATIC streams_pool_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
unified pga pool STATIC _unified_pga_pool_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
DEFAULT buffer cache STATIC db_cache_size 0 5318377472 5318377472 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME CON_ID
------------------------------ -------------------------- ------------------ ------------------------------ ------------ ----------- ---------- ------------------ ------------------- ------------------- ----------
ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
DEFAULT buffer cache INITIALIZING db_cache_size 5318377472 5318377472 5318377472 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
shared pool STATIC shared_pool_size 0 1409286144 1409286144 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
memoptimize buffer cache STATIC memoptimize_pool_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
In Memory RO Extension Area STATIC _inmemory_ext_roarea 0 0 0 COMPLETE 2022-04-06 16:56:00 2022-04-06 16:56:00 0
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 5318377472 5251268608 5251268608 COMPLETE 2022-04-07 22:02:34 2022-04-07 22:02:34 0
COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME CON_ID
------------------------------ -------------------------- ------------------ ------------------------------ ------------ ----------- ---------- ------------------ ------------------- ------------------- ----------
shared pool GROW DEFERRED shared_pool_size 1409286144 1476395008 1476395008 COMPLETE 2022-04-07 22:02:34 2022-04-07 22:02:34 0
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 5251268608 5184159744 5184159744 COMPLETE 2022-04-10 18:00:57 2022-04-10 18:00:57 0
shared pool GROW DEFERRED shared_pool_size 1476395008 1543503872 1543503872 COMPLETE 2022-04-10 18:00:57 2022-04-10 18:00:57 0
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 5184159744 5117050880 5117050880 COMPLETE 2022-04-11 22:00:52 2022-04-11 22:00:52 0
shared pool GROW DEFERRED shared_pool_size 1543503872 1610612736 1610612736 COMPLETE 2022-04-11 22:00:52 2022-04-11 22:00:52 0
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 5117050880 5049942016 5049942016 COMPLETE 2022-04-12 18:00:51 2022-04-12 18:00:51 0
shared pool GROW DEFERRED shared_pool_size 1610612736 1677721600 1677721600 COMPLETE 2022-04-12 18:00:51 2022-04-12 18:00:51 0
shared pool GROW DEFERRED shared_pool_size 1677721600 1761607680 1761607680 COMPLETE 2022-04-12 22:01:43 2022-04-12 22:01:44 0
DEFAULT buffer cache SHRINK DEFERRED db_cache_size 5049942016 4966055936 4966055936 COMPLETE 2022-04-12 22:01:43 2022-04-12 22:01:44 0
1.4、内存大小历史变动情况
select * from DBA_HIST_MEM_DYNAMIC_COMP where component='shared pool' order by snap_id desc;
SNAP_ID DBID INSTANCE_NUMBER COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE CON_DBID CON_ID
---------- ---------- --------------- ------------------------------ ------------ ---------- ---------- ------------------- ---------- -------------------------- ------------------ ------------------- ------------ ---------- ----------
130 942089955 2 shared pool 1308622848 1308622848 1308622848 0 0 STATIC 16777216 942089955 0
130 942089955 1 shared pool 1409286144 1191182336 1409286144 0 4 GROW DEFERRED 2022-04-03 22:04:52 16777216 942089955 0
129 942089955 2 shared pool 1308622848 1308622848 1308622848 0 0 STATIC 16777216 942089955 0
129 942089955 1 shared pool 1409286144 1191182336 1409286144 0 4 GROW DEFERRED 2022-04-03 22:04:52 16777216 942089955 0
128 942089955 1 shared pool 1409286144 1191182336 1409286144 0 4 GROW DEFERRED 2022-04-03 22:04:52 16777216 942089955 0
128 942089955 2 shared pool 1308622848 1308622848 1308622848 0 0 STATIC 16777216 942089955 0
127 942089955 2 shared pool 1308622848 1308622848 1308622848 0 0 STATIC 16777216 942089955 0
127 942089955 1 shared pool 1409286144 1191182336 1409286144 0 4 GROW DEFERRED 2022-04-03 22:04:52 16777216 942089955 0
二、处理方法
2.1、内存大小不足导致
select * from V$SHARED_POOL_RESERVED;
FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE USED_COUNT MAX_USED_SIZE REQUESTS REQUEST_MISSES LAST_MISS_SIZE MAX_MISS_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS LAST_ABORTED_SIZE CON_ID
---------- ------------- ---------- ------------- ---------- ------------- ---------- ------------- ---------- -------------- -------------- ------------- ---------------- ----------------- ------------------------- ---------------- ----------------- ----------
82758152 335053.247 103 2621240 2905872 11764.664 144 427232 7868 0 0 0 0 0 4194304 0 0 0
select count (*) from V$SHARED_POOL_RESERVED where REQUEST_FAILURES > 0
检查REQUEST_FAILURES，请求失败次数
检查LAST_FAILURE_SIZE大小是否大于__SHARED_POOL_RESERVED_MIN_ALLOC，即查看是否是大块内存请求保留区，如果是请求保留区，那么需要增大SHARED_POOL_RESERVED_SIZE，至于增加__SHARED_POOL_RESERVED_MIN_ALLOC的值，还是别动的好。SHARED_POOL_RESERVED_SIZE值的增大会导致标准shared pool区域减少，所以还是整体增大shared pool比较保险。
2.1.2、检查命中率
SQL> SELECT SUM(pins) "EXECUTIONS",SUM(reloads) "CACHE MISSES WHILE EXECUTING",SUM(reloads)/SUM(pins)*100 FROM v$librarycache;
EXECUTIONS CACHE MISSES WHILE EXECUTING SUM(RELOADS)/SUM(PINS)*100
---------- ---------------------------- --------------------------
3915112 24390 .622970684
如果未命中与执行的比率超过 1%，library cache区域不足，增大shared pool以增大命中率。
2.2、内存碎片导致
select * from V$SHARED_POOL_RESERVED;
FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE USED_COUNT MAX_USED_SIZE REQUESTS REQUEST_MISSES LAST_MISS_SIZE MAX_MISS_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS LAST_ABORTED_SIZE CON_ID
---------- ------------- ---------- ------------- ---------- ------------- ---------- ------------- ---------- -------------- -------------- ------------- ---------------- ----------------- ------------------------- ---------------- ----------------- ----------
82758152 335053.247 103 2621240 2905872 11764.664 144 427232 7868 0 0 0 0 0 4194304 0 0 0
如果REQUEST_FAILURES > 0 ，LAST_FAILURE_SIZE < _SHARED_POOL_RESERVED_MIN_ALLOC。表示请求内存大小并不是大内存块请求。
确定大部分请求内存块大小，如果为4100至4400，那么降低_SHARED_POOL_RESERVED_MIN_ALLOC的方法可以考虑，但是毕竟隐含参数，能不动就不动。4100是shared pool最大chunk大小。所以不要将_SHARED_POOL_RESERVED_MIN_ALLOC小于4100，会导致数据大量涌入，因为当系统请求内存不足是，会向较大有空闲chunk区域请求并拆分。
有几种技术可以增加游标的可共享性，从而减少共享池碎片以及 ORA-4031 错误的可能性。最好的方法是修改应用程序以使用绑定变量。无法修改应用程序时的另一个解决方法是将 CURSOR_SHARING 设置为与 EXACT 不同的值（请注意，这可能会导致执行计划发生变化，因此建议先测试应用程序）。当上述技术都无效时，刷新共享池可能有助于缓解碎片。但是，必须考虑一些因素：
刷新共享池将导致所有未使用的游标从库缓存中删除。因此，在发出共享池刷新之后，大多数 SQL 和 PL/SQL 游标都必须进行硬解析。这会增加系统的 CPU 使用率，也会增加锁存器活动。
当应用程序不使用绑定变量并且很可能许多用户执行频繁的类似操作（如在 OLTP 系统中）时，通常在发出刷新后不久，碎片又会恢复原状。因此，建议刷新共享池并不总是解决不良应用程序的方法。
对于大型共享池，刷新共享池可能会导致系统停止，特别是当实例非常活跃时。建议在非高峰时段刷新共享池。
2.3、其它原因
每次需要执行 SQL 或 PL/SQL 语句时，都会将解析表示加载到需要特定数量的可用连续空间的库缓存中。数据库扫描的第一个资源是共享池中可用的空闲内存。一旦空闲内存用完，数据库就会寻找重用已分配但未使用的块。如果相同大小的块不可用，则扫描会根据以下条件继续寻找空间：
- 块大小大于所需大小
- 空间是连续的
- 可用块（未使用）
然后将该块拆分，并将剩余的可用空间添加到适当的可用空间列表中。当数据库以这种方式运行一段时间后，共享池结构就会产生碎片。
当数据库找不到连续的空闲内存时机会发生因碎片导致的ORA-4031错误。因此，分配一块空闲空间需要更多时间，性能可能会受到影响（“块分配”由一个称为“共享池锁存器”的锁存器保护，该锁存器在整个操作期间保持不变）。然而，ORA-4031 错误并不总是影响数据库的性能。
如果 SHARED_POOL_SIZE 足够大，大多数 ORA-04031 错误是动态 SQL 对共享池进行分段的结果。
- 不共享 SQL
- 进行不必要的解析调用
- 将 SESSION_CACHED_CURSORS 设置得太高
- 不使用绑定变量
2.4、内存中trunc分配
SELECT '0 (<140)' bucket, ksmchcls, ksmchidx, 10*TRUNC(ksmchsiz/10) "From",
COUNT(*) "Count", MAX(ksmchsiz) "Biggest",
TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz<140
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 10*TRUNC(ksmchsiz/10)
UNION ALL
SELECT '1 (140-267)' bucket, ksmchcls, ksmchidx,20*TRUNC(ksmchsiz/20),
COUNT(*), MAX(ksmchsiz),
TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 140 AND 267
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 20*TRUNC(ksmchsiz/20)
UNION ALL
SELECT '2 (268-523)' bucket, ksmchcls, ksmchidx, 50*TRUNC(ksmchsiz/50),
COUNT(*), MAX(ksmchsiz),
TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 268 AND 523
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 50*TRUNC(ksmchsiz/50)
UNION ALL
SELECT '3-5 (524-4107)' bucket, ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500),
COUNT(*), MAX(ksmchsiz) ,
TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz BETWEEN 524 AND 4107
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 500*TRUNC(ksmchsiz/500)
UNION ALL
SELECT '6+ (4108+)' bucket, ksmchcls, ksmchidx, 1000*TRUNC(ksmchsiz/1000),
COUNT(*), MAX(ksmchsiz),
TRUNC(AVG(ksmchsiz)) "AvgSize", TRUNC(SUM(ksmchsiz)) "Total"
FROM x$ksmsp
WHERE ksmchsiz >= 4108
AND ksmchcls='free'
GROUP BY ksmchcls, ksmchidx, 1000*TRUNC(ksmchsiz/1000);
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
---------------------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
0 (<140) free 1 50 446 56 56 24976
0 (<140) free 1 110 195 112 112 21840
0 (<140) free 1 60 230 64 64 14720
0 (<140) free 1 130 169 136 136 22984
0 (<140) free 1 120 733 128 124 91440
0 (<140) free 1 80 544 88 83 45584
0 (<140) free 1 70 202 72 72 14544
0 (<140) free 1 40 487 48 48 23376
0 (<140) free 1 100 395 104 104 41080
0 (<140) free 1 90 196 96 96 18816
1 (140-267) free 1 240 2804 256 251 706152
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
---------------------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
1 (140-267) free 1 260 127 264 264 33528
1 (140-267) free 1 220 394 232 229 90240
1 (140-267) free 1 200 820 216 207 170448
1 (140-267) free 1 140 448 152 147 65984
1 (140-267) free 1 180 632 192 188 119064
1 (140-267) free 1 160 568 176 167 95160
2 (268-523) free 1 450 395 488 465 183992
2 (268-523) free 1 250 516 296 284 146936
2 (268-523) free 1 400 1112 448 422 470032
2 (268-523) free 1 350 936 392 371 347336
2 (268-523) free 1 300 1861 344 315 587288
BUCKET KSMCHCLS KSMCHIDX From Count Biggest AvgSize Total
---------------------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
3-5 (524-4107) free 1 1000 1278 1496 1195 1528344
3-5 (524-4107) free 1 4000 200 4088 4040 808168
3-5 (524-4107) free 1 1500 251 1976 1605 402904
3-5 (524-4107) free 1 2000 3 2496 2477 7432
3-5 (524-4107) free 1 2500 14 2832 2641 36976
3-5 (524-4107) free 1 3000 1 3096 3096 3096
3-5 (524-4107) free 1 3500 224 3992 3940 882696
3-5 (524-4107) free 1 500 2692 992 830 2237024
6+ (4108+) free 1 5246000 1 5246216 5246216 5246216
6+ (4108+) free 1 4328000 1 4328888 4328888 4328888
6+ (4108+) free 1 322000 1 322200 322200 322200
2.5、SGA中内存分布
SELECT ksmchcls CLASS, COUNT(ksmchcls) NUM, SUM(ksmchsiz) SIZ,
TO_CHAR(((SUM(ksmchsiz)/COUNT(ksmchcls)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY ksmchcls;
CLASS NUM SIZ AVG SIZE
---------------- ---------- ---------- ------------------------
perm 89803 857625704 9.33k
freeabl 156266 517478816 3.23k
R-freea 350 2915760 8.14k
R-free 105 84436856 785.31k
free 18880 19236936 1.00k
recr 123629 246305272 1.95k
如果可用内存 (SIZ) 较低（小于 5mb 左右,可能需要增加 shared_pool_size 和 shared_pool_reserved_size。
如果 perm 持续增长，那么可能会看到系统内存泄漏。
如果 freeabl 和 recr 总是很大，这表明存储了很多未释放的游标信息。
如果 free 很大，但您仍然收到 4031 错误,可能是碎片。
关于几个类型的说明：
- free：空闲的Chunk。这种Chunk可以被直接覆盖。进程从共享池分配内存时，第一步就是寻找free的Chunk直接覆盖。所有free的Chunk都会被放进FreeList链表中
- perm：永久型Chunk。和Free对应，这种Chunk不会被释放，所占内存在数据库运行期间将一直持有，不会被覆盖。Oracle中众多的内部内存结构，还有很多X$视图，占用的都是此块内存。
- recr：即Recreatable，可重建Chunk。
- freeabl：也就是Freeable，可以空闲
- R开头的是shared_pool_reserved_size（保留区的）
2.6、大池的4031
大池没有 LRU 列表。它与共享池中的保留空间不同，它使用与从共享池中分配的其他内存相同的 LRU 列表。内存块永远不会从大池中老化，内存必须由每个会话显式分配和释放。如果在发出请求时没有剩余可用内存，则会发出 ORA-4031：
SELECT pool, name, bytes FROM v$sgastat WHERE pool = 'large pool';
内存以 LARGE_POOL_MIN_ALLOC 字节的块从大型池中分配。任何分配块大小小于 LARGE_POOL_MIN_ALLOC 的请求都将分配大小为 LARGE_POOL_MIN_ALLOC的块。
解决办法：增大大池大小
三、其它
3.1、偏方
select KGHLUTRN,KGHLURCR,KGHLUTRN/KGHLURCR,KGHLUFSH/KGHLUOPS from x$kghlu;
–KGHLUFSH/KGHLUOPS<0.05表示shared_pool过小，KGHLUTRN/KGHLURCR>3表示过大
3.2、共享池中对象
select kglhdnsd,kglobtyd,count(*), sum(kglobhs0+kglobhs1+kglobhs2+kglobhs3+
kglobhs4+kglobhs5+kglobhs6+kglobt16)/1024 from x$kglob group by kglhdnsd,kglobtyd;
3.3、查询类型是fre e的Chunk，判断是否碎片太多
SELECT ksmchsiz,count(*) FROM x$ksmsp where KSMCHCLS='free' and
ksmchsiz<=150 group by ksmchsiz order by ksmchsiz ;
用x$ksmsp视图查询时，会持有Shared pool Latch很长时间，这可能会影响共享池的其他操作
