SQL执行计划管理可以分为哪些基本任务

本篇内容介绍了“SQL执行计划管理可以分为哪些基本任务”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！使用dbms_spm与dbms_xplan包来执行大部分的SQL执行计划管理任务。SQL执行计划管理可以分为以下基本任务:
.配置SQL执行计划管理
.显示SQL执行计划基线中的执行计划
.加载SQL执行计划基线
.手动evolve执行计划基线中的执行计划
.删除SQL执行计划基线
.管理SQL Management Base(SMB)
.迁移Stored Outlines to SQL Plan Baselines配置SQL执行计划管理
.配置捕获与使用SQL Plan Baselines
.管理SPM Evolve Advisor Task配置捕获与使用SQL Plan Baselines
可以使用optimizer_capture_sql_plan_baselines与optimizer_use_sql_plan_baselines参数来控制SQL plan管理。optimizer_capture_sql_plan_baselines的缺省值为false。对于不在执行计划历史中的任何重复的SQL语句，数据库不会对SQL语句自动创建一个初始的SQL Plan Baseline。如果optimizer_capture_sql_plan_baselines参数设置为true，那么可以使用dbms_spm.configure过程来配置过滤器来判断哪些SQL语句满足捕获条件。缺省情况是没有配置过滤器的，这意味着所有重复执行的SQL语句都满足捕获条件。optimizer_use_sql_plan_baselines的缺省值为true。对于已经在SQL plan baseline中存在的任何SQL语句，数据库会自动向SQL plan baselines中以未接受的执行计划来添加新的SQL plan。对SQL Plan管理启用自动初始化Plan捕获
将optimizer_capture_sql_plan_baselines参数设置为true是对在plan历史中不存在的任何SQL语句自动创建一个初始化SQL Plan baseline所必要的。缺省情况下，当自动SQL plan baseline捕获被启用后，数据库会为每个重复的SQL语句，包括所有递归SQL语句与监控SQL语句创建一个SQL Plan baseline。因此，自动捕获功能可能会造成大量的SQL Plan Baseline。 为了限制捕获的SQL Plan Baselines的数量可以使用dbms_spm.configure过程来配置过滤条件。optimizer_capture_sql_plan_baselines参数不控制自动向之前创建的SQL plan baseline添加新发现的执行计划。启用自动捕获SQL plan baseline操作如下:
1.以有相关权限的用户用SQL*Plus登录数据库2.显示当前SQL Plan管理的设置情况3.为了对重复的SQL语句启用自动生成SQL Plan Baseline执行下面的语句当启用SQL Plan Baselines自动捕获功能后可以从下面的结果看到对重复的所有SQL语句进行了执行执行的捕获为自动SQL Plan Baseline捕获配置过滤条件
如果optimizer_capture_sql_plan_baselines设置为true，那么你可以使用dbms_spm.configure过程来对重复执行的SQL语句创建一个自动捕获过滤条件。自动过滤可以只捕获想要的SQL语句并排除非关键语句，这样可以节省SYSAUX表空间的使用。可以对不同的类型配置多个参数，也可以在单独的语句中对相同的参数指定多个参数值，数据库会进行组合。这种设置是附加型的：一个参数设置不会覆盖之前的设置。例如，下面的过滤设置用来捕获解析方案SYS或SYSTEM中的SQL语句:然而，不能在相同的过程中对相同的参数指定多个参数值。例如不能对AUTO_CAPTURE_SQL_TEXT指定多个SQL文本字符串。DBA_SQL_MANAGEMENT_CONFIG视图可以用来显示当前参数值。下面的操作假设optimizer_capture_sql_plan_baselines参数被设置为true。只要捕获sh方案所有执行的SQL语句并且想要排除包含test_only文本的语句
1.以有相关权限的用户用SQL*Plus登录数据库2.为了删除对解析方案与SQL文本已经存在的任何过滤条件执行以下语句:3.只对sh方案所执行的语句启用自动捕获4.从自动捕获中排除任何包含test_only文本的语句5.通过查询dba_sql_management_config视图来确认配置的过滤条件禁用所有SQL Plan Baselines
当optimizer_use_sql_plan_baselines参数设置为false时，数据库不会使用任何SQL Plan Baseline。为了禁用所有SQL Plan baselines执行以下操作:
1.以有相关权限的用户用SQL*Plus登录数据库2.为了忽略所有现存的SQL Plan Baselines执行以下语句管理SPM Evolve Advisor Task
SPM Evolve Advisor是一个SQL Advisor可以对最近添加到SQL Plan Baseline中的SQL Plan进行evolve。缺省情况下，SYS_AUTO_SPM_EVOLVE_TASK在调度维护窗口中每天运行。SPM Evolve Advisor Task执行以下操作:
1.定位未接受的SQL Plan
2.对所有未接受的SQL Plan进行排名
3.在维护窗口尽可能的对大量的SQL Plan进行测试执行
4.选择一个成本最低的执行计划与每个未接受的执行计划进行比较
5.使用基于成本的算法来自动接受比现有已接受的执行计划性能更好的任何未接受的执行计划启用与禁用SPM Evolve Advisor Task
对于自动SPM Evolve Advisor Task没有单独的调度客户端存在。一个调度客户端控制着自动SQL Tuning Advisor与自动SPM Evolve Advisor。配置自动SPM Evolve Advisor Task
通过使用dbms_spm.set_evolve_task_parameter过程来指定任务参数来配置自动SQL Plan Evolve。因为SYS_AUTO_SPM_EVOLVE_TASK任务的所有者为SYS，只有SYS用户可以设置任务参数。dbms_spm.set_evolve_task_parameter有以下参数
alternate_plan_source:决定添加SQL Plan的搜索源:cursor_cache,automatic_workload_repository或sql_tuning_sets。可以使用+号来组合多个参数值，缺省值为cursor_cache+automatic_workload_repositoryalternate_plan_baseline:决定那个替代plan应该被加载。EXISING它是缺省值，使用现有的SQL Plan baseline来为语句加载SQL plan。NEW不使用现有SQL plan baseline来为语句加载SQL plan，并且会创建一个新的SQL Plan baseline。可以使用+号来组合多个参数值。alternate_plan_limit:指定可以加载SQL Plan的最大数量，缺省值为0。accept_plans:指定是否自动接受建议的SQL Plan。当accept_plans设置为true(缺省值)时，SQL Plan管理自动接受由SPM Evolve Advisor Task所建议的所有SQL Plan。当设置为false时，如果找到替代的SQL plan，SPM Evolve Advisor Task会验证SQLPlan并生成一个报告，但不会evolve这个SQL plan。下面的操作假如满足以下条件
.想要数据库自动接受SQL Plan
.想在任务每次执行1200秒后就会超时
.想要evolve任务在共享SQL区与AWR档案库中查找最多500个SQL Plan设置自动evolve任务参数
1.以sys用户登录数据库2.查询sys_auto_spm_evolve_task任务的当前参数设置情况3.使用以下PLSQL块来配置sys_auto_spm_evolve_task任务自动接收SQL plan,在共享SQL区与AWR档案库中查找最多500个SQL plan，并且在执行20分钟后任务就会超时终止。4.确认sys_auto_spm_evolve_task任务的当前参数设置情况显示SQL执行计划基线中的执行计划
为了查看指定SQL语句存储在SQL Plan Baseline中的SQL Plan，可以使用dbms_xplan.display_sql_plan_baseline 函数。这个函数使用存储在plan history中的执行计划信息来显示SQL Plan。它有以下参数:
sql_handle:语句的 SQL handle可以通过连接v$sql.sql_plan_baseline与dba_sql_plan_baselines.plan_name列来进行查询
plan_name:语句执行计划的名字假设要显示SQL ID为34q7g1h59b79n的语句所存储在SQL Plan Baseline中的执行计划执行下面的语句上面的结果显示SQL ID为4gc64454ax64x的执选执行计划名字叫SQL_PLAN_bmz5xhst6b31y41975532并且是被自动捕获的。加载SQL执行计划基线
使用dbms_spm可以批量加载一组现有的执行计划到一个SQL Plan Baseline中。dbms_spm包可以从以下来源加载执行计划:
.AWR:要从AWR快照加载执行计划，那么必须指定快照开始与结束的范围，另外也可以应用过滤条件来只加载满足条件的执行计划。缺省情况下，数据库在下一次执行SQL语句时优化器就会使用加载到SQL Plan Baseline中的执行计划。.共享SQL区:直接从共享SQL区来加载执行计划。通过对模块名，方案名或SQL ID应用过滤条件可以标识需要被捕获的SQL语句或一组SQL语句。数据库在下一次执行SQL语句时优化器就会使用加载到SQL Plan Baseline中的执行计划。当应用程序SQL已经通过手写hints进行过优化之后直接从共享SQL区中加载执行计划是非常有用的。因为你可能不能更改SQL包括hint，使用SQL Plan Baseline可以确保应用程序SQL使用最优的执行计划。.SQL tuning set(STS):捕获SQL工作量的执行计划到一个STS中，然后加载执行计划到SQL Plan Baselines中。数据库在下一次执行SQL语句时优化器就会使用加载到SQL Plan Baseline中的执行计划。从STS中批量加载执行计划是在数据库升级后防止执行计划回归有效的方法。.Staging table:使用dbms_spm包可以定义一个staging表，dbms_spm.pack_stgtab_baseline过程可以复制SQLPlan baseline到一个staging表中，并使用Oracle data pump将共staging表传输到另一个数据库。在目标数据库中，使用dbms_spm.unpack_stgtab_baseline过程来从staging表中把SQL plan baseline加载到SMB中。.Stored outline:迁移stroed outlines到SQL Plan Baselines中。在迁移之后，你可以通过SQL Plan管理所提供的更高级的功能来维护相同的执行计划稳定性。从AWR加载执行计划
假设我们要将下面的查询语句的执行计划加载到SQL Plan Baseline中，那么要确保用户sh有查询dba_hist_snapshot和dba_sql_plan_baselines视图，执行dbms_workload_repository.create_snapshot和dbms_spm.load_plans_from_awr的权限为了从AWR中加载执行计划到SQL Plan Baselines中执行以下操作
1.以有相关权限的用户登录到数据库，然后查询最近生成的3个AWR快照2.查询sh.sales表，使用load_awr标记来识别这个SQL语句3.生成一个新的AWR快照4.查询最近生成的3个AWR快照来确保新的AWR快照已经生成了5.使用最近生成的2个AWR快照来加载执行计划6.查询数据字典来确保load_awr语句的执行计划被加载到SQL Plan Baselines中了7.再次执行load_awr语句，查看其执行计划可以看到SQL plan baseline SQL_PLAN_4kr99sru62b6u54bc8843 used for this statement这样的信息，说明生成的执行计划基线应用到该语句了从共享SQL区加载执行计划
假设要从共享SQL区将下面的查询语句的执行计划加载到SQL Plan Baseline中需要执行以下操作
1.执行SQL语句2.查询v$sql视图查询执行语句的SQL ID3.从共享SQL区加载指定语句的执行计划到SQL Plan Baseline中4.查询dba_sql_plan_baselines视图来确认语句的执行计划是否加载到SQL Plan Baselines中了从SQL Tuning Set中加载执行计划
一个SQL Tuning Set是一个数据库对象它包括一个或多个SQL语句，执行统计信息与执行上下文信息。假设SQLTuning Set包含下面的语句，要从SQL Tuning Set中加载该语句的执行计划到SQL Plan Baselines中要执行以下操作
1.执行SQL语句并找到其SQL ID2.将执行的SQL语句加载到SQL Tuning Set中3.从SQL Tuning Set中加载执行计划到SQL Plan Baseline中basic_filter参数指定了一个where子句用来只加载需要的SQL语句,v_plan_cnt用来存储从SQL Tuning Set所加载的执行计划数。4.查询数据字典来确保SQL Tuning Set中的语句的执行计划是否成功加载到SQL Plan Baselines中5.删除SQL Tuning Set从Staging Table中加载执行计划
有时可能需要从一个源数据库传输最优化的执行计划到一个目标数据库那么需要执行以下操作
1.使用create_stgtab_baseline过程来创建一个staging表2.在源数据库中，将SQL Plan Baseline从SQL管理基础框架中打包到staging表中3.将staging表stage1使用Oracle Data Pump Export导出到一个dump文件中4.将dump文件传输到目标数据库5.在目标数据库中，使用Oracle Data Pump Import将dump文件中的数据导入到staging表stage1中6.在目标数据库中，将SQL Plan Baseline从staging表中解压到SQL管理基础框架中SQL Plan baselines Evolve
这里将介绍如何使用命令行来evolve sql plan baselines。为了evolve一个特定的sql执行计划执行以下操作:
1.创建一个evolve任务
2.设置evolve任务参数
3.执行evolve任务
4.实现任务中给出的建议
5.显示任务执行的结果下面将举例来说明，假设满足以下条件
.数据库没有启用自动evolve任务
.对下面的查询创建一个SQL Plan Baseline.想要创建两个索引来提高查询语句的性能，如果使用索引的性能比SQL Plan Baseline中的当前执行计划的性能好那么就evolve该执行计划为了evolve一个特定的执行计划需要执行以下操作
1.执行初始化设置操作清空共享池与缓冲区缓存启用自动捕获SQL Plan Baselines以sh用户登录到数据库，然后设置SQLPLUS的显示参数2.执行SQL语句，因此可以自动捕获它
执行下面的SQL语句查询数据字典确认在SQL Plan Baseline中不存在执行计划，因为只有重复执行的SQL语句才会被捕获再次执行SQL语句3.查询数据字典来确保执行计划已经被加载到SQL Plan Baseline中了，下面的查询显示执行计划已经被接受，这意味着执行计划已经存储在SQL Plan Baselines中了。origin列显示为AUTO-CAPTURE，这意味着执行计划是被自动捕获的4.下面对SQL语句进行解析并验证优化器是否会使用SQL Plan Baseline中的执行计划从执行计划的Note部分可以看到SQL Plan Baseline已经应用到这个SQL语句了5.创建两个索引用来提高上面SQL语句的性能6.再次执行SQL语句，因为启用了自动捕获功能，所以新的执行计划会被加载到SQL Plan Baseline中7.查询数据字典来确保新的执行计划被加载到SQL Plan Baseline中了上面的查询结果显示新的执行计划是为被接受的。8.再次解析SQL语句并验证优化器是不是使用原始没有索引的执行计划上面的Note部分指示优化器使用了原始的没有索引的执行计划9.以管理员用户登录数据库，然后创建一个evolve任务它包含未被接受执行计划相关的所有SQL语句10.执行evolve任务11.查看报告报告显示使用两个索引的执行计划比原始执行计划性能更好12.实现evolve任务所给出的建议13.查询数据字典来确保新的执行计划已经是接受状态14.执行清除操作删除SQL Plan Baselines
可以从SQL Plan Baselines中删除一些或所有执行计划。管理SQL Management Base
SQL Management Base是数据字典的一部分，它存储在SYSAUX表空间中。它存储语句日志,执行计划历史记录，SQL执行计划基线与SQL Profiles。使用dbms_spm.configure过程可以对SMB进行选项设置与维护SQL Plan Baselines。dba_sql_management_config视图可以用来查看SMB的免费主机域名当前配置信息。下面介绍parameter_name列可以设置的参数列表:
space_budget_percent:SQL Management Base可以使用的SYSAUX表空间的最大百分比。缺省值是10%。允许的范围是1%到50%。plan_retention_weeks:在清除之前没有被使用的执行计划需要保留多少周，缺省值是53。auto_capture_parsing_schema_name:它是(% LIKE a OR % LIKE b …) AND (%NOT LIKE c AND % NOT LIKE d …)形式的列表，它代表了解析方案名过滤。auto_capture_module:它是(% LIKE a OR % LIKE b …) AND (%NOT LIKE c AND % NOT LIKE d …)形式的列表，它代表了模块过滤。auto_capture_action:它是(% LIKE a OR % LIKE b …) AND (%NOT LIKE c AND % NOT LIKE d …)形式的列表，它代表了操作过滤。auto_capture_sql_text:它是(% LIKE a OR % LIKE b …) AND (%NOT LIKE c AND % NOT LIKE d …)形式的列表，它代表了SQL文本过滤。修改SMB空间使用限制
一个由SMB所调用的每周运行一次的后台进程将会检测空间使用情况。当超过定义限制时，后台进程将会写一个告警信息到alert日志文件中。数据库会每周生成一个告警信息直到SMB空间限制被增加为止，SYSAUX表空间被增加为止或者通过清除SQL Management对象(sql plan baselines或sql profiles)来减少SMB所使用的空间为止。1免费主机域名.查看当前SMB所用空间的限制大小,从查询结果可以看到当前大小是SYSAUX表空间大小的10%2.将SMB所用空间限制大小修改为SYSAUX表空间大小的30%3.确认SMB所有空间限制大小是否成功被修改为SYSAUX表空间大小的30%修改SMB中的Plan Retention Policy
每周调度清除任务来管理由SQL Plan Management所使用的空间。这个任务是一个在维护窗口内自动执行的任务。数据库会自动清除超过Plan Retention期限而没有被使用的执行计划，它是执行计划存储在SMB中的last_executed字段来标识的。缺生活上的执行计划保留周期是53周。这个周期可以设置的范围是5到523周。1.查看当前执行计划保留周期2.修改执行计划保留周期为105周3.确保执行计划保留周期是否成功被修改为105周“SQL执行计划管理可以分为哪些基本任务”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注云技术网站，小编将为大家输出更多高质量的实用文章！

相关推荐: 数据库中如何获取方法、查看执行顺序、统计信息

小编给大家分享一下数据库中如何获取方法、查看执行顺序、统计信息，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 个人常用的获取执行计划的方法1.select * from table(…