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

PPT 内容

2. Oracle的共享池第2页library cache包含 SQL语句,分析的代码,执行计划等 data dictionary cache包含 table, column等对象的定义以及权限的信息共享池的大小由 SHARED_POOL_SIZE 确定.Shared poolLibrarycacheDatadictionarycacheSGA
3. 共享池分解第3页564000005680000056C0000057000000574000005780000057C0000058000000Shared Pool 包含下面组成部分 Permanent Area Segmented Arrays Library Cache Row Cache Reserved Area
4. Permanent Area第4页数据库启动时固定分配包含以下组件：进程会话 Segmented Arrays（锁、事务、资源等）
5. 保留池保留给大对象使用的区域只有当共享池无法分配时才分配 V$SHARED_POOL_RESERVED SHARED_POOL_RESERVED_SIZE 定义保留池的大小缺省是SHARED_POOL_SIZE的5% _SHARED_POOL_RESERVED_PCT 缺省 5% _SHARED_POOL_RESERVED_MIN_ALLOC 缺省 4400 超过这个大小才被认为是大对象
6. 自动共享内存管理到底如何分配?Java poolDatabase buffer cacheRedo log bufferShared poolLarge pool Fixed SGAStreams poolSGA
7. 自动共享内存管理的原理定期捕获系统状态将内存分配给需要的对象启动时恢复上次关闭时的分配情况SPFILE
8. 自动共享内存管理与共享池抖动自动内存管理适用于SGA资源充足的情况如果系统负载较高，并且SGA资源不足的情况，使用ASMM可能出现SGA抖动除了设置SGA_TARGET外，设置某些缓冲池的最小值，可以缓解SGA抖动 SGA抖动严重的系统，建议关闭自动共享内存管理注意监控V$SGA_RESIZE_OPS视图
9. 共享池故障的危害影响系统性能消耗过多CPU资源导致系统宕机
10. 共享池主要性能问题共享池不足共享池碎片 SQL分析/执行过于频繁业务高峰期大量编译存储过程和视图过高的LOGON/LOGOFF频率
11. 共享池诊断路径检查ALERT LOG是否有异常检查V$SESSION_WAIT是否有共享池相关闩锁等待 HANGANALYZE分析是否HANG住现象检查CURSOR相关情况检查保留池情况 STATSPACK/AWR报告检查整体情况
12. 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
13. 共享池优化要点8I共享池和9I的结构不同，9i的共享池效率更高共享池过小可能导致性能问题或者出现ORA-4031 共享池碎片化可能导致严重的问题 9i开始共享池采用SUBPOOL机制利：减少共享池争用，避免管理过大的共享池弊：增加共享池碎片的机会权衡：CPU开销和共享池碎片 10g采用共享内存自动管理机制利：减少了犹豫ORA-4031导致的宕机，灵活分配共享内存弊：存在BUG,导致共享内存抖动权衡：有限的共享内存自动管理
14. 经验分享：分析共享池常用脚本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;
15. 例子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
16. 经验分享：分析共享池常用脚本（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');
17. 例子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
18. 共享池相关的几个指标共享池命中率：共享池命中率的目标是100% 每秒分析/硬分析数量硬分析比例共享池空闲比例 none parse CPU% 执行超过一次的SQL的百分比
19. 共享池相关的闩锁共享池相关LATCH一般和共享池不足或者分析过于频繁有关如果共享池命中率不高或者共享池使用率接近于100%使用，需要加大共享池共享池碎片问题也会加大闩锁竞争 shared pool library cache library cache pin row cache objects row cache enqueue latch
20. 共享池优化相关的参数shared_pool_size open_cursors session_cached_cursors cursor_space_for_time cursor_sharing _kghdsidx_count
21. 案例某联通的优化步骤：加大SESSOIN_CACHED_CURSORS 设置CURSOR_SPACE_FOR_TIME=TRUE 后果：第二天由于ORA-4031导致宕机
22. 优化SQL分析使用绑定变量调整CURSOR_SHARING 加大共享池生产时间减少数据字典修改使用SESSION_CACHED_CURSORS参数减少软分析将TRIGGER/PACKAGE KEEP到共享池 KEEP常用SQL到共享池
23. 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;
24. 共享池抖动的例子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
25. 共享池碎片和ORA-4031共享池碎片的原因共享池的分配和释放十分频繁共享池申请中几十到几百字节的申请量很大动态扩展的PERMENT对象 BUG CACHED CURSOR ... 共享池有自恢复能力 ORA-4031带来的问题性能下降宕机
26. 常用脚本-可用于查看ORA-4031select indx, kghlurcr, kghlutrn, kghlufsh, kghluops, kghlunfu, kghlunfs from sys.x$kghlu where inst_id = userenv('Instance')
27. 例子 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
28. 缓解共享池碎片问题配置合理的参数，尽可能少的动态扩展PERMENT的数组减少SUBPOOL的数量将常用大对象PIN到内存慎用CURSOR_SPACE_FOR_TIME 尽可能使用绑定变量定期清理长期连接数据库的非用户会话定期重连数据库连接池定期刷新共享池定期重启实例
29. SUBPOOL和共享池碎片_kghdsidx_count参数可以手工配置SUBPOOL数量 SUBPOOL和CPU_COUNT的关系每4个CPU一个最多7个 SUBPOOL的最小大小 9i：128M 10g：256M 11g：512M SUBPOOL的两面性提高并发访问性能增加碎片的机会
30. 案例-分析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
31. 下节预告：DB CACHE分析DB CACHE的基本原理 DB CACHE优化的要点 DB CACHE性能问题诊断的方法如何解决热块冲突
32. 感谢您对华章培训网的支持!http://www. hztraining.com

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

houye122

贡献于2012-07-05

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

PPT 内容

PPT 图集

相关PPT