Oracle - SPM固定执行计划(一)

从前两步中可以看到该sql有两条执行计划，假如plan_hash_value为’2214001748’才是对的，而此时数据库选择的是另一条执行计划，我们可以通过执行以下function去将执行计划固定为我们想要的。
SQL> var temp number;
SQL> begin
:temp := dbms_spm.load_plans_from_cursor_cache(sql_id=>‘66a4184u0t6hn‘,plan_hash_value=>2214001748);
end;
/

三、做个实验

1. 准备测试表

实验环境，使用scott账号，并给scott赋予dba权限

SQL> create table test1 as select * from dba_objects;
SQL> insert into test1 select * from test1;
SQL> update test1 set object_id = 1 where rownum < (select count(*) from test1) - 10;
SQL> commit;

SQL> select object_id,count(*) from test1 group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         1     173927
     82112          1
     82121          1
     82118          1
     82119          1
     82122          1
     82113          1
     82114          1
     82120          1
     82115          1
     82116          1
     82117          1

?

2. 创建索引并收集统计信息

SQL> create index idx_test1 on test1(object_id) online;

SQL> begin
dbms_stats.gather_table_stats(ownname => ‘SCOTT‘,
tabname => ‘TEST1‘,
cascade => true,?
method_opt => ‘for columns object_id size 10‘,
no_invalidate => false);
end;
/

?

3. 通过修改优化器模式，模拟同样的sql产生两条不同的执行计划

开启一个窗口A
SQL> set autot trace
SQL> alter session set optimizer_mode = all_rows;? // 11g默认的值
SQL> select /*for_test*/ * from test1 where object_id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   173K|    15M|   693   (1)| 00:00:09 |
|*  1 |  TABLE ACCESS FULL| TEST1 |   173K|    15M|   693   (1)| 00:00:09 |
---------------------------------------------------------------------------

?

开启另一个窗口B
SQL> set autot trace
SQL> alter session set optimizer_mode = first_rows_10;
SQL> select /*for_test*/ * from test1 where object_id = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2214001748

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    11 |  1056 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1     |    11 |  1056 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST1 |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

?

再开启一个窗口C
SQL> select sql_id,sql_text,optimizer_mode,plan_hash_value,child_number from v$sql where sql_text like ‘select /*for_test*/ * from test1%‘;

SQL_ID        SQL_TEXT                                                OPTIMIZER_ PLAN_HASH_VALUE CHILD_NUMBER
------------- ------------------------------------------------------- ---------- --------------- ------------
66a4184u0t6hn select /*for_test*/ * from test1 where object_id = 1    ALL_ROWS        4122059633            0
66a4184u0t6hn select /*for_test*/ * from test1 where object_id = 1    FIRST_ROWS      2214001748            1

?

可以看到，因为优化器模式的不同，相同的sql产生了两条截然不同的执行计划
当optimizer_mode = all_rows为全表扫描，当optimizer_mode = first_rows_10为索引扫描

?

4. 绑定执行计划

（编辑：吕梁站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!