繫結變數、BIND PEEKING、histogram(柱狀圖)的使用

sxzhanghl發表於2009-06-07
在我們進行對oracle系統進行優化的時候,我們希望在資料中執行的語句儘量的少出現hard parse,儘量使用繫結變數,可以減少SQL分析,節約共享池的空間,減少CPU的使用,所以對於系統中未使用的繫結語句通常告訴開發人員儘量改成使用繫結變數,當where 條件上的欄位分佈均勻的時候,繫結變數可以達到比較好的效果, 但當該欄位資料分佈傾斜嚴重時,並在該欄位上收集了histogram資訊時會出現什麼情況呢?下面將一一測試

一. create table testtb, 並插入98304條值為(1,’aaa’),3條值為(2,’bbb’);

[oracle@rac1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

SQL> select count(1) from hr.testtb where id=1; 

  COUNT(1)
----------
     98304

SQL> select count(1) from hr.testtb where id=2;

  COUNT(1)
----------
         4

SQL> create index hr.testtb_ind on hr.testtb(id);    

Index created.

二.不使用繫結變數的情況:
1.當不進行分析時
SQL> Select  count(*) from hr.testtb where id=1;

  COUNT(*)
----------
     98304

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  40c2k892vm3hy, child number 0
-------------------------------------
Select  count(*) from hr.testtb where id=1

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB |   116K|  1480K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------
::走的全表掃描,是正確計劃

SQL> select count(*) from hr.testtb where id =2;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  450qy7mmbsq0j, child number 0
-------------------------------------
select count(*) from hr.testtb where id =2

Plan hash value: 3027455797

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     1 |     3 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
執行計劃走的是索引,也是正確的執行計劃。

2.分析但不生成柱狀圖資訊

SQL> alter system flush shared_pool;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100);

PL/SQL procedure successfully completed.

SQL> select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZE   NUM_ROWS
------------ ----------
15-JUL-08         98308

SQL> select count(1) from hr.testtb where id=1;

  COUNT(1)
----------
     98304
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7gsvvztdv0vhk, child number 0
-------------------------------------
select count(1) from hr.testtb where id=1

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
分析後走的是正確的全表掃描的執行計劃。

SQL> select count(1) from hr.testtb where id=2;

  COUNT(1)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  b1wjkn7rw2tn6, child number 0
-------------------------------------
select count(1) from hr.testtb where id=2

Plan hash value: 3027455797

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
執行計劃走的是索引,也是正確的執行計劃。


3.分析並生成柱狀圖資訊
SQL> alter system flush shared_pool;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');
PL/SQL procedure successfully completed.

SQL> select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZED         NUM_ROWS
------------------- ----------
2008-07-15 22:50:00      98308

SQL> select * from dba_tab_histograms where table_name='TESTTB';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
----------------------------------------------------------------------------------------------------
HR                             TESTTB
ID
          98304              1

 

OWNER                          TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
ENDPOINT_ACTUAL_VALUE
----------------------------------------------------------------------------------------------------
HR                             TESTTB
ID
          98308              2

SQL> select count(1) from hr.testtb where id=1;

  COUNT(1)
----------
     98304

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7gsvvztdv0vhk, child number 0
-------------------------------------
select count(1) from hr.testtb where id=1

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
依然走的是正確的全表掃描的執行計劃。

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  b1wjkn7rw2tn6, child number 0
-------------------------------------
select count(1) from hr.testtb where id=2

Plan hash value: 3027455797

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
依然走的是索引正確的執行計劃.

由此可以看出當表中資料傾斜很厲害的時候,並且未使用繫結變數時,無論是否存在histogram每次執行時,都走了正確的執行計劃。

三.使用繫結變數的情況:
在oracle 9i之後引入了bind peeking,在第一次分析的時候,優化器會根據繫結變數來確定執行計劃。BIND PEEKING只有當該SQL第一次執行的時候,並且在進行HARD PARSE的時候才進行,第二次呼叫該SQL,就不會再次進行BIND PEEKING,繼續使用上一次產生的執行計劃。以下為3種測試:

1. 當不進行分析時

SQL> exec dbms_stats.delete_table_stats(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE_COLUMNS=>TRUE,CASCADE_INDEXES=>TRUE);
PL/SQL procedure successfully completed.

SQL> select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZED         NUM_ROWS
------------------- ----------


SQL> var testid number;
SQL> exec :testid:=1;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
     98304

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB |   116K|  1480K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
全表掃描,對了

   2 - filter("ID"=:TESTID)

Note
-----
   - dynamic sampling used for this statement

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------


23 rows selected.

SQL> exec :testid:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |    13 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB |   116K|  1480K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
不正確-----正確應該是走索引。

2.分析但不生成柱狀圖資訊
SQL> exec dbms_stats.delete_table_stats(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE_COLUMNS=>TRUE,CASCADE_INDEXES=>TRUE);
PL/SQL procedure successfully completed.

SQL>EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'TESTTB',ESTIMATE_PERCENT=>100,CASCADE=>TRUE);       

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> /

System altered.

SQL> exec :testid:=2

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 3027455797

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=:TESTID)


19 rows selected.

SQL>
SQL>  exec :testid:=1  

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
     98304

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 3027455797

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

從上面可以看出: :testid=1時候本因走全表掃描的因為bind peeking的原因走了錯誤的執行計劃----索引

3.分析並生成柱狀圖資訊
SQL> alter system flush shared_pool;

System altered.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');


PL/SQL procedure successfully completed.

SQL> SQL>
SQL>  select last_analyzed,num_rows from dba_tables where table_name='TESTTB';

LAST_ANALYZED         NUM_ROWS
------------------- ----------
2008-07-15 23:27:49      98308

SQL> exec :testid:=1;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
     98304

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=:TESTID)


19 rows selected.

SQL> exec :testid:=2

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:testid;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  43yay36rd8vy9, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:testid

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
由上可見:生成了柱狀圖時,後面一次本應起索引的,還是走了全表掃描,可以看出在繫結變數時,柱狀圖是無效的。

另外如果SQL語句的條件使用了資料分佈傾斜嚴重的列時,並且存在對各個值要使用繫結變數進行查詢時,bind peeking可能導致產生不正確的執行計劃.這時候,我們應該注意SQL語句的寫法, 在這種情況下不要使用繫結變數,或者讓其產生幾種計劃可供自動選擇。例如:採用別名來生成另外一個執行計劃:
SQL> var testid2 number
SQL> exec :testid2:=1;

PL/SQL procedure successfully completed.

SQL>  select count(*) from hr.testtb T where T.id=:testid2;

  COUNT(*)
----------
     98304

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  76zpyb6bgab49, child number 0
-------------------------------------
 select count(*) from hr.testtb T where T.id=:testid2

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 98304 |   288K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

這時生成了正確的執行計劃:
需要大量資料時應用採用別名語句來查詢:select count(*) from hr.testtb T where T.id=:testid2
需要小量資料時應用採用別名語句來查詢:select count(*) from hr.testtb  where id=:testid
---如下:
SQL> exec :testid:=2

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb  where id=:testid;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  gmmf6n5fznrpv, child number 0
-------------------------------------
select count(*) from hr.testtb  where id=:testid

Plan hash value: 3027455797

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |            |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN| TESTTB_IND |     4 |    12 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

三:在執行的會話中關閉bind peeking 時
我們可以通過以下語句查到
select ksppinm name, kspftctxvl value, ksppdesc description,kspftctxdf DefaultV
from x$ksppi x, x$ksppcv2 y
where (x.indx = y.indx)
and ksppinm like '%peek_%'
order by name;


_optim_peek_user_binds   的9i 以後default值為TRUE

Alter session set  “_optim_peek_user_binds”=false;
之後,優化器將按照欄位的中值個數,來平均計算各個值資料的分佈情況:
欄位有20值時,oracle將認為每個值佔5%, 如果此欄位有索引的時候,將按索引查詢。值少時將進行全表掃描。此進histogram資訊也將無效。

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> var pp number;
SQL> exec :pp:=1;

PL/SQL procedure successfully completed.

SQL> exec :pp:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb TTBB where TTBB.id=:pp;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2psh0smda07hg, child number 0
-------------------------------------
select count(*) from hr.testtb TTBB where TTBB.id=:pp

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB | 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TTBB"."ID"=:PP)


19 rows selected.

SQL> alter system flush shared_pool;    

System altered.

SQL> var id_value number;
SQL> exec :id_value:=1; 

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_value;

  COUNT(*)
----------
     98304

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HR',tabname=>'TESTTB',ESTIMATE_PERCENT=>100,CASCADE=>TRUE);

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');

PL/SQL procedure successfully completed.

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> var id_value number;
SQL> exec :id_value:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_value;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4kkcyg71r92bv, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:id_value

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |  TABLE ACCESS FULL| TESTTB | 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=:ID_VALUE)


19 rows selected.

SQL> alter system flush shared_pool;

System altered.

SQL> var id_22 number;
SQL> exec :id_22:=1;   

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_22;

  COUNT(*)
----------
     98304

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7apbjnbmat3f7, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:id_22

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |  TABLE ACCESS FULL| TESTTB | 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ID"=:ID_22)


19 rows selected.

SQL> exec :id_22:=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.testtb where id=:id_22;

  COUNT(*)
----------
         4

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7apbjnbmat3f7, child number 0
-------------------------------------
select count(*) from hr.testtb where id=:id_22

Plan hash value: 1163950994

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE    |        |     1 |     3 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| TESTTB| 49154 |   144K|    44   (7)| 00:00:01 |
-----------------------------------------------------------------------------

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

相關文章