繫結變數、BIND PEEKING、histogram(柱狀圖)的使用(zt)
一. 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/82387/viewspace-1022014/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數、BIND PEEKING、histogram(柱狀圖)的使用變數Histogram
- 繫結變數和BIND PEEKING變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- oracle bind value peeking繫結變數窺視Oracle變數
- 繫結變數的正確使用，bind peeking，Adaptive Cursor Sharing變數APT
- 從不繫結變數與繫結變數兩種情況討論柱狀圖的作用變數
- Event 10053 執行計劃 繫結變數 Bind peeking變數
- 如何解決執行計劃繫結變數bind peeking重新硬解析變數
- 索引失效系列——繫結變數引起的peeking索引變數
- oracle 繫結變數（bind variable）Oracle變數
- 查詢沒有使用繫結變數的sql zt變數SQL
- oracle繫結變數窺視(zt)Oracle變數
- Oracle繫結變數分級（Bind Graduation）Oracle變數
- [SQL]oracle 繫結變數(bind variable)[轉載]SQLOracle變數
- v$sql_bind_capture 獲取繫結變數SQLAPT變數
- v$sql_bind_capture 獲取繫結變數資訊SQLAPT變數
- PLSQL使用繫結變數SQL變數
- zt_繫結變數和cursor_sharing變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- Echarts根據資料長度變換柱狀圖柱狀的顏色Echarts
- 繫結變數的使用範圍變數
- 關於繫結變數的使用變數
- 透過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 通過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 柱狀圖
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- 使用繫結變數的一點總結！變數
- 九、柱狀圖和3D柱狀圖3D
- v$sql_bind_capture與timestamp型別的繫結變數的數值SQLAPT型別變數
- 繫結變數變數
- SQL Developer中使用繫結變數SQLDeveloper變數
- PLSQL中使用繫結變數的語法SQL變數
- 使用 Flutter 繪製圖表（一）柱狀圖?Flutter
- Oracle 繫結變數Oracle變數
- Oracle 中bind peeking的處理Oracle
- 【sql調優】使用繫結變數（二）SQL變數
- 【sql調優】使用繫結變數（一）SQL變數
- oracle 柱狀圖（Histograms）OracleHistogram