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

Oracle数据库性能优化实务第五讲 共享池分析 主讲人 白鳝 华章培训网 华章培训网版权所有 Oracle的共享池 第2页 librarycache包含SQL语句 分析的代码 执行计划等datadictionarycache包含table column等对象的定义以及权限的信息共享池的大小由SHARED POOL SIZE确定 Sharedpool Librarycache Datadictionarycache SGA 共享池分解 第3页 SharedPool包含下面组成部分PermanentAreaSegmentedArraysLibraryCacheRowCacheReservedArea PermanentArea 第4页 数据库启动时固定分配包含以下组件 进程会话SegmentedArrays 锁 事务 资源等 保留池 保留给大对象使用的区域只有当共享池无法分配时才分配V SHARED POOL RESERVEDSHARED POOL RESERVED SIZE定义保留池的大小缺省是SHARED POOL SIZE的5 SHARED POOL RESERVED PCT缺省5 SHARED POOL RESERVED MIN ALLOC缺省4400超过这个大小才被认为是大对象 自动共享内存管理 到底如何分配 Javapool Databasebuffercache Redologbuffer Sharedpool Largepool FixedSGA Streamspool SGA 自动共享内存管理的原理 定期捕获系统状态 将内存分配给需要的对象 启动时恢复上次关闭时的分配情况 SPFILE 自动共享内存管理与共享池抖动 自动内存管理适用于SGA资源充足的情况如果系统负载较高 并且SGA资源不足的情况 使用ASMM可能出现SGA抖动除了设置SGA TARGET外 设置某些缓冲池的最小值 可以缓解SGA抖动SGA抖动严重的系统 建议关闭自动共享内存管理注意监控V SGA RESIZE OPS视图 共享池故障的危害 影响系统性能消耗过多CPU资源导致系统宕机 共享池主要性能问题 共享池不足共享池碎片SQL分析 执行过于频繁业务高峰期大量编译存储过程和视图过高的LOGON LOGOFF频率 共享池诊断路径 检查ALERTLOG是否有异常检查V SESSION WAIT是否有共享池相关闩锁等待HANGANALYZE分析是否HANG住现象检查CURSOR相关情况检查保留池情况STATSPACK AWR报告检查整体情况 AWR报告中的共享池问题 InstanceEfficiencyPercentages Target100 BufferNowait 99 97RedoNoWait 100 00BufferHit 99 30In memorySort 100 00LibraryHit 86 73SoftParse 87 07ExecutetoParse 37 18LatchHit 99 96ParseCPUtoParseElapsd 55 39 Non ParseCPU 84 52 共享池优化要点 8I共享池和9I的结构不同 9i的共享池效率更高共享池过小可能导致性能问题或者出现ORA 4031共享池碎片化可能导致严重的问题9i开始共享池采用SUBPOOL机制利 减少共享池争用 避免管理过大的共享池弊 增加共享池碎片的机会权衡 CPU开销和共享池碎片10g采用共享内存自动管理机制利 减少了犹豫ORA 4031导致的宕机 灵活分配共享内存弊 存在BUG 导致共享内存抖动权衡 有限的共享内存自动管理 经验分享 分析共享池常用脚本 col avgsize formata30truncate colsizformat999999999999SELECTKSMCHCLSCLASS COUNT KSMCHCLS NUM SUM KSMCHSIZ SIZ To char SUM KSMCHSIZ COUNT KSMCHCLS 1024 999 999 00 k AVGSIZE FROMX KSMSPGROUPBYKSMCHCLS 例子 CLASSNUMSIZAVGSIZE R free3403241358493 10kR freea8561233640814 07kR perm110293928826 09kfree435378312087744 70kfreeabl13743207516561 47kperm7014798068574961 12krecr56553791616 65k 经验分享 分析共享池常用脚本 2 colsga heapformata15colsizeformata10selectKSMCHIDX SubPool sgaheap KSMCHIDX 0 sga heap ksmchcomChunkComment 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 ksmchclsStatus sum ksmchsiz Bytesfromx ksmspwhereKSMCHCOM freememory groupbyksmchidx ksmchcls sgaheap 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 例子 SubPoolSGA HEAPCHUNKCOMMENTsizeCOUNT STATUSBYTES 1sgaheap 1 0 freememory0 1K9121free9503201sgaheap 1 0 freememory2 3K1free22401sgaheap 1 0 freememory4 5K49free1963041sgaheap 1 0 freememory8 9k126free10547281sgaheap 1 0 freememory9 10k46free4123281sgaheap 1 0 freememory 10K796free239003361sgaheap 1 0 freememory 10K73R free2473230162sgaheap 2 0 freememory0 1K11462free12708642sgaheap 2 0 freememory3 4K1free27922sgaheap 2 0 freememory4 5K1free38482sgaheap 2 0 freememory6 7k1free64962sgaheap 2 0 freememory8 9k128free10715362sgaheap 2 0 freememory9 10k38free3366642sgaheap 2 0 freememory 10K861free462579682sgaheap 2 0 freememory 10K67R free247528016 共享池相关的几个指标 共享池命中率 共享池命中率的目标是100 每秒分析 硬分析数量硬分析比例共享池空闲比例noneparseCPU 执行超过一次的SQL的百分比 共享池相关的闩锁 共享池相关LATCH一般和共享池不足或者分析过于频繁有关如果共享池命中率不高或者共享池使用率接近于100 使用 需要加大共享池共享池碎片问题也会加大闩锁竞争sharedpoollibrarycachelibrarycachepinrowcacheobjectsrowcacheenqueuelatch 共享池优化相关的参数 shared pool sizeopen cursorssession cached cursorscursor space for timecursor sharing kghdsidx count 案例 某联通的优化步骤 加大SESSOIN CACHED CURSORS设置CURSOR SPACE FOR TIME TRUE后果 第二天由于ORA 4031导致宕机 优化SQL分析 使用绑定变量调整CURSOR SHARING加大共享池生产时间减少数据字典修改使用SESSION CACHED CURSORS参数减少软分析将TRIGGER PACKAGEKEEP到共享池KEEP常用SQL到共享池 10G中的共享内存抖动 altersessionsetnls date format yyyy mm ddhh24 mi ss colcomponentformata40truncate selectcomponent oper type oper mode start time end time trunc target size 1024 1024 targetfromv sga resize ops 共享池抖动的例子 COMPONENTOPER TOPER MODESTART TIMEEND TIME DEFAULTbuffercacheSHRINKDEFERRED2010 08 0414 54 522010 08 0414 55 07sharedpoolGROWDEFERRED2010 08 0414 54 522010 08 0414 55 07DEFAULTbuffercacheSHRINKDEFERRED2010 08 0414 58 522010 08 0414 59 00sharedpoolGROWDEFERRED2010 08 0414 58 522010 08 0414 59 00DEFAULTbuffercacheSHRINKDEFERRED2010 08 0415 05 232010 08 0415 05 32sharedpoolGROWDEFERRED2010 08 0415 05 232010 08 0415 05 32DEFAULTbuffercacheSHRINKDEFERRED2010 08 0415 08 532010 08 0415 08 57sharedpoolGROWDEFERRED2010 08 0415 08 532010 08 0415 08 57DEFAULTbuffercacheSHRINKDEFERRED2010 08 0415 12 232010 08 0415 12 29sharedpoolGROWDEFERRED2010 08 0415 12 232010 08 0415 12 29 共享池碎片和ORA 4031 共享池碎片的原因共享池的分配和释放十分频繁共享池申请中几十到几百字节的申请量很大动态扩展的PERMENT对象BUGCACHEDCURSOR 共享池有自恢复能力ORA 4031带来的问题性能下降宕机 常用脚本 可用于查看ORA 4031 selectindx kghlurcr kghlutrn kghlufsh kghluops kghlunfu kghlunfsfromsys x kghluwhereinst id userenv Instance 例子 INDXKGHLURCRKGHLUTRNKGHLUFSHKGHLUOPSKGHLUNFUKGHLUNFS 06669150784394545283429408016875153834647741993665784064246891225734960449154240803129921961341194655729541408042348366647447641659154080583081424245672630403344112 缓解共享池碎片问题 配置合理的参数 尽可能少的动态扩展PERMENT的数组减少SUBPOOL的数量将常用大对象PIN到内存慎用CURSOR SPACE FOR TIME尽可能使用绑定变量定期清理长期连接数据库的非用户会话定期重连数据库连接池定期刷新共享池定期重启实例 SUBPOOL和共享池碎片 kghdsidx count参数可以手工配置SUBPOOL数量SUBPOOL和CPU COUNT的关系每4个CPU一个最多7个SUBPOOL的最小大小9i 128M10g 256M11g 512MSUBPOOL的两面性提高并发访问性能增加碎片的机会 案例 分析4031TRACE AllocationNameSize freememory 29327792 miscellaneous 12908400 transaction 800448 UNDOINFOSEGMENTEDARRAY 325056 errors 23080 temporarytabl 3136 SEQS O 264800 partitioningd 92400 db handles 1740000 replicationsessionstats 503120 gesregularmsgbuffers 1576248 tabledefiniti 776 PL SQL