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博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-1241962/ ，如需转载，请注明出处，否则将追究法律责任。
