控制执行计划之-SPM BASELINE（六）

baseline的怪异行为

本节会论述一些baseline的怪异行为，比如，你在test这个schema的t表的相关SQL上创建了baseline，可能会被另一个shema的t表的文本相同的SQL所用，也就是说baseline具有全局属性。还有一些其他的怪异行为，本节都会讲述到。首先我们先构建测试用例，分别在scheme为test和monitor的用户下创建了相同的表和索引。

schema test test@DLSP>create table t as select * from dba_objects;   Table created.   test@DLSP>create index t_ind on t(object_id);   Index created.   ----------收集统计信息略 schema monitor monitor@DLSP>create table t as select * from dba_objects;   Table created.   monitor@DLSP>create index t_ind on t(object_id);   Index created.   ----------收集统计信息略

baseline可以应用在不同的schema上

 

我们在schema为test上为SQL建立baseline

sys@DLSP>conn test/test Connected.   test@DLSP>select count(object_name) from t where object_id=2;   COUNT(OBJECT_NAME) ------------------                  1   test@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  1k6p0yds2rjgp, child number 0 ------------------------------------- select count(object_name) from t where object_id=2   Plan hash value: 2602990223   -------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          | |   1 |  SORT AGGREGATE              |       |     1 |    79 |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    79 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------   test@DLSP>declare   2   l_pls number;   3   begin   4     l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id          => '1k6p0yds2rjgp',   5                                                    plan_hash_value => 2602990223   6                                                    );   7   end;   8   /   PL/SQL procedure successfully completed.

这里请注意，SQL执行计划的plan_hash_value为：2602990223。我们查看是否可以在schema为monitor上使用到

test@DLSP>conn monitor/monitor Connected.   monitor@DLSP>select count(object_name) from t where object_id=2;   COUNT(OBJECT_NAME) ------------------                  1   monitor@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------SQL_ID  1k6p0yds2rjgp, child number 0 ------------------------------------- select count(object_name) from t where object_id=2   Plan hash value: 2602990223   -------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          | |   1 |  SORT AGGREGATE              |       |     1 |    79 |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    79 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - access("OBJECT_ID"=2)   Note -----    - SQL plan baseline SQL_PLAN_4ug0vh8ggw97r5a34b667 used for this statement

可以看到在scheme test上创建的baseline已经在schema monitor上使用到了，同时注意plan_hash_value没有变化。

 

索引发生变化后baseline可以继续使用

我们对原先的索引增加一列，看看是否还可以使用到之前创建的baseline。

monitor@DLSP>conn test/test Connected.   test@DLSP>drop index t_ind;   Index dropped.   test@DLSP>create index t_ind on t(object_id,object_type);   Index created.   test@DLSP>alter system flush shared_pool;   System altered.   test@DLSP>select count(object_name) from t where object_id=2;   COUNT(OBJECT_NAME) ------------------                  1   test@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  1k6p0yds2rjgp, child number 0 ------------------------------------- select count(object_name) from t where object_id=2   Plan hash value: 2602990223   -------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          | |   1 |  SORT AGGREGATE              |       |     1 |    79 |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    79 |     3   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_IND |     1 |       |     2   (0)| 00:00:01 | --------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - access("OBJECT_ID"=2)   Note -----    - SQL plan baseline SQL_PLAN_4ug0vh8ggw97r5a34b667 used for this statement

索引变化后，之前创建的baseline可以使用，同时注意plan_hash_value的值没有发生变化，依然是2602990223。我们再试试，如果把object_name增加到第一次创建的索引里会怎么样，我们查询的SQL如果不出所料的话，应该会直接在索引里就可以获取到所需要的信息，不需要回表了，这次应该plan_hash_value会发生变化。

test@DLSP>drop index t_ind;   Index dropped.   test@DLSP>create index t_ind on t(object_id,object_name);   Index created.   test@DLSP>alter system flush shared_pool;   System altered.   test@DLSP>select count(object_name) from t where object_id=2;   COUNT(OBJECT_NAME) ------------------                  1   test@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  1k6p0yds2rjgp, child number 0 ------------------------------------- select count(object_name) from t where object_id=2   Plan hash value: 468740019   --------------------------------------------------------------------------- | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |       |       |       |     2 (100)|          | |   1 |  SORT AGGREGATE   |       |     1 |    79 |            |          | |*  2 |   INDEX RANGE SCAN| T_IND |     1 |    79 |     2   (0)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - access("OBJECT_ID"=2)

我们看到baseline没有被使用到，同时plan_hash_value的值也发生了变化。这里我们不难得出结论，baseline能被使用的条件是，SQL产生的执行计划的plan_hash_value必须跟当时创建baseline时候指定的plan_hash_value值一样，否则baseline不会被使用。同理，如果我们把索引删除，或者索引名保持不变，但是索引值完全跟object_id无关，执行计划必须走全表扫描的情况下，也一定不会用到baseline，我们试试保持索引名保持不变，但是索引是依据object_type创建的情况：

test@DLSP>drop index t_ind;   Index dropped.   test@DLSP>create index t_ind on t(object_type);   Index created.   test@DLSP>select count(object_name) from t where object_id=2;   COUNT(OBJECT_NAME) ------------------                  1   test@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  1k6p0yds2rjgp, child number 0 ------------------------------------- select count(object_name) from t where object_id=2   Plan hash value: 2966233522   --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |       |       |    50 (100)|          | |   1 |  SORT AGGREGATE    |      |     1 |    79 |            |          | |*  2 |   TABLE ACCESS FULL| T    |     1 |    79 |    50   (0)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("OBJECT_ID"=2)

结果如我们猜想的一样，由于plan_hash_value发生了变化，导致baseline没有被使用到。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-1241962/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-1241962/