繫結變數優缺點、使用、繫結變數窺探

繫結變數是Oracle解決硬解析的首要利器，能解決OLTP系統中library cache的過度耗用以提高效能。然刀子磨的太快，使起來鋒利，卻容易折斷。凡事皆有利弊二性，因地制宜，因時制宜，全在如何權衡而已。本文講述了繫結變數的使用方法，以及繫結變數的優缺點、使用場合。

一、繫結變數

    提到繫結變數，就不得不瞭解硬解析與軟解析。硬解析簡言之即一條SQL語句沒有被執行過，處於首次執行，則需要對其進行語法分析，語義識別，跟據統計資訊生成最佳的執行計劃，然後對其執行。而軟解析呢，則是由於library cache已經存在與該SQL語句一致的SQL語句文字、執行環境，即有相同的父遊標與子游標，採用拿來主義，直接執行即可。軟解析同樣經歷語法分析，語義識別，且生成hash value ，接下來在library cache搜尋相同的hash value ，如存在在實施軟解析。有關更多的硬解析與軟解析以及父遊標，子游標請作如下參考：

    有關硬解析與軟解析，請參考：Oracle 硬解析與軟解析
    有關父遊標、子游標，請參考：父遊標、子游標與共享遊標

    繫結變數
      首先其實質是變數，有些類似於我們經常使用的替代變數，替代變數使用&佔位符，只不過繫結變數使用:
替代變數使用時為 &variable_para，相應的繫結變數則為 :bind_variable_para
      通常一個SQL語句包含動態部分和靜態部分，佔位符實質是SQL語句中容易發生變化的部分，通常為其條件或取值範圍。動態部分在一般情況下(資料傾斜除外)，對執行計劃的生成的影響是微乎其微的。故同一SQL語句不同的動態部分產生的執行計劃都是相同的。


二、繫結變數的使用
    1、在SQLPlus中使用繫結變數

			[sql] view plaincopy
			
 print? 
		
			SQL> variable eno number;                           -->使用variable定義變數                                               
		
			SQL> exec :eno:=7788;                                                                                                     
		
			SQL> select ename,job,sal from emp where empno=:eno;                                                                      
		
			ENAME      JOB              SAL                                                                                           
		
			---------- --------- ----------                                                                                           
		
			SCOTT      ANALYST         3000                                                                                           
		
			SQL> col sql_text format a55                                                                                              
		
			SQL> select sql_id,sql_text,executions from v$sqlarea   -->首次查詢後在v$sqlarea儲存父遊標且執行次數EXECUTIONS為1         
		
			  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                   
		
			SQL_ID        SQL_TEXT                                                EXECUTIONS                                          
		
			------------- ------------------------------------------------------- ----------                                          
		
			dbc6vx6z6n1zv select ename,job,sal from emp where empno=:eno                   1                                          
		
			SQL> select sql_id,hash_value,child_number,sql_text from v$sql -->查詢檢視v$sql檢視該SQL對應的子游標，且CHILD_NUMBER為0   
		
			  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                   
		
			SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                            
		
			------------- ---------- ------------ -------------------------------------------------------                             
		
			dbc6vx6z6n1zv 3194619899            0 select ename,job,sal from emp where empno=:eno                                      
		
			SQL> exec :eno:=7369;                                                                                                     
		
			SQL> select ename,job,sal from emp where empno=:eno;  -->再次對變數賦值並查詢                                             
		
			ENAME      JOB              SAL                                                                                           
		
			---------- --------- ----------                                                                                           
		
			SMITH      CLERK            800                                                                                           
		
			SQL> exec :eno:=7521                                                                                                      
		
			SQL> select ename,job,sal from emp where empno=:eno;                                                                      
		
			ENAME      JOB              SAL                                                                                           
		
			---------- --------- ----------                                                                                           
		
			WARD       SALESMAN        1250                                                                                           
		
			SQL> select sql_id,sql_text,executions from v$sqlarea -->檢視v$sqlarea中EXECUTIONS值為3，對應的SQL被執行了3次             
		
			  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                   
		
			SQL_ID        SQL_TEXT                                                EXECUTIONS                                          
		
			------------- ------------------------------------------------------- ----------                                          
		
			dbc6vx6z6n1zv select ename,job,sal from emp where empno=:eno                   3                                          
		
			-->檢視v$sql中對應的子游標也實現了完全共享，保持CHILD_NUMBER為0                                                           
		
			SQL> select sql_id,hash_value,child_number,sql_text from v$sql                                                            
		
			  2  where sql_text like '%select ename,job,sal%' and sql_text not like '%from v$sql%';                                   
		
			SQL_ID        HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                            
		
			------------- ---------- ------------ -------------------------------------------------------                             
		
			dbc6vx6z6n1zv 3194619899            0 select ename,job,sal from emp where empno=:eno

2、PL/SQL塊中使用繫結變數

			[sql] view plaincopy
			
 print? 
		
			SQL> create table t(id number,val number);  -->首先建立表t                                                                
		
			SQL> get get_parse.sql                                                                                                    
		
			  1  select name,value from v$mystat a join v$statname b                                                                  
		
			  2* on a.statistic#=b.statistic# where b.name like 'parse count%';                                                       
		
			SQL> @get_parse.sql   -->獲得當前的解析情況，此時hard parase 為63                                                         
		
			NAME                           VALUE                                                                                      
		
			------------------------- ----------                                                                                      
		
			parse count (total)              394                                                                                      
		
			parse count (hard)                63                                                                                      
		
			parse count (failures)             1                                                                                      
		
			-->下面的pl/sql程式碼中，Oracle實現自動變數自動繫結，執行了30次的insert操作，但oracle認為每次執行的語句都是一樣的           
		
			/**************************************************/                                                                      
		
			/* Author: Robinson Cheng                         */                                                                      
		
			/* Blog:   http://blog.csdn.net/robinson_0612     */                                                                      
		
			/* MSN:    robinson_0612@hotmail.com              */                                                                      
		
			/* QQ:     645746311                              */                                                                      
		
			/**************************************************/                                                                      
		
			SQL> begin                     -->執行pl/sql程式碼，向表t中插入30條記錄                                                     
		
			  2  for i in 1..30 loop                                                                                                  
		
			  3  insert into t values(i,i*2);                                                                                         
		
			  4  end loop;                                                                                                            
		
			  5  commit;                                                                                                              
		
			  6  end;                                                                                                                 
		
			  7  /                                                                                                                    
		
			PL/SQL procedure successfully completed.                                                                                  
		
			SQL>  @get_parse              -->程式碼執行後的結果，硬解析數量僅僅增加了3次                                                
		
			NAME                           VALUE                                                                                      
		
			------------------------- ----------                                                                                      
		
			parse count (total)              401                                                                                      
		
			parse count (hard)                67                                                                                      
		
			parse count (failures)             1

3、在儲存過程或包中使用繫結變數

			[sql] view plaincopy
			
 print? 
		
			-->儲存過程和保重，對引數的傳遞即是使用自動繫結變數來實現，因此程式設計人員無須操心繫結變數問題，如下例所示：                
		
			SQL> create or replace procedure ins_t(p_id in number,p_value in number) -->建立一個過程用於向表t插入記錄                
		
			  2  as                                                                                                                  
		
			  3    begin                                                                                                             
		
			  4      insert into t values(p_id,p_value);                                                                             
		
			  5      commit;                                                                                                         
		
			  6    end;                                                                                                              
		
			  7  /                                                                                                                   
		
			Procedure created.                                                                                                       
		
			SQL> select sid,serial# from v$session where username='SCOTT';  -->獲得當前使用者的sid,serial#                             
		
			       SID    SERIAL#                                                                                                    
		
			---------- ----------                                                                                                    
		
			      1084        938                                                                                                    
		
			SQL> exec dbms_monitor.session_trace_enable(session_id=>1084,serial_num=>938);  -->對當前的session啟用跟蹤               
		
			PL/SQL procedure successfully completed.                                                                                 
		
			SQL> exec ins_t(31,62);               -->執行儲存過程                                                                    
		
			PL/SQL procedure successfully completed.                                                                                 
		
			SQL> exec ins_t(32,64);                                                                                                  
		
			PL/SQL procedure successfully completed.                                                                                 
		
			SQL> exec dbms_monitor.session_trace_disable(session_id=>1084,serial_num=>938); -->關閉對session的跟蹤                   
		
			PL/SQL procedure successfully completed.                                                                                 
		
			SQL> SET LINESIZE 180                                                                                                    
		
			SQL> COLUMN trace_file FORMAT A100                                                                                       
		
			SQL> SELECT s.sid,                   -->獲得跟蹤檔案位置                                                                 
		
			  2  s.serial#,                                                                                                          
		
			  3  p.spid,                                                                                                             
		
			  4  pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||                                                 
		
			  5  '_ora_' || p.spid || '.trc' AS trace_file                                                                           
		
			  6  FROM v$session s,                                                                                                   
		
			  7  v$process p,                                                                                                        
		
			  8  v$parameter pa                                                                                                      
		
			  9  WHERE pa.name = 'user_dump_dest'                                                                                    
		
			 10  AND s.paddr = p.addr                                                                                                
		
			 11  AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');                                                                 
		
			       SID    SERIAL# SPID         TRACE_FILE                                                                            
		
			---------- ---------- ------------ --------------------------------------------------------------                        
		
			      1084        938 10883        /u02/database/CNMMBO/udump/cnmmbo_ora_10883.trc                                       
		
			SQL> SQL>                                                                                                                
		
			SQL> ho pwd                                                                                                              
		
			/users/oracle                                                                                                            
		
			-->使用tkprof工具格式化跟蹤檔案便於閱讀                                                                                  
		
			SQL> ho tkprof /u02/database/CNMMBO/udump/cnmmbo_ora_10883.trc /users/oracle/ins_t.txt explain=goex_admin/goex_admin     
		
			TKPROF: Release 10.2.0.3.0 - Production on Fri Sep 9 12:55:18 2011                                                       
		
			Copyright (c) 1982, 2005, Oracle.  All rights reserved.                                                                  
		
			SQL> ho cat /users/oracle/ins_t.txt  -->檢視跟蹤檔案                                                                     
		
			......                                                                                                                   
		
			BEGIN ins_t(31,62); END;                                                                                                 
		
			......                                                                                                                   
		
			INSERT INTO T      -->可以看到insert into語句中使用了繫結變數                                                            
		
			VALUES                                                                                                                   
		
			(:B2 ,:B1 )                                                                                                              
		
			call     count       cpu    elapsed       disk      query    current        rows                                         
		
			------- ------  -------- ---------- ---------- ---------- ----------  ----------                                         
		
			Parse        0      0.00       0.00          0          0          0           0                                         
		
			Execute      2      0.11       0.11          2        281         27           2                                         
		
			.......

4、在動態SQL中是使用繫結變數

			[sql] view plaincopy
			
 print? 
		
			-->動態SQL中不能自動使用繫結變數，需要手動設定繫結變數                                                                    
		
			SQL> @get_parse     -->獲得當前hard parse解析情況，此時為120                                                              
		
			NAME                           VALUE                                                                                      
		
			------------------------- ----------                                                                                      
		
			parse count (total)              533                                                                                      
		
			parse count (hard)               120                                                                                      
		
			parse count (failures)             1                                                                                      
		
			SQL> begin                                                                                                                
		
			  2  for i in 1..30 loop                                                                                                  
		
			  3  execute immediate 'insert into t values(:1,:2)' using i,i+i-2;  -->動態SQL使用繫結變數，該語句將執行30次             
		
			  4  end loop;                                                                                                            
		
			  5  commit;                                                                                                              
		
			  6  end;                                                                                                                 
		
			  7  /                                                                                                                    
		
			PL/SQL procedure successfully completed.                                                                                  
		
			SQL> @get_parse     --> 動態SQL執行後，儘管執行了30次，但硬解析數量僅僅增加了2次                                          
		
			NAME                           VALUE                                                                                      
		
			------------------------- ----------                                                                                      
		
			parse count (total)              537                                                                                      
		
			parse count (hard)               122                                                                                      
		
			parse count (failures)             1                                                                                      
		
			SQL> set serveroutput on;                                                                                                 
		
			SQL> get get_sal.sql    -->下面的pl/sql中使用了繫結變數                                                                   
		
			  1   DECLARE                                                                                                             
		
			  2   TYPE emp_cur IS REF CURSOR;                                                                                         
		
			  3   my_emp_cur emp_cur;                                                                                                 
		
			  4   my_emp_rec emp%ROWTYPE;                                                                                             
		
			  5   BEGIN                                                                                                               
		
			  6   OPEN my_emp_cur FOR 'select * from emp where deptno=:dno' USING 10;                                                 
		
			  7   LOOP                                                                                                                
		
			  8   FETCH my_emp_cur INTO my_emp_rec;                                                                                   
		
			  9   EXIT WHEN my_emp_cur%NOTFOUND;                                                                                      
		
			 10   dbms_output.put_line(my_emp_rec.ename||'''s salary is : '||my_emp_rec.sal);                                         
		
			 11   END LOOP;                                                                                                           
		
			 12*  END;                                                                                                                
		
			 13  /                                                                                                                    
		
			CLARK's salary is : 4900                                                                                                  
		
			KING's salary is : 5000                                                                                                   
		
			MILLER's salary is : 1300                                                                                                 
		
			PL/SQL procedure successfully completed.                                                                                  
		
			SQL> /                                                                                                                    
		
			CLARK's salary is : 4900                                                                                                  
		
			KING's salary is : 5000                                                                                                   
		
			MILLER's salary is : 1300                                                                                                 
		
			PL/SQL procedure successfully completed.                                                                                  
		
			SQL> select sql_text,executions,sql_id from v$sqlarea where sql_text like 'select * from emp where deptno=:dno%';         
		
			SQL_TEXT                                      EXECUTIONS SQL_ID                                                           
		
			--------------------------------------------- ---------- -------------                                                    
		
			select * from emp where deptno=:dno                    2 c1nx6x02h655a

三、繫結變數的優缺點及使用場合
    優點：
        可以在library cache中共享遊標，避免硬解析以及與之相關的額外開銷在大批次資料操作時將呈數量級來減少閂鎖的使用，避免閂鎖的競爭

    缺點：
        繫結變數被使用時，查詢最佳化器會忽略其具體值，因此其預估的準確性遠不如使用字面量值真實，尤其是在表存在資料傾斜(表上的資料非均勻分佈)的列上會提供錯誤的執行計劃。從而使得非高效的執行計劃被使用。

    使用場合：
        OLTP
            在OLTP系統中SQL語句重複執行頻度高，但處理的資料量較少，結果集也相對較小，尤其是使用表上的索引來縮小中間結果集，其解析時間通常會接近或高於執行時間，因此該場合適合使用繫結變數。

        OLAP
            在OLAP系統中，SQL語句執行次數相對較少，但返回的資料量較大，因此多數情況下傾向於使用權標掃描更高效，其SQL語句執行時間遠高於其解析時間，因此使用繫結變數對於總響應時間影響不大。而且增加生成低效執行計劃的風險。即在在OLAP系統中使用字面量的效能高於使用繫結變數。

    注意：
        對於實際的資料庫物件，如(表，檢視，列等)，不能使用繫結變數替換，只能替換字面量。如果物件名是在執行時生成的，則需要對其用字串拼接，同時，sql只會匹配已經在共享池中相同的物件名。

2、繫結變數窺探

Bind Peeking是Oracle 9i中引入的新特性，一直持續到Oracle 10g R2。它的作用就是在SQL語句硬分析的時候，檢視一下當前SQL謂詞的值，以便生成最佳的執行計劃。而在oracle 9i之前的版本中，Oracle 只根據統計資訊來做出執行計劃。

使用SQL首次執行時的值來生成執行計劃。後續再次執行該SQL語句則使用首次執行計劃來執行。

影響的版本：Oracle 9i, Oracle 10g

對於繫結變數列中的特殊值或非均勻分佈列上的繫結變數會造成非高效的執行計劃被選擇並執行。

要注意的是，Bind Peeking只發生在硬分析的時候，即SQL被第一次執行的時候，之後的變數將不會在做peeking。我們可以看出，Bind peeking並不能最終解決不同謂詞導致選擇不同執行計劃的問題，它只能讓SQL第一次執行的時候，執行計劃選擇更加準確，並不能幫助OLAP系統解決繫結變數導致執行計劃選擇錯誤的問題。這也是OLAP不應該使用繫結變數的一個原因。

更確切地說，繫結變數窺探是在SQL解析的物理階段，查詢最佳化器將會窺探繫結變數的值並將其作為字面量來使用。即ORACLE首次解析SQL時會將變數的真實值代入產生執行計劃，後續對所有使用該繫結變數SQL語句都採用首次生存的執行計劃。如此這般？那效能究竟如何？

結果是並非最佳的執行計劃的使用。此問題在Oracle 11g中得以解決。

二、示例繫結變數窺探
1、建立演示環境

				[sql] view plaincopy
				
 print?
			
				SQL> select * from v$version where rownum<2;    -->檢視當前資料庫版本                                           
			
				BANNER                                                                                                          
			
				----------------------------------------------------------------                                                
			
				Oracle Database 10g Release 10.2.0.3.0 - 64bit Production                                                       
			
				SQL> create table t(id,owner,object_id) as       -->建立測試表t                                                 
			
				  2  select rownum,owner,object_id from all_objects where rownum<=1000;                                         
			
				SQL> alter table t add constraint t_pk primary key(id);  -->為表t新增主鍵                                       
			
				SQL> begin                                               -->收集統計資訊,此處未生成直方圖資訊                   
			
				  2  dbms_stats.gather_table_stats(                                                                             
			
				  3  ownname=>'SCOTT',                                                                                          
			
				  4  tabname=>'T',                                                                                              
			
				  5  estimate_percent=>100,                                                                                     
			
				  6  method_opt=>'for all columns size 1');                                                                     
			
				  7  end;                                                                                                       
			
				  8  /                                                                                                          
			
				PL/SQL procedure successfully completed.                                                                        
			
				SQL> select count(id),count(distinct id),min(id),max(id) from t;  -->檢視值的分佈情況                           
			
				 COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)                                                              
			
				---------- ----------------- ---------- ----------                                                              
			
				      1000              1000          1       1000

2、未使用繫結變數情形下SQL語句的執行計劃

				[sql] view plaincopy
				
 print?
			
				SQL> select sum(object_id) from t where id<900;     -->釋出SQL 查詢語句                                                
			
				SUM(OBJECT_ID)                                                                                                         
			
				--------------                                                                                                         
			
				        446549                                                                                                         
			
				SQL> select * from table(dbms_xplan.display_cursor()); -->由其執行計劃可知，當前的SQL語句使用了全表掃描                
			
				/**************************************************/                                                                   
			
				/* Author: Robinson Cheng                         */                                                                   
			
				/* Blog:   http://blog.csdn.net/robinson_0612     */                                                                   
			
				/* MSN:    robinson_0612@hotmail.com              */                                                                   
			
				/* QQ:     645746311                              */                                                                   
			
				/**************************************************/                                                                   
			
				PLAN_TABLE_OUTPUT                                                                                                      
			
				---------------------------------------------------------------------------                                            
			
				SQL_ID  bz6h6fdsxgjka, child number 0                                                                                  
			
				-------------------------------------                                                                                  
			
				select sum(object_id) from t where id<900                                                                              
			
				Plan hash value: 2966233522                                                                                            
			
				---------------------------------------------------------------------------                                            
			
				| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
			
				---------------------------------------------------------------------------                                            
			
				|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
			
				|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
			
				|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
			
				---------------------------------------------------------------------------                                            
			
				Predicate Information (identified by operation id):                                                                    
			
				---------------------------------------------------                                                                    
			
				   2 - filter("ID"<900)                                                                                                
			
				SQL> select sum(object_id) from t where id<10;   -->釋出另一條SQL 查詢語句                                             
			
				SQL> select * from table(dbms_xplan.display_cursor()); -->此時的查詢生成的執行計劃走索引範圍掃描                       
			
				                                                       -->由於字面量不同，因此兩條SQL語句生成了不同的SQL_ID與執行計劃  
			
				PLAN_TABLE_OUTPUT                                                                                                      
			
				--------------------------------------------------------------------------                                             
			
				SQL_ID  6y2280pyvacfq, child number 0                                                                                  
			
				-------------------------------------                                                                                  
			
				select sum(object_id) from t where id<10                                                                               
			
				Plan hash value: 4270555908                                                                                            
			
				-------------------------------------------------------------------------------------                                  
			
				| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
			
				-------------------------------------------------------------------------------------                                  
			
				|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
			
				|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
			
				|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
			
				|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
			
				-------------------------------------------------------------------------------------                                  
			
				Predicate Information (identified by operation id):                                                                    
			
				---------------------------------------------------                                                                    
			
				   3 - access("ID"<10)

3、使用繫結變數情形下的執行計劃

				[sql] view plaincopy
				
 print?
			
				SQL> variable v_id number;   -->定義繫結變數                                                                           
			
				SQL> exec :v_id:=900;        -->給繫結變數賦值                                                                         
			
				PL/SQL procedure successfully completed.                                                                               
			
				SQL> select sum(object_id) from t where id<:v_id;                                                                      
			
				SUM(OBJECT_ID)                                                                                                         
			
				--------------                                                                                                         
			
				        446549                                                                                                         
			
				SQL> select * from table(dbms_xplan.display_cursor());   -->此時上一條SQL語句走了全表掃描，其SQL_ID 為7qcp6urqh7d2j    
			
				PLAN_TABLE_OUTPUT                                                                                                      
			
				------------------------------------------------------------------------------                                         
			
				SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
			
				-------------------------------------                                                                                  
			
				select sum(object_id) from t where id<:v_id                                                                            
			
				Plan hash value: 2966233522                                                                                            
			
				---------------------------------------------------------------------------                                            
			
				| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
			
				---------------------------------------------------------------------------                                            
			
				|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
			
				|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
			
				|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
			
				---------------------------------------------------------------------------                                            
			
				Predicate Information (identified by operation id):                                                                    
			
				---------------------------------------------------                                                                    
			
				   2 - filter("ID"<:V_ID)    -->謂詞資訊表明此時使用了繫結變數                                                         
			
				SQL> exec :v_id:=10;         -->對繫結變數重新賦值                                                                     
			
				PL/SQL procedure successfully completed.                                                                               
			
				SQL> select sum(object_id) from t where id<:v_id;   -->再次執行SQL語句                                                 
			
				SUM(OBJECT_ID)                                                                                                         
			
				--------------                                                                                                         
			
				           254                                                                                                         
			
				SQL> select * from table(dbms_xplan.display_cursor());  -->此時執行計劃中依然選擇的是全表掃描                          
			
				                                                        -->其SQL_ID同上一次執行的SQL語句相同，即實現了完全共享         
			
				PLAN_TABLE_OUTPUT                                       -->對於未使用繫結變數時id<10的情形則為走索引範圍掃描           
			
				-----------------------------------------------         -->由此可知，並非最佳的執行計劃被執行                          
			
				SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
			
				-------------------------------------                                                                                  
			
				select sum(object_id) from t where id<:v_id                                                                            
			
				Plan hash value: 2966233522                                                                                            
			
				---------------------------------------------------------------------------                                            
			
				| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
			
				---------------------------------------------------------------------------                                            
			
				|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
			
				|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
			
				|*  2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
			
				---------------------------------------------------------------------------                                            
			
				Predicate Information (identified by operation id):                                                                    
			
				---------------------------------------------------                                                                    
			
				   2 - filter("ID"<:V_ID)                                                                                              
			
				SQL> alter system flush shared_pool;    -->清空共享池，此時共享的父遊標與子游標全部釋放                                
			
				SQL> print v_id;                                                                                                       
			
				      V_ID                                                                                                             
			
				----------                                                                                                             
			
				        10                                                                                                             
			
				SQL> select round(avg(object_id)) from t where id<:v_id;   -->使用id<10來執行SQL語句                                   
			
				ROUND(AVG(OBJECT_ID))                                                                                                  
			
				---------------------                                                                                                  
			
				                   28                                                                                                  
			
				SQL> select * from table(dbms_xplan.display_cursor());    -->此時該SQL語句使用了最佳的執行計劃，即走索引範圍掃描       
			
				PLAN_TABLE_OUTPUT                                                                                                      
			
				---------------------------------------------------------------------------------------                                
			
				SQL_ID  0bx53mgt4qqnt, child number 0                                                                                  
			
				-------------------------------------                                                                                  
			
				select round(avg(object_id)) from t where id<:v_id                                                                     
			
				Plan hash value: 4270555908                                                                                            
			
				-------------------------------------------------------------------------------------                                  
			
				| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
			
				-------------------------------------------------------------------------------------                                  
			
				|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
			
				|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
			
				|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
			
				|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
			
				-------------------------------------------------------------------------------------                                  
			
				Predicate Information (identified by operation id):                                                                    
			
				---------------------------------------------------                                                                    
			
				   3 - access("ID"<:V_ID)                                                                                              
			
				SQL> exec :v_id:=900;                   -->為變數賦新值                                                                
			
				PL/SQL procedure successfully completed.                                                                               
			
				SQL> select round(avg(object_id)) from t where id<:v_id;                                                               
			
				ROUND(AVG(OBJECT_ID))                                                                                                  
			
				---------------------                                                                                                  
			
				                  497                                                                                                  
			
				SQL> select * from table(dbms_xplan.display_cursor()); -->此次執行的SQL語句本該使用全表掃描，而此時選擇了索引範圍掃描  
			
				PLAN_TABLE_OUTPUT                                                                                                      
			
				--------------------------------------------------------------------------------------                                 
			
				SQL_ID  0bx53mgt4qqnt, child number 0                                                                                  
			
				-------------------------------------                                                                                  
			
				select round(avg(object_id)) from t where id<:v_id                                                                     
			
				Plan hash value: 4270555908                                                                                            
			
				-------------------------------------------------------------------------------------                                  
			
				| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
			
				-------------------------------------------------------------------------------------                                  
			
				|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
			
				|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
			
				|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     9 |    72 |     3   (0)| 00:00:01 |                                  
			
				|*  3 |    INDEX RANGE SCAN          | T_PK |     9 |       |     2   (0)| 00:00:01 |                                  
			
				-------------------------------------------------------------------------------------                                  
			
				Predicate Information (identified by operation id):                                                                    
			
				---------------------------------------------------                                                                    
			
				   3 - access("ID"<:V_ID)                                                                                              
			
				SQL> drop table t;

從上面的演示可以，由於繫結變數窺探特性，對於後續生成的執行計劃，不僅套用了首次生成的執行計劃，而且執行計劃中的Row,Bytes,Cost(%CPU)等都與首次生存執行計劃得值相同。由此可知，儘管可以使用繫結變數解決OLTP系統中大量重複SQL的反覆解析的問題。但繫結變數可能會導致SQL語句選擇非最佳的執行計劃。尤其是對於存在資料傾斜的列，且生成了直方圖更不宜於使用繫結變數。在Oracle 11g 中，自適應遊標特性從一定程度解決了繫結變數窺探所導致的問題。

Oracle自適應共享遊標

自適應遊標共享Adaptive Cursor Sharing或擴充套件的遊標共享(Extended Cursor Sharing)是Oracle 11g的新特性之一，主要用於解決以前版本中由於繫結變數窺探導致SQL語句無法獲得最佳執行計劃的缺陷，即能夠對效率低下的遊標(子游標)進行自動識別而選擇最佳的執行計劃。本文詳細描述了自適應遊標共享並給出示例。
有關繫結變數窺探請參考：Oracle 繫結變數窺探

一、示例自適應遊標共享
1、建立演示環境

					[sql] view plaincopy
					
 print?
				
					SQL> select * from v$version where rownum<2;                                                    
				
					BANNER                                                                                          
				
					--------------------------------------------------------------------------------                
				
					Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production                          
				
					SQL> create table t(id,owner,object_id) as                                                      
				
					  2  select rownum,owner,object_id from all_objects where rownum<=1000;                         
				
					SQL> alter table t add constraint t_pk primary key(id);                                         
				
					SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);                             
				
					SQL> select count(id),count(distinct id),min(id),max(id) from t;                                
				
					 COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)                                              
				
					---------- ----------------- ---------- ----------                                              
				
					      1000              1000          1       1000

2、使用繫結變數執行SQL語句並獲得首次執行情況

					[sql] view plaincopy
					
 print?
				
					SQL> var v_id number;                                                                                              
				
					SQL> exec :v_id:=9;                                                                                                
				
					SQL> set linesize 180                                                                                              
				
					SQL> select sum(object_id) from t where id<:v_id;                                                                  
				
					SUM(OBJECT_ID)                                                                                                     
				
					--------------                                                                                                     
				
					          2078                                                                                                     
				
					SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));                               
				
					SQL_ID  7qcp6urqh7d2j, child number 0                                                                              
				
					-------------------------------------                                                                              
				
					select sum(object_id) from t where id<:v_id            -->變數值為9時，使用了正確的執行計劃，且預估的行數也準確    
				
					Plan hash value: 4270555908                                                                                        
				
					-------------------------------------------------------------------------------------                              
				
					| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                              
				
					-------------------------------------------------------------------------------------                              
				
					|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                              
				
					|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                              
				
					|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |                              
				
					|   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |                              
				
					-------------------------------------------------------------------------------------                              
				
					SQL> col SQL_TEXT format a45    -->下面的語句獲得自適應遊標共享的3個欄位值                                         
				
					SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                   
				
					  2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';   
				
					SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I                          
				
					------------- --------------------------------------------- ------------ ---------- - - -                          
				
					7qcp6urqh7d2j select sum(object_id) from t where id<:v_id              0          1 Y N Y

    3、自適應遊標共享的外在體現
        自適應遊標共享主要透過三個欄位來得以體現，即is_bind_sensitive,is_bind_aware,is_shareable。(注:此三個欄位僅在Oracle 11g
        中存在)。透過上面從v$sql(v$sqlarea中不存在is_shareable)的查詢可知，三個欄位分別被賦予了不同的值，代表了不同的含義。

      is_bind_sensitive(繫結是否敏感)
          表示該子游標中是否使用了繫結變數要素，且採用bind peeking方法進行執行計劃生成。如果執行計劃依賴於窺探到的值，此處為Y，
          否則為N。

      is_bind_aware(繫結是否可知)
          表示該子游標是否使用了extended cursor sharing技術，是則為Y，否則為N，如為N，則該遊標將廢棄，不再可用。

      is_shareable(是否可共享)
          表示該子游標可否被下次軟解析是否可共享使用。可共享則為Y，否則為N，表示該子游標失去了共享價值，按LRU演算法淘汰。

      由於該SQL語句為首次執行，因此從v$sql查詢的結果中得知
          is_bind_sensitive 為Y值(首次執行，執行了bind peeking)
          is_bind_aware     為N值(首次執行，不被extended cursor sharing支援)
          is_shareable      為Y值(執行計劃可共享)

4、重新賦值後觀察遊標共享情況

					[sql] view plaincopy
					
 print?
				
					SQL> exec :v_id:=900;                                                                                                  
				
					SQL> select sum(object_id) from t where id<:v_id;                                                                      
				
					SUM(OBJECT_ID)                                                                                                         
				
					--------------                                                                                                         
				
					       1826561                                                                                                         
				
					SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));                                   
				
					SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
				
					-------------------------------------                                                                                  
				
					select sum(object_id) from t where id<:v_id  -->此次執行的變數值為900，執行計劃位上次變數為9的執行計劃                 
				
					                                             -->此時為非正確的執行計劃，等同於Oracle 9i,10g中的情形                    
				
					Plan hash value: 4270555908                                                                                            
				
					-------------------------------------------------------------------------------------                                  
				
					| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
				
					-------------------------------------------------------------------------------------                                  
				
					|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
				
					|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
				
					|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |                                  
				
					|   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |                                  
				
					-------------------------------------------------------------------------------------                                  
				
					-->自適應遊標共享的3個欄位值並未發生任何變化                                                                           
				
					SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                       
				
					  2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
				
					SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I                              
				
					------------- --------------------------------------------- ------------ ---------- - - -                              
				
					7qcp6urqh7d2j select sum(object_id) from t where id<:v_id              0          2 Y N Y                              
				
					SQL> select sum(object_id) from t where id<:v_id;   -->再次執行變數為900值的SQL語句                                    
				
					SUM(OBJECT_ID)                                                                                                         
				
					--------------                                                                                                         
				
					       1826561                                                                                                         
				
					SQL> select * from table(dbms_xplan.display_cursor(null,null,'typical -predicate'));                                   
				
					SQL_ID  7qcp6urqh7d2j, child number 1                                                                                  
				
					-------------------------------------                                                                                  
				
					select sum(object_id) from t where id<:v_id    -->此時執行計劃較上一次發生了變化，使用了全表掃描,Rows接近於實際值      
				
					                                               -->自適應遊標共享特性得以體現                                           
				
					Plan hash value: 2966233522                                                                                            
				
					---------------------------------------------------------------------------                                            
				
					| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
				
					---------------------------------------------------------------------------                                            
				
					|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
				
					|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
				
					|   2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
				
					---------------------------------------------------------------------------                                            
				
					-->自適應遊標共享特性的幾個值發生了變化，生成了新的子游標，其子游標號為1                                               
				
					SQL> select sql_id,sql_text,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                       
				
					  2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
				
					SQL_ID        SQL_TEXT                                      CHILD_NUMBER EXECUTIONS I I I                              
				
					------------- --------------------------------------------- ------------ ---------- - - -                              
				
					7qcp6urqh7d2j select sum(object_id) from t where id<:v_id              0          2 Y N Y                              
				
					7qcp6urqh7d2j select sum(object_id) from t where id<:v_id              1          1 Y Y Y                              
				
					SQL> exec :v_id:=800      -->為變數賦於不同的值                                                                        
				
					SQL> select sum(object_id) from t where id<:v_id;  -->利用新的變數值執行SQL語句                                        
				
					SUM(OBJECT_ID)                                                                                                         
				
					--------------                                                                                                         
				
					       1548431                                                                                                         
				
					SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                                
				
					  2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
				
					SQL_ID        CHILD_NUMBER EXECUTIONS I I I                                                                            
				
					------------- ------------ ---------- - - -                                                                            
				
					7qcp6urqh7d2j            0          2 Y N Y                                                                            
				
					7qcp6urqh7d2j            1          1 Y Y N                                                                            
				
					7qcp6urqh7d2j            2          1 Y Y Y    -->生成了新的子游標號為2                                                
				
					SQL> exec :v_id:=500;     -->為變數賦於新值                                                                            
				
					SQL> select sum(object_id) from t where id<:v_id;  -->利用新的變數值執行SQL語句                                        
				
					SUM(OBJECT_ID)                                                                                                         
				
					--------------                                                                                                         
				
					        826694                                                                                                         
				
					/**************************************************/                                                                   
				
					/* Author: Robinson Cheng                         */                                                                   
				
					/* Blog:   http://blog.csdn.net/robinson_0612     */                                                                   
				
					/* MSN:    robinson_0612@hotmail.com              */                                                                   
				
					/* QQ:     645746311                              */                                                                   
				
					/**************************************************/                                                                   
				
					SQL> select sql_id,child_number,executions,is_bind_sensitive,is_bind_aware,is_shareable                                
				
					  2  from v$sql where sql_text like '%select sum(object_id) from t where%' and sql_text not like '%from v$sql%';       
				
					SQL_ID        CHILD_NUMBER EXECUTIONS I I I                                                                            
				
					------------- ------------ ---------- - - -                                                                            
				
					7qcp6urqh7d2j            0          2 Y N Y                                                                            
				
					7qcp6urqh7d2j            1          1 Y Y N                                                                            
				
					7qcp6urqh7d2j            2          1 Y Y N       -->注意看子游標1,2的is_shareable值為N，表示不可共享                  
				
					7qcp6urqh7d2j            3          1 Y Y Y       -->生成了新的子游標號為3，                                           
				
					-->檢視最終該SQL語句的不同子游標的所有執行計劃                                                                         
				
					SQL> select * from table(dbms_xplan.display_cursor('7qcp6urqh7d2j',null,'typical -predicate'));                        
				
					PLAN_TABLE_OUTPUT                                                                                                      
				
					------------------------------------------------------------------------------------------------------------------     
				
					SQL_ID  7qcp6urqh7d2j, child number 0                                                                                  
				
					-------------------------------------                                                                                  
				
					select sum(object_id) from t where id<:v_id     -->0號子游標為索引範圍掃描                                             
				
					Plan hash value: 4270555908                                                                                            
				
					-------------------------------------------------------------------------------------                                  
				
					| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                  
				
					-------------------------------------------------------------------------------------                                  
				
					|   0 | SELECT STATEMENT             |      |       |       |     3 (100)|          |                                  
				
					|   1 |  SORT AGGREGATE              |      |     1 |     8 |            |          |                                  
				
					|   2 |   TABLE ACCESS BY INDEX ROWID| T    |     8 |    64 |     3   (0)| 00:00:01 |                                  
				
					|   3 |    INDEX RANGE SCAN          | T_PK |     8 |       |     2   (0)| 00:00:01 |                                  
				
					-------------------------------------------------------------------------------------                                  
				
					SQL_ID  7qcp6urqh7d2j, child number 1                                                                                  
				
					-------------------------------------                                                                                  
				
					select sum(object_id) from t where id<:v_id   -->1號子游標為全表掃描，其預估的行數接近實際影響行數的值為900            
				
					Plan hash value: 2966233522                                                                                            
				
					---------------------------------------------------------------------------                                            
				
					| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
				
					---------------------------------------------------------------------------                                            
				
					|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
				
					|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
				
					|   2 |   TABLE ACCESS FULL| T    |   900 |  7200 |     3   (0)| 00:00:01 |                                            
				
					---------------------------------------------------------------------------                                            
				
					SQL_ID  7qcp6urqh7d2j, child number 2                                                                                  
				
					-------------------------------------                                                                                  
				
					select sum(object_id) from t where id<:v_id   -->2號子游標為全表掃描，但其預估的行數接近實際影響行數的值為800          
				
					Plan hash value: 2966233522                                                                                            
				
					---------------------------------------------------------------------------                                            
				
					| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
				
					---------------------------------------------------------------------------                                            
				
					|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
				
					|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
				
					|   2 |   TABLE ACCESS FULL| T    |   800 |  6400 |     3   (0)| 00:00:01 |                                            
				
					---------------------------------------------------------------------------                                            
				
					SQL_ID  7qcp6urqh7d2j, child number 3                                                                                  
				
					-------------------------------------                                                                                  
				
					select sum(object_id) from t where id<:v_id  -->3號子游標為全表掃描，但其預估的行數等於實際影響行數的值499             
				
					Plan hash value: 2966233522                                                                                            
				
					---------------------------------------------------------------------------                                            
				
					| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                            
				
					---------------------------------------------------------------------------                                            
				
					|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |                                            
				
					|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |                                            
				
					|   2 |   TABLE ACCESS FULL| T    |   499 |  3992 |     3   (0)| 00:00:01 |                                            
				
					---------------------------------------------------------------------------

二、自適應遊標共享的幾個相關檢視

1、v$sql_cs_statistics
用於監控自適應遊標共享的相關統計資訊.下面的查詢中列出了每個子游標的peeking情況,以及執行次數,預處理行數,BUFFER_GETS等

					[sql] view plaincopy
					
 print?
				
					SQL> select child_number,bind_set_hash_value,peeked,executions,rows_processed,buffer_gets                        
				
					  2  from v$sql_cs_statistics where sql_id='7qcp6urqh7d2j'                                                       
				
					  3  order by 1;                                                                                                 
				
					CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS                                         
				
					------------ ------------------- - ---------- -------------- -----------                                         
				
					           0          1706589901 Y          1             17          69     -->17行，索引範圍掃描               
				
					           1          3116944019 Y          1            900           5     -->900行，全表掃描                  
				
					           2          1328865654 Y          1            800           5     -->800行，全表掃描                  
				
					           3          1624350242 Y          1            500           5     -->500行，全表掃描

2、v$sql_cs_selectivity
顯示每個子游標的遊標的選擇性範圍。下面的查詢中列出了謂詞，選擇性範圍，列上的選擇性的值

					[sql] view plaincopy
					
 print?
				
					SQL> select child_number,predicate,range_id,low,high from v$sql_cs_selectivity         
				
					  2  where sql_id='7qcp6urqh7d2j' order by 1;                                          
				
					CHILD_NUMBER PREDICATE            RANGE_ID LOW        HIGH                             
				
					------------ ------------------ ---------- ---------- ----------                       
				
					           1 <V_ID                       0 0.809910   0.989890                         
				
					           2 <V_ID                       0 0.719820   0.989890                         
				
					           3 <V_ID                       0 0.449550   0.989890

3、v$sql_cs_histogram
用於決定一個查詢是否允許自適應遊標共享，以直方圖形式儲存

					[sql] view plaincopy
					
 print?
				
					SQL> select CHILD_NUMBER,BUCKET_ID,COUNT from v$sql_cs_histogram where SQL_ID='7qcp6urqh7d2j'     
				
					  2  order by 1;                                                                                  
				
					CHILD_NUMBER  BUCKET_ID      COUNT                                                                
				
					------------ ---------- ----------                                                                
				
					           0          1          1                                                                
				
					           0          0          1                                                                
				
					           0          2          0                                                                
				
					           1          1          0                                                                
				
					           1          0          1                                                                
				
					           1          2          0                                                                
				
					           2          1          0                                                                
				
					           2          0          1                                                                
				
					           2          2          0                                                                
				
					           3          1          0                                                                
				
					           3          0          1                                                                
				
					           3          2          0

三、總結
    1、自適應遊標共享在SQL語句首次執行時(使用繫結變數)，進行窺探，並記錄窺探結果，如果後續有相同的的SQL語句執行，則對窺探結果進行比較以判斷是否需要生成新的執行計劃。此即為繫結變數是否敏感。
    2、繫結變數的可知性用於判斷當前的遊標是否為可擴充套件性遊標共享，當不可知時，則遊標被廢棄。
    3、自適應遊標共享的實質是在Oracle 10g以前的基礎上實現了多次繫結變數窺探，增加了獲取最佳執行計劃選擇的機率。
    4、儘管使用自適應遊標共享特性，但並不能保證每次執行SQL語句一定按最佳計劃執行，本次演示中SQL語句的第二次執行並非最佳執行計劃。
    5、自適應遊標共享也不能保證兩次執行相同SQL語句一定按相同的執行計劃執行，因為自適應遊標共享會不斷嘗試peeking。

在Oracle中，下面有關SQL繫結變數的描述中，錯誤的是（）

A、繫結變數是指在SQL語句中使用變數，改變變數的值來改變SQL語句的執行結果

B、使用繫結變數，可以減少SQL語句的解析，能減少資料庫引擎消耗在SQL語句解析上的資源

C、使用繫結變數，提高了程式設計效率和可靠性，減少訪問資料庫的次數

D、使用繫結變數，查詢最佳化器會預估的比字面變數更加真實

繫結變數是相對文字變數來講的，所謂文字變量是指在SQL中直接書寫查詢條件，這樣的SQL在不同條件下需要反覆解析，繫結變數是指使用變數來代替直接書寫條件，查詢繫結變數在執行時傳遞，然後繫結執行。優點是減少硬解析，降低CPU的爭用，節省SHARED_POOL；缺點是不能使用固定的執行計劃，SQL最佳化比較困難。

本題中，對於選項A，繫結變數就是之前不知道具體的值，只有執行的時候才知道值，改變變數的值來改變SQL語句的執行結果。所以，選項A錯誤。

對於選項B，使用繫結變數，可以減少SQL語句的解析，說法正確。所以，選項B錯誤。

對於選項C，使用繫結變數，減少解析次數，提高了程式設計效率和可靠性。所以，選項C錯誤。

對於選項D，使用繫結變數，查詢最佳化器不知道具體的值，所以，其執行計劃也不真實。所以，選項D正確。

所以，本題的答案為D。

About Me

...............................................................................................................................

● 本文整理自網路（http://blog.csdn.net/leshami/article/details/6904229）

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-04-28 09:00 ~ 2017-04-30 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標

繫結變數優缺點、使用、繫結變數窺探

2、繫結變數窺探

Oracle自適應共享遊標

在Oracle中，下面有關SQL繫結變數的描述中，錯誤的是（）

相關文章