在运维过程中，我们经常会遇到Oracle由于数据、统计信息等的变动，导致Oracle CBO选择不是我们期望的执行计划的情况，下面记录一下手动固定执行计划的过程。
1、查询每个执行计划消耗的资源情况
SQL> set lines 150 pages 999;
SQL> col p_user for 99999 ;
SQL> col execs for 9999999 ;
SQL> select a.INSTANCE_NUMBER inst_id,to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24:mi:ss') time,plan_hash_value,buffer_gets_delta/executions_delta get_exec, disk_reads_delta/executions_delta read_exec,cpu_time_delta/executions_delta/1000 cpu_exec_ms ,elapsed_time_delta/executions_delta/1000 elaps_exec_ms ,parsing_schema_id p_user,ROWS_PROCESSED_delta/executions_delta rows_exec,EXECUTIONS_DELTA execs
2 from dba_hist_sqlstat a, dba_hist_snapshot b
3 where a.sql_id='bq6kas7t6x9vh'
4 and a.snap_id = b.snap_id
5 and a.instance_number = b.instance_number
6 and b.END_INTERVAL_TIME between sysdate - 3 and sysdate
7 and executions_delta>0 order by 2,1;
------------------------Created by Tangyun[Tony.Tang]------------------------
INST_ID TIME PLAN_HASH_VALUE GET_EXEC READ_EXEC CPU_EXEC_MS ELAPS_EXEC_MS P_USER ROWS_EXEC EXECS
---------- ----------------- --------------- ---------- ---------- ----------- ------------- ------ ---------- --------
1 20151125 11:00:15 402930455 133 0 6.33266667 6.744 46 19.3333333 3
1 20151125 11:00:15 3783743255 144.666667 .333333333 8.1655 8.42 46 23.8333333 6
1 20151125 11:00:15 1639688791 100 0 4.666 4.99166667 46 13.6666667 3
1 20151125 12:00:18 3783743255 104.230769 0 7.30661538 12.2459231 46 18.9230769 13
1 20151125 12:00:18 402930455 78 0 5.66633333 8.95633333 46 13 3
1 20151125 12:00:18 1639688791 127.5 0 6.9985 7.252 46 11.5 2
1 20151125 13:00:21 1639688791 218.375671 .00017316 .886103896 .872582251 46 24.1582684 11550
1 20151125 14:00:23 1639688791 211.849579 0 .842681107 .83075722 46 22.2030686 6648
1 20151125 15:00:26 1639688791 221.616694 0 .915307131 .903271745 46 26.3946515 3702
9 rows selected.
------------------------Created by Tangyun[Tony.Tang]------------------------
2、固定执行计划为 1639688791。
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;
no rows selected
SQL> var n number
SQL> begin
2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'bq6kas7t6x9vh', plan_hash_value=>1639688791, fixed =>'YES', enabled=>'YES');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines
SQL_HANDLE PLAN_NAME ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
QL_368f6240d3b95ad6 SQL_PLAN_3d3v2839vkqqqa3c44420 YES YES 34
－－－－删除固定执行计划的方法BEGIN－－－－－－－
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_PLAN_3d3v2839vkqqqa3c44420',plan_name=>null);
END;
/
－－－－删除固定执行计划的方法END－－－－－－－