在php中使用繫結變數的方法(Oracle SQL共享的機制)(轉)

但是，許多開發員在用php開發基於Oracle 資料庫的應用時，仍沿習開發基於 Mysql 的應用的方法，未使用繫結變數，使得Oracle SGA 區中SQL語句的重用性極低，浪費了記憶體，降低了系統效能。
因而，在此，先簡單介紹一下Oracle SQL共享的機制，再介紹如何在 php 中使用繫結變數，從而實現Oracle 資料庫中 sql語句的共享。

一、Oracle SQL語句共享區的機制
1、SGA 區結構：
Oracle 資料庫啟動時，在記憶體中分配了一大片空間，為系統全域性區(System Global Area)，其中包含 Sql 共享池及資料快取器(Data Buffer Cache)。SGA 區的共享池部分主要由三個區域組成: 庫快取, 字典快取, 控制結構。庫快取包括共享 SQL 區，私有SQL區，PL/SQL 過程及包, 及控制結構，如鎖及庫快取handles。使用者執行過的 Sql 語句存放於 Sql 共享池中，以便可以重用，提高其效率。

2、SQL語句在記憶體中的分佈：
Oracle 將其執行的每一條 SQL 語句存於共享SQL 區及私有 SQL 區中。當Oracle 發現兩個使用者執行相同的SQL語句時，則為這些使用者重用SQL共享區。但是，每一使用者必須在私有SQL區中擁有該語句的一份獨立複製。共享SQL 區包含單一SQL語句或相同的SQL語句的解析樹及執行計劃。透過為多個相同的DML語句使用一個共享SQL區，Oracle 節省了記憶體的使用, 特別是當許多使用者使用同一應用時。 共享SQL區永遠駐留在共享池中。

3、SQL語句解析時進行的記憶體分配操作：
當一個SQL語句被提交至Oracle 去執行時，Oracle 自動地執行以下記憶體分配步驟:
Oracle 檢查共享池，看是否在共享SQL區中已存在相同的語句。 若有，則該共享SQL區被用於執行該語句的新例項的後續操作。 相應地，若在共享池中無該語句，則Oracle在共享池中分配一新的共享SQL區,其尺寸決定於該語句的複雜性。 若一個SQL語句要求新的共享SQL區而整個共享池已被分配完畢，則 Oracle 可透過一個最近最少修改機理從共享池中釋放部分專案，直至可為新語句的共享SQL區提供足夠的空間。 若 Oracle釋放了一個共享 SQL 區，則與該區相關聯的SQL語句在下次重執行時，須重新解析並重新分配至另一共享SQL區。 在兩種情況下，使用者專用SQL區與包含該語句的共享SQL區相關聯。
因而，若能使語句得到共享，則其將減少記憶體的佔用，同時，減少了cpu 的佔用，加快了語句執行的速度。
即使一個游標仍處於開啟狀態，若其很久未被使用了，則其共享區也可能被從共享池中移出。若該游標以後又被用於執行其語句，則Oracle重解析該語句並且在共享池中分配一新的共享SQL區。

4、私有SQL區
私有SQL區包含繫結資訊及執行時緩衝等資料。 每一個提交一個SQL語句的會話均有一個私有SQL區。 每一提交相同SQL語句的使用者有其使用單一共享SQL區的私有SQL區。許多私有SQL區可以與同一共享SQL區相關聯
一個私有SQL區包括一個永久區和一個執行時區:
一個永久區包含在執行過程中保持的繫結資訊，資料型別轉換的程式碼(在定義的資料型別與查詢列的資料型別不一致時), 及其它狀態資訊(比如遞迴或遠端游標數或並行查詢的狀態)。 永久區的尺寸決定於繫結變數的數目及語句中指定的列數。 例如, 若一個查詢中指定了很多列，則永久區要大一些。
執行時區包含SQL語句被執行時使用的一些資訊。 執行時區的尺寸信賴於被執行的SQL語句的型別及其複雜性及被該語句處理的行的尺寸。 一般而言, 用於INSERT, UPDATE, 及 DELETE 的語句其執行區要比 SELECT 語句所需的執行區尺寸要小。

二、在 php 中不使用繫結變數與使用繫結變數的語法對比
在 php 中，若不使用繫結變數，其對資料庫的操作語法為：

先解析已用變數值取代變數的語句，
ora_parse(游標號，"包含變數的值的sql語句")；
再執行語句
ora_exec(游標號)；

使用繫結變數後，語法為先解析不含變數值的使用繫結變數的語句，再將php 變數與sql 中繫結變數相繫結，然後為為變數賦值，最後為執行語句。
如此，則儘管變數值可不斷改變，但語句不會變化，從而可避免不必要的解析。
ora_parse(游標號，"包含未與變數對應的繫結變數的sql語句")；
ora_bind(int 游標號, string PHP 變數名, string SQL 引數名, int 變數值長, int [變數型別] );
語法中的 type 為可省略的引數選項，可以設成下面三種數字之一：0 為內定值，表示輸入/輸出 (in/out)；1 表示輸入 (in)；2 表示輸出 (out)。
然後，為為php變數進行賦值。
最後，才為執行該語句。
ora_exec(游標號)；

三、在 php 中不使用繫結變數與使用繫結變數的對比示例
1、示例1，在select 語句中使用繫結變數：
語句：select sid, serial#, machine from v$session where username='使用者名稱';
假設執行三次，其引數值分別為 user1, user2, user3

未使用繫結變數時，其語句為：
ora_parse($list_cursor, "select sid, serial#, machine from v$session where username=$var_username");
ora_execute($list_cursor);

記憶體中SQL共享區中便會存在以下三條語句：
select sid, serial#, machine from v$session where username='user1';
select sid, serial#, machine from v$session where username='user2';
select sid, serial#, machine from v$session where username='user3';
由於每次執行時，語句中的var_username 值不同，從而語句便相應地不同，使得其無法共享。


使用繫結變數時，其語法為：
先解析僅含繫結變數 p_1（p: parameter，引數），但無變數值的語句
ora_parse($list_cursor, "select sid, serial#, machine from v$session where username=:p1");
再將 php 程式變數 v_1 (v: Variable 變數)與 sql 語句中的繫結變數 p_1 相繫結，
ora_bind($list_cursor，"v_1"，"p_1"，strlen($var_username)，1)；
在執行語句前，對該php 程式變數進行賦值
$v_1= $var_username；
然後，執行語句。
ora_exec($list_cursor)；

記憶體中SQL共享區中只會存在以下一條語句：
select sid, serial#, machine from v$session where username=:p_1;
而引數值user1, user2, user3 則存放在執行該語句的使用者會話的私有sql區

此時，在系統 sql 共享區中，將該語句分兩部分儲存，一部分為前面僅含繫結變數的語句，為共享部分，一部分為含有變數值的部分，為私有部分。由於共享部分不含值，因而，對於不同使用者不同引數值的查詢，其語句為一致的，從而實現了共享，避免了不必要的解析。

2、示例2，在insert 語句中使用繫結變數：
語句：insert into test_table values(col1, col2);
假設執行三次，其引數值分別為 1,2; 2,3; 3,4
未使用繫結變數時，其語法為：
ora_parse($list_cursor, "insert into test_table values($var_col1,$var_col2)");
ora_execute($list_cursor);
記憶體中SQL共享區中便會存在以下三條語句：
insert into test_table values(1,2);
insert into test_table values(2,3);
insert into test_table values(3,4);

使用繫結變數後，其語句為：
首先在原放變數的地方放入繫結變數，使其語句可以共享, 解析語句
ora_parse($list_cursor，"insert into test_table values(:p_col1,:p_col2)") or die；
將 php變數與 sql 語句中的繫結變數相繫結
ora_bind($list_cursor，"v_col0"，"p_col1"，strlen($var_col1)，1)；
ora_bind($list_cursor，"v_col1"，"p_col2"，strlen($var_col2)，1)；
為php 變數進行賦值
$v_col0 = $var_col1；
$v_col1 = $var_col2；
執行語句
ora_exec($list_cursor)；

記憶體中SQL共享區中只會存在以下一條語句：
insert into test_table values(:p_col1,:p_col2);
而引數值則存放在執行該語句的使用者會話的私有sql區, 從而由於共享部分為一致的，可以在多使用者中實現共享。節約記憶體及cpu 時間。

若為透過陣列進行多組值的插入，則可將 ora_parse 及ora_bind 置於迴圈開始之前，因為語句在迴圈中不會關閉，而且只是變數值變化，語句本身不變化，因而，只需一次解析及繫結。而將 賦值語句及 ora_exec 語句置於迴圈中，由於減少了函式呼叫及網路傳輸的花費，更會大大提高速度。

四、在其它系統中使用繫結變數的方法：
在 PowerBuilder 開發中，對於支援繫結變數的資料庫系統，PowerBuilder 的繫結開關預設引數為開啟，從而其在系統中引數位置為"?"，實現了語句的共享。若在連線資料庫時，將DBParm 引數的DisableBind設為1，則關閉繫結開關，不同引數值的同一語句無法共享。
在 Oracle Developer2000 開發的應用中，系統也為預設使用繫結變數。但是，在二者中開發員自定義的程式碼，便需開發員自己使用繫結變數，否則也會由於未使用繫結變數而影響效能。

五、檢查系統中sql語句共享程度及未使用繫結變數的語句的方法：
在 Oracle 8 及以上版本中，我們可以透過查詢檢視 v$sysstat 獲知系統中語句解析情況，從而瞭解繫結變數的使用情況。
select name , value
from v$sysstat
where name like 'parse count%';
其會返回兩條記錄：
parse count (hard) 為“硬”解析，即第一次執行sql 語句時進行的解析，parse count (total) 為所有解析次數，其由hard 與 soft 兩部分之和組成，soft 解析為當語句在共享池中找到時，進行的許可權檢查操作，其速度比 hard parse 要快得多。因而，若發現 hard parse 佔total 的比率較高，則表示語句未得到很好的共享，系統效能將受到影響。
此時，可透過檢查 v$sqlarea 檢視或 v$sqltext 檢視中所有sql語句的內容確定哪些語句未使用繫結變數，並由開發員相應進行修改。

從 v$sqlarea 檢視 sql 語句的方法為,
select SQL_TEXT, EXECUTIONS , PARSE_CALLS
from v$sqlarea
where 限制條件；
其只能檢視 sql 語句的前1000個位元組。若有超過1000位元組的sql語句，則應透過v$sqltext 檢視檢視。

select sql_text, piece, hash_value
from v$sqltext
where 限制條件
order by hash_value, piece;
其為按每行64位元組分佈，piece為行號。
為了不影響效能，一般為先將某一時間點的 v$sqlarea 的內容複製到一個臨時表中，再對該表中記錄進行分析。
在 Oracle 7 中，只能從 v$sysstat 中查出所有的解析計數，但仍可從 v$sqlarea 及 v$sqltext 中查出未使用繫結變數的語句並進行修改。