繫結變數窺視測試案例

yingyifeng306發表於2021-06-17
變數

 

我們平時在sql中經常使用捆綁變數來使sql在shared pool中可以被重用，大大減少了sql的hard parse。oracle在hard parse一個sql的時候，如果收集了histogram的資訊，如果隱藏引數_optim_peek_user_binds被設定成true(default to true),

那麼cbo會去偷窺一下捆綁變數的值，來選擇一個更好的執行計劃。當然，捆綁變數的偷窺只發生在hard parse。


前期測試表格建立

CREATE TABLE   TEST_20150518 AS SELECT * FROM DBA_OBJECTS;

select count(*)   from TEST_20150518;

update   TEST_20150518 set status ='VALID';

commit;

select count(*)   from TEST_20150518 where status='VALID';

update   TEST_20150518 set status='INVALID' where rownum<=100;

commit;

update   TEST_20150518 set STATUS='TEST' WHERE ROWNUM<=3;

commit;

 

SQL> select   count(*),status from TEST_20150518 group by status;

  COUNT(*) STATUS

----------   -------

        97 INVALID

         3 TEST

     50112 VALID

create index   idx_TEST_20150518 on TEST_20150518(status);

統計資訊包括柱狀圖收集

EXEC   DBMS_STATS.GATHER_TABLE_STATS   (ownname=>'sys',tabname=>'TEST_20150518',method_opt=>'FOR ALL   INDEXED COLUMNS SIZE AUTO',estimate_percent=>100,cascade=>true);

從以上可以看出TEST_20150518表中status='VALID'的有50112行，status='INVALID'的有97行，status='TEST'的有三行，如果select * from TEST_20150518

where status='VALID', 那麼必定是用全表掃描比較好，如果select * from TEST_20150518 where status='INVALID' 或者select * from TEST_20150518 where status='TEST'那麼必定是走索引比較好。

 

檢視柱狀圖的分佈情況

SQL> set line   150

SQL> col owner   for a10

SQL> col   table_name for a15

SQL> col   column_name for a15

SQL> col   ENDPOINT_ACTUAL_VALUE for a30

SQL> SELECT *   FROM DBA_HISTOGRAMS WHERE table_name='TEST_20150518' and   column_name='STATUS';

 

OWNER      TABLE_NAME      COLUMN_NAME     ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE

----------   --------------- --------------- --------------- --------------   ------------------------------

SYS        TEST_20150518   STATUS                       97     3.8063E+35

SYS        TEST_20150518   STATUS                      100     4.3756E+35

SYS        TEST_20150518   STATUS                    50212     4.4786E+35

histogram 裡三個bucket明確表明了該列資料分佈情況.

 


  

繫結變數窺視測試

var test   varchar2(10);

exec   :test:='VALID';

select   object_id,status FROM TEST_20150518 where status=:test;

SQL> select   sql_id,CHILD_NUMBER from v$sql where sql_text like '%select object_id,status   FROM TEST_20150518 where status%';

SQL_ID        CHILD_NUMBER

-------------   ------------

82hsrmdvg3vna            0

select * from   table(dbms_xplan.display_cursor('82hsrmdvg3vna',0,'ADVANCED'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------

SQL_ID  82hsrmdvg3vna, child number 0

-------------------------------------

select   object_id,status FROM TEST_20150518 where status=:test

 

Plan hash value:   2123920744

-----------------------------------------------------------------------------------

| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |               |       |         |   156 (100)|          |

|*  1 |  TABLE ACCESS FULL|   TEST_20150518 | 50112 |   587K|   156     (2)| 00:00:02 |

Peeked Binds   (identified by position):

--------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

 

   1 - :TEST (VARCHAR2(30), CSID=852):   'VALID'

 

可以看到，第一次硬解析，status=“valid” 時，走了正確的執行計劃

 

exec   :test:='INVALID';

select   object_id,status FROM TEST_20150518 where status=:test;

SQL> select   sql_id,CHILD_NUMBER from v$sql where sql_text like '%select object_id,status   FROM TEST_20150518 where status%';

SQL_ID        CHILD_NUMBER

-------------   ------------

82hsrmdvg3vna            0

select * from   table(dbms_xplan.display_cursor('82hsrmdvg3vna',0,'ADVANCED'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------

SQL_ID  82hsrmdvg3vna, child number 0

-------------------------------------

select   object_id,status FROM TEST_20150518 where status=:test

 

Plan hash value:   2123920744

-----------------------------------------------------------------------------------

| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |               |       |         |   156 (100)|          |

|*  1 |  TABLE ACCESS FULL|   TEST_20150518 | 50112 |   587K|   156     (2)| 00:00:02 |

Peeked Binds   (identified by position):

--------------------------------------

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

 

   1 - :TEST (VARCHAR2(30), CSID=852):   'VALID'

可以看到，雖然繫結變數的值變了，但是oracle沒有硬解析，沒有重新偷窺繫結變數，還是走了錯誤的全表掃描。

 

重新整理共享池，使得語句重新硬解析

alter system   flush shared_pool;

讓 sql 重新被硬解析

exec   :test:='INVALID';

select   object_id,status FROM TEST_20150518 where status=:test;

select   sql_id,CHILD_NUMBER,FIRST_LOAD_TIME,last_load_time,LAST_ACTIVE_TIME from   v$sql where sql_text like '%select object_id,status FROM TEST_20150518 where   status%';

SQL_ID        CHILD_NUMBER FIRST_LOAD_TIME                        LAST_LOAD_TIME                         LAST_ACTI

-------------   ------------ --------------------------------------   -------------------------------------- ---------

d78upf6d4hpqw            0 2015-05-18/13:21:27                    2015-05-18/13:21:27                    18-MAY-15

82hsrmdvg3vna            0 2015-05-18/13:21:21                    2015-05-18/13:21:21                    18-MAY-15

 

select * from   table(dbms_xplan.display_cursor('82hsrmdvg3vna',0,'ADVANCED'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------

SQL_ID  82hsrmdvg3vna, child number 0

-------------------------------------

select   object_id,status FROM TEST_20150518 where status=:test;

 

Plan hash value:   498082260

-------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |       |         |     3 (100)|          |

|   1 |    TABLE ACCESS BY INDEX ROWID| TEST_20150518     |      97 |  1164 |     3     (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_20150518 |    97 |         |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Peeked Binds   (identified by position):

--------------------------------------

 

   1 - :TEST (VARCHAR2(30), CSID=852):   'INVALID'

可以看到，sql被重新hard parse後，走了正確的索引路線

 


  

解決辦法

讓sql在shared pool裡失效，使用DBMS_SHARED_POOL.PURGE，或者alter system flush shared pool.　當然，後兩者不推薦，尤其是最後一個！！

不過，oracle 11g裡可以動態偷窺繫結變數，放多個執行計劃在shared pool,　對這個副作用做了很大改進。具體新特性可以去看11g文件。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23732248/viewspace-2777117/，如需轉載，請註明出處，否則將追究法律責任。

相關文章