Oracle SQL操作计划基线总结(SQL Plan Baseline)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle SQL操作计划基线总结(SQL Plan Baseline),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含17658字,纯文字阅读大概需要26分钟。
内容图文
![Oracle SQL操作计划基线总结(SQL Plan Baseline)](/upload/InfoBanner/zyjiaocheng/453/4b78302a7edb4652a82a912f0e779852.jpg)
二、工作机制 从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确认这个运行计划能够带来更好的性能)。
三、基线的一些特点 简单归纳例如以下几个
- 通过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语句使用一个基线应用多个语句。
四、基线的几种状态 一个SQL语句相应的基线,我将它们归纳为三种状态
- accepted(可接受),仅仅有这样的状态的基线,优化器才会考虑此基线中的运行计划
- no-accepted(不可接受)。这样的状态的基线,优化器在SQL语句解析期间不会考虑。这样的状态的基线必须通过演化和验证通过后,转变为accepted状态后。才会被优化器考虑使用
- fixed为yes(固定),这样的状态的基线固有最高优先级!
比其它两类基线都要优先考虑
六、演化基线 为了验证基线中一个处于不可接受状态的运行计划是否比一个处于可接受状态的运行计划具有更高的效率。必须通过演化来验证,须要让优化器以不同的运行计划来运行这条SQL语句。观察不可接受状态的运行计划基线是否会带来更好的性能,假设性能确实更高,这个不可接受状态的基线将会转换为可接受状态。演化的方式有两种: 1、手工执行执行 SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => ‘SYS_SQL_xxxxxxxxxxxxx‘) From dual; 还有time_limit/verify/commit几个參数,能够參考文档 2、调优包实现基线的自己主动演化,能够理解为,启动一个调度任务,周期性的检查是否有不可接受状态的基线能够被演化
七、改动基线 能够通过dbms_spm.alter_sql_plan_baseline包来改动基线的一些属性。主要有例如以下几个属性
- ENABLED :设置该属性的值为NO告诉Oracle 11g暂时禁用某个计划,一个SQL计划必须同一时候标记为ENABLED和ACCEPTED,否则CBO将忽略它
- FIXED:设置为YES,那个计划将是优化器唯一的选择[最高优先级]。即使假设某个计划可能拥有更低的成本。这让DBA能够撤销SMB的默认行为,对于转换一个存储概要进入一稳定的SQL计划基线特别实用,注意当一个新计划被加入到被标记为FIXED的SQL计划基线,该新计划不能被利用除非它申明为FIXED状态
- AUTOPURG:设置这个属性的值为NO告诉Oracle 11g无限期保留它。从而不用操心SMB的自己主动清除机制
- plan_name : 改变SQL plan 名字
- description : 改变SQL plan描写叙述
八、迁移基线 dbms_spm提供了多个过程来在数据库之间迁移SQL计划基线
- create_stgtab_baseline创建一个计划基线保存表
- pack_stgtab_baseline将基线从数据字典拷贝到第一步的表中
- unpack_stgtab_baseline将基线从保存表中拷贝到迁移数据库的数据字典中
九、删除基线
- 能够通过dbms_SPM.drop_sql_plan_baseline包来手工删除数据字典里的基线
- 为使用的基线。fixed为no的基线,将在一定的保留期后自己主动删除(可查看dba_sql_management_config视图)
十、将一个SQL语句固定为我们期望的运行计划 我一般通过例如以下几步实现(仅供參考) 1、为这个SQL语句创建基线 2、给这个SQL语句加入hint赖宇星,确保SQL语句加入hint后的运行计划与我们期望一样 3、将第2步产生的运行计划。加入到第一步创建的基线中(注意,前面已经说过,一个SQL语句能够有多个基线。) 4、删除基线中第1步创建的那个运行计划(这样,我们就能够确保基线中仅仅有我们期望的运行计划,即保存第2步SQL语句的运行计划) 5、验证是否生效 兴许有演示样例。加深理解!
十一、演示样例(将一个SQL语句固定为我们期望的运行计划) 首先运行两个结构同样的语句,以下的实验通过SQL计划基线。将一个语句的运行计划通过还有一个语句的运行计划来固定 SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines; 未选定行 SQL> alter system flush shared_pool; 系统已更改。 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX SQL> select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,‘hh24:mi:ss‘) time 2 from v$sql a where sql_text like ‘%outlinetest%‘ and sql_text not like ‘%v$sql%‘; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:27:31 h_stat where id=711 select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711
SQL> select * from table(dbms_xplan.display_cursor(‘4vaj9fgjysy9c‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 0 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 Plan hash value: 1845196118 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 124 (100)| | |* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=711) 已选择19行。
SQL> select * from table(dbms_xplan.display_cursor(‘fm35jcmypb3qu‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID fm35jcmypb3qu, child number 0 ------------------------------------- select /* outlinetest3 */ /*+ index(dh_stat) */ * from dh_stat where id=711 Plan hash value: 2780970545 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=711) 已选择20行。
SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>‘4vaj9fgjysy9c‘, 6 plan_hash_value=>1845196118 7 ); 8 end; 9 / PL/SQL 过程已成功完毕。
SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACC ------------------------------ ------------------------------ ------------------------------------------------------- --- SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta
刚生产sql plan baseline的时候。第一次查询,无法找到运行计划,直到第二次运行的时候。才干看到,例如以下 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX SQL> select * from table(dbms_xplan.display_cursor(‘4vaj9fgjysy9c‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT --------------------------------------------------------- SQL_ID: 4vaj9fgjysy9c cannot be found SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,‘hh24:mi:ss‘) time 2 from v$sql a where sql_text like ‘%outlinetest%‘ and sql_text not like ‘%v$sql%‘; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX SQL> select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME,‘hh24:mi:ss‘) time 2 from v$sql a where sql_text like ‘%outlinetest%‘ and sql_text not like ‘%v$sql%‘;
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE TIME ------------------------------------------------------- ------------- ---------- ------------ --------------- -------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 4vaj9fgjysy9c 3823925548 0 1845196118 12:30:54 h_stat where id=711 select /* outlinetest3 */ /*+ index(dh_stat) */ * from fm35jcmypb3qu 4250242778 0 2780970545 12:27:41 dh_stat where id=711
SQL> select * from table(dbms_xplan.display_cursor(‘4vaj9fgjysy9c‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 0 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711
Plan hash value: 1845196118 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 124 (100)| | |* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("ID"=711) Note ----- - SQL plan baseline SQL_PLAN_13g6p1maja17934f41c8d used for this statement 已选择23行。
将符合我们预期的运行计划的载入到第一次生成的sql baseline中! SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( 5 sql_id=>‘fm35jcmypb3qu‘, 6 plan_hash_value=>2780970545,sql_handle=>‘SYS_SQL_11bcd50cd51504e9‘ 7 ); 8 end; 9 /
PL/SQL 过程已成功完毕。
能够看到,SYS_SQL_11bcd50cd51504e9下眼下有两个plan_name SQL> select sql_handle,plan_name,dbms_lob.substr(sql_text,60,1) sql_text,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE PLAN_NAME SQL_TEXT ACC ------------------------------ ------------------------------ ------------------------------------------------------- --- SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja1790cce5f0e select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta
SYS_SQL_11bcd50cd51504e9 SQL_PLAN_13g6p1maja17934f41c8d select /* outlinetest2 */ /*+ full(dh_stat) */ * from d YES h_sta 删除第一个plan_name。即将我们不须要的运行计划版本号去除掉。 SQL> DECLARE 2 k1 pls_integer; 3 begin 4 k1 := DBMS_SPM.drop_sql_plan_baseline ( sql_handle=>‘SYS_SQL_11bcd50cd51504e9‘,plan_name=>‘SQL_PLAN_13g6p1maja17934f41c8d‘); 5 end; 6 /
PL/SQL 过程已成功完毕。
通过以下的一部分測试,我们能够看到。新的SQL计划基线已经正常生效。及时语句中包括full提示。运行计划也走索引定位数据 SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711; ID NAME TYPE ---------- ------------------------------ --------------- 711 I_STREAMS_PROCESS_PARAMS1 INDEX
SQL> select * from table(dbms_xplan.display_cursor(‘4vaj9fgjysy9c‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 4vaj9fgjysy9c, child number 1 ------------------------------------- select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=711 Plan hash value: 2780970545 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT 本文系统来源:http://www.cnblogs.com/bhlsheji/p/4659863.html
内容总结
以上是互联网集市为您收集整理的Oracle SQL操作计划基线总结(SQL Plan Baseline)全部内容,希望文章能够帮你解决Oracle SQL操作计划基线总结(SQL Plan Baseline)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。