OracleSQL执行计划基线总结（SQLPlanBaseline） - 数据库编程

.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描述语法： SET SERVEROUTPUT ON DECLARE v_text PLS_INTEGER; BEGIN v_text := DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_xxxxxx',plan_name => 'SYS_SQL_PLAN_xxxxxxxxx', attribute_name => 'fixed',attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || v_text ); END; /

八、迁移基线 dbms_spm提供了多个过程来在数据库之间迁移SQL计划基线 create_stgtab_baseline创建一个计划基线保存表pack_stgtab_baseline将基线从数据字典复制到第一步的表中unpack_stgtab_baseline将基线从保存表中复制到迁移数据库的数据字典中大概过程如下： 1、创建一张保存数据字典中基线表内容的用户表 exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT',tablespace_name =>''); 2、将数据字典中基线表的内容插入到第一步创建的用户表中 exec :i := dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TEST', table_owner => 'SCOTT'); 备注：可以支持多种方式插入,例如包含特定字符的SQL相关的基线，sql_handle来精确识别一个基线，具体见文档 3、通过迁移工具迁移用户表 exp/imp or expdp/impdp 4、将迁移过来的用户表中保存的基线内容插入到当前库的数据字典中，从而实现迁移 exec :i := dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TEST',table_owner => 'SCOTT'); 备注：可以支持多种方式，与步骤2一样，具体见文档

九、删除基线可以通过dbms_SPM.drop_sql_plan_baseline包来手工删除数据字典里的基线为使用的基线，fixed为no的基线，将在一定的保留期后自动删除(可查看dba_sql_management_config视图）手工删除方法如下 SET SERVEROUTPUT ON DECLARE v_text PLS_INTEGER; BEGIN v_text := DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_7b76323ad90440b9',plan_name => NULL); DBMS_OUTPUT.put_line(v_text); END; /

十、将一个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','',''))

OracleSQL执行计划基线总结（SQLPlanBaseline）(二)