Test oracle 11g fixed execution plan-Baseline

Test oracle 11g fixed execution plan-Baseline
1. Select SQL _id [html] SQL> pagesize 300 SQL> set linesize 300 SQL> set autotrace on SQL> var name varchar2 (10); SQL> exec: name: = 'it'; select department_name from hr. orders ments dept where department_id in (select department_id from hr. employees emp) and department_name =: name; www.2cto.com DEPARTMENT_NAME ------------------------------ IT 1 rows selected. execution Plan -------------------------------- -------------------------- Plan hash value: 2605691773 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | STATEMENT | 0 | select statement | 1 | 19 | 3 (0) | 00:00:01 | 1 | nested loops semi | 1 | 19 | 3 (0) | 00:00:01 | * 2 | table access full | ments | 1 | 16 | 3 (0) | 00:00:01 | * 3 | index range scan | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0) | 00:00:01 | www.2cto.com Predicate Information (identified by operation id): --------------------------------------------------- 2-filter ("DEPARTMENT_NAME" =: NAME) 3-access ("D EPARTMENT_ID "=" DEPARTMENT_ID ") SQL> select SQL _id from v $ SQL where SQL _fulltext like 'select department_name % '; SQL _ID ------------- bd8mzf35svfm3 the current SQL Execution Plan is the case, assuming that we want to use other execution plans (full table scan emp) to replace the above execution plan efficiency, and use the SQL plan baseline in oracle 11g to implement fixed SQL statements.
2. Add an Hint SQL statement: [html] select department_name from hr. departments dept where department_id in (select/* + FULL (emp) */department_id from hr. employees emp) and department_name =: name; www.2cto.com Execution Plan ---------------------------------------------------- Plan hash value: 2317224448 bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------------------------------------------- | 0 | select statement | 1 | 19 | 7 (15) | 00:00:01 | * 1 | hash join semi | 1 | 19 | 7 (15) | 00:00:01 | * 2 | table access full | ments | 1 | 16 | 3 (0) | 00:00:01 | 3 | table access full | EMPLOYEES | 107 | 321 | 3 (0) | 00:00:01 | --------------------------------------- --------------------------------------- Predicate Information (identified by operation id): descri1-access ("DEPARTMENT_ID" = "DEPARTMENT_ID") 2-filter ("DEPARTMENT_NAME" =: NAME) SQL> select SQL _id from v $ SQL _plan where plan_hash_value = 2317224448; SQL _ID ------------- 5kuqnnugsrhj3 www.2cto.com this plan SQL _id: 5kuqnnugsrhj3 hash_value: 2317224448 III. Now assume that The SQL statement in use is not Hint, and SQL _id is 5kuqnnugsrhj3, and its execution plan is not optimal. Now we want to fix it with plan hash: 2317224448. The procedure is as follows: [html] SQL> set serveroutput on; SQL> declare 2 v_clob clob; 3 v_ SQL _id varchar2 (13); 4 v_plan_hash_value number; 5 v_fixed varchar2 (3 ); 6 v_enabled varchar2 (3); 7 begin www.2cto.com 8 v_ SQL _id: = '& hint_ SQL _id'; 9 v_plan_hash_value: = to_number ('& hint_plan_hash_value'); 10 v_fixed: = '& ED fix '; 11 v_enabled: = '& enabled'; 12 select SQL _fulltext into v_clob 13 from v $ SQL 14 where SQL _id = 'bd8mzf35svfm3' 15 and child_number = 0; 16 dbms_output.put_line (v_clob ); 17 dbms_output.put_line (18 rows (19 SQL _id => v_ SQL _id, 20 plan_hash_value => v_plan_hash_value, 21 SQL _text => v_clob, 22 fixed => v_fixed, 23 enabled => v_enabled )); 24 end; 25/www.2cto.com Enter value for hint_ SQL _id: Too old 8: v_ SQL _id: = '& hint_ SQL _id'; new 8: v_ SQL _id: = 'hangzhou'; Enter value for hint_plan_hash_value: 2317224448 old 9: v_plan_hash_value: = to_number ('& hint_plan_hash_value'); new 9: v_plan_hash_value: = to_number ('123'); Enter value for fixed: YES old 10: v_fixed: = '& fixed'; new 10: v_fixed: = 'yes'; Enter value for enabled: YES old 11: v_enabled: =' & enabled'; new 11: v_enabled: = 'yes'; select department_name from hr. orders ments dept where department_id in (select department_id from hr. employees emp) www.2cto.com PL/SQL procedure successfully complete [html] view the generated SQL baseline information: [html] <pre class = "html" name = "code"> select signature, SQL _handle, plan_name, origin, enabled, accepted, fixed, autopurge from dba_ SQL _plan_baselines where SQL _text like 'select department_name from hr. parameters dept % '; SIGNATURE SQL _HANDLE PLAN_NAME origin ena acc fix aut ---------- -------------------------------- ~-------------- --- 3.0216E + 17 SYS_ SQL _c9bc6fc0e997f27c SQL _PLAN_cmg3gs3ntgwmwec845e1a MANUAL-LOAD YES
4. Let's verify if it takes effect: [html] <pre class = "html" name = "code"> 1 * select SQL _text from dba_ SQL _plan_baselines where SQL _handle = 'sys _ parallel' SQL>/SQL _TEXT explain select department_name from hr. orders ments dept where department_id in (select department_id from hr. employees emp) and department_name =: name www.2cto.com SQL> explain plan for 2 select department_name 3 from hr. orders ments dept 4 where department_id in (select department_id from hr. employees emp) 5 and department_name =: name; Explained. SQL> select * from table (dbms_xplan.display (); PLAN_TABLE_OUTPUT partition [html] Plan hash value: 2317224448 [html] Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 19 | 7 (15) | 00:00:01 | * 1 | hash join semi | 1 | 19 | 7 (15) | 00:00:01 | * 2 | table access full | ments | 1 | 16 | 3 (0) | 00:00:01 | 3 | table access full | EMPLOYEES | 107 | 321 | 3 (0) | 00:00:01 | identified Predicate Information (identified by operation id ): www.2cto.com 1-access ("DEPARTMENT_ID" = "DEPARTMENT_ID") 2-filter ("DEPARTMENT_NAME" =: NAME) Note ---SQL plan baseline used for this statement author Coast_lichao