Oracle手動固定SQL執行計劃
在運維過程中,我們經常會遇到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-------
------------------------Created by Tangyun[Tony.Tang] 2016.03------------------------
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-------
------------------------Created by Tangyun[Tony.Tang] 2016.03------------------------
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24930246/viewspace-2058675/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- oracle 固定執行計劃Oracle
- oracle固定執行計劃--sqlprofileOracleSQL
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- oracle sqlprofile 固定執行計劃,並遷移執行計劃OracleSQL
- 【SPM】Oracle如何固定執行計劃Oracle
- baseline固定SQL執行計劃SQL
- 用sql profile來固定執行計劃SQL
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- SQL BASELINE修改固定執行計劃SQL
- Oracle sql執行計劃OracleSQL
- 使用coe_xfr_sql_profile固定執行計劃SQL
- Oracle緊急固定執行計劃之手段Oracle
- oracle使用outline固定執行計劃事例Oracle
- Oracle如何手動重新整理執行計劃Oracle
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- Oracle中檢視已執行sql的執行計劃OracleSQL
- Oracle 獲取SQL執行計劃方法OracleSQL
- Oracle 檢視SQL的執行計劃OracleSQL
- 使用OUTLINE固定執行計劃
- sql 執行計劃SQL
- 使用SPM和STA進行固定執行計劃
- oracle中跟蹤sql執行計劃的方法OracleSQL
- 用outline修改固定執行計劃
- Oracle優化案例-view merge與coe_load_sql_profile固定執行計劃(十五)Oracle優化ViewSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- ORACLE執行計劃Oracle
- zt_sql baseline_sql profile_sql outline修改固定執行計劃SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- 多種方法檢視Oracle SQL執行計劃OracleSQL
- oracle10g 檢視SQL執行計劃OracleSQL
- Oracle優化案例-coe_xfr_sql_profile固定執行計劃與刪除profile(二十五)Oracle優化SQL
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL