Oracle SQL共享的機(jī)制

1、Oracle SQL共享的機(jī)制-在php中使用綁定變量的方法在網(wǎng)站應(yīng)用的研發(fā)中，一個(gè)較流行的方法便是使用php編程，php編程方法簡(jiǎn)單明了，直接在html中嵌 入php代碼，對(duì)于研發(fā)基于數(shù)據(jù)庫(kù)的動(dòng)態(tài)應(yīng)用十分方便。不過(guò)，許多研發(fā)員在用php研發(fā)基于Oracle數(shù) 據(jù)庫(kù)的應(yīng)用時(shí)，仍沿習(xí)研發(fā)基于Mysql的應(yīng)用的方法，未使用綁定變量，使得Oracle SGA區(qū)中SQL語(yǔ) 句的重用性極低，浪費(fèi)了內(nèi)存，降低了系統(tǒng)性能。因而，在此，先簡(jiǎn)單介紹一下Oracle SQL共享的機(jī)制，再介紹怎么在php中使用綁定變量，從而實(shí)現(xiàn) Oracle數(shù)據(jù)庫(kù)中sql語(yǔ)句的共享。一、Oracle SQL語(yǔ)句共享區(qū)的機(jī)制1、SG

2、A區(qū)結(jié)構(gòu)：Oracle數(shù)據(jù)庫(kù)啟動(dòng)時(shí)，在內(nèi)存中分配了一大片空間，為系統(tǒng)全局區(qū)(System Global Area)，其中包含Sql共 享池及數(shù)據(jù)緩存器(Data Buffer Cache)。SGA區(qū)的共享池部分主要由三個(gè)區(qū)域組成：庫(kù)緩存，字典緩存， 控制結(jié)構(gòu)。庫(kù)緩存包括共享SQL區(qū)，私有SQL區(qū)，PL/SQL過(guò)程及包，及控制結(jié)構(gòu)，如鎖及庫(kù)緩存 handles。用戶(hù)執(zhí)行過(guò)的Sql語(yǔ)句存放于Sql共享池中，以便能重用，提高其效率。2、SQL語(yǔ)句在內(nèi)存中的分布：Oracle將其執(zhí)行的每一條SQL語(yǔ)句存于共享SQL區(qū)及私有SQL區(qū)中。當(dāng)Oracle發(fā)現(xiàn)兩個(gè)用戶(hù)執(zhí)行 相同的SQL語(yǔ)句時(shí)，則為這些用戶(hù)重用

3、SQL共享區(qū)。不過(guò)，每一用戶(hù)必須在私有SQL區(qū)中擁有該語(yǔ)句的 一份獨(dú)立拷貝。共享SQL區(qū)包含單一 SQL語(yǔ)句或相同的SQL語(yǔ)句的解析樹(shù)及執(zhí)行計(jì)劃。通過(guò)為多個(gè)相 同的DML語(yǔ)句使用一個(gè)共享SQL區(qū)，Oracle節(jié)省了內(nèi)存的使用，特別是當(dāng)許多用戶(hù)使用同一應(yīng)用時(shí)。共 享SQL區(qū)永遠(yuǎn)駐留在共享池中。www.bitsCN.com3、SQL語(yǔ)句解析時(shí)進(jìn)行的內(nèi)存分配操作：當(dāng)一個(gè)SQL語(yǔ)句被提交至Oracle去執(zhí)行時(shí)，Oracle自動(dòng)地執(zhí)行以下內(nèi)存分配步驟：Oracle檢查共享池，看是否在共享SQL區(qū)中已存在相同的語(yǔ)句。若有，則該共享SQL區(qū)被用于執(zhí)行該 語(yǔ)句的新實(shí)例的后續(xù)操作。相應(yīng)地，若在共享池中無(wú)該語(yǔ)句，

4、則Oracle在共享池中分配一新的共享SQL 區(qū)，其尺寸決定于該語(yǔ)句的復(fù)雜性。若一個(gè)SQL語(yǔ)句需求新的共享SQL區(qū)而整個(gè)共享池已被分配完畢， 則Oracle可通過(guò)一個(gè)最近最少修改機(jī)理從共享池中釋放部分項(xiàng)目，直至可為新語(yǔ)句的共享SQL區(qū)提供足 夠的空間。若Oracle釋放了一個(gè)共享SQL區(qū)，則和該區(qū)相關(guān)聯(lián)的SQL語(yǔ)句在下次重執(zhí)行時(shí)，須重新 解析并重新分配至另一共享SQL區(qū)。在兩種情況下，用戶(hù)專(zhuān)用SQL區(qū)和包含該語(yǔ)句的共享SQL區(qū)相關(guān) 聯(lián)。因而，若能使語(yǔ)句得到共享，則其將減少內(nèi)存的占用，同時(shí)，減少了 cpu的占用，加快了語(yǔ)句執(zhí)行的速度。 即使一個(gè)光標(biāo)仍處于打開(kāi)狀態(tài)，若其非常久未被使用了，則其共享區(qū)

5、也可能被從共享池中移出。若該光標(biāo) 以后又被用于執(zhí)行其語(yǔ)句，則Oracle重解析該語(yǔ)句并且在共享池中分配一新的共享SQL區(qū)。4、私有SQL區(qū) 私有SQL區(qū)包含綁定信息及運(yùn)行時(shí)緩沖等數(shù)據(jù)。每一個(gè)提交一個(gè)SQL語(yǔ)句的會(huì)話(huà)均有一個(gè)私有SQL區(qū)。 每一提交相同SQL語(yǔ)句的用戶(hù)有其使用單一共享SQL區(qū)的私有SQL區(qū)。許多私有SQL區(qū)能和同一共享 SQL區(qū)相關(guān)聯(lián)一個(gè)私有SQL區(qū)包括一個(gè)永久區(qū)和一個(gè)運(yùn)行時(shí)區(qū)：一個(gè)永久區(qū)包含在執(zhí)行過(guò)程中保持的綁定信息，數(shù)據(jù)類(lèi)型轉(zhuǎn)換的代碼（在定義的數(shù)據(jù)類(lèi)型和查詢(xún)列的數(shù)據(jù)類(lèi) 型不一致時(shí)），及其他狀態(tài)信息（比如遞歸或遠(yuǎn)程光標(biāo)數(shù)或并行查詢(xún)的狀態(tài)）。永久區(qū)的尺寸決定于綁定變量 的數(shù)目及語(yǔ)

6、句中指定的列數(shù)。例如，若一個(gè)查詢(xún)中指定了非常多列，則永久區(qū)要大一些。運(yùn)行時(shí)區(qū)包含SQL語(yǔ)句被執(zhí)行時(shí)使用的一些信息。運(yùn)行時(shí)區(qū)的尺寸信賴(lài)于被執(zhí)行的SQL語(yǔ)句的類(lèi)型及其 復(fù)雜性及被該語(yǔ)句處理的行的尺寸。一般而言，用于INSERT, UPDATE,及DELETE的語(yǔ)句其運(yùn)行區(qū)要 比SELECT語(yǔ)句所需的運(yùn)行區(qū)尺寸要小。二、在php中不使用綁定變量和使用綁定變量的語(yǔ)法對(duì)比在php中，若不使用綁定變量，其對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)法為：先解析已用變量值取代變量的語(yǔ)句，ora_parse（光標(biāo)號(hào)，”包含變量的值的sql語(yǔ)句”）；再執(zhí)行語(yǔ)句ora_exec（光標(biāo)號(hào)）；使用綁定變量后，語(yǔ)法為先解析不含變量值的使用綁定變量

7、的語(yǔ)句，再將php變量和sql中綁定變量相綁 定，然后為為變量賦值，最后為執(zhí)行語(yǔ)句。如此，則盡管變量值可不斷改動(dòng)，但語(yǔ)句不會(huì)變化，從而可避免不必要的解析。中國(guó)網(wǎng)管聯(lián)盟ora_parse（光標(biāo)號(hào)，”包含未和變量對(duì)應(yīng)的綁定變量的sql語(yǔ)句”）；ora_bind（int光標(biāo)號(hào)，string PHP變量名，string SQL參數(shù)名,int變量值長(zhǎng),int 變量類(lèi)型）;語(yǔ)法中的type為可省略的參數(shù)選項(xiàng)，能設(shè)成下面三種數(shù)字之一：0為內(nèi)定值，表示輸入/輸出（in/out）； 1 表示輸入（in） ； 2表示輸出（out）。然后，為為php變量進(jìn)行賦值。最后，才為執(zhí)行該語(yǔ)句。ora_exec（光標(biāo)號(hào)）；三

8、、在 php 中不使用綁定變量和使用綁定變量的對(duì)比示例1、示例1，在select語(yǔ)句中使用綁定變量：語(yǔ)句：select sid, serial#, machine from v$session where username=用戶(hù)名；假設(shè)執(zhí)行三次，其參數(shù)值分別為user1, user2, user3未使用綁定變量時(shí)，其語(yǔ)句為：ora_parse($list_cursor, select sid, serial#, machine from v$session where username=$var_username);ora_execute($list_cursor);內(nèi)存中SQL共享區(qū)中便會(huì)存

9、在以下三條語(yǔ)句：select sid, serial#, machine from v$session where username=user1;select sid, serial#, machine from v$session where username=user2; bbs.bitsCN.comselect sid, serial#, machine from v$session where username=user3;由于每次執(zhí)行時(shí)，語(yǔ)句中的var_username值不同，從而語(yǔ)句便相應(yīng)地不同，使得其無(wú)法共享。使用綁定變量時(shí)，其語(yǔ)法為：先解析僅含綁定變量p_1(p: parame

10、ter，參數(shù))，但無(wú)變量值的語(yǔ)句ora_parse($list_cursor, select sid, serial#, machine from v$session where username=:p1);再將php程式變量v_1 (v: Variable變量)和sql語(yǔ)句中的綁定變量p_1相綁定，ora_bind($list_cursor，v_1，p_1，strlen($var_username)，1)；在執(zhí)行語(yǔ)句前，對(duì)該php程式變量進(jìn)行賦值$v_1= $var_username ；然后，執(zhí)行語(yǔ)句。ora_exec($list_cursor)；內(nèi)存中SQL共享區(qū)中只會(huì)存在以下一條語(yǔ)句：s

11、elect sid, serial#, machine from v$session where username=:p_1;而參數(shù)值user1, user2, user3則存放在執(zhí)行該語(yǔ)句的用戶(hù)會(huì)話(huà)的私有sql區(qū)此時(shí)，在系統(tǒng)sql共享區(qū)中，將該語(yǔ)句分兩部分存儲(chǔ)，一部分為前面僅含綁定變量的語(yǔ)句，為共享部分， 一部分為含有變量值的部分，為私有部分。由于共享部分不含值，因而，對(duì)于不同用戶(hù)不同參數(shù)值的查詢(xún)， 其語(yǔ)句為一致的，從而實(shí)現(xiàn)了共享，避免了不必要的解析。中國(guó)網(wǎng)管聯(lián)盟2、示例2，在insert語(yǔ)句中使用綁定變量：語(yǔ)句：insert into test_table values(col1, col

12、2);假設(shè)執(zhí)行三次，其參數(shù)值分別為1,2; 2,3; 3,4未使用綁定變量時(shí)，其語(yǔ)法為：ora_parse($list_cursor, insert into test_table values($var_col1,$var_col2);ora_execute($list_cursor);內(nèi)存中SQL共享區(qū)中便會(huì)存在以下三條語(yǔ)句：insert into test_table values(1,2);insert into test_table values(2,3);insert into test_table values(3,4);使用綁定變量后，其語(yǔ)句為：首先在原放變量的地方放入綁定變量

13、，使其語(yǔ)句能共享，解析語(yǔ)句ora_parse($list_cursor，insert into test_table values(:p_col1,:p_col2) or die ；將php變量和sql語(yǔ)句中的綁定變量相綁定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變量進(jìn)行賦值$v_col0 = $var_col1 ；$v_col1 = $var_col2 ；執(zhí)行語(yǔ)句ora_exec($list_c

14、ursor) ； bbs.bitsCN.com內(nèi)存中SQL共享區(qū)中只會(huì)存在以下一條語(yǔ)句：insert into test_table values(:p_col1,:p_col2);而參數(shù)值則存放在執(zhí)行該語(yǔ)句的用戶(hù)會(huì)話(huà)的私有sql區(qū)，從而由于共享部分為一致的，能在多用戶(hù)中實(shí)現(xiàn)共 享。節(jié)約內(nèi)存及cpu時(shí)間。若為通過(guò)數(shù)組進(jìn)行多組值的插入，則可將ora_parse及ora_bind置于循環(huán)開(kāi)始之前，因?yàn)檎Z(yǔ)句在循環(huán)中 不會(huì)關(guān)閉，而且只是變量值變化，語(yǔ)句本身不變化，因而，只需一次解析及綁定。而將 賦值語(yǔ)句及ora_exec 語(yǔ)句置于循環(huán)中，由于減少了函數(shù)調(diào)用及網(wǎng)絡(luò)傳輸?shù)幕ㄙM(fèi)，更會(huì)大大提高速度。四、在其他

15、系統(tǒng)中使用綁定變量的方法：在PowerBuilder研發(fā)中，對(duì)于支持綁定變量的數(shù)據(jù)庫(kù)系統(tǒng)，PowerBuilder的綁定開(kāi)關(guān)缺省參數(shù)為打開(kāi)， 從而其在系統(tǒng)中參數(shù)位置為?，實(shí)現(xiàn)了語(yǔ)句的共享。若在連接數(shù)據(jù)庫(kù)時(shí)，將DBParm參數(shù)的DisableBind 設(shè)為1,則關(guān)閉綁定開(kāi)關(guān)，不同參數(shù)值的同一語(yǔ)句無(wú)法共享。在Oracle Developer2000研發(fā)的應(yīng)用中，系統(tǒng)也為默認(rèn)使用綁定變量。不過(guò)，在二者中研發(fā)員自定義的 代碼，便需研發(fā)員自己使用綁定變量，否則也會(huì)由于未使用綁定變量而影響性能。五、檢查系統(tǒng)中sql語(yǔ)句共享程度及未使用綁定變量的語(yǔ)句的方法：bitsCN_com 在Oracle 8及以上版本

16、中，我們能通過(guò)查詢(xún)視圖v$sysstat獲知系統(tǒng)中語(yǔ)句解析情況，從而了解綁定變 量的使用情況。select name , valuefrom v$sysstatwhere name like parse count%,;其會(huì)返回兩條記錄：parse count (hard)為“硬解析，即第一次執(zhí)行sql語(yǔ)句時(shí)進(jìn)行的解析，parse count (total)為所有解析次 數(shù)，其由hard和soft兩部分之和組成，soft解析為當(dāng)語(yǔ)句在共享池中找到時(shí)，進(jìn)行的權(quán)限檢查操作，其 速度比hard parse要快得多。因而，若發(fā)現(xiàn)hard parse占total的比率較高，則表示語(yǔ)句未得到非常好 的共享，系統(tǒng)性能將受到影響。此時(shí)，可通過(guò)檢查v$sqlarea視圖或v$sqltext視圖中所有sql語(yǔ)句的內(nèi)容確定哪些語(yǔ)句未使用綁定變量， 并由研發(fā)員相應(yīng)進(jìn)行修改。從v$sqlarea查看sql語(yǔ)句的方法為，select SQL_TEXT, EXECUTIONS , PARSE_CALLSfrom v$sqlareawhere限制條件；其只能查看sql語(yǔ)句的前1000個(gè)字節(jié)。若有超過(guò)1000字節(jié)