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

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,华章培训,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,第,*,页,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,O,racle数据库性能优化实务,第五讲：共享池分析,华章培训网、,华章培训网版权所有,Oracle的共享池,第,2,页,library cache包含 SQL语句,分析的代码,执行计划等,data dictionary cache包含 table, column等对象的定义以及权限的信息,共享池的大小由 SHARED_POOL_SIZE 确定.,Shared pool,Librarycache,Datadictionarycache,SGA,2,共享池分解,第,3,页,56C00000,57C00000,Shared Pool,包含下面组成部分,Permanent Area,Segmented Arrays,Library Cache,Row Cache,Reserved Area,3,Permanent Area,第,4,页,数据库启动时固定分配,包含以下组件：,进程,会话,Segmented Arrays（锁、事务、资源等）,4,保留池,保留给大对象使用的区域,只有当共享池无法分配时才分配,V$SHARED_POOL_RESERVED,SHARED_POOL_RESERVED_SIZE,定义保留池的大小,缺省是,SHARED_POOL_SIZE,的5%,_SHARED_POOL_RESERVED_PCT,缺省,5%,_SHARED_POOL_RESERVED_MIN_ALLOC,缺省,4400,超过这个大小才被认为是大对象,5,自动共享内存管理,到底如何分配,?,Java pool,Database,buffer cache,Redo log,buffer,Shared pool,Large pool,Fixed SGA,Streams pool,SGA,6,自动共享内存管理的原理,定期捕获系统状态,将内存分配给需要的对象,启动时恢复上次关闭时的分配情况,SPFILE,7,自动共享内存管理与共享池抖动,自动内存管理适用于SGA资源充足的情况,如果系统负载较高，并且SGA资源不足的情况，使用ASMM可能出现SGA抖动,除了设置SGA_TARGET外，设置某些缓冲池的最小值，可以缓解SGA抖动,SGA抖动严重的系统，建议关闭自动共享内存管理,注意监控V$SGA_RESIZE_OPS视图,8,共享池故障的危害,影响系统性能,消耗过多CPU资源,导致系统宕机,9,共享池主要性能问题,共享池不足,共享池碎片,SQL分析/执行过于频繁,业务高峰期大量编译存储过程和视图,过高的LOGON/LOGOFF频率,10,共享池诊断路径,检查ALERT LOG是否有异常,检查V$SESSION_WAIT是否有共享池相关闩锁等待,HANGANALYZE分析是否HANG住现象,检查CURSOR相关情况,检查保留池情况,STATSPACK/AWR报告检查整体情况,11,AWR报告中的共享池问题,Instance Efficiency Percentages (Target 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,12,共享池优化要点,8I共享池和9I的结构不同，9i的共享池效率更高,共享池过小可能导致性能问题或者出现ORA-4031,共享池碎片化可能导致严重的问题,9i开始共享池采用SUBPOOL机制,利：减少共享池争用，避免管理过大的共享池,弊：增加共享池碎片的机会,权衡：CPU开销和共享池碎片,10g采用共享内存自动管理机制,利：减少了犹豫ORA-4031导致的宕机，灵活分配共享内存,弊：存在BUG,导致共享内存抖动,权衡：有限的共享内存自动管理,13,经验分享：分析共享池常用脚本,col avg size format a30 truncate;,col siz format 999999999999,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;,14,例子,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.12k,recr 5655 3791616 .65k,15,经验分享：分析共享池常用脚本（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 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);,16,例子,SubPool SGA_HEAP CHUNKCOMMENT size COUNT(*) 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 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 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,17,共享池相关的几个指标,共享池命中率：共享池命中率的目标是100%,每秒分析/硬分析数量,硬分析比例,共享池空闲比例,none parse CPU%,执行超过一次的SQL的百分比,18,共享池相关的闩锁,共享池相关LATCH一般和共享池不足或者分析过于频繁有关,如果共享池命中率不高或者共享池使用率接近于100%使用，需要加大共享池,共享池碎片问题也会加大闩锁竞争,shared pool,library cache,library cache pin,row cache objects,row cache enqueue latch,19,共享池优化相关的参数,shared_pool_size,open_cursors,session_cached_cursors,cursor_space_for_time,cursor_sharing,_kghdsidx_count,20,案例,某联通的优化步骤：,加大SESSOIN_CACHED_CURSORS,设置CURSOR_SPACE_FOR_TIME=TRUE,后果：,第二天由于ORA-4031导致宕机,21,优化SQL分析,使用绑定变量,调整CURSOR_SHARING,加大共享池,生产时间减少数据字典修改,使用SESSION_CACHED_CURSORS参数减少软分析,将TRIGGER/PACKAGE KEEP到共享池,KEEP常用SQL到共享池,22,10G中的共享内存抖动,alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss;,col component format a40 truncate;,select component,oper_type, oper_mode, start_time, end_time, trunc(target_size/1024/1024) target,from v$sga_resize_ops;,23,共享池抖动的例子,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 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 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,24,共享池碎片和ORA-4031,共享池碎片的原因,共享池的分配和释放十分频繁,共享池申请中几十到几百字节的申请量很大,动态扩展的PERMENT对象,BUG,CACHED CURSOR,.,共享池有自恢复能力,ORA-4031带来的问题,性能下降,宕机,25,常用脚本-可用于查看ORA-4031,select,indx,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs,from,sys.x$kghlu,where,inst_id = userenv(Instance),26,例子,INDX KGHLURCR KGHLUTRN KGHLUFSH KGHLUOPS KGHLUNFU KGHLUNFS,- - - - - - -,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,27,缓解共享池碎片问题,配置合理的参数，尽可能少的动态扩展PERMENT的数组,减少SUBPOOL的数量,将常用大对象PIN到内存,慎用CURSOR_SPACE_FOR_TIME,尽可能使用绑定变量,定期清理长期连接数据库的非用户会话,定期重连数据库连接池,定期刷新共享池,定期重启实例,28,SUBPOOL和共享池碎片,_kghdsidx_count,参数可以手工配置SUBPOOL数量,SUBPOOL和CPU_COUNT的关系,每4个CPU一个,最多7个,SUBPOOL的最小大小,9i：128M,10g：256M,11g：512M,SUBPOOL的两面性,提高并发访问性能,增加碎片的机会,29,案例-分析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,table definiti 776,PL/SQL MPCODE 419304,gcs resource hash table 2097152,PL/SQL DIANA 608352,trigger inform 64,ges enqueues 6563240,PL/SQL PPCODE 0,ges resource hash table 4325376,trigger defini 0,gcs resources 45304256,sim memory hea 2558400,dictionary cache 1065728,db_block_hash_buckets 19589168,ges resources 441539912,KQR M PO 45568,Checkpoint queue 5245440,library cache 6492608,type object de 0,sql area 1236744,sessions 1119456,gcs shadows 28805632,event statistics per sess 4696416,trigger source 0,VIRTUAL CIRCUITS 926800,KGLS heap 517760,parameters 34048,fixed allocation callback 312,30,下节预告：,DB CACHE分析,DB CACHE的基本原理,DB CACHE优化的要点,DB CACHE性能问题诊断的方法,如何解决热块冲突,31,感谢您对华章培训网的支持,!,. ,