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,导致共享内存抖动权衡：有限的共享内存自动管理,经验分享：分析共享池常用脚本,colavgsizeformata30truncate;colsizformat999999999999SELECTKSMCHCLSCLASS,COUNT(KSMCHCLS)NUM,SUM(KSMCHSIZ)SIZ,To_char(SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024),999,999.00)|kAVGSIZEFROMX$KSMSPGROUPBYKSMCHCLS;,例子,CLASSNUMSIZAVGSIZE-R-free3403241358493.10kR-freea8561233640814.07kR-perm110293928826.09kfree435378312087744.70kfreeabl13743207516561.47kperm7014798068574961.12krecr56553791616.65k,经验分享：分析共享池常用脚本（2）,colsga_heapformata15colsizeformata10selectKSMCHIDXSubPool,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=freememorygroupbyksmchidx,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)freememory10K796free239003361sgaheap(1,0)freememory10K73R-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)freememory10K861free462579682sgaheap(2,0)freememory10K67R-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_freememory29327792miscellaneous12908400transaction800448UNDOINFOSEGMENTEDARRAY325056errors23080temporarytabl3136SEQS.O.264800partitioningd92400db_handles1740000replicationsessionstats503120gesre