东莞网站系统后缀,广州线上教学,网站微信支付怎么开通,简洁大方网站建设OBA技能1-获取执行计划OBA技能2-执行计划顺序OBA技能#xff13;-执行计划顺序表连接ODBA 技能#xff14;实战执行计划ODBA 技能5 固定执行计划因为每次统计信息作业在收集完信息后#xff0c;会触发ACS自适应游标管理程序#xff0c;进行对绑定变量的窥探工作#xff0c… OBA技能1-获取执行计划OBA技能2-执行计划顺序OBA技能-执行计划顺序表连接ODBA 技能实战执行计划ODBA 技能5 固定执行计划 因为每次统计信息作业在收集完信息后会触发ACS自适应游标管理程序进行对绑定变量的窥探工作窥探完可能就进行硬解析生成新的执行计划。为此一个SQL语句因为不可描述的原因导致自己的执行计划变多了有的新计划的成本更低了自然选择新计划。或者原来成本低的计划变得不可用了只好选择次级计划。总之最终可能导致新选的计划ORACLE认为好实际执行更慢。 为此11G后出来个SPM和执行计划基线BASELINE。请不要被BASELINE给误导真实行为含义就是执行计划库。SPM就是管理基库的一套程序。一、基础概念Oracle 11g开始提供了一种新的固定执行计划的方法即SQL plan baseline中文名SQL执行计划基线(简称基线)可以认为是OUTLINE(大纲)或者SQL PROFILE的改进版本基本上它的主要作用可以归纳为如下两个1、稳定给定SQL语句的执行计划防止执行环境或对象统计信息等等因子的改变对SQL语句的执行计划产生影响2、减少数据库中出现SQL语句性能退化的概率理论上不允许一条语句切换到一个比已经执行过的执行计划慢很多的新的执行计划上注意1、从Oracle的发展角度来看估计这种方法是Oracle发展和改进的方向如今outline已经被废弃sql profile估计在后续的发行版本中也难有改进因此对于从11g开始接触Oracle的朋友来说一定要对sql计划基线有所了解因为这是以后的主流2、SQL执行计划基线保存在数据字典中查询优化器会自动判断使用他们。二、工作机制从Oracle 11g开始由于基线的存在一条语句的解析过程大概如下SQL语句被硬解析后CBO(优化器)会产生很多个的执行计划CBO从中选择一个成本最低执行计划。基于SQL语句的文本形成一个哈希值(signature)通过这个哈希值来检查数据字典中是否存在同样的基线。如果基线存在优化器会对刚刚产生的执行计划和保存在SQL plan baseline中的执行计划进行比较。如果基线中有与CBO刚产生的执行计划的匹配的SQL执行计划存在并且被标记为可接受(‘accepted’)则这个CBO生成的执行计划被启用。如果基线中没有匹配的SQ执行计划存在CBO评估基线中被标记为‘accepted’的的多个执行计划并选择其中cost最低的执行计划。(注意一个语句的基线可以有多个执行计划被保存这是与其他Outline和SQL profiel都不同的地方)如果刚刚硬解析过程中CBO选择的执行计划比保存在基线中的执行计划COST都低这个新生成的执行计划被标记为‘not-accepted’并保存在基线中。直到这个执行计划被演化且验证后才会被考虑使用即标记为accepted(演化和验证可以简单理解为Oracle确认这个执行计划可以带来更好的性能)。上面讲解的工作机制有点绕我们白话翻译一下。1 假如你开启了SQL的执行计划库。2 如果1个SQL语句发生硬解析产生新的执行计划。然后去基库里找看是否有已经存在的执行计划3 如果存在就使用该计划4 如果不存在但是还存在其他的执行计划那么选择COST最低那个执行。5 如果这个新生成的计划比库里的成本都低它就被纳入库中并标记待检验。这里有两个疑问谁去验证是不是每次SQL执行都要产生硬解析呢三、基库的一些特点简单归纳如下几个通过OPTIMIZER_USE_SQL_PLAN_BASELINE来控制Oracle是否使用基库默认值为TRUE即会自动使用基库。11g中默认是不会自动创建基库与OUTLINE和SQL Profile不同基库中不存在分类的概念与OUTLINE和SQL Profile不同每个SQL语句可以有多个基库。Oracle根据制定的规则来判断具体是否哪个基库基线针对RAC中所有的实例都生效基库有两个表示一个为sql_handle可以理解为表示语句文本的唯一标识一个为sql_plan_name可以理解为执行计划的唯一标识不能像sql profile一样通过force_matching属性将字面值不一样的SQL语句使用一个基库应用多个语句。三、创建基库的几种方式1、自动捕获基库通过将optimizer_cature_sql_plan_baselines设置为true优化器为重复执行两次以上的SQL语句生成并保存基库 2、从SQL调优集合中加载通过使用包dbms_spm.load_plans_from_sqlset来从SQL调优集合中加载基库DECLARE l_plans_loaded PLS_INTEGER;BEGIN l_plans_loaded : DBMS_SPM.load_plans_from_sqlset( sqlset_name my_sqlset);END;/ 3、从库缓存中加载通过包dbms_spm.load_plans_from_cursor_cache函数为一条已经在游标缓存中的语句创建基线DECLARE1fkh93md0802n,plan_hash_value四、基库的几种状态一个SQL语句对应的基线我将它们归纳为三种状态accepted(可接受)只有这种状态的基库优化器才会考虑此基库中的执行计划no-accepted(不可接受)这种状态的基库优化器在SQL语句解析期间不会考虑。这种状态的基库必须通过演化和验证通过后转变为accepted状态后才会被优化器考虑使用fixed为yes(固定)这种状态的基库固有最高优先级比其他两类基库都要优先考虑五、查看基库1、基本视图dba_sql_plan_baselines、dba_sql_management_config2、通过函数来查看基线的详细信息select * from table( dbms_xplan.display_sql_plan_baseline ( sql_handleSYS_SQL_11bcd50cd51504e9, plan_nameSQL_PLAN_13g6p1maja1790cce5f0e )); 下面显示具体的执行计划信息--------------------------------------------------------------------------------SQL handle: SQL_44c9b37ac97e85e9SQL text: select * from test_objects -------------------------------------------------------------------------------- --------------------------------------------------------------------------------Plan name: SQL_PLAN_49kdmgb4rx1g95cd5cc6d Plan id: 1557515373Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD-------------------------------------------------------------------------------- Plan hash value: 3570092908 ----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 68108 | 6518K| 275 (1)| 00:00:04 || 1 | TABLE ACCESS FULL | TEST_OBJECTS | 68108 | 6518K| 275 (1)| 00:00:04 |----------------------------------------------------------------------------------显然这有点不方便六、演化基库为了验证基库中一个处于不可接受状态的执行计划是否比一个处于可接受状态的执行计划具有更高的效率必须通过演化来验证需要让优化器以不同的执行计划来执行这条SQL语句观察不可接受状态的执行计划基库是否会带来更好的性能如果性能确实更高这个不可接受状态的基库将会转换为可接受状态。演化的方式有两种1、手工执行运行SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle From dual;------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report-------------------------------------------------------------------------------Inputs:------- SQL_HANDLE SQL_44c9b37ac97e85e9 PLAN_NAME TIME_LIMIT DBMS_SPM.AUTO_LIMIT VERIFY YES COMMIT YES------------------------------------------------------------------------------- Report Summary-------------------------------------------------------------------------------There were no SQL plan baselines that required processing.还有time_limit/verify/commit几个参数可以参考文档2、调优包实现基线的自动演化可以理解为启动一个调度任务周期性的检查是否有不可接受状态的基线库可以被演化手动select DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle NULL, plan_name NULL, verify YES, commit NO ) from dual;这里由两个标记控制o Verify YES (只有性能更好的计划才会被演化) NO (演化所有的计划)o Commit YES (直接演化) NO (只生成报告)不让系统智能选择使用新计划七、修改基库可以通过dbms_spm.alter_sql_plan_baseline包来修改基库的一些属性主要有如下几个属性ENABLED 设置该属性的值为NO告诉Oracle 11g临时禁用某个计划一个SQL计划必须同时标记为ENABLED和ACCEPTED否则CBO将忽略它FIXED设置为YES那个计划将是优化器唯一的选择即使如果某个计划可能拥有更低的成本。这让DBA可以撤销SPM的默认行为对于转换一个存储概要进入一稳定的SQL计划基库特别有用注意当一个新计划被添加到被标记为FIXED的SQL计划基库该新计划不能被利用除非它申明为FIXED状态AUTOPURG设置这个属性的值为NO告诉Oracle 11g无限期保留它从而不用担心SMB的自动清除机制plan_name : 改变SQL plan 名字description : 改变SQL plan描述实验1 开启SCOTT用户2 创建表 create table test_objects as select * from all_objects3 执行SQL语句 select * from test_objects 4 使用SYS等其他高级用户获得刚才的语句SQLIDselect * from v$sqlarea s where sql_text order 5 SYS创建该SQL的基库SQL DECLARE 2 my_plans pls_integer; 3 BEGIN 4 my_plans : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id 0g9hjnj0x1nbb); 5 END; 6 /PL/SQL procedure successfully completed6 查看执行计划基库SELECT * FROM dba_sql_plan_baselines2 修改已有的Baseline/*********语法DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle IN VARCHAR2 : NULL, plan_name IN VARCHAR2 : NULL, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2 )RETURN PLS_INTEGER;************/SET SERVEROUTPUT ONDECLARE l_plans_altered PLS_INTEGER;BEGINl_plans_altered : DBMS_SPM.alter_sql_plan_baseline(sql_handle SQL_44c9b37ac97e85e9,plan_name SQL_PLAN_49kdmgb4rx1g95cd5cc6d,attribute_name AUTOPURGE,attribute_value NO);DBMS_OUTPUT.put_line(Plans Altered: || l_plans_altered);END;/把自动清除机制关闭了往执行计划基库里添加新的计划。1 添加索引 create index IX_OBJECT_NAME on TEST_OBJECTS (OBJECT_NAME2使用提示强制走索引select /* index(test_objects IX_OBJECT_NAME)*/ * from test_objects执行成本特高3从V$SQL_PLAN获得select * (select * 4加入SQL DECLARE 2 k1 pls_integer; 3 begin 4 k1 : DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 5 ( 6 sql_handleSQL_44c9b37ac97e85e9, 7 sql_id5ptdb66rf053s, 8 plan_hash_value127622217 9 ); 10 end; 11 /PL/SQL procedure successfully completed5查询基库SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN PARSING_SCHEMA_NAME ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE OPTIMIZER_COSTSQL_44c9b37ac97e85e9 SQL_PLAN_49kdmgb4rx1g95cd5cc6d MANUAL-LOAD SCOTT YES YES NO YES NO 275SQL_44c9b37ac97e85e9 CLOB SQL_PLAN_49kdmgb4rx1g9f1aba20d MANUAL-LOAD SCOTT YES YES NO YES YES 35397我们成功加入了 启用我们走索引的计划SET SERVEROUTPUT ONDECLARE l_plans_altered PLS_INTEGER;BEGINl_plans_altered : DBMS_SPM.alter_sql_plan_baseline(sql_handle SQL_44c9b37ac97e85e9,plan_name SQL_PLAN_49kdmgb4rx1g9f1aba20d,attribute_name FIXED,attribute_value YES);DBMS_OUTPUT.put_line(Plans Altered: || l_plans_altered);END;/验证有了基库后普通的V$SQL_PLAN 就没它了 FROM V$SQL_PLAN WHERE SQL_ID 0g9hjnj0x1nbb9 删除基库里一个执行计划SET SERVEROUTPUT ONDECLARE l_plans_dropped PLS_INTEGER;BEGIN l_plans_dropped : DBMS_SPM.drop_sql_plan_baseline ( sql_handle SQL_44c9b37ac97e85e9, plan_name SQL_PLAN_49kdmgb4rx1g95cd5cc6d); DBMS_OUTPUT.put_line(l_plans_dropped);END;/总结下SPM是好东西直接开启参数产生基库然后使用人工进化非智能模式。基库唯一缺点没有SQLID无法通过SQLID找到对应的基库只能看SQLTEXT。
