Oracle的變數綁定與變數窺視

資料庫環境

LEO1@LEO1> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

一用樣本說明綁定變數的應用領域是OLTP而不是OLAP

變數綁定：這是一個老生常談的話題，我所理解的綁定就是執行計畫的綁定，我所理解的變數就是謂詞替換的變數。

變數綁定機制：要說機制不得不說一下SQL執行的過程，三部曲：解析–>執行->取操作，而綁定變數就發生在解析這一步，而解析又分成硬解析和軟解析。

硬解析：當一條SQL語句第一次執行時，首先產生執行計畫，並把這個執行計畫存放到shared_pool的library cache中，這個過程叫做硬解析。

軟解析：如果SQL語句已經被硬解析過了，那麼可以直接從library cache中抽取現成的執行計畫來重用，這個過程叫做軟解析，目的減少產生執行計畫這方面的資源消耗。為什麼這麼說呢，硬解析會消耗一些系統資源，尤其是CPU的資源，從而影響系統的效率，如果能把這方面的影響消除，那麼對系統當然是多多益善了，哈多侃了幾句。

SQL詳細執行過程：當oracle接收到一條sql語句時，首先會把這條sql語句字元做成雜湊值，然後到library cache中尋找是否有和這個雜湊值相匹配的sql存在，如果有就直接使用這個sql的執行計畫去執行當前的sql語句，最後將結果返回給使用者。如果沒有找到相同的雜湊值，oracle會認為這是一條新的sql，將會重建執行計畫來執行（在這個過程中先要檢查文法分析和語義分析），最後將結果返回給使用者。

實驗

下面我們示範一下綁定變數和非綁定變數在資源消耗上的差異

LEO1@LEO1> drop table leo1 purge;               清理環境

Table dropped.

LEO1@LEO1> drop table leo2 purge;  

Table dropped.

LEO1@LEO1> create table leo1 as select * from dba_objects;   建立leo1

Table created.

LEO1@LEO1> create table leo2 as select * from dba_objects;   建立leo2

Table created.

LEO1@LEO1> alter session set tracefile_identifier='bind_variable';   設定trace檔案標識

Session altered.

LEO1@LEO1> alter session set sql_trace=true;      啟動trace功能，追蹤sql資源消耗情況

Session altered.

LEO1@LEO1> begin

for i in 1..100 loop

execute immediate 'select * from leo1 where object_id=:i' using i;

end loop;

end;

/

PL/SQL procedure successfully completed.

我們對一條sql執行了100次並採用了綁定變數技術，oracle對這條sql只有一次硬解析，沒有軟解析，反覆執行100次。

LEO1@LEO1> alter session set sql_trace=false;           關閉trace功能

Session altered.

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select * from leo1 where %';

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------

select * from leo1 where object_id=:i         1          1        100

SQL_TEXT：我們跟蹤的sql語句

PARSE_CALLS：硬解析+軟解析次數       1次   只有硬解析沒有軟解析

LOADS：硬解析次數                    1次

EXECUTIONS：執行次數                 100次

雖說值隱藏在變數中，但在解析環節oracle認為是一樣的

[oracle@leonarding1 trace]$ tkprof LEO1_ora_16433_bind_variable.trc bind_variable.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on Fri Feb 1 13:18:08 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

使用tkprof工具過濾和匯總trace檔案的，sys=no不輸出sys使用者遞迴語句，預設yes，實際上設定成no更具有可讀性