Oracle manual fixed SQL Execution Plan

Oracle manual fixed SQL Execution Plan

During the O & M process, we often encounter Oracle CBO selection rather than the expected execution plan due to changes in data and statistics of Oracle, the following is a record of the manual execution plan.

1. query the resources consumed by each execution plan
SQL> set lines 150 pages 999;
SQL> col p_user for 99999;
SQL> col execs for 9999999;
SQL> select. INSTANCE_NUMBER inst_id, to_char (B. expiration, 'yyyymmdd hh24: mi: ss') time, plan_hash_value, response/executions_delta get_exec, disk_reads_delta/executions_delta read_exec, response/executions_delta/1000 response, response/executions_delta/1000 response, 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 = 'bq6kas7t6x9f8'
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 46 6.744 3
1 20151125 11:00:15 3783743255. 144.666667 333333333 8.1655 46 8.42 6
1 20151125 11:00:15 1639688791 100 0 4.666 46 4.99166667 3
1 20151125 12:00:18 3783743255 104.230769 0 7.30661538 46 12.2459231 13
1 20151125 12:00:18 402930455 78 0 5.66633333 46 13 3
1 20151125 12:00:18 1639688791 127.5 0 6.9985 46 7.252 2
1 20151125 13:00:21 1639688791. 218.375671. 00017316. 886103896 46 872582251 24.1582684
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. The fixed execution plan is 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 => 'bq6kas7t6x9f8', 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_rjf6240d3b95ad6 SQL _PLAN_3d3v2839vkqqqa3c44420 YES 34

 

---- BEGIN ------- Method for deleting a fixed execution plan -------
Declare
Xx PLS_INTEGER;
BEGIN
Xx: = dbms_spm.drop_ SQL _plan_baseline (SQL _handle => 'SQL _ PLAN_3d3v2839vkqqqa3c44420', plan_name => null );
END;
/
---- Delete the fixed execution plan method END -------