久久亚洲电影www电影网,日韩伦理电影免费在线观看,操美女在线看

??? 綁定變量是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只會匹配已經在共享池中相同的對象名。

綁定變量及其優缺點

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義