控制執行計劃之-SPM BASELINE（六）

wei-xh發表於2014-07-30

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章