原有2个sql语句有多个表连接,执行计划一直在走错误的执行计划.表级统计信息以及索引规划都已经是最新(这里统计信息有狗血不做描述),只是SQL里还有六个绑定变量以及字段的柱状图影响了执行计划,在这个优化里没有删除柱状图和对绑定变量的影响进行处理(星形连接不建议使用绑定变量),现场环境微妙最终选择通过sql profile以及spm对这2个sql的执行计划进行固定处理.先用sqlprofile固定后让sql重新解析后发现未能生效,逐用spm的方式固定.
这里以其中一个sql_id为bwwnw7r1gzhdf的语句为例,这是收集到对应1个小时内的sqlrpt,其中plan_hash_value为711942702执行计划为正确的执行计划,从报告中可以看到这个sql选择了错误的执行计划,并且从中也可以看到sql有多个执行计划.当中执行计划正确与否的判断方式就不做描述.
SQL ID: bwwnw7r1gzhdf
- 1st Capture and Last Capture Snap IDs refer to Snapshot IDs witin the snapshot range
- SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, ‘YYYY…
| # | Plan Hash Value | Total Elapsed Time(ms) | Executions | 1st Capture Snap ID | Last Capture Snap ID |
|---|---|---|---|---|---|
| 1 | 3052678239 | 13,512,877 | 10 | 25060 | 25060 |
| 2 | 3392573872 | 0 | 0 | 25060 | 25060 |
| 3 | 4134955434 | 0 | 0 | 25060 | 25060 |
| 4 | 1564064893 | 0 | 0 | 25060 | 25060 |
| 5 | 2504448979 | 0 | 0 | 25060 | 25060 |
| 6 | 147966509 | 0 | 0 | 25060 | 25060 |
| 7 | 711942702 | 0 | 0 | 25060 | 25060 |
通过coe_xfr_sql_profile.sql脚本对bwwnw7r1gzhdf的sql进行固定711942702,生成sql profile的名字为coe_bwwnw7r1gzhdf_711942702.
(该部分可以参考
1.Using Sqltxplain to create a ‘SQL Profile’ to consistently reproduce a good plan (文档 ID 1487302.1)
2.Automatic SQL Tuning and SQL Profiles (文档 ID 271196.1)
3.Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (文档 ID 1955195.1))
让sql从新解析后从v$sql视图中的sql profile字段没有看到生效的迹象，原因是在脚本coe_xfr_sql_profile.sql中对创建的sqlprofile默认的生效是false的，所以创建出来的profile不会失效,监控中的执行计划未变（现场我对此处的profile drop）.
SQL> select name,created,status from dba_sql_profiles; NAME CREATED STATUS ------------------------------ ------------------------------ -------- coe_bwwnw7r1gzhdf_711942702 26-JUN-15 02.09.30.000000 PM ENABLED coe_g87an0j5djjpm_334801256 26-JUN-15 11.30.25.000000 AM ENABLED SQL> select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='bwwnw7r1gzhdf' and sql_profile is not null; no rows SQL> select sql_profile,EXECUTIONS,PLAN_HASH_VALUE,parse_calls,ELAPSED_TIME/1000000, ELAPSED_TIME/1000000/EXECUTIONS,LAST_LOAD_TIME,ROWS_PROCESSED from v$sql where EXECUTIONS>0 and sql_id='bwwnw7r1gzhdf' order by LAST_LOAD_TIME desc; ...
逐对profile进行disable并drop
=====disable profile============== BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'coe_bwwnw7r1gzhdf_711942702', attribute_name => 'STATUS', value => 'DISABLED'); END; / BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE( name => 'coe_g87an0j5djjpm_334801256', attribute_name => 'STATUS', value => 'ENABLED'); END; / =====drop profile================= begin DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_bwwnw7r1gzhdf_711942702'); end; / begin DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_g87an0j5djjpm_334801256'); end; /
由于已经存在了正确的执行计划,所以通过DBMS_SPM直接创建baseline,并通过DBMS_SPM包对该sql的baseline的enable,accept,fixed三个属性指定为yes.
该部分可以参考:
Plan Stability Features (Including SQL Plan Management (SPM)) (文档 ID 1359841.1)
为sql创建baseline
variable cnt number; execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702) ;
验证该baseline已经生成
SQL> set linesize 200 SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text From Dba_Sql_Plan_Baselines Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX OPTIMIZER_COST SQL_TEXT ------------------------------ ------------------------------ -------------- --- --- --- -------------- -------------------------------------------------------------------------------- SQL_995463d3d1edd710 SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD YES YES NO 69 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2
为sqlbaseline的fixed属性改为yes
variable cnt number; execute :cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID => 'bwwnw7r1gzhdf', PLAN_HASH_VALUE => 711942702,fixed => 'yes') ; 验证修改完成 SQL> set linesize 200 SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text 2 From Dba_Sql_Plan_Baselines 3 Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX OPTIMIZER_COST SQL_TEXT ------------------------------ ------------------------------ -------------- --- --- --- -------------- -------------------------------------------------------------------------------- SQL_995463d3d1edd710 SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD YES YES YES 574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2
最终验证生效
SQL> Select Sql_Handle, Plan_Name, Origin, Enabled, Accepted,Fixed,Optimizer_Cost,Sql_Text 2 From Dba_Sql_Plan_Baselines 3 Where Sql_Text Like '%FROM P1EDBADM.MES_PROCESSOPERATIONSPEC%' Order By Last_Modified; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX OPTIMIZER_COST SQL_TEXT ------------------------------ ------------------------------ -------------- --- --- --- -------------- -------------------------------------------------------------------------------- SQL_995463d3d1edd710 SQL_PLAN_9kp33ug8yvpsh4af503b5 MANUAL-LOAD YES YES YES 574 SELECT D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(D.CREATETIME, 'YYYY-MM-DD HH2 SQL_2e1c8025edb165b3 SQL_PLAN_2w7404rqv2tdm56eb6fa8 MANUAL-LOAD YES YES YES 311 SELECT 1 " ", D.LOTNAME LOT, D.PRODUCTNAME GLASS, TO_CHAR(MAX(H.EVENTTIME), 'YYY
SPM主要和2个参数有关,一个是baseline生效(optimizer_user_sql_plan_baselines,前提是accept属性要为yes,否则会产生干扰),一个是捕获sql语句生成baseline(optimizer_cature_sql_plan_baselines).在数据库中我一般不开启捕获,但是开启baseline生效.
开启的语法:
alter system set optimizer_user_sql_plan_baselines=true scope=both; alter system set optimizer_cature_sql_plan_baselines=true scope=both;
关闭的语法:
alter system set optimizer_user_sql_plan_baselines=false scope=both; alter system set optimizer_cature_sql_plan_baselines=false scope=both;
开启捕获的情况在一些11g版本中会触发该bug
Bug 9910484 – SQL Plan Management Capture uses excessive space in SYSAUX (文档 ID 9910484.8)
此bug会造成sysaux的表空间暴增,主要为sqllob$data,我遇见的是在一天内从2g增长到4g.关闭了捕获后,该现象消失.
删除掉不必要的baseline后可以通过shrink的方式回收sysaux的空间,具体可以参考
Reducing the Space Usage of the SQL Management Base in the SYSAUX Tablespace (文档 ID 1499542.1)
Pingback: 数据库上线争议参数修改集绵 | Ludatou's data life