Oracle数据库性能优化实务_共享池分析

1、Oracle数据库性能优化实务第五讲：共享池分析,主讲人：白鳝,华章培训网、华章培训网版权所有,Oracle的共享池,第2页,library cache包含 SQL语句,分析的代码,执行计划等 data dictionary cache包含 table, column等对象的定义以及权限的信息 共享池的大小由 SHARED_POOL_SIZE 确定.,Shared pool,Librarycache,Datadictionarycache,SGA,共享池分解,第3页,Shared Pool 包含下面组成部分 Permanent Area Segmented Arrays Library Cac

2、he Row Cache Reserved Area,Permanent Area,第4页,数据库启动时固定分配 包含以下组件： 进程 会话 Segmented Arrays（锁、事务、资源等）,保留池,保留给大对象使用的区域 只有当共享池无法分配时才分配 V$SHARED_POOL_RESERVED SHARED_POOL_RESERVED_SIZE 定义保留池的大小 缺省是SHARED_POOL_SIZE的5% _SHARED_POOL_RESERVED_PCT 缺省 5% _SHARED_POOL_RESERVED_MIN_ALLOC 缺省 4400 超过这个大小才被认为是大对象,自动共

3、享内存管理,到底如何分配?,Java pool,Database buffer cache,Redo log buffer,Shared pool,Large pool,Fixed SGA,Streams pool,SGA,自动共享内存管理的原理,定期捕获系统状态,将内存分配给需要的对象,启动时恢复上次关闭时的分配情况,SPFILE,自动共享内存管理与共享池抖动,自动内存管理适用于SGA资源充足的情况 如果系统负载较高，并且SGA资源不足的情况，使用ASMM可能出现SGA抖动 除了设置SGA_TARGET外，设置某些缓冲池的最小值，可以缓解SGA抖动 SGA抖动严重的系统，建议关闭自动共享内存

4、管理 注意监控V$SGA_RESIZE_OPS视图,共享池故障的危害,影响系统性能 消耗过多CPU资源 导致系统宕机,共享池主要性能问题,共享池不足 共享池碎片 SQL分析/执行过于频繁 业务高峰期大量编译存储过程和视图 过高的LOGON/LOGOFF频率,共享池诊断路径,检查ALERT LOG是否有异常 检查V$SESSION_WAIT是否有共享池相关闩锁等待 HANGANALYZE分析是否HANG住现象 检查CURSOR相关情况 检查保留池情况 STATSPACK/AWR报告检查整体情况,AWR报告中的共享池问题,Instance Efficiency Percentages (Targe

5、t 100%) Buffer Nowait %: 99.97 Redo NoWait %: 100.00 Buffer Hit %: 99.30 In-memory Sort %: 100.00 Library Hit %: 86.73 Soft Parse %: 87.07 Execute to Parse %: 37.18 Latch Hit %: 99.96 Parse CPU to Parse Elapsd %: 55.39 % Non-Parse CPU: 84.52,共享池优化要点,8I共享池和9I的结构不同，9i的共享池效率更高 共享池过小可能导致性能问题或者出现ORA-4031

6、 共享池碎片化可能导致严重的问题 9i开始共享池采用SUBPOOL机制 利：减少共享池争用，避免管理过大的共享池 弊：增加共享池碎片的机会 权衡：CPU开销和共享池碎片 10g采用共享内存自动管理机制 利：减少了犹豫ORA-4031导致的宕机，灵活分配共享内存 弊：存在BUG,导致共享内存抖动 权衡：有限的共享内存自动管理,经验分享：分析共享池常用脚本,col avg size format a30 truncate; col siz format 999999999999 SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SI

7、Z, To_char( (SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024),999,999.00)|k AVG SIZE FROM X$KSMSP GROUP BY KSMCHCLS;,例子,CLASS NUM SIZ AVG SIZE - - - - R-free 340 32413584 93.10k R-freea 856 12336408 14.07k R-perm 110 2939288 26.09k free 435378 312087744 .70k freeabl 13743 20751656 1.47k perm 701479 806857496 1.1

8、2k recr 5655 3791616 .65k,经验分享：分析共享池常用脚本（2）,col sga_heap format a15 col size format a10 select KSMCHIDX SubPool, sga heap(|KSMCHIDX|,0)sga_heap,ksmchcom ChunkComment, decode(round(ksmchsiz/1000),0,0-1K, 1,1-2K, 2,2-3K,3,3-4K, 4,4-5K,5,5-6k,6,6-7k,7,7-8k,8, 8-9k, 9,9-10k, 10K) size, count(*),ksmchcls

9、 Status, sum(ksmchsiz) Bytes from x$ksmsp where KSMCHCOM = free memory group by ksmchidx, ksmchcls, sga heap(|KSMCHIDX|,0),ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,0-1K, 1,1-2K, 2,2-3K, 3,3-4K,4,4-5K,5,5-6k,6, 6-7k,7,7-8k,8,8-9k, 9,9-10k, 10K);,例子,SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*)

10、STATUS BYTES - - - - - - - 1 sga heap(1,0) free memory 0-1K 9121 free 950320 1 sga heap(1,0) free memory 2-3K 1 free 2240 1 sga heap(1,0) free memory 4-5K 49 free 196304 1 sga heap(1,0) free memory 8-9k 126 free 1054728 1 sga heap(1,0) free memory 9-10k 46 free 412328 1 sga heap(1,0) free memory 10K

11、 796 free 23900336 1 sga heap(1,0) free memory 10K 73 R-free 247323016 2 sga heap(2,0) free memory 0-1K 11462 free 1270864 2 sga heap(2,0) free memory 3-4K 1 free 2792 2 sga heap(2,0) free memory 4-5K 1 free 3848 2 sga heap(2,0) free memory 6-7k 1 free 6496 2 sga heap(2,0) free memory 8-9k 128 free

12、1071536 2 sga heap(2,0) free memory 9-10k 38 free 336664 2 sga heap(2,0) free memory 10K 861 free 46257968 2 sga heap(2,0) free memory 10K 67 R-free 247528016,共享池相关的几个指标,共享池命中率：共享池命中率的目标是100% 每秒分析/硬分析数量 硬分析比例 共享池空闲比例 none parse CPU% 执行超过一次的SQL的百分比,共享池相关的闩锁,共享池相关LATCH一般和共享池不足或者分析过于频繁有关 如果共享池命中率不高或者共享

13、池使用率接近于100%使用，需要加大共享池 共享池碎片问题也会加大闩锁竞争 shared pool library cache library cache pin row cache objects row cache enqueue latch,共享池优化相关的参数,shared_pool_size open_cursors session_cached_cursors cursor_space_for_time cursor_sharing _kghdsidx_count,案例,某联通的优化步骤： 加大SESSOIN_CACHED_CURSORS 设置CURSOR_SPACE_FOR_TI

14、ME=TRUE 后果： 第二天由于ORA-4031导致宕机,优化SQL分析,使用绑定变量 调整CURSOR_SHARING 加大共享池 生产时间减少数据字典修改 使用SESSION_CACHED_CURSORS参数减少软分析 将TRIGGER/PACKAGE KEEP到共享池 KEEP常用SQL到共享池,10G中的共享内存抖动,alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss; col component format a40 truncate; select component,oper_type, oper_mode, star

15、t_time, end_time, trunc(target_size/1024/1024) target from v$sga_resize_ops;,共享池抖动的例子,COMPONENT OPER_T OPER_MODE START_TIME END_TIME - - - - - DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 14:54:52 2010-08-04 14:55:07 shared pool GROW DEFERRED 2010-08-04 14:54:52 2010-08-04 14:55:07 DEFAULT buffer

16、 cache SHRINK DEFERRED 2010-08-04 14:58:52 2010-08-04 14:59:00 shared pool GROW DEFERRED 2010-08-04 14:58:52 2010-08-04 14:59:00 DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:05:23 2010-08-04 15:05:32 shared pool GROW DEFERRED 2010-08-04 15:05:23 2010-08-04 15:05:32 DEFAULT buffer cache SHRINK

17、DEFERRED 2010-08-04 15:08:53 2010-08-04 15:08:57 shared pool GROW DEFERRED 2010-08-04 15:08:53 2010-08-04 15:08:57 DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:12:23 2010-08-04 15:12:29 shared pool GROW DEFERRED 2010-08-04 15:12:23 2010-08-04 15:12:29,共享池碎片和ORA-4031,共享池碎片的原因 共享池的分配和释放十分频繁 共享池申

18、请中几十到几百字节的申请量很大 动态扩展的PERMENT对象 BUG CACHED CURSOR . 共享池有自恢复能力 ORA-4031带来的问题 性能下降 宕机,常用脚本-可用于查看ORA-4031,select indx, kghlurcr, kghlutrn, kghlufsh, kghluops, kghlunfu, kghlunfs from sys.x$kghlu where inst_id = userenv(Instance),例子,INDX KGHLURCR KGHLUTRN KGHLUFSH KGHLUOPS KGHLUNFU KGHLUNFS - - - - - - -

19、 0 6669 15078 43945 452834 29 4080 1 6875 15383 46477 41993665 78 4064 2 4689 12257 34960 449154 2 4080 3 12992 19613 41194 65572954 1 4080 4 2348 3666 47447 641659 15 4080 5 8308 14242 45672 630403 34 4112,缓解共享池碎片问题,配置合理的参数，尽可能少的动态扩展PERMENT的数组 减少SUBPOOL的数量 将常用大对象PIN到内存 慎用CURSOR_SPACE_FOR_TIME 尽可能使用

20、绑定变量 定期清理长期连接数据库的非用户会话 定期重连数据库连接池 定期刷新共享池 定期重启实例,SUBPOOL和共享池碎片,_kghdsidx_count参数可以手工配置SUBPOOL数量 SUBPOOL和CPU_COUNT的关系 每4个CPU一个 最多7个 SUBPOOL的最小大小 9i：128M 10g：256M 11g：512M SUBPOOL的两面性 提高并发访问性能 增加碎片的机会,案例-分析4031 TRACE,Allocation Name Size _ _ free memory 29327792 miscellaneous 12908400 transaction 800448 UNDO INFO SEGMENTED ARRAY 325056 errors 23080 temporary tabl 3136 SEQ S.O. 264800 partitioning d 92400 db_handles 1740000 replication session stats 503120 ges regular msg buffers 1576248 tabl