绑定计划-凯发app官方网站

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}最佳低的。

阅读(497) | 评论(0) | 转发(0) |

上一篇：收集4t数据库的统计信息耗时多久？

下一篇：ora-16086: redo data cannot be written to the standby redo log

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们