繫結變數及其優缺點
繫結變數是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> 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> 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> 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中不能自動使用繫結變數，需要手動設定繫結變數
- 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只會匹配已經在共享池中相同的物件名。
四、相關參考
Oracle 硬解析與軟解析
父遊標、子游標與共享遊標
啟用使用者程式跟蹤
PL/SQL --> 動態SQL
PL/SQL --> 動態SQL的常見錯誤
-->>轉載於:http://blog.csdn.net/leshami/article/details/6904229
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1243324/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 繫結變數優缺點、使用、繫結變數窺探、 Oracle自適應共享遊標變數Oracle
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- 使用繫結變數的一點總結！變數
- ORACLE優化實戰（繫結變數）Oracle優化變數
- 【SQL 調優】繫結變數窺測SQL變數
- 【sql調優】繫結變數與CBOSQL變數
- 【sql調優】使用繫結變數（二）SQL變數
- 【sql調優】使用繫結變數（一）SQL變數
- 繫結變數變數
- TokuDB優缺點總結
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- Oracle 繫結變數Oracle變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 【Redis】四種部署模式的介紹及其優缺點Redis模式
- ORACLE 繫結變數用法總結Oracle變數
- MVP 與 MVVM 優缺點總結MVPMVVM
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- 【效能優化】查詢繫結變數的sql語句優化變數SQL
- 繫結變數窺測的演變變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數的測試變數
- Oracle 繫結變數窺探Oracle變數
- oracle 繫結變數（bind variable）Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- js/javascript 繼承方式主要有哪些?及其優缺點特點JSJavaScript繼承
- ArrayList、Vector、LinkedList的區別及其優缺點？ （轉載）
- Python 字串不可變性的優缺點Python字串
- 關於繫結變數的SQL繫結什麼值變數SQL
- 線性表順序儲存優缺點，線性連結串列的優缺點
- GAN原理，優缺點、應用總結
- RabbitMQ優缺點MQ
- MySQL高階特性——繫結變數MySql變數
- SQL Developer中使用繫結變數SQLDeveloper變數