测试oracle 11g固定执行计划-Baseline(二)
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 := '&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 / www.2cto.com
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)
www.2cto.com
PL/SQL procedure successfully complete
[html]
查看生成的sql baseline信息：
[html]
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
四,下面我们来验证是否生效：
[html]
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 www.2cto.com
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
---------------------------------------------------------------------------------------------
[html]
Plan hash value: 2317224448
[html]
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 |