Microsoft Word - twp-sql-plan-management-11gr doc

Size: px

Start display at page:

Download "Microsoft Word - twp-sql-plan-management-11gr doc"

秉微系
5 years ago
Views:

1 Oracle 白皮书 2010 年 11 月 Oracle Database 11g 的 SQL 计划管理

2 引言... 1 SQL 计划管理... 2 SQL 计划基准捕获... 3 SQL 计划基准选择使用和管理 SQL 管理库初始化参数管理 SQL 管理库的空间使用监视 SQL 计划管理企业管理器通过 DBA 视图监视 SPM 与自动 SQL 调优集成将 SQL 计划管理用于升级使用 SQL 调优集使用存储大纲从游标缓存批量加载总结... 23

3 引言任何数据库应用程序的性能在很大程度上都依赖于查询执行尽管 Oracle 优化器无需用户干预就可以评估最佳计划, 但是 SQL 语句的执行计划仍可能由于以下多种原因发生意外更改 : 重新收集优化器统计信息改变优化器参数或模式 / 元数据定义由于无法保证计划始终向更好的情况改变, 因此导致某些客户冻结了自己的执行计划 ( 存储大纲 ) 或锁定了优化器统计信息但是, 这样做的结果导致许多环境永远无法利用那些可以提高 SQL 语句性能的新优化器功能或访问路径如果在环境变化时能够维持当前执行计划不变, 或者只向更好的情况改变, 将是最理想的解决方案 Oracle Database 11g 是市场上第一个能解决这一挑战的数据库 SQL 计划管理 (SPM) 为完全透明的受控执行计划演进提供了框架有了 SPM, 优化器可以自动管理执行计划并确保仅使用已知的和经过验证的计划发现 SQL 语句的一个新计划时, 暂时不会使用它, 只有经过数据库验证说明它比当前计划具有相当或更佳性能时, 才会使用 1

SQL 计划管理保证计划稳定性并控制计划演进 SQL 计划管理 (SPM) 确保运行时性能永远不会因为执行计划的更改而降低为了确保这点, 只使用已接受 ( 受信任 ) 的执行计划 ; 跟踪任何计划发展并随后对其进行评估, 如果新计划未造成运行时性能改变或运行时性能得到提高, 则接受新计划为经验证的计划 SQL 计划管理由三部分组成 : 1.

4 SQL 计划管理保证计划稳定性并控制计划演进 SQL 计划管理 (SPM) 确保运行时性能永远不会因为执行计划的更改而降低为了确保这点, 只使用已接受 ( 受信任 ) 的执行计划 ; 跟踪任何计划发展并随后对其进行评估, 如果新计划未造成运行时性能改变或运行时性能得到提高, 则接受新计划为经验证的计划 SQL 计划管理由三部分组成 : 1. SQL 计划基准捕获 : 创建 SQL 计划基准, 这些基准表示所有相关 SQL 语句的已认可 ( 受信任 ) 的执行计划 SQL 计划基准存储在 SYSAUX 表空间的 SQL 管理库的计划历史记录中 2. SQL 计划基准选择 : 确保只对带有 SQL 计划基准的语句使用已接受的执行计划, 并跟踪语句的计划历史记录中的所有新执行计划计划历史记录既包含已接受的计划也包含未接受的计划未接受的计划可能是未经验证 ( 新发现但未验证 ) 的计划, 也可能是被拒绝 ( 已验证但未发现有性能提高 ) 的计划 3. SQL 计划基准发展 : 对计划历史记录中特定语句的未经验证的执行计划进行评估, 使其成为已接受或被拒绝的计划 SYSAUX 表空间 SQL 管理库语句日志可重复的 SQL 语句计划历史记录计划基准计划历史记录计划基准计划历史记录计划基准等待验证的计划图 1 SQL 管理库, 由语句日志和可重复的 SQL 语句的计划历史记录组成 2

SQL 计划基准捕获动态捕获计划, 或者从游标缓存 SQL 调优集批量加载带有计划的 SPM, 或者从另一个系统导入计划要使用 SPM, 必须首先植入带有当前基于性能开销的执行计划的 SQL 管理库, 该 SQL 管理库将成为每条语句的 SQL 计划基准填充 SQL 管理库有两种方法 : 自动捕获执行计划批量加载执行计划自动计划捕获动态通过将 init.

5 SQL 计划基准捕获动态捕获计划, 或者从游标缓存 SQL 调优集批量加载带有计划的 SPM, 或者从另一个系统导入计划要使用 SPM, 必须首先植入带有当前基于性能开销的执行计划的 SQL 管理库, 该 SQL 管理库将成为每条语句的 SQL 计划基准填充 SQL 管理库有两种方法 : 自动捕获执行计划批量加载执行计划自动计划捕获动态通过将 init.ora 的参数 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 设置为 TRUE ( 默认为 FALSE) 可以打开自动计划捕获启用自动计划捕获后, 将针对任何可重复的 SQL 语句自动填充 SPM 信息库为了识别可重复的 SQL 语句, 优化器将在每条 SQL 语句首次编译时将其标识 (SQL 签名 ) 记录到语句日志中如果再次处理 ( 执行或编译 ) 该 SQL 语句, 语句日志中保存的该语句的标识则表明该语句是一条重复语句将为该语句创建 SQL 计划历史记录, 其中包括优化器重新生成执行计划所使用的信息, 如 SQL 文本大纲绑定变量和编译环境首先把当前基于成本的计划添加为首个 SQL 计划基准, 将该计划标记为已接受只使用已接受的计划, 如果以后又发现了该 SQL 语句的新计划, 则将新执行计划添加到计划历史记录中, 并标记为等待验证只有新计划的性能比从当前 SQL 计划基准选择的计划的性能更优时, 才将新计划标记为已接受的计划发出 SQL 生成执行计划是否跟踪该 SQL 是是否存在 SQL 计划基准是执行来自 SQL 基准计划的计划否在 SQL 日志中添加一项否创建 SQL 计划基准执行该计划执行该计划图 2 自动计划捕获工作流程图 3

6 批量加载当数据库从以前的版本升级到 Oracle Database 11g 时或部署新应用程序时, 批量加载执行计划特别有用批量加载可以与自动计划捕获结合使用, 也可以代替自动计划捕获将自动接受批量加载的执行计划来创建新 SQL 计划基准, 或者将它们添加到已有的计划基准中可使用四种不同的技术对 SQL 管理库进行批量加载 : 1. 针对给定的 SQL 调优集 (STS) 加载执行计划 2. 从存储大纲加载执行计划 3. 使用游标缓存中目前的执行计划 4. 从临时表解压缩现有的 SQL 计划基准从 SQL 调优集 (STS) 批量加载可以将某个 ( 关键 )SQL 负载的计划捕获到 SQL 调优集 (STS) 中, 然后使用 PL/SQL 过程 DBMS_SPM.LOAD_PLANS_FROM_SQLSET 或者通过 Oracle 企业管理器 (EM) 将这些计划作为 SQL 计划基准加载到 SQL 管理库中下次执行这些语句时将使用 SQL 计划基准从 STS 批量加载执行计划是保证数据库升级时不改变计划的一种有效方法下面是执行这一方法的四个步骤 : 1. 在 Oracle Database 10gR2 中, 创建一个包括每个 SQL 语句执行计划的 STS 2. 将 STS 加载到临时表中, 然后将临时表导出到一个平面文件中 3. 将临时表从平面文件导入到 Oracle Database 11g 中, 然后卸载 STS 4. 使用 EM 或 DBMS_SPM.LOAD_PLANS_FROM_SQLSET 将执行计划加载到 SQL 管理库中 4

数据库升级计划历史记录计划基线 Oracle Database 11g 第 4 步无计划性能降低 Oracle Database 11g DBA 第 2 步和第 3 步良好调优的计划第 1 步 Oracle Database 10g 图 3 使用 STS 为数据库升级批量加载 SMB 一旦创建了 SQL 计划基准, 将使用这些基准, 从而保证了 10gR2 和 11gR1

7 数据库升级计划历史记录计划基线 Oracle Database 11g 第 4 步无计划性能降低 Oracle Database 11g DBA 第 2 步和第 3 步良好调优的计划第 1 步 Oracle Database 10g 图 3 使用 STS 为数据库升级批量加载 SMB 一旦创建了 SQL 计划基准, 将使用这些基准, 从而保证了 10gR2 和 11gR1 之间无计划更改如果 Oracle database 11g 中的优化器提出了不同的执行计划, 该计划将添加到计划历史记录中并标记为等待验证只有该计划的性能等同或优于当前的 SQL 计划基准 (10gR2 的计划 ) 时, 才将其标记为已接受的计划从存储大纲批量加载如果您无权访问 SQL 调优集, 或您是从早于 Oracle Database 10gR2 的版本进行升级, 您可以使用存储大纲捕获现有的执行计划可以使用 PL/SQL 过程 DBMS_SPM.MIGRATE_STORED_OUTLINE 或通过 Oracle 企业管理器 (EM) 将存储大纲作为 SQL 计划基准加载到 SQL 管理库中下次执行这些语句时, 将使用这些 SQL 计划基准有两种方法可以捕获存储大纲 : 可以手动地使用 CREATE OUTLINE 命令为每条 SQL 语句创建一个存储大纲, 也可以让 Oracle 自动为要执行的每条 SQL 语句创建一个存储大纲下面是 Oracle 自动创建存储大纲所需要执行的步骤 5

1. 首先启动一个新会话并打开自动捕获来捕获每条 SQL 语句的存储大纲这将立即开始解析每条语句, 直到您明确地将自动捕获关闭 2. 然后通过运行应用程序或手动地使用 SQL 语句来执行负载注意 : 如果手动地使用 SQL 语句, 请确保与应用程序使用完全相同的 SQL 文本如果应用程序使用绑定变量, 则您也必须使用绑定变量 3. 执行完关键 SQL 语句后, 应该关闭自动捕获 4.

8 1. 首先启动一个新会话并打开自动捕获来捕获每条 SQL 语句的存储大纲这将立即开始解析每条语句, 直到您明确地将自动捕获关闭 2. 然后通过运行应用程序或手动地使用 SQL 语句来执行负载注意 : 如果手动地使用 SQL 语句, 请确保与应用程序使用完全相同的 SQL 文本如果应用程序使用绑定变量, 则您也必须使用绑定变量 3. 执行完关键 SQL 语句后, 应该关闭自动捕获 4. 实际的存储大纲存储在 OUTLN 模式中您可以导出该模式, 然后将其导入到 11g 数据库, 也可以将现有数据库升级到 11g 5. 使用 EM 或 DBMS_SPM.MIGRATE_STORED_OUTLINE 将存储大纲加载到 SQL 管理库中 1. 打开自动捕获 CREATE_STORED_OUTLINES=true Oracle Database 11g Oracle Database 9i 计划历史记录 2. 运行应用程序中的所有 SQL, 并为每条语句自动创建存储大纲 OH 模式存储大纲 4. 升级到 11g 5. 将存储大纲迁移到 SPM 计划基线 3. 关闭自动捕获 CREATE_STORED_OUTLINES=false 图 4 升级后使用存储大纲批量加载 SMB 从游标缓存批量加载启动 Oracle Database 11g, 可以直接从游标缓存将语句的计划加载到 SQL 管理库中通过对模块名模式或 SQL_ID 应用筛选器, 可以确定希望捕获的一条 SQL 语句或一组 SQL 语句 6

9 可以使用 PL/SQL 过程 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 或通过 Oracle 企业管理器加载计划下次执行这些语句时将使用它们的 SQL 计划基准如果已经使用提示对应用程序 SQL 进行了手动调优, 那么直接从游标缓存加载计划是非常有用的因为不可能修改应用程序 SQL 使其包含提示, 因此通过将调优的执行计划捕获为 SQL 计划基准, 就可以确保应用程序 SQL 在以后仍将使用经过调优的计划通过下面的几个简单步骤, 就可以使用 SPM 捕获带提示的执行计划并将其与未带提示的 SQL 语句相关联首先要捕获未带提示的 SQL 语句的 SQL 计划基准 1. 在 SQL*Plus 会话中, 运行未带提示的 SQL 语句, 以便我们可以开始 SQL 计划基准捕获 SQL> SELECT prod_name, SUM(amount_sold) FROM Sales s, Products p WHERE s.prod_id=p.prod_id AND prod_category = :ctgy GROUP BY prod_name; 2. 然后在 V$SQL 视图中查找该语句的 SQL_ID SQL> SELECT sql_id, sql_fulltext FROM V$SQL WHERE sql_text LIKE '%SELECT prod_name, SUM(%'; SQL_ID SQL_FULLTEXT hnd835n81yv select SQL_ID, SQL_FULLTEXT from v$sql chj6q8z7ykbyy SELECT PROD_NAME, SUM(AMOUNT_SOLD) 3. 使用查到的 SQL_ID 为该语句创建一个 SQL 计划基准 SQL> variable cnt number; SQL> EXECUTE :cnt :=DBMS_SPM.LOAD_PLAN_FROM_CURSOR_CACHE( sql_id=>'chj6q8z7ykbyy'); 4. 捕获到的计划是次优计划, 需要禁用该计划 SQL_HANDLE 和 PLAN_NAME 是需要禁用的计划可在 DBA_SQL_PLAN_BASELINE 视图中找到这些计划 SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines; 7

10 SQL_HANDLE SQL_TEXT PLAN_NAME ENABLE SYS_SQL_bf5c9b08f72bde3e SELECTPROD_NAME,SUM SQL_PLAN_byr4v13vkrrjy Y 5. 使用 DBMS_SPM.ALTER_SQL_PLAN_BASELINE 禁用拙劣的计划 SQL> variable cnt number; SQL> exec :cnt :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE( SQL_HANDLE => 'SYS_SQL_bf5c9b08f72bde3e', PLAN_NAME => 'SQL_PLAN_byr4v13vkrrjy ', ATTRIBUTE_NAME => 'enabled', ATTRIBUTE_VALUE => 'NO'); SQL> SELECT sql_handle, sql_text, plan_name, enabled FROM dba_sql_plan_baselines; SQL_HANDLE SQL_TEXT PLAN_NAME ENABLE SYS_SQL_bf5c9b08f72bde3e SELECTPROD_NAME,SUM SQL_PLAN_byr4v13vkrrjy N 6. 现在, 您需要使用必要的提示修改 SQL 语句, 然后执行修改后的语句 SQL> SELECT /*+ INDEX(p) */ prod_name, SUM(amount_sold) FROM Sales s, Products p WHERE s.prod_id=p.prod_id AND prod_category = :ctgy GROUP BY prod_name; 7. 在 V$SQL 视图中查找带有提示的 SQL 语句的 SQL_ID 和 PLAN_HASH_VALUE SQL> SELECT sql_id, plan_hash_value, fulltext FROM V$SQL WHERE sql_text LIKE '%SELECT /*+ INDEX(p) */ prod_na%'; SQL_ID PLAN_HASH_VALUE SQL_FULLTEXT t5v8swp79svs select SQL_ID, SQL_FULLTEXT djkqjd0kvgmb SELECT /*+ INDEX(p) */ 8

11 8. 使用修改计划的 SQL_ID 和 PLAN_HASH_VALUE, 通过将修改后的计划关联到原来语句的 SQL_HANDLE 来为原来的 SQL 语句创建新的已接受的计划 exec :cnt:=dbms_spm.load_plans_from_cursor_cache( sql_id => 'djkqjd0kvgmb5', plan_hash_value => , sql_handle => 'SYS_SQL_bf5c9b08f72bde3e ); 从临时表解压缩基准计划部署新应用程序模块也就意味着将全新的 SQL 语句引入到数据库中使用 Oracle Database 11g, 任何第三方软件供应商都可以将它们的应用软件与为新引入的 SQL 语句对应的 SQL 计划基准一起销售这保证了作为 SQL 计划基准组成部分的所有 SQL 语句将首先运行已知的计划, 因此在标准测试配置下获得良好的性能再者, 如果是在内部开发和测试应用程序, 也可以通过以下步骤从测试系统导出正确的计划然后将其导入生产系统 : 1. 在原始系统上, 使用 DBMS_SPM.CREATE_STGTAB_BASELINE 过程创建临时表 2. 使用 DBMS_SPM.PACK_STGTAB_BASELINE 函数将您希望从 SQL 管理库导出的 SQL 计划基准压缩到临时表中 3. 使用导出命令或 Oracle Data Pump 将临时表导出到一个平面文件中 4. 将该平面文件传输到目标系统 5. 使用导入命令或 Oracle Data Pump 从平面文件导入到临时表 6. 使用 DBMS_SPM.UNPACK_STGTAB_BASELINE 函数从临时表将 SQL 计划基准解压缩到目标系统的 SQL 管理库中 9

部署新应用程序计划历史记录生产数据库计划基线第 6 步无计划性能降低第 3 4 5 步计划历史记录计划基线第 1 2 步良好的调优计划开发数据库基准计划临时表图 5 部署新应用程序时从测试系统导入 SQL 计划基准有关所有批量加载技术的更多信息, 参见后面的将 SQL 计划管理用于升级一节 SQL 计划基准选择使用 SPM

12 部署新应用程序计划历史记录生产数据库计划基线第 6 步无计划性能降低第步计划历史记录计划基线第 1 2 步良好的调优计划开发数据库基准计划临时表图 5 部署新应用程序时从测试系统导入 SQL 计划基准有关所有批量加载技术的更多信息, 参见后面的将 SQL 计划管理用于升级一节 SQL 计划基准选择使用 SPM 时将只选择已知的和经验证的计划来执行每次编译 SQL 语句时, 优化器首先使用基于性能开销的传统搜索方法来构建开销最优的计划如果初始化参数 OPTIMIZER_USE_SQL_PLAN_BASELINES 设置为 TRUE( 默认值 ), 那么在执行基于性能开销的计划之前, 优化器将尝试在 SQL 语句的 SQL 计划基准查找匹配的计划 ; 这是作为内存中操作执行的, 因此对任何应用程序不会带来明显的开销如果找到匹配的计划则使用该计划继续执行否则, 如果未找到匹配的计划, 将向计划历史记录中添加一个新生成的计划 ; 必须对新生成的计划进行验证才能接受为 SQL 计划基准优化器并不执行新生成的计划, 而是计算该 SQL 语句的每个已接受的计划的开销, 然后选择性能开销最低的那个 ( 注意 : 某个特定语句可能有多个经验证的 / 已接受的计划 ) 然而, 如果系统中的某个更改 ( 如删除索引 ) 造成所有已接受的计划无法重现, 则优化器将使用新生成的基于性能开销的计划 10

13 发出 SQL 语句生成执行计划是否存在 SQL 计划基准? 是该计划是否在 SQL 计划基准中? 是执行该计划否否执行该计划将新计划排队等待验证执行已知的基准计划图 6 如何使用 SPM 选择 SQL 执行计划优化器从 SQL 计划基准选择计划时有可能影响优化器的计划选择可以将 SQL 计划基准标记为固定的固定的 SQL 计划基准向优化器表明它们是首选的基准如果优化器正在计算 SQL 计划基准的性能开销, 而其中一个计划是固定的, 优化器将只计算这个固定计划的性能开销并在该计划可以重现时使用它如果固定计划 ( 一个或多个 ) 不可重现, 则优化器返回并计算其余 SQL 计划基准的性能开销, 并选择开销最低的那个注意, 计算某个计划的性能开销比硬解析要小得多优化器并不查看所有可能的访问方法, 而是查看一个特殊访问路径 11

14 SQL 计划基准发展任何时候都可以对计划进行人工发展和验证, 也可以调度一个数据库作业来运行发展进程优化器发现某个 SQL 语句的新计划后, 该计划作为一个未接受的计划添加到计划历史记录中, 需要对其验证才能成为一个已接受的计划可以使用 Oracle 企业管理器或运行命令行函数 DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE 来发展 SQL 语句的执行计划无论使用哪种方法, 您都有三种选择 : 1. 仅当其性能比现有 SQL 计划基准更佳时才接受新计划 2. 不进行性能验证就接受新计划 3. 运行性能比较并生成报告, 但不发展新计划如果选择了选项 1, 则将触发对新计划的评估, 评估它是否比所选计划基准的性能更佳如果比所选计划基准更佳, 则将其作为一个已接受的计划添加到 SQL 计划基准中否则, 新计划将作为一个未接受的计划保留在计划历史记录中, 但它的 LAST_VERIFIED 属性将更新为当前时间戳函数将返回一个格式化文本报告, 其中包含了函数所执行的操作, 同时并行显示了新计划与原计划的性能统计信息如果选择了选项 2, 则不验证它的性能就将新计划作为一个已接受的计划添加到 SQL 计划基准中也将生成报告如果选择了选项 3, 将对新计划进行评估, 看它的性能是否比所选的计划基准更佳, 但即使更佳, 也不自动接受新计划评估后只生成报告使用和管理 SQL 管理库初始化参数有两个控制 SPM 的新 init.ora 参数 optimizer_capture_sql_plan_baselines 控制可重复 SQL 语句的新 SQL 计划基准的自动捕获 11gR1 中该参数默认设置为 False optimizer_use_sql_plan_baselines 控制 SQL 计划基准的使用启用该参数后, 优化器在 SQL 计划基准中寻找正在编译的 SQL 语句的计划如果找到, 优化器将计算 SQL 计划基准中每个计划的性能开销, 然后选择开销最低的那个 11gR1 中该参数默认设置为 True 12

15 管理 SQL 管理库的空间使用语句日志计划历史记录和 SQL 计划基准都存储在 SQL 管理库中 SQL 管理库是数据库字典的组成部分, 存储在 SYSAUX 表空间中默认情况下,SQL 管理库的空间限制为不超过 SYSAUX 表空间的 10% 但是, 可以使用 PL/SQL 过程 DBMS_SPM.CONFIGURE 将这一限制修改为 1% 至 50% 之间的任意值后台进程每周测量一次 SQL 管理库的占用空间, 超过定义的限制时, 将在警报日志中生成警告每周还要执行定期清理任务, 来管理 SQL 管理库内部的 SPM 使用的磁盘空间在维护时段自动运行该任务, 任何超过 53 周一直未使用过的计划都将被清理, 从而确保那些即使每年仅运行一次的所有语句保持可用可以使用 DBMS_SPM.CONFIGURE 或企业管理器修改未使用计划的保留时间 ; 保留时间值的范围可以是 5 至 523 周 ( 稍长于 10 年 ) 参见下面的图 6 因为 SQL 管理库完整地存储在 SYSAUX 表空间中, 如果该表空间不可用, 则 SPM 也将无法使用使用 EM DBControl 或新的字典视图 DBA_SQL_ PLAN_B ASELINES 监视 SPM 图 7 在 EM 中修改计划保留设置监视 SQL 计划管理在 Oracle Database 11g 引入了几个新 Enterprise Manage 页面和 DBA 视图用于监视 SPM 功能企业管理器通过企业管理器 Database Control 可以管理和监视 SQL 计划基准的各个方面 13

16 开始使用企业管理器进入 SQL 计划基准页面 : 1. 进入企业管理器的 Database Home 页面 2. 在该页面的顶部, 单击 Server 显示 Server 页面 3. 在 Query Optimizer 部分, 单击 SQL Plan Control 4. 出现 SQL Plan Control 页面有关该页面的信息请参见联机帮助 5. 在该页面顶部, 单击 SQL Plan Baseline 显示 SQL 计划基准子页面图 8 Oracle Enterprise Manager DB Control 中的 SQL 计划基准主页从该主页可以控制 init.ora 参数调度加载或发展作业, 也可以修改现有 SQL 计划基准的某些属性 14

修改 init.ora 参数值 SQL 计划基准主页的左上角是 Settings 部分, 其中列出了控制 SQL 计划管理的一些参数在这一部分, 通过快速浏览可以了解自动 SQL 计划捕获是否开启, 还可以了解是否将使用某个 SQL 计划基准修改 init.ora 参数的值 1. 单击该参数的值 2.

17 修改 init.ora 参数值 SQL 计划基准主页的左上角是 Settings 部分, 其中列出了控制 SQL 计划管理的一些参数在这一部分, 通过快速浏览可以了解自动 SQL 计划捕获是否开启, 还可以了解是否将使用某个 SQL 计划基准修改 init.ora 参数的值 1. 单击该参数的值 2. 将打开初始化参数页面 ( 参见图 9) 从下拉菜单中选择希望修改的参数的值 3. 单击 OK 图 9 在 EM 中设置 SPM init.ora 参数批量加载计划可以使用 SQL 计划基准上面右手侧的加载按钮直接从游标缓存加载计划可以加载游标缓存中所有语句的计划, 也可以选择只加载一部分计划 15

18 1. 单击加载按钮 2. 将出现加载 SQL 计划基准页面选择 load from the cursor cache 单选按钮 ( 如图 10 中间所示 ) 3. 手工输入一个或多个 SQL_ID, 或者单击手电筒图标查看游标缓存中每个计划的所有 SQL_ID 及 SQL 的列表 4. 选择 SQL_ID( 一个或多个 ) 后, 完成作业调度信息 ( 默认为立即加载 ) 5. 单击 OK 图 10 在 EM 中从游标缓存批量加载 SQL 计划基准修改属性在 SQL 计划基准主页面中, 可以修改计划基准的任何属性修改属性 16

19 1. 单击计划基准前面的复选框 2. 单击希望修改的属性按钮 3. 将出现一个对话框要求您确认您的选择单击 OK 查看 SQL 计划基准的执行计划要查看 SQL 计划基准的实际执行计划, 单击计划名称要查看指定 SQL 语句的所有执行计划, 单击 SQL 文本演进一个 SQL 计划基准在 SQL 计划基准主页面中, 可以看到哪些计划被接受了, 哪些计划没被接受如果想发展一个未接受的计划, 则 1. 单击该计划前面的复选框, 然后选择列表上面的发展按钮 2. 将打开发展 SQL 计划基准页面, 其中有三个单选按钮选项 a. Verify Performance 如果您要保证未接受的计划的性能等同或优于现有的 SQL 计划基准, 则选择 YES 如果您已经知道未接受的计划有比较好的性能而希望绕过这项检查, 则选择 NO b. Time Limit 仅当对 Verify Performance 选择了 Yes, 才应用该选项选择 Auto 意味着由 Oracle 决定用多长时间来验证未接受的计划的性能选择 Unlimited 意味着将完成计划验证过程的运行选择 Specify 意味着需要为计划验证过程设置时间限制 c. Action 是希望自动接受新计划 ; 还是只希望得到一份验证结果报告, 再根据这份报告决定是否接受新计划 3. 单击 OK 4. 将出现 SQL 计划基准主页面在该页面的右上角的 Jobs 部分中应该看到列出了您的发展作业 ( 如果需要请单击刷新 ) 17

20 图 11 计划发展通过 DBA 视图监视 SPM DBA_SQL_PLAN_BASELINES 视图显示了当前为特定 SQL 语句创建的 SQL 计划基准的有关信息下面是一个示例 select sql_handle, sql_text, plan_name, origin, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines; 上面选择的语句返回下面各行 SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX AUT SYS_SQL_6fe2 select... SYS_SQL_PLAN_1ea AUTO-CAP YES NO NO YES SYS_SQL_6fe2 select... SYS_SQL_PLAN_4be AUTO-CAP YES YES NO YES 在该示例中, 同一个 SQL 语句有两个计划, 这两个计划都是自动捕获的其中一个计划 (SYS_SQL_PLAN_4be) 是计划基准, 因为它既是启用的计划, 也是已接受的计划另一个计划 (SYS_SQL_PLAN_1ea) 是未接受的计划, 该计划已经排队等待发展或验证已经自动捕获了该计划并将其排队等待验证 ; 它的接受值设置为 NO 这两个计划都不是固定计划, 因此都有可能被自动清理 18

21 要检查任何 SQL 计划基准的详细执行计划, 可以使用过程 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE 通过查看 V$SQL 可以检查 SQL 语句是否正在使用 SQL 计划基准如果 SQL 语句正在使用 SQL 计划基准, 从该计划基准选定的计划的 plan_name 将出现在 V$SQL 的 sql_plan_baseline 列中可以使用下列查询将 V$SQL 视图加入到 DBA_SQL_PLAN_BASELINES 视图 : Select s.sql_text, b.plan_name, b.origin, b.accepted From dba_sql_plan_baselines b, v$sql s Where s.exact_matching_signature = b.signature And s.sql_plan_baseline = b.plan_name; 与自动 SQL 调优集成在 Oracle Database 11g 中, 在维护时段会自动运行 SQL Tuning Advisor( 调优与诊断包的组成部分 ) 该自动 SQL 调优针对的是高负载 SQL 语句通过自动负载信息库 (AWR) 快照中收集的执行性能数据来识别这些语句如果 SQL Tuning Advisor 发现了某个 SQL 语句的更好的执行计划, 它将推荐一个 SQL profile 这些高负载 SQL 语句中的某些语句可能已经创建了 SQL 计划基准如果实施了自动 SQL 调优任务推荐的 SQL profile, 那么 SQL 调优任务发现的执行计划将作为一个已接受的 SQL 计划基准被添加也可以通过为特定的 SQL 语句创建 SQL 调优集来人工调用 SQL Tuning Advisor 如果 SQL Tuning Advisor 为该语句推荐了一个 SQL profile, 并人工实施了该 SQL profile, 则该 profile 将作为一个已接受的计划添加到 SQL 语句计划基准中 ( 如果存在 ) 将 SQL 计划管理用于升级进行数据库升级对任何 DBA 来说都是一项艰巨的任务数据库成功升级后,DBA 必然会因可能的数据库表现变化而受到指责每个 DBA 面临的可能表现变化中最重要的就是执行计划的更改引入了 SQL 计划管理后, 您就具备了一个额外的安全保证, 可以确保升级期间执行计划不会更改为了充分利用这一安全保证, 在升级前需要捕获现有的执行计划, 以便将其植入 SPM 19

22 使用 SQL 调优集如果您有权使用诊断包中的 SQL 调优集 (STS), 那么这是捕获现有 10g 执行计划的最方便的方法 STS 是一个数据库对象, 其中包括一个或多个 SQL 语句及其执行统计信息执行上下文和这些语句的执行计划 (Oracle Database 10gR1 中的 STS 将无法捕获 SQL 语句的执行计划, 因此无法使用它植入 SPM 只有 10gR2 STS 才会捕获计划 ) 首先需要创建一个新 STS 可以通过 Oracle Enterprise Manager (EM), 也可以使用 DBMS_SQLTUNE 包完成这一操作本示例中我们将使用 DBMS_SQLTUNE BEGIN SYS.DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name => 'SPM_STS', description => '10g plans'); END; \ 创建完新 STS 后, 需要对其进行加载可以从负载信息库另一个 STS 或游标缓存来加载 STS 本示例中我们将从游标缓存中捕获 SQL 语句及其执行计划这需要两个步骤在第一步中, 我们创建一个参考游标以便从游标缓存中选择指定的 SQL( 本例中为所有非系统 SQL 语句 ) 在接着的第二步中我们使用参考游标来加载 STS DECLARE stscur dbms_sqltune.sqlset_cursor; BEGIN OPEN stscur FOR SELECT VALUE(P) FROM TABLE(dbms_sqltune.select_cursor_cache( parsing_schema_name <> SYS, null, null, null, null, 1, null, 'ALL')) P; END; / -- populate the sqlset dbms_sqltune.load_sqlset(sqlset_name => 'SPM_STS', populate_cursor => stscur); 20

23 软件升级完成后, 可以使用 PL/SQL 过程 DBMS_SPM.LOAD_PLANS_FROM_SQLSET 或通过 Oracle 企业管理器 (EM) 将执行计划从 STS 批量加载到 SPM 中 SQL> Variable cnt number SQL> execute :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( - sqlset_name => 'SPM_STS'); 使用存储大纲如果您无权访问 SQL 调优集, 则可以使用存储大纲来捕获现有的执行计划有两种方法可以捕获存储大纲, 可以使用 CREATE OUTLINE 命令为每个 SQL 语句创建一个存储大纲, 也可以让 Oracle 自动为每个要执行的 SQL 语句创建一个存储大纲下面是 Oracle 自动创建存储大纲所需要执行的步骤 1. 启动一个新会话或发出下列命令, 切换为自动捕获每条 SQL 语句的存储大纲, 从现在开始获得解析, 直到您明确地将其关闭 SQL > alter system set CREATE_STORED_OUTLINES=OLDPLAN; 注意 : 要确保为其创建存储大纲的用户有 CREATE ANY OUTLINE 权限如果他们没有这个权限, 则无法捕获存储大纲 2. 现在, 通过运行应用程序或人工发出 SQL 语句来执行负载注意 : 如果人工发出 SQL 语句, 请确保与应用程序使用完全相同的 SQL 文本如果应用程序使用绑定变量, 则您也必须使用绑定变量 3. 执行完关键 SQL 语句后, 应该使用如下命令关闭自动捕获 SQL > alter system set CREATE_STORED_OUTLINES=false; 4. 要确认您已经捕获了必要的存储大纲, 请使用以下 SQL 语句 SQL> select name, sql_text, category from user_outlines; 注意 : 每个存储大纲都应该位于 OLDPLAN 类别中 5. 实际的存储大纲存储在 OUTLN 模式中升级之前应该将该模式导出作为备份 exp outln/outln file=soutline.dmp owner=outln rows=y 21

24 6. 升级到 Oracle Database 11gR2 后, 可以使用 DBMS_SPM.MIGRATE_STORED_OUTLINE 或通过 Oracle 企业管理器 (EM) 将某个或多个 SQL 语句的存储大纲迁移到 SQL 计划基准您可以通过大纲名称 SQL 文本或大纲类别来指定要迁移的存储大纲, 也可以选择系统中的所有存储大纲, 将它们迁移到 SQL 计划基准 SQL> variable report clob; -- Migrate a single Stored Outline by name SQL> exec :report:=dbms_spm.migrate_stored_outline( - attribute_name=>'outline_name', attribute_value => 'stmt01'); -- Migrate all Stored Outlines SQL> exec :report:=dbms_spm.migrate_stored_outline( - attribute_name=>'all'); 注意 : 如果您不计划进行就地升级, 则必须将 STS SQL 跟踪文件或存储大纲移至 Oracle Database 11g 系统从游标缓存批量加载如果升级前无法捕获某些或全部语句的计划, 那么直接从游标缓存加载计划可能是非常有用的在升级前将参数 OPTIMIZER_FEATURES_ENABLE 设置为使用的 10g 版本, 那么应该能够回退到升级前使用的同样的执行计划从游标缓存捕获这些 10g 执行计划, 能够在将 OPTIMIZER_FEATURES_ENABLE 设置为 11g 版本前使用 10g 计划植入 SPM 注意, 您必须使用与 10g 中使用的优化器统计信息完全相同的优化器统计信息在完全捕获所有 10g 计划之前, 不应该再重新收集统计信息 22

25 1. 首先将 OFE 设置为 10 O_F_E=10 Oracle Database 11g 计划历史记录 2. 运行应用程序中的所有 SQL 3. 将 10g 计划加载到 SPM 计划基线 4. 加载计划后, 将 OFE 变为 11 O_F_E=11 等待验证的 11g 计划队列无计划降低 optimizer_features_enable 图 12 通过从游标缓存捕获 10g 计划进行升级总结在 Oracle Database 11g 中引入了一个新特性 SQL 计划管理, 以提供受控执行计划发展有了 SPM, 优化器可以自动管理执行计划并确保仅使用已知的和经过验证的计划通过在升级前将当前版本数据库的执行计划植入或加载到 SPM, 可以避免因计划更改而造成性能降低由于只执行已知的和植入的计划, 因而 Oracle Database 11gR2 中应用程序的表现与它在之前版本中的表现将完全相同在 Oracle Database 11gR2 中发现的任何新执行计划将记录在该语句的计划历史记录中, 但不会使用如果经验证, 每条语句所记录的 ( 即未接受的 ) 计划在性能上优于现有已接受的计划, 才会使用它这样, 不仅在升级中而且在 Oracle Database 11g 中的系统扩展或发展时, 都可以控制使用哪些执行计划, 从而使系统更加稳定更具有一致的良好性能 23

26 甲骨文 ( 中国 ) 软件系统有限公司北京总部地址 : 北京市朝阳区建国门外大街 1 号, 国贸大厦 2 座 2208 室邮编 : 电话 :(86.10) 传真 :(86.10) 沈阳分公司地址 : 沈阳市沈河区青年大街 219 号, 华新国际大厦 17 层 D 单元邮编 : 电话 :(86.24) 传真 :(86.24) 北京上地 6 号办公室地址 : 北京市海淀区上地信息产业基地, 上地西路 8 号, 上地六号大厦 D 座 702 室邮编 : 电话 :(86.10) 传真 :(86.10) 南京分公司地址 : 南京市玄武区洪武北路 55 号, 置地广场 19 层 1911 室邮编 : 电话 :(86.25) 传真 :(86.25) 上海分公司地址 : 上海市卢湾区湖滨路 222 号, 企业天地商业中心 1 号楼 16 层邮编 : 电话 :(86.21) 传真 :(86.21) 杭州分公司地址 : 杭州市西湖区杭大路 15 号, 嘉华国际商务中心 702 室邮编 : 电话 :(86.571) 传真 :(86.571) 广州分公司地址 : 广州市天河北路 233 号, 中信广场 53 楼 5301&5308 室邮编 : 电话 :(86.20) 传真 :(86.20) 西安分公司地址 : 西安市高新区科技二路 72 号, 零壹广场主楼 1401 室邮编 : 电话 :(86.29) 传真 :(86.29) 成都分公司地址 : 成都市人民南路二段 18 号, 四川川信大厦 20 层 A&D 座邮编 : 电话 :(86.28) 传真 :(86.28) 福州分公司地址 : 福州市五四路 158 号, 环球广场 1601 室邮编 : 电话 :(86.591) 传真 :(86.591) 大连分公司地址 : 大连软件园东路 23 号, 大连软件园国际信息服务中心 2 号楼五层 502 号 A 区邮编 : 电话 :(86.411) 传真 :(86.411) 重庆分公司地址 : 重庆市渝中区邹容路 68 号, 大都会商厦 1611 室邮编 : 电话 :(86.23) 传真 :(86.23) 济南分公司地址 : 济南市泺源大街 150 号, 中信广场 11 层 1113 单元邮编 : 电话 :(86.531) 传真 :(86.531) 深圳分公司地址 : 深圳市南山区高新南一道飞亚达大厦 16 层邮编 : 电话 :(86.755) 传真 :(86.755) 甲骨文软件研究开发中心 ( 北京 ) 有限公司地址 : 北京市海淀区中关村软件园孵化器 2 号楼 A 座一层邮编 : 电话 :(86.10) 传真 :(86.10) 甲骨文亚洲研发中心 ( 上海 ) 地址 : 上海市杨浦区淞沪路 290 号, 创智天地 10 号楼单元邮编 : 电话 : 传真 : 甲骨文研究开发中心 ( 深圳 ) 有限公司地址 : 深圳市南山区高新南一道飞亚达大厦 16 层邮编 : 电话 :(86.755) 传真 :(86.755)

27 白皮书标题 :Oracle Database 11g 中的 SQL 计划管理 2010 年 11 月作者 :Maria Colgan 公司网址 : 英文 ) 中文网址 : 简体中文 ) 销售中心 : 售后服务热线 : 培训服务热线 : 欢迎访问 : 英文 ) 简体中文 ) 版权 2010 归 Oracle 公司所有未经允许, 不得以任何形式和手段复制和使用本文的宗旨只是提供相关信息, 其内容如有变动, 恕不另行通知 Oracle 公司对本文内容的准确性不提供任何保证, 也不做任何口头或法律形式的其他保证或条件, 包括关于适销性或符合特定用途的所有默示保证和条件本公司特别声明对本文档不承担任何义务, 而且本文档也不能构成任何直接或间接的合同责任未经 Oracle 公司事先书面许可, 严禁将此文档为了任何目的, 以任何形式或手段 ( 无论是电子的还是机械的 ) 进行复制或传播 Oracle 是 Oracle 公司和 / 或其分公司的注册商标其他名字均可能是各相应公司的商标

Agilent N5700 N5741A-49A, N5750A-52A, N5761A-69A, N5770A-72A W 1500 W 600 V 180 A 1 U Vac AC LAN,USB GPIB Agilent N5700 1U 750W 1500W 24

Agilent N5700 N5741A-49A, N5750A-52A, N5761A-69A, N5770A-72A W 1500 W 600 V 180 A 1 U Vac AC LAN,USB GPIB Agilent N5700 1U 750W 1500W 24 Agilent N700 N71A-9A, N70A-2A, N761A-69A, N770A-72A 2 70 W 100 W 600 V 180 A 1 U 8-26 Vac AC LAN,USB GPIB Agilent N700 1U 70W 100W 2 6V 600V 1.A 180A N700 1U 19 100W LED N700 OVP UVL UVL OVP N700 GPIB