繫結變數之基本概念

13811135929發表於2017-04-27
變數
 在oracle資料庫對於一條SQL，可能存在硬解析、軟解析以及軟軟解析。
  硬解析(Hard Parse)是指Oracle在執行目標SQL時，在庫快取找不到可以重用的解析樹和執行計劃，而不得不從頭開始解析SQL並生成相應的Parent Cursor和Child Cursor的過程。硬解析不僅僅耗費CPU等硬體資源，更重要的是其會導致閂(Shared Pool Latch以及Library Cache Latch)等爭用。大量的硬解析會嚴重影響系統的效能及其擴充套件性，尤其對於OLTP系統而言。
  那麼怎樣才能降低OLTP應用系統硬解析的數量呢？-----繫結變數。繫結變數的實質就是用一種特殊型別的變數後者稱之為佔位符替代SQL語句中的動態部分，從而保證每次提交的語句都一樣。Oracle資料庫中繫結變數的語法規則為：":variable_name"。下面透過具體實驗來說明繫結變數的具體用法。
 1.簡單SELECT語句使用繫結變數
 執行不含任何繫結變數的SQL語句如下：

點選(此處)摺疊或開啟

  1. select /*no_bind*/ * from emp where empno=7902;
  2. ...省略輸出內容

  4. //檢視執行計劃
  5. SELECT * FROM table(dbms_xplan.display_cursor(null,null,'advanced'));
  6. PLAN_TABLE_OUTPUT
  7. ----------------------------------------------------------------------------------------------------
  8. SQL_ID 8qng5nrymtdc1, child number 0
  9. -------------------------------------
  10. select /*no_bind*/ * from emp where empno=7902
  11. Plan hash value: 2949544139
  12. --------------------------------------------------------------------------------------
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  14. --------------------------------------------------------------------------------------
  15. | 0 | SELECT STATEMENT | | | | 1 (100)| |
  16. | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
  17. |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
  18. --------------------------------------------------------------------------------------
  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------
  21.    2 - access("EMPNO"=7902)
  1. select /*no_bind*/ * from emp where empno=7876;
    1. ...省略輸出內容

    3. //檢視執行計劃
  3. select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
  4. PLAN_TABLE_OUTPUT
  5. ----------------------------------------------------------------------------------------------------
  6. SQL_ID 0x1jqmhdwjg7p, child number 0
  7. -------------------------------------
  8. select /*no_bind*/ * from emp where empno=7876
  9. Plan hash value: 2949544139
  10. --------------------------------------------------------------------------------------
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  12. --------------------------------------------------------------------------------------
  13. | 0 | SELECT STATEMENT | | | | 1 (100)| |
  14. | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
  15. |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |
  16. --------------------------------------------------------------------------------------
  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------
  19.    2 - access("EMPNO"=7876)
 查詢v$sqlarea檢視：

點選(此處)摺疊或開啟

  1. SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%no_bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
  2. SQL_TEXT                                                     SQL_ID        VERSION_COUNT EXECUTIONS
  3. ------------------------------------------------------------ ------------- ------------- ----------
  4. select /*no_bind*/ * from emp where empno=7902               8qng5nrymtdc1  1            1
  5. select /*no_bind*/ * from emp where empno=7876               0x1jqmhdwjg7p  1            1
兩條語句唯一的區別在於WHERE條件不同，且可以看到兩條語句的執行計劃都是索引範圍掃描。僅僅由於SQL文字的細微差別，導致資料庫不得不進行兩次硬解析，佔用了系統的寶貴資源。
我們嘗試用繫結變數重複上面的工作，看一下硬解析的次數以及效果。

點選(此處)摺疊或開啟

  1. var x number;
  2. exec :x := 7902;
  3. select /*bind*/ * from emp where empno=:x;
  4. ...省略輸出內容
  5. exec :x :=7876;
  6. select /*bind*/ * from emp where empno=:x;
  7. ...省略輸出內容

  9. SELECT t.SQL_TEXT,t.SQL_ID,t.VERSION_COUNT,t.EXECUTIONS FROM v$sqlarea t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
  10. SQL_TEXT                                                     SQL_ID        VERSION_COUNT EXECUTIONS
  11. ------------------------------------------------------------ ------------- ------------- ----------
  12. select /*bind*/ * from emp where empno=:x                    5cnzfrvqdqccg  1            2

  13.
  14. SELECT t.SQL_TEXT,t.SQL_ID,t.CHILD_NUMBER,t.EXECUTIONS FROM v$sql t WHERE t.SQL_TEXT LIKE '%bind%' AND t.SQL_TEXT NOT LIKE '%v$sql%';
  15. SQL_TEXT                                                     SQL_ID        CHILD_NUMBER EXECUTIONS
  16. ------------------------------------------------------------ ------------- ------------ ----------
  17. select /*bind*/ * from emp where empno=:x                    5cnzfrvqdqccg 0            2
VERSION_COUNT=1,說明兩條語句只進行了一次硬解析（從v$sql輸出結果也印證了這一點），並執行了EXECUTIONS=2次。相比前面未使用繫結變數的情況，使用繫結變數減少了硬解析的次數。

2.儲存過程使用繫結變數
  分別建立兩個PROCEDURE，兩個儲存過程的作用一樣都是向表插入10萬條資料，唯一不同的是其中PROC1未使用變數，PROC2使用繫結變數。

點選(此處)摺疊或開啟

  1. CREATE TABLE T (A INT);
  2. //未使用繫結變數
  3. CREATE OR REPLACE PROCEDURE PROC1
  4. AS
  5. BEGIN
  6.   FOR i IN 1 .. 100000
  7.   LOOP
  8.   EXECUTE IMMEDIATE 'INSERT INTO T VALUES('||i|| ')' ;
  9.   END LOOP ;
  10. END ;
  11. //使用繫結變數
  12. CREATE OR REPLACE PROCEDURE PROC2
  13. AS
  14. BEGIN
  15.   FOR i IN 1 .. 100000
  16.   LOOP
  17.   EXECUTE IMMEDIATE 'INSERT INTO T VALUES(:X)' USING i;
  18.   END LOOP ;
  19. END ;
下面具體看一下硬解析次數以及執行時間:
proc1的執行情況如下：

點選(此處)摺疊或開啟

  1. select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
  2. NAME                                                              VALUE
  3. ---------------------------------------------------------------- ----------
  4. parse count (total)                                               505
  5. parse count (hard)                                                312
  6. parse count (failures)                                            4
  7. parse count (describe)                                            0

  9. exec proc1;
  10. Elapsed: 00:01:23.09

  12. select name,value from v$mystat a,v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
  13. NAME                                                              VALUE
  14. ---------------------------------------------------------------- ----------
  15. parse count (total)                                               100515
  16. parse count (hard)                                                100313
  17. parse count (failures)                                            4
  18. parse count (describe)                                            0
執行前後硬解析次數增加了10萬次左右(100313-312=100001),每一次insert 都進行一次硬解析。同時注意到執行時間為1分鐘23秒。
proc2的執行情況：

點選(此處)摺疊或開啟

  1. select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
  2. NAME                                                             VALUE
  3. ---------------------------------------------------------------- ----------
  4. parse count (total)                                              100570
  5. parse count (hard)                                               100345
  6. parse count (failures)                                           4
  7. parse count (describe)                                           0
  8. Elapsed: 00:00:00.01

  10. SQL> exec proc2;
  11. PL/SQL procedure successfully completed.
  12. Elapsed: 00:00:11.02

  13.
  14. select name,value from v$mystat a , v$statname b where a.statistic#=b.statistic# and b.name like '%parse count%';
  15. NAME                                                             VALUE
  16. ---------------------------------------------------------------- ----------
  17. parse count (total)                                              100573
  18. parse count (hard)                                               100347
  19. parse count (failures)                                           4
  20. parse count (describe)                                           0
執行前後硬解析次數僅增加2次(100347-100345)，執行時間僅用了11秒。
看到PROC1的執行時間幾乎是PROC2執行時間的8倍。

3.JAVA語言使用繫結變數的情況
java PrepareStatement物件，可以將sql語句做預編譯操作，被封裝的sql語句可以包含動態引數，減少編譯的次數，提高資料庫效能.PrepareStatement的具體使用方法如下：

點選(此處)摺疊或開啟

  1. for (int i =1 ;i<=1000;i++ ) {
  2. v_sql ="select object_name from objects where object_id= :x ";
  3. stmt=conn.prepareStatement(v_sql);
  4. stmt.setString(1,Integer.toString(i));
  5. rset = stmt.executeQuery();
  6. stmt.close();
  7. }

【總結】
TOM曾說過：Oracle將已解析、已編譯的SQL連同其他內容儲存在共享池(shared pool)中，這個是系統全域性區(System Golbal Area,SGA)中一個非常重要的共享記憶體結構。如果你確實想讓Oracle緩慢執行，甚至幾近停頓，只要根本不使用繫結變數就可以辦到，足以見繫結變數的重要性。






  
 
 

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

相關文章