测试oracle 11g 固定执行计划-Baseline
一，选定sql_id
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.departments dept
where department_id in (select department_id from hr.employees emp)
and department_name=:name;
DEPARTMENT_NAME
------------------------------
IT
1 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2605691773
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 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| DEPARTMENTS | 1 | 16 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("DEPARTMENT_NAME"=:NAME)
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
SQL> select sql_id from v$sql where sql_fulltext like'select department_name%';
SQL_ID
-------------
bd8mzf35svfm3
上面的sql现在的执行计划是情况，假设如上的执行计划效率底下，我们想用其它的执行计划(全表扫描emp)来代替它，并用oracle 11g中的sql plan baseline来固定次SQL的执行。
二,添加Hint的sql：
select department_name
from hr.departments dept
where department_id in (select/*+FULL(emp)*/ department_id from hr.employees emp)
and department_name=:name;
Execution Plan
----------------------------------------------------------
Plan hash value: 2317224448
----------------------------------------------------------------------------------
| 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| DEPARTMENTS | 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):
---------------------------------------------------
1 - 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
此计划sql_id:5kuqnnugsrhj3 hash_value:2317224448
三,那么现在假设应用中的sql是未添加Hint的，sql_id为5kuqnnugsrhj3，并且它的执行计划不是最优的，现在我们想用plan hash:2317224448来固定它。
操作如下：
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
8 v_sql_id := '&hint_sql_id';
9 v_plan_hash_value := to_number('&hint_plan_hash_value');
10 v_fixed := '&fixed';
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 dbms_spm.load_plans_from_cursor_cache(
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 /
Enter value for hint_sql_id: b6k9pwv7pw0s0
old 8: v_sql_id := '&hint_sql_id';
new 8: v_sql_id := 'b6k9pwv7pw0s0';
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('2317224448');
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.departments dept
where department_id in (select
department_id from hr.employees emp)
PL/SQL procedure successfully complete
查看生成的sql baseline信息：
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.departments dept%'; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX AUT ---------- ------------------------------ ------------------------------ -------------- --- --- --- --- 3.0216E+17 SYS_SQL_c9bc6fc0e997f27c SQL_PLAN_cmg3gs3ntgwmwec845e1a MANUAL-LOAD YES YES YES YES四,下面我们来验证是否生效：
1* select sql_text from dba_sql_plan_baselines where sql_handle='SYS_SQL_c9bc6fc0e997f27c' SQL> / SQL_TEXT -------------------------------------------------------------------------------- select department_name from hr.departments dept where department_id in (select department_id from hr.employees emp) and department_name=:name SQL> explain plan for 2 select department_name 3 from hr.departments 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 ---------------------------------------------------------------------------------------------Plan hash value: 2317224448---------------------------------------------------------------------------------- | 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| DEPARTMENTS | 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): --------------------------------------------------- 1 - access("DEPARTMENT_ID"="DEPARTMENT_ID") 2 - filter("DEPARTMENT_NAME"=:NAME) Note —– - SQL plan baseline SYS_SQL_c9bc6fc0e997f27c used for this statement