Oracle数据库性能优化实务-共享池分析.ppt
32页
Oracle数据库性能优化实务第五讲：共享池分析主讲人：白鳝华章培训网、[]华章培训网版权所有 华章培训华章培训Oracle的共享池第2页§library cachelibrary cache包含包含 SQL SQL语句语句, ,分析的代码分析的代码, ,执行计划等执行计划等§data dictionary cachedata dictionary cache包含包含 table, column table, column等对象的定等对象的定义以及权限的信息义以及权限的信息§共享池的大小由共享池的大小由 SHARED_POOL_SIZE SHARED_POOL_SIZE 确定确定. .Shared poolLibrarycacheDatadictionarycacheSGA 华章培训华章培训共享池分解第3页564000005680000056C0000057000000574000005780000057C0000058000000Shared Pool 包含下面组成部分包含下面组成部分u Permanent Areau Segmented Arraysu Library Cacheu Row Cacheu Reserved Area 华章培训华章培训Permanent Area第4页•数据库启动时固定分配数据库启动时固定分配•包含以下组件：包含以下组件：§进程§会话§SegmentedArrays（锁、事务、资源等） 华章培训华章培训保留池•保留给大对象使用的区域保留给大对象使用的区域•只有当共享池无法分配时才分配只有当共享池无法分配时才分配•V$SHARED_POOL_RESERVEDV$SHARED_POOL_RESERVED§SHARED_POOL_RESERVED_SIZE•定义保留池的大小•缺省是SHARED_POOL_SIZE的5%§_SHARED_POOL_RESERVED_PCT•缺省5%§_SHARED_POOL_RESERVED_MIN_ALLOC•缺省4400•超过这个大小才被认为是大对象 华章培训华章培训自动共享内存管理到底如何分配到底如何分配?Java poolDatabasebuffer cacheRedo log bufferShared poolLarge pool Fixed SGAStreams poolSGA 华章培训华章培训自动共享内存管理的原理定期捕获系统状态定期捕获系统状态将内存分配给需要的对象将内存分配给需要的对象启动时恢复上次关闭时的分配情况启动时恢复上次关闭时的分配情况SPFILE 华章培训华章培训自动共享内存管理与共享池抖动•自动内存管理适用于自动内存管理适用于SGASGA资源充足的情况资源充足的情况•如果系统负载较高，并且如果系统负载较高，并且SGASGA资源不足的情况，使用资源不足的情况，使用ASMMASMM可可能出现能出现SGASGA抖动抖动•除了设置除了设置SGA_TARGETSGA_TARGET外，设置某些缓冲池的最小值，可以外，设置某些缓冲池的最小值，可以缓解缓解SGASGA抖动抖动•SGASGA抖动严重的系统，建议关闭自动共享内存管理抖动严重的系统，建议关闭自动共享内存管理•注意监控注意监控V$SGA_RESIZE_OPSV$SGA_RESIZE_OPS视图视图 华章培训华章培训共享池故障的危害•影响系统性能影响系统性能•消耗过多消耗过多CPUCPU资源资源•导致系统宕机导致系统宕机 华章培训华章培训共享池主要性能问题•共享池不足共享池不足•共享池碎片共享池碎片•SQLSQL分析分析/ /执行过于频繁执行过于频繁•业务高峰期大量编译存储过程和视图业务高峰期大量编译存储过程和视图•过高的过高的LOGON/LOGOFFLOGON/LOGOFF频率频率 华章培训华章培训共享池诊断路径•检查检查ALERTLOGALERTLOG是否有异常是否有异常•检查检查V$SESSION_WAITV$SESSION_WAIT是否有共享池相关闩锁等待是否有共享池相关闩锁等待•HANGANALYZEHANGANALYZE分析是否分析是否HANGHANG住现象住现象•检查检查CURSORCURSOR相关情况相关情况•检查保留池情况检查保留池情况•STATSPACK/AWRSTATSPACK/AWR报告检查整体情况报告检查整体情况 华章培训华章培训AWR报告中的共享池问题Instance Efficiency Percentages (Target 100%)Instance Efficiency Percentages (Target 100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.97 Redo NoWait %: 100.00 Buffer Nowait %: 99.97 Redo NoWait %: 100.00 Buffer Hit %: 99.30 In-memory Sort %: 100.00 Buffer Hit %: 99.30 In-memory Sort %: 100.00 Library Hit %: 86.73 Soft Parse %: 87.07 Library Hit %: 86.73 Soft Parse %: 87.07 Execute to Parse %: 37.18 Latch Hit %: 99.96 Execute to Parse %: 37.18 Latch Hit %: 99.96Parse CPU to Parse Elapsd %: 55.39 % Non-Parse CPU: 84.52Parse CPU to Parse Elapsd %: 55.39 % Non-Parse CPU: 84.52 华章培训华章培训共享池优化要点•8I8I共享池和共享池和9I9I的结构不同，的结构不同，9i9i的共享池效率更高的共享池效率更高•共享池过小可能导致性能问题或者出现共享池过小可能导致性能问题或者出现ORA-4031ORA-4031•共享池碎片化可能导致严重的问题共享池碎片化可能导致严重的问题•9i9i开始共享池采用开始共享池采用SUBPOOLSUBPOOL机制机制§利：减少共享池争用，避免管理过大的共享池§弊：增加共享池碎片的机会§权衡：CPU开销和共享池碎片•10g10g采用共享内存自动管理机制采用共享内存自动管理机制§利：减少了犹豫ORA-4031导致的宕机，灵活分配共享内存§弊：存在BUG,导致共享内存抖动§权衡：有限的共享内存自动管理 华章培训华章培训经验分享：分析共享池常用脚本col "avg size" format a30 truncate;col siz format 999999999999SELECT 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 SIZECLASS NUM SIZ AVG SIZE-------- ---------- ------------- ---------------------------- ---------- ------------- --------------------R-free 340 32413584 93.10kR-free 340 32413584 93.10kR-freea 856 12336408 14.07kR-freea 856 12336408 14.07kR-perm 110 2939288 26.09kR-perm 110 2939288 26.09kfree 435378 312087744 .70kfree 435378 312087744 .70kfreeabl 13743 20751656 1.47kfreeabl 13743 20751656 1.47kperm 701479 806857496 1.12kperm 701479 806857496 1.12krecr 5655 3791616 .65krecr 5655 3791616 .65k 华章培训华章培训经验分享：分析共享池常用脚本（2）col sga_heap format a15col size format a10select 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) Bytesfrom x$ksmspwhere 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(*) STATUS BYTESSubPool 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 0-1K 9121 free 950320 1 sga heap(1,0) free memory 2-3K 1 free 2240 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 4-5K 49 free 196304 1 sga heap(1,0) free memory 8-9k 126 free 1054728 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 9-10k 46 free 412328 1 sga heap(1,0) free memory > 10K 796 free 23900336 1 sga heap(1,0) free memory > 10K 796 free 23900336 1 sga heap(1,0) free memory > 10K 73 R-free 247323016 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 0-1K 11462 free 1270864 2 sga heap(2,0) free memory 3-4K 1 free 2792 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 4-5K 1 free 3848 2 sga heap(2,0) free memory 6-7k 1 free 6496 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 8-9k 128 free 1071536 2 sga heap(2,0) free memory 9-10k 38 free 336664 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 861 free 46257968 2 sga heap(2,0) free memory > 10K 67 R-free 247528016 2 sga heap(2,0) free memory > 10K 67 R-free 247528016 华章培训华章培训共享池相关的几个指标•共享池命中率：共享池命中率的目标是共享池命中率：共享池命中率的目标是100%100%•每秒分析每秒分析/ /硬分析数量硬分析数量•硬分析比例硬分析比例•共享池空闲比例共享池空闲比例•noneparseCPU%noneparseCPU%•执行超过一次的执行超过一次的SQLSQL的百分比的百分比 华章培训华章培训共享池相关的闩锁•共享池相关共享池相关LATCHLATCH一般和共享池不足或者分析过一般和共享池不足或者分析过于频繁有关于频繁有关•如果共享池命中率不高或者共享池使用率接近于如果共享池命中率不高或者共享池使用率接近于100%100%使用，需要加大共享池使用，需要加大共享池•共享池碎片问题也会加大闩锁竞争共享池碎片问题也会加大闩锁竞争§sharedpool§librarycache§librarycachepin§rowcacheobjects§rowcacheenqueuelatch 华章培训华章培训共享池优化相关的参数•shared_pool_sizeshared_pool_size•open_cursorsopen_cursors•session_cached_cursorssession_cached_cursors•cursor_space_for_timecursor_space_for_time•cursor_sharingcursor_sharing•_kghdsidx_count_kghdsidx_count 华章培训华章培训案例某联通的优化步骤：某联通的优化步骤：•加大SESSOIN_CACHED_CURSORS•设置CURSOR_SPACE_FOR_TIME=TRUE后果：后果：•第二天由于ORA-4031导致宕机 华章培训华章培训优化SQL分析•使用绑定变量使用绑定变量•调整调整CURSOR_SHARINGCURSOR_SHARING•加大共享池加大共享池•生产时间减少数据字典修改生产时间减少数据字典修改•使用使用SESSION_CACHED_CURSORSSESSION_CACHED_CURSORS参数减少软分析参数减少软分析•将将TRIGGER/PACKAGEKEEPTRIGGER/PACKAGEKEEP到共享池到共享池•KEEPKEEP常用常用SQLSQL到共享池到共享池 华章培训华章培训10G中的共享内存抖动alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';col component format a40 truncate;col component format a40 truncate;select component,oper_type, oper_mode, start_time, end_time, select component,oper_type, oper_mode, start_time, end_time, trunc(target_size/1024/1024) target trunc(target_size/1024/1024) target from v$sga_resize_ops;from v$sga_resize_ops; 华章培训华章培训共享池抖动的例子COMPONENT OPER_T OPER_MODE START_TIME END_TIME 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:07DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 14:54:52 2010-08-04 14:55:07shared pool GROW DEFERRED 2010-08-04 14:54:52 2010-08-04 14:55:07shared pool GROW DEFERRED 2010-08-04 14:54:52 2010-08-04 14:55:07DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 14:58:52 2010-08-04 14:59:00DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 14:58:52 2010-08-04 14:59:00shared pool GROW DEFERRED 2010-08-04 14:58:52 2010-08-04 14:59:00shared pool GROW DEFERRED 2010-08-04 14:58:52 2010-08-04 14:59:00DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:05:23 2010-08-04 15:05:32DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:05:23 2010-08-04 15:05:32shared pool GROW DEFERRED 2010-08-04 15:05:23 2010-08-04 15:05:32shared pool GROW DEFERRED 2010-08-04 15:05:23 2010-08-04 15:05:32DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:08:53 2010-08-04 15:08:57DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:08:53 2010-08-04 15:08:57shared pool GROW DEFERRED 2010-08-04 15:08:53 2010-08-04 15:08:57shared pool GROW DEFERRED 2010-08-04 15:08:53 2010-08-04 15:08:57DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:12:23 2010-08-04 15:12:29DEFAULT buffer cache SHRINK DEFERRED 2010-08-04 15:12:23 2010-08-04 15:12:29shared pool GROW DEFERRED 2010-08-04 15:12:23 2010-08-04 15:12:29shared pool GROW DEFERRED 2010-08-04 15:12:23 2010-08-04 15:12:29 华章培训华章培训共享池碎片和ORA-4031•共享池碎片的原因共享池碎片的原因§共享池的分配和释放十分频繁§共享池申请中几十到几百字节的申请量很大§动态扩展的PERMENT对象§BUG§CACHEDCURSOR§...•共享池有自恢复能力共享池有自恢复能力•ORA-4031ORA-4031带来的问题带来的问题§性能下降§宕机 华章培训华章培训常用脚本-可用于查看ORA-4031selectselectindx,indx,kghlurcr,kghlurcr,kghlutrn,kghlutrn,kghlufsh,kghlufsh,kghluops,kghluops,kghlunfu,kghlunfu,kghlunfskghlunfsfromfromsys.x$kghlusys.x$kghluwherewhereinst_id=userenv('Instance')inst_id=userenv('Instance') 华章培训华章培训例子 INDX KGHLURCR KGHLUTRN KGHLUFSH KGHLUOPS KGHLUNFU KGHLUNFS INDX KGHLURCR KGHLUTRN KGHLUFSH KGHLUOPS KGHLUNFU KGHLUNFS---------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- ---------- 0 6669 15078 43945 452834 29 4080 0 6669 15078 43945 452834 29 4080 1 6875 15383 46477 41993665 78 4064 1 6875 15383 46477 41993665 78 4064 2 4689 12257 34960 449154 2 4080 2 4689 12257 34960 449154 2 4080 3 12992 19613 41194 65572954 1 4080 3 12992 19613 41194 65572954 1 4080 4 2348 3666 47447 641659 15 4080 4 2348 3666 47447 641659 15 4080 5 8308 14242 45672 630403 34 4112 5 8308 14242 45672 630403 34 4112 华章培训华章培训缓解共享池碎片问题•配置合理的参数，尽可能少的动态扩展配置合理的参数，尽可能少的动态扩展PERMENTPERMENT的数组的数组•减少减少SUBPOOLSUBPOOL的数量的数量•将常用大对象将常用大对象PINPIN到内存到内存•慎用慎用CURSOR_SPACE_FOR_TIMECURSOR_SPACE_FOR_TIME•尽可能使用绑定变量尽可能使用绑定变量•定期清理长期连接数据库的非用户会话定期清理长期连接数据库的非用户会话•定期重连数据库连接池定期重连数据库连接池•定期刷新共享池定期刷新共享池•定期重启实例定期重启实例 华章培训华章培训SUBPOOL和共享池碎片•_kghdsidx_count_kghdsidx_count参数可以手工配置参数可以手工配置SUBPOOLSUBPOOL数量数量•SUBPOOLSUBPOOL和和CPU_COUNTCPU_COUNT的关系的关系§每4个CPU一个§最多7个•SUBPOOLSUBPOOL的最小大小的最小大小§9i：128M§10g：256M§11g：512M•SUBPOOLSUBPOOL的两面性的两面性§提高并发访问性能§增加碎片的机会 华章培训华章培训案例-分析4031 TRACE Allocation Name Size Allocation Name Size _________________________ ___________________________________ __________"free memory " 29327792 "free memory " 29327792 "miscellaneous " 12908400 "miscellaneous " 12908400 "transaction " 800448 "transaction " 800448 "UNDO INFO SEGMENTED ARRAY" 325056 "UNDO INFO SEGMENTED ARRAY" 325056 "errors " 23080 "errors " 23080 "temporary tabl " 3136 "temporary tabl " 3136 "SEQ S.O. " 264800 "SEQ S.O. " 264800 "partitioning d " 92400 "partitioning d " 92400 "db_handles " 1740000 "db_handles " 1740000 "replication session stats" 503120 "replication session stats" 503120 "ges regular msg buffers " 1576248 "ges regular msg buffers " 1576248 "table definiti " 776 "table definiti " 776 "PL/SQL MPCODE " 419304 "PL/SQL MPCODE " 419304 "gcs resource hash table " 2097152 "gcs resource hash table " 2097152 "PL/SQL DIANA " 608352 "PL/SQL DIANA " 608352 "trigger inform " 64 "trigger inform " 64 "ges enqueues " 6563240 "ges enqueues " 6563240 "PL/SQL PPCODE " 0 "PL/SQL PPCODE " 0 "ges resource hash table " 4325376 "ges resource hash table " 4325376 "trigger defini " 0 "trigger defini " 0 "gcs resources " 45304256 "gcs resources " 45304256 "sim memory hea " 2558400 "sim memory hea " 2558400 "dictionary cache " 1065728 "dictionary cache " 1065728 "db_block_hash_buckets " 19589168 "db_block_hash_buckets " 19589168 "ges resources " 441539912 "ges resources " 441539912 "KQR M PO " 45568 "KQR M PO " 45568 "Checkpoint queue " 5245440 "Checkpoint queue " 5245440 "library cache " 6492608 "library cache " 6492608 "type object de " 0 "type object de " 0 "sql area " 1236744 "sql area " 1236744 "sessions " 1119456 "sessions " 1119456 "gcs shadows " 28805632 "gcs shadows " 28805632 "event statistics per sess" 4696416 "event statistics per sess" 4696416 "trigger source " 0 "trigger source " 0 "VIRTUAL CIRCUITS " 926800 "VIRTUAL CIRCUITS " 926800 "KGLS heap " 517760 "KGLS heap " 517760 "parameters " 34048 "parameters " 34048 "fixed allocation callback" 312 "fixed allocation callback" 312 华章培训华章培训下节预告：DB CACHE分析•DB CACHEDB CACHE的基本原理的基本原理•DB CACHEDB CACHE优化的要点优化的要点•DB CACHEDB CACHE性能问题诊断的方法性能问题诊断的方法•如何解决热块冲突如何解决热块冲突 感谢您对华章培训网的支持!http://www. 。
