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

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心
  • 博客访问: 3502506
  • 博文数量: 718
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 7790
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(718)

文章存档

2024年(4)

2023年(74)

2022年(134)

2021年(238)

2020年(115)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(7)

2014年(4)

2013年(1)

2012年(11)

2011年(27)

2010年(35)

2009年(11)

2008年(11)

最近访客
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·

分类: oracle

2022-11-22 15:18:25

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

  1. set lines 200 pages 1000
  2. col sid for 9999
  3. col username for a11
  4. col event for a30 trunc
  5. col sql_id for a13
  6. col logon_time for a19
  7. col seconds for 99999
  8. col state for a18
  9. col blk_sid for 9999
  10. col blk_inst for 99
  11. col program for a23 trunc
  12. 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后确认内容

  1. col sql_text for a80
  2. select sql_text from v$sqltext where sql_id='&sql_id' order by piece;
看一下执行计划

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

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


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

  1. -- sql 历史

  2. set lines 200
  3. set pages 1000
  4. col shijian for a16
  5. col execu_d for 999999
  6. col bg_d for 9999999999
  7. col dr_d for 9999999999
  8. col et_d for 99999999
  9. col ct_d for 99999999
  10. col io_time for 999999
  11. col clus_time for 999999
  12. col ap_time for 999999
  13. col cc_time for 999999
  14. col et_onetime for 999999

  15. select to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi') shijian,
  16.        plan_hash_value,
  17.        round(sum(a.executions_delta),1) exec_cnt,
  18.        round(sum(a.buffer_gets_delta),1) buffer_get,
  19.        round(sum(a.disk_reads_delta),1) disk_read,
  20.        round(sum(a.elapsed_time_delta / 1000000),1) et_d,
  21.        round(sum(a.cpu_time_delta / 1000000),1) ct_d,
  22.        round(sum(iowait_delta / 1000000),1) io_time,
  23.        round(sum(clwait_delta / 1000000),1) clus_time,
  24.        round(sum(apwait_delta / 1000000),1) ap_time,
  25.        round(sum(ccwait_delta / 1000000),1) cc_time,
  26.        round(decode(sum(a.executions_delta), 0,
  27.               sum(a.buffer_gets_delta),
  28.               round(sum(a.buffer_gets_delta) / sum(a.executions_delta), 0)),1) get_onetime,
  29.        round(decode(sum(a.executions_delta), 0,
  30.               sum(a.rows_processed_delta),
  31.               round(sum(a.rows_processed_delta) / sum(a.executions_delta), 0)),1) rows_onetime,
  32.       round( decode(sum(a.executions_delta), 0,
  33.               sum(a.elapsed_time_delta / 1000),
  34.               round(sum(a.elapsed_time_delta / 1000) /
  35.                     sum(a.executions_delta), 0)),4) exec_ms
  36.   from dba_hist_sqlstat a, dba_hist_snapshot b
  37.  where a.snap_id = b.snap_id
  38.    and a.instance_number = b.instance_number
  39.    and a.sql_id = '&1'
  40.  group by to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi'),plan_hash_value
  41.  order by 1,2;

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

  1. var return_code number;
  2. exec :return_code := dbms_spm.load_plans_from_cursor_cache (sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value, fixed=>'yes', enabled=>'yes');
  3. 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;

  --删除绑定
  1. var return_code number;
  2. exec :return_code := dbms_spm.drop_sql_plan_baseline(sql_handle=>'&sql_hdl',plan_name=>'&plan_nm');
  3. print return_code

再观察。

为什么会改变,还是看cost

  1. index access i/o cost = lvls ceil(#lb * ix_sel)
  2.                       = blevel ceil(leaf_blocks * ix_sel)
  3.                       = 1 ceil(191 * (1000-2)/87482)
  4.                       = 4
  5.         
  6. table access i/o cost = ceil(cluf * ix_sel_with_filters)
  7.                       = ceil(clustering_factor * ix_sel)
  8.                       = ceil(1306 * (1000-2)/87482)

选择{banned}最佳低的。
阅读(497) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图