11性能优化共享池

1、14优化共享池优化共享池 14-2 课程目标课程目标 学完本课后，应能完成下列工作： 诊断和解决硬分析问题 诊断和解决软分析问题 调整共享池的大小 诊断和解决共享池碎片问题 将对象保留在共享池中 调整保留区域的大小 管理结果高速缓存 14-3 共享池体系结构共享池体系结构 共享池的主要组件包括： 库高速缓存数据字典高速缓存 结果高速缓存 共享池共享池 库高速缓存库高速缓存字典高速缓存字典高速缓存 共享区域共享区域 UGA 结果高结果高速缓存速缓存 数据库缓数据库缓冲区高速冲区高速缓存缓存 重做日志重做日志 缓冲区缓冲区 大型池大型池 14-4 共享池操作共享池操作 通过 LRU 算法来管理共享

2、池。 新对象需要内存分配。 可重新创建的对象将在高速缓存中到期释放。 对象由内存块构成。 内存分配是一个整块。 块是连续的。 LRU 操作由闩锁或互斥进行保护。 14-5 库高速缓存库高速缓存 存储与游标关联的复杂对象元数据 存储供用户共享的 SQL 语句和 PL/SQL 块 允许游标共享 库高速缓存活动库高速缓存活动 14-6 14-7 闩锁和互斥闩锁和互斥 闩锁是一个内存对象，允许： 在不产生损坏的情况下共享资源 对更新和分配的独占访问 对读取的共享访问 互斥对象允许： 在不产生损坏的情况下共享资源 对读取的共享访问 对更新的独占访问 每个对象都有自己的互斥 14-8 14-9 闩锁和互斥

3、：视图和统计信息闩锁和互斥：视图和统计信息 闩锁视图： V$LATCH V$LATCH_MISSES V$LATCH_PARENT V$LATCH_CHILDREN V$LATCHHOLDER V$LATCHNAME 闩锁等待事件： latch: latchname 互斥视图： V$MUTEX_SLEEP V$MUTEX_SLEEP_HISTORY 互斥等待事件： cursor:mutex X cursor:mutex S cursor:pin X cursor:pin S cursor:pin S wait on X 14-10 14-11 V$SGASTAT V$SGAINFO V$LIB

4、RARYCACHE V$LIBRARY_CACHE_MEMORY V$SHARED_POOL_RESERVED V$SQLSTATS V$SQL V$SQLAREA V$SQLTEXT V$DB_OBJECT_CACHE 用于优化共享池的诊断工具用于优化共享池的诊断工具 影响组件的参数：影响组件的参数： SHARED_POOL_SIZE, OPEN_CURSORS SESSION_CACHED_CURSORS, CURSOR_SPACE_FOR_TIME CURSOR_SHARING, SHARED_POOL_RESERVED_SIZE Statpack report AWR report 数

5、据字典数据字典高速缓存高速缓存共享池共享池 库高速缓存库高速缓存 共享共享 SQL 和和 PL/SQL 视图视图 14-12 14-13 AWR/Statspack 指标指标 AWR 和 Statspack 报表包含以下指标： 负载概要文件 实例效率 顶级等待事件时间模型 14-14 负载概要文件负载概要文件 14-15 实例效率实例效率 14-16 顶级等待顶级等待 14-17 时间模型时间模型14-18 14-19 库高速缓存活动库高速缓存活动 14-20 避免硬分析避免硬分析 在 OLTP 系统中，可以通过将硬分析保持为最低来减少未命中数： 确保用户可以共享语句。 通过分配足够的空间，防

6、止频繁使用的语句到期释放。 避免会导致重新分析的无效情况。 14-21 游标是否处于共享状态游标是否处于共享状态 检查 V$LIBRARYCACHE 中的 GETHITRATIO： 确定可以共享的语句： SQL SELECT plan_hash_value, count(*) 2 FROM V$SQL 3 WHERE parsing_schema_name not in 4 (SYS,SYSMAN,DBSNMP) 5 GROUP BY plan_hash_value ORDER BY 2;SQL SELECT sql_text, executions 2 FROM V$SQLAREA 3 WH

7、ERE plan_hash_value = NNNNNNNNNSQL SELECT gethitratio 2 FROM V$LIBRARYCACHE 3 WHERE namespace = SQL AREA;14-22 14-23 共享游标共享游标 CURSOR_SHARING 的值包括： EXACT（默认值） SQL 语句只有完全相同才能共享游标。 SIMILAR 只要其各自的执行计划相同，类似的 SQL 语句便可以共享游标。 FORCE 不管对执行计划的影响如何，类似的 SQL 语句都共享游标。绑定取数在第一个分析上进行，并可能由于自适应游标共享在以后进行。 除一些文字值之外，这些类似的

8、语句完全相同。 14-24 14-25 自适应游标共享：示例自适应游标共享：示例 初始选择度立方初始选择度立方 绑定敏感的游标绑定敏感的游标 .0.15 0.0025 S(:1)=0.15, S(:2)=0.0025 .0.18 0.003 S(:1)=0.18, S(:2)=0.003 HJ GB HJ 初始计划初始计划 HJ GB HJ 无需新计划无需新计划 .0.3 0.009 S(:1)=0.3, S(:2)=0.009 HJ GB HJ HJ GB HJ .0.28 0.004 S(:1)=0.28, S(:2)=0.004 HJ GB HJ HJ GB HJ 相同选择度立方相同选择

9、度立方 第二个选择度立方第二个选择度立方 需要新计划需要新计划 合并的选择度立方合并的选择度立方 合并的立方合并的立方 无需新计划无需新计划 识别绑定的游标识别绑定的游标 软软分分析析硬硬分分析析硬硬分分析析345系统对语句系统对语句观察片刻。观察片刻。12SELECT * FROM emp WHERE sal = :1 and dept = :214-26 14-27 自适应游标共享视图自适应游标共享视图 以下视图提供了有关自适应游标共享使用情况的信息： V$SQL 两个列显示游标是绑定敏感的还是识别绑定的。 V$SQL_CS_HISTOGRAM 显示执行计数在执行历史记录直方图上的分布。

10、V$SQL_CS_SELECTIVITY 显示为每个谓词存储的选择度立方，这些谓词包含绑定变量且在游标共享检查中使用其选择度。 V$SQL_CS_STATISTICS 显示使用不同绑定集的游标的执行统计信息。 14-28 与自适应游标共享交互与自适应游标共享交互 CURSOR_SHARING： 如果未将 CURSOR_SHARING 设置为 EXACT，则可能使用绑定变量来重新编写包含文字的语句。 如果重新编写语句，则自适应游标共享可能应用于这些语句。 SQL 计划管理 (SPM)： 如果将 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 设置为 TRUE，则仅使用首

11、个生成的计划。 解决方法： 将 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 参数设置为 FALSE，然后运行应用程序，直至所有计划都加载到游标高速缓存为止。 将游标高速缓存手动加载到相应的计划基线中。 14-29 避免软分析避免软分析 减少软分析可以降低库高速缓存闩锁的争用。 通过以下措施可将软分析保持为最低： 设置 SESSION_CACHED_CURSORS 在应用程序预编译器中设置 HOLD_CURSOR 设置 CURSOR_SPACE_FOR_TIME 14-30 调整共享池大小调整共享池大小 使用自动共享内存管理。 使用共享池指导，在数据具有可操作的历史

12、记录时，确认使用其它诊断。 如果没有任何历史记录，则使用 40% 的 SGA 大小来开始。根据需要进行监视和调整。 有可用空闲内存时，请不要增加此大小。 SQL SELECT * FROM V$SGASTAT 2 WHERE NAME = free memory 3 AND POOL = shared pool; 14-31 共享池指导共享池指导 SQL SELECT shared_pool_size_for_estimate AS 2 pool_size, estd_lc_size, 3 estd_lc_time_saved 4 FROM V$SHARED_POOL_ADVICE; POOL

13、_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED- - - 104 2 54531 116 14 71636 128 26 144857 140 38 188636 152 51 213387 14-32 14-33 共享池指导共享池指导 14-34 避免碎片避免碎片 可通过以下措施避免碎片： 升级到 10.2.0.x 或更高版本 保留频繁需要使用的大对象 保留空间以供大对象使用 消除大型匿名 PL/SQL 块 允许使用大型池 14-35 较大的内存要求较大的内存要求 满足对较大的连续内存的请求。 在共享池中保留连续内存。SHARED_POOL_SIZE SHARED

14、_POOL_RESERVED_SIZE 数据字典数据字典高速缓存高速缓存共享池共享池 库高速缓存库高速缓存 共享共享 SQL 和和 PL/SQL V$SHARED_POOL_RESERVED 保留区域保留区域 14-36 14-37 优化共享池保留空间优化共享池保留空间 如果如果 REQUEST_FAILURES REQUEST_FAILURES 操作操作 0 并正在增加 AND Request_misses 0 降低 SHARED_POOL_RESERVED_SIZE 0 并正在增加 AND SHARED_POOL_RESERVED_SIZE 的 Free_memory = 50% 增加 S

15、HARED_POOL_SIZE =0 OR SHARED_POOL_RESERVED_SIZE 的 Free_memory = 50% 降低 SHARED_POOL_RESERVED_SIZE 14-38 14-39 保留大对象保留大对象 查找那些未保留在库高速缓存中的 PL/SQL 对象： 将大型程序包固定在库高速缓存中： SQL EXECUTE DBMS_SHARED_POOL.KEEP(package_name); SQL SELECT * FROM V$DB_OBJECT_CACHE 2 WHERE sharable_mem 10000 3 AND (type=PACKAGE OR t

16、ype=PACKAGE BODY OR 4 type=FUNCTION OR type=PROCEDURE) 5 AND kept=NO;14-40 14-41 数据字典高速缓存数据字典高速缓存 数据字典高速缓存保存数据字典行的行图像。 数据字典高速缓存数据字典高速缓存 数据字典数据字典 14-42 AWR/Statspack 报表输出：报表输出： 如果有太多的高速缓存未命中，则应增加如果有太多的高速缓存未命中，则应增加 SHARED_POOL_SIZE 参数。参数。 字典高速缓存未命中字典高速缓存未命中 14-43 SQL 查询结果高速缓存：概览查询结果高速缓存：概览 对查询结果或查询块进行

17、缓存供未来重用。 除非高速缓存过时，否则高速缓存将跨语句和会话使用。 优点： 可伸缩性 减少内存使用量 合适的候选语句： 访问行数多 返回行数少 SELECT SQL 查询结果查询结果高速缓存高速缓存SELECT 会话会话 1 会话会话 2 12314-44 管理管理 SQL 查询结果高速缓存查询结果高速缓存 使用以下初始化参数： RESULT_CACHE_MODE 值： MANUAL：使用 result_cache 提示来指定将存储在高速缓存中的结果。 FORCE：所有结果都存储在高速缓存中。 RESULT_CACHE_MAX_SIZE 设置分配给结果高速缓存的内存 如果值为 0，则禁用结果

18、高速缓存。 限于共享池的 75% 从其它内存设置派生默认设置 memory_target 的 0.25%，或 sga_target 的 0.5%，或 shared_pool_size 的 1% 14-45 管理管理 SQL 查询结果高速缓存查询结果高速缓存 使用以下初始化参数： RESULT_CACHE_MAX_RESULT 设置单个结果的最大高速缓存内存 默认值是 RESULT_CACHE_MAX_SIZE 的 5% RESULT_CACHE_REMOTE_EXPIRATION 设置取决于远程数据库对象的高速缓存结果的过期时间 默认值为 0 14-46 使用使用 RESULT_CACHE 提

19、示提示 EXPLAIN PLAN FORSELECT /*+ RESULT_CACHE */ department_id, AVG(salary) FROM employeesGROUP BY department_id;SELECT /*+ NO_RESULT_CACHE */ department_id, AVG(salary) FROM employeesGROUP BY department_id;-| Id | Operation | Name |Rows -| 0 | SELECT STATEMENT | | 11 | 1 | RESULT CACHE | 8fpza04gtwsf

20、r6n595au15yj4y | | 2 | HASH GROUP BY | | 11 | 3 | TABLE ACCESS FULL| EMPLOYEES | 107 -14-47 使用使用 DBMS_RESULT_CACHE 程序包程序包 使用 DBMS_RESULT_CACHE 程序包可： 管理查询结果高速缓存的内存分配 查看高速缓存的状态： 检索有关高速缓存内存使用量的统计信息： 删除全部现有结果并清除高速缓存内存： 使取决于特定对象的高速缓存结果变为无效： EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT;EXECUTE DBMS_RESULT_CACH

21、E.FLUSH;SELECT DBMS_RESULT_CACHE.STATUS FROM DUAL;EXEC DBMS_RESULT_CACHE.INVALIDATE(JFV,MYTAB);14-48 14-49 查看查看 SQL 结果高速缓存字典信息结果高速缓存字典信息 以下视图提供了有关查询结果高速缓存的信息： (G)V$RESULT_CACHE_STATISTICS 列出各个高速缓存设置和内存使用情况统计信息 (G)V$RESULT_CACHE_MEMORY 列出所有内存块和对应的统计信息 (G)V$RESULT_CACHE_OBJECTS 将所有对象（高速缓存结果和依赖性）及其属性一并

22、列出 (G)V$RESULT_CACHE_DEPENDENCY 列出高速缓存结果与依赖对象之间的依赖性详细资料 14-50 SQL 查询结果高速缓存：注意事项查询结果高速缓存：注意事项 对包含以下内容的查询禁用结果高速缓存： 临时表或字典表 非确定性的 PL/SQL 函数 序列 CURRVAL 和 NEXTVAL SQL 函数 current_date、sysdate、sys_guid 等 远程数据库上的 DML/DDL 不会使高速缓存的结果过期。 可以缓存闪回查询。 结果高速缓存不会自动释放内存。 它将一直增加到最大大小为止。 DBMS_RESULT_CACHE.FLUSH 清除内存。 14-51 UGA 和和 Oracle 共享服务器共享服务器 共享池共享池 PGA 共享池共享池或或大型池大型池 堆栈堆栈 空间空间 PGA 堆栈堆栈 空间空间 用户会用户会话数据话数据 UGA