### Oracle裡的繫結變數

使用繫結變數，是可以重用解析樹和執行計劃基礎條件.

#### 繫結變數的典型寫法

```
--sql
var x number;
exec :x := 7369;
select ename from emp where empno= :x;
--plsql
declare
  vc_name varchar2(10);
begin
  execute immediate 'select ename from emp whre empno= :1' into vc_name using 7369;
  dbms_output.put_line(vc_name);
end;
/
--execute immediate [帶繫結變數的sql] using [對應繫結變數的具體輸入值]

--dml語句的繫結變數
declare
   vc_sql_1 varchar2(4000);
   vc_sql_2 varchar2(4000);
   n_temp_1 number;
   n_temp_1 number;
begin
  vc_sql_1 := 'insert into emp(empno,ename,job) values(:1,:2,:3)';
  execute immediate vc_sql_1 using 7370,'CUIHUA2','DBA';
  n_temp_1 := sql%rowcount;
  vc_sql_2 := 'insert into emp(empno,enmae,job) values(:1,:1,:1)';
  execute immediate vc_sql_2 using 7371,'CUIHUA3','DBA';
  n_temp_2 := sql%rowcount;
  dbms_output.put_line(to_char(n_temp_1+n_temp_2));
  commit;
end;
/
--using  根據位置傳入相關變數引數值

--動態sql可以使用繫結變數,returning 可以和帶繫結變數的目標sql連用，目的把受該sql影響的行記錄的對應欄位值給取出來. 
--eg
declare
    vc_column varchar2(10);
    vc_sql varchar2(4000);
    n_temp number;
    vc_name varchar2(10);
begin
   vc_column := 'empno';
   vc_sql :='delete from emp where ' || vc_column || ' = :1 returning ename into :2';
   execute immediate vc_sql using 7369 returning into vc_name;
   dbms_output.put_line(vc_ename);
   commit;
end;
/
```

###### PL/SQL中批次繫結的典型用法

> 主要優勢，一次處理一批資料。

可以見到地將PL/SQL引擎看做專門用來處理PL/SQL程式碼塊中除了sql之外的所有部分(eg:變數、複製、迴圈等)子系統，SQL引擎用來處理sql語句的子系統。 這裡的PL/SQL引擎和SQL引起上下文切換就是指他們之間的互動。

```
--減少互動，提高效能
fetch cursorname bulk collect into [自定義的屬組] <limit cn_batch_size="">

--eg  "forall" 表示一次執行一批sql
declare
    cur_emp sys_refcursor;
    vc_sql  varchar2(4000);
    type namelist is table of varchar2(10);
    enames namelist;
    CN_BATCH_SIZE constant pls_integer :=1000;
begin
   vc_sql := 'select ename from emp where empno > :1';
   open cur_emp for vc_sql using 7900;
   loop
     fetch cur_emp bulk collect into enames limit CN_BATCH_SIZE;
     
     for i in 1..enames.count loop
      dbms_output.put_line(enames(i));
     end loop;
    exit when enames.count < CN_BATCH_SIZE;
    end loop;
close cur_emp;
end;
/
```

##### 繫結變數的使用原則和最佳實踐

- OLTP系統，建議使用繫結變數，最好是批次使用，前臺程式碼和後臺PL/SQL程式碼都使用批次繫結。
- 對於OLTP/OLAP混合型，如果有迴圈，迴圈內的SQL腰使用批次繫結變數。

page 245

### Oracle裡的遊標共享

未使用繫結變數再整改代價大，繫結變數的窺探影響
 常規遊標解決第一個問題，自適應遊標共享解決第二個問題
 
 #### 常規遊標共享
 
 
```
--引數cursor_sharing控制
--exact 預設不會自動替換
--similar  會被替換，12c之後過時，問題較多，對於它認為不安全的，都會執行一次硬解析
--force 強制，不再適用於Oracle11g及後續版本
```

> 如果想在不改變一行程式碼的情況下，設定成force可以，雖然不是理想的方案，省事。

#### 自適應遊標

> 啟動了繫結變數窺探的情況前提條件下，讓目標SQL在其可能的多個執行計劃之間“自適應”地做出選擇，而不再像之前那樣必須刻板地沿用該sql硬解析時所產生的解析樹和執行計劃。

擴充套件遊標共享做的第一件事情就是將目標sql所對應的Child Cursor標記為Bind Sensitive。 它就是指Oracle覺得某個含繫結變數的目標SQL的執行計劃可能會隨著所傳入的繫結 變數輸入值的變化而變化。

當滿足如下三個條件時，目標sql所對應的Child Cursor就會被Oracle標記為Bind Sensitive
- 啟動了繫結變數窺探
- 該SQL使用了繫結變數（不管是sql自帶還是開啟常規遊標共享後系統產生的）
- 該SQL使用的是不安全的為此條件（例如範圍查詢，目標列上有直方圖統計資訊的等值查詢等）

自適應遊標共享要做的第二件事將目標SQL所對應的Child Cursor標記為Bind Aware。指Oracle已經確定某個含繫結變數的目標SQL的執行計劃會隨著所傳入的繫結變數輸入值的變化而變化

當滿足一下兩個條件時，目標SQL所對應的子游標就會被標記為Bind Aware
- 該SQL所對應的Child Cursor在之前已經被標記為Bind Sensitive
- 該SQL在接下來連續兩次執行時，所對應的runtime統計資訊與該sql之前硬解析時所對應的runtime統計資訊均存在較大差異。

> 對於自適應遊標共享而言，v$sql中列IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE分別用來表示Child Cursor是和否，及是否共享。共享的含義是指儲存在該Child Cursor中的解析樹和執行計劃是否能被重用。非共享不能被重用，並且也會在第一時間被age out出shared Pool.

> 與自適應遊標共享兩個重要檢視，v$sql_cs_statistics(用於顯示指定Child Cursor中儲存的runtime統計資訊)  v$sql_cs_selectivity（用於顯示指定的、已經被標記為Bind Aware的子游標中儲存的含繫結變數的為此條件所對應的可選擇率的範圍）

###### 自適應遊標的整體流程

1. 第一次執行，硬解析，根據一系列條件(如繫結變數、相關引數、是否有直方圖等)來判斷是否將該sql所對應的子游標標記為Bind Sensitive。標記後，物件的runtime統計資訊額外地儲存在該sql的子游標中。
2. 第二次執行，軟解析，重用該sql第一次子游標的解析樹和執行計劃
3. 第三次執行時，如該sql對應的子游標被標記為Bind Sensitive，同時第二次和第三次記錄的runtime統計資訊和該sql第一次硬解析時所記錄的runtime統計資訊均存在較大差異，第三次會使用硬解析。產生新的子游標，且Oracle會標記新的子游標為Bind Aware。
4. 標記為Bind Aware的子游標所對應的目標sql，當該sql再次被執行時，Oracle就會根據當前傳入的繫結變數值所對應的謂詞條件可選擇率，來決定此時使用哪種解析方式。
5. 如果硬解析，且所產生的執行計劃和原有子游標相同，則除了生成新的子游標之外，還會把儲存相同執行計劃的原有子游標標記為非共享。

> 隱患：可能導致更多硬解析，因為需要對比可選擇率。 可能導致一定數量的額外的子游標掛在同一個父遊標下。這會增加軟解析時查詢匹配子游標的工作量。建議增大shared pool，或禁止自適應遊標

> 一條sql繫結變數超過14個時，oracle11g不會使用繫結變數。

### Oracle裡的應用型別

#### 應用型別一 硬解析

- 沒有使用繫結變數，系統硬解析的比率非常高
- 效能和可擴充套件性是最差的，可用於olap型別系統
- 將CURSOR_SHARING設定為force，可以有效降低硬解析，提高系統的效能和可擴充套件性

#### 應用型別二 軟解析

每次都會經歷 Open Parse,Bind,Execute,Fetch Close

> 引數 SESSION_CACHED_CURSORS 修改為較大值，能進一步提升效能。如果為0，無法透過已快取的Session Cursor中的父遊標來建立目標sql的Session Cursor和父遊標之間的聯絡。

- 有效降低了硬解析
- 執行時，其對應的Session Cursor都需要經歷Open Parse,Bind,Execute,Fetch Close
- 由於軟解析不斷的open，close，以及對庫快取相關Latch的爭用(Oracle11g之前的版本)，會影響效能
- SESSION_CACHED_CURSORS 修改為較大值，能進一步提升效能
- 純粹的軟解析很少見，SESSION_CACHED_CURSORS預設非0

#### 應用型別三 軟軟解析

當Session Cursor對應的sql解析和執行的次數超過3次，Oracle就不會對上述sql執行Close操作，而是標記為Soft Closed.

- open和Close只需經歷一次，其他都需要經歷，如Parse,Bind,Execute,Fetch

#### 應用型別四 一次解析，多次執行

該型別透過一些手段：PL/SQL程式碼的迴圈內部執行目標SQL，使得每一條SQL語句所對應的Session Cursor會反覆經歷Execute和Fetch，其他只用經歷一次。</limit>

【ORACLE】Oracle繫結變數知識梳理

相關文章