绑定计划

发现sql变慢
{BANNED}中国第一步确定sql

set lines 200 pages 1000
col sid for 9999
col username for a11
col event for a30 trunc
col sql_id for a13
col logon_time for a19
col seconds for 99999
col state for a18
col blk_sid for 9999
col blk_inst for 99
col program for a23 trunc
SELECT sid, username,state, event, sql_id, FINAL_BLOCKING_INSTANCE blk_inst, FINAL_BLOCKING_SESSION blk_sid, program, to_char(logon_time, 'yy-mm-dd hh24:mi:ss') logon_time, SECONDS_IN_WAIT seconds, WAIT_TIME, LAST_CALL_ET FROM V$SESSION WHERE (WAIT_CLASS <> 'Idle' or state <> 'WAITING') and SID <> sys_context('userenv','sid') and rownum < 51 ORDER BY SECONDS_IN_WAIT DESC, event;

找到sql_id后确认内容

col sql_text for a80
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;

看一下执行计划

select distinct sql_id,plan_hash_value from v$sql where sql_id='&sql_id';

set linesize 180 pagesize 1000
col column_name for a30 trunc
select * from table(dbms_xplan.display_cursor('&sql_id',null,'advanced'));

如果不对，检查当前其他执行计划

-- sql 历史

set lines 200
set pages 1000
col shijian for a16
col execu_d for 999999
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999

select to_char(b.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi') shijian,
       plan_hash_value,
       round(sum(a.EXECUTIONS_DELTA),1) exec_cnt,
       round(sum(a.BUFFER_GETS_DELTA),1) buffer_get,
       round(sum(a.DISK_READS_DELTA),1) disk_read,
       round(sum(a.ELAPSED_TIME_DELTA / 1000000),1) et_d,
       round(sum(a.CPU_TIME_DELTA / 1000000),1) ct_d,
       round(sum(IOWAIT_DELTA / 1000000),1) io_time,
       round(sum(CLWAIT_DELTA / 1000000),1) clus_time,
       round(sum(APWAIT_DELTA / 1000000),1) ap_time,
       round(sum(ccwait_delta / 1000000),1) cc_time,
       round(decode(sum(a.EXECUTIONS_DELTA), 0,
              sum(a.BUFFER_GETS_DELTA),
              round(sum(a.BUFFER_GETS_DELTA) / sum(a.EXECUTIONS_DELTA), 0)),1) get_onetime,
       round(decode(sum(a.EXECUTIONS_DELTA), 0,
              sum(a.rows_processed_delta),
              round(sum(a.rows_processed_delta) / sum(a.EXECUTIONS_DELTA), 0)),1) rows_onetime,
      round( decode(sum(a.EXECUTIONS_DELTA), 0,
              sum(a.ELAPSED_TIME_DELTA / 1000),
              round(sum(a.ELAPSED_TIME_DELTA / 1000) /
                    sum(a.EXECUTIONS_DELTA), 0)),4) exec_ms
  from dba_hist_sqlstat a, dba_hist_snapshot b
 where a.SNAP_ID = b.SNAP_ID
   and a.INSTANCE_NUMBER = b.INSTANCE_NUMBER
   and a.sql_id = '&1'
 group by to_char(b.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi'),plan_hash_value
 order by 1,2;

找个{BANNED}最佳佳的绑定上

var return_code number;
exec :return_code := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value, fixed=>'YES', enabled=>'YES');
print return_code

 set lin 200 pages 1000
 col SQL_HANDLE for a25
 col PLAN_NAME for a32
 col SQL_TEXT for a60
 select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines;

  --删除绑定

var return_code number;
exec :return_code := dbms_spm.drop_sql_plan_baseline(sql_handle=>'&sql_hdl',plan_name=>'&plan_nm');
print return_code

再观察。

为什么会改变，还是看cost

Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)
                      = BLEVEL + CEIL(LEAF_BLOCKS * ix_sel)
                      = 1 + CEIL(191 * (1000-2)/87482)
                      = 4

Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)
                      = CEIL(CLUSTERING_FACTOR * ix_sel)
                      = CEIL(1306 * (1000-2)/87482)

选择{BANNED}最佳低的。