綁定變量(binding variable),共享池(shared buffer pool), SGA(system global area);
在開(kāi)發(fā)一個(gè)數據庫系統前,有誰(shuí)對Oracle 系統了解很多,尤其是它的特性,好象很少吧;對初學(xué)者來(lái)講,這更是不可能的事情;僅僅簡(jiǎn)單掌握了SQL的寫(xiě)法,就開(kāi)始了數據庫的開(kāi)發(fā),其結果只能是開(kāi)發(fā)一個(gè)沒(méi)有效率,也沒(méi)有可擴展的系統;
因此,我寫(xiě)這個(gè)主題也是希望讓大家更多地掌握Oracle數據庫的特性,從而在架構一個(gè)新系統時(shí),能考慮系統的可擴展,可伸縮性,也兼顧系統的效率和穩定;
使用綁定變量是Oracle數據庫的特性之一;于是大家要問(wèn),為什么使用,怎樣使用,它的使用限制條件是什么?我會(huì )按照這樣的想法去解答大家的疑問(wèn),我也會(huì )以舉例子的方式來(lái)回答這些問(wèn)題;
1. 為什么使用綁定變量?
這是解決Oracle應用程序可伸縮性的一個(gè)關(guān)鍵環(huán)節;而Oracle的共享池就決定了開(kāi)發(fā)人員必須使用綁定變量;如果想要Oracle 運行減慢,甚至完全終止,那就可以不用綁定變量;
這里舉例說(shuō)明上述問(wèn)題;
為了查詢(xún)一個(gè)員工代號是123,你可以這樣查詢(xún):
select * from emp where empno=’123’;
你也可以這樣查詢(xún):
select * from emp where empno=:empno;
象我們往常一樣,你查詢(xún)員工’123’一次以后,有可能再也不用;接著(zhù)你有可能查詢(xún)員工’456’,然后查詢(xún)’789’等等;如果查詢(xún)使用象第一個(gè)查詢(xún)語(yǔ)句,你每次查詢(xún)都是一個(gè)新的查詢(xún)(我們叫它硬編碼的查詢(xún)方法);因此,Oracle每次必須分析,解析,安全檢查, 優(yōu)化等等;
第二個(gè)查詢(xún)語(yǔ)句提供了綁定變量:empno,它的值在查詢(xún)執行時(shí)提供,查詢(xún)經(jīng)過(guò)一次編譯后,查詢(xún)方案存儲在共享池中,可以用來(lái)檢索和重用;在性能和伸縮性方面,這兩者的差異是巨大的,甚至是驚人的;通俗點(diǎn)講,就不是一個(gè)級別;
第一個(gè)查詢(xún)使用的頻率越高,所消耗的系統硬件資源越大,從而降低了用戶(hù)的使用數量;它也會(huì )把優(yōu)化好的其它查詢(xún)語(yǔ)句從共享池中踢出;就象一個(gè)老鼠壞了一鍋湯似的,系統的整體性能降低; 而執行綁定變量,提交相同對象的完全相同的查詢(xún)的用戶(hù)(這句話(huà),大家聽(tīng)起來(lái)比較難理解,隨后我會(huì )給出詳細的解釋),一次性使用就可重復使用,其效率不言耳語(yǔ); 打個(gè)形象的比喻來(lái)說(shuō),第一個(gè)查詢(xún)就象一次性使用的筷子,而第二個(gè)查詢(xún)象是鐵筷子,只要洗干凈,張三李四都能用,合理有效地使用了資源;
下面舉例子去詳細論證上述的問(wèn)題,不使用綁定變量為生病狀況:
這是一個(gè)未使用的綁定變量(吃藥前):
set echo on;(把執行結果顯示出來(lái))
alter system flush shared_pool;
這條語(yǔ)句是清空共項池,每次都必須使用,確保共享池是空的,以提高執行效率;
set timing on(打開(kāi)記時(shí)器.)
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
/
PL/SQL 過(guò)程已成功完成。
執行時(shí)間: 已用時(shí)間: 00: 00: 07.03
這是一個(gè)使用的綁定變量(吃藥后):
set echo on
alter system flush shared_pool;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from all_objects
where object_id = :x'
using i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
PL/SQL 過(guò)程已成功完成。
執行時(shí)間: 已用時(shí)間: 00: 00: 00.75
大家自己比較結果,相差就是一個(gè)數量級;使用綁定變量不僅僅是運行快,而且允許多個(gè)用戶(hù)同時(shí)使用;
上述的環(huán)境是在數據庫Oracle 8.1.7, 操作系統: Windows Server 2003,內存 1G , CPU: P4 3.4GHZ ; 電腦配置不同,執行的結果是有差異的;
2. 怎樣使用綁定變量?
下面舉例說(shuō)明:
2.1.讓Oracle自己綁定變量(也叫靜態(tài)綁定變量)
set serverout on;
set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
select count(*) into l_count from table1 where col_1=l_param1 and col_2=l_param2;
dbms_output.put_line(l_count);
end;
/
在上面的情況，Oracle會(huì )自己綁定變量，即，如果參數保存在一個(gè)數組中，select語(yǔ)句放在一個(gè)循環(huán)中，
select 語(yǔ)句只會(huì )編譯一次。
2.2 .動(dòng)態(tài)綁定變量
set serverout on;
set timing on;
declare
l_sql varchar2(2000);
l_count number;
l_param1 varchar2(100);
l_param2 varchar2(100);
begin
l_param1:='a';
l_param2:='b';
l_sql:='select count(*) into :x from table1 where col_1=:y and col_2=:z ';
Execute Immediate l_sql into l_count using l_param1,l_param2;
dbms_output.put_line(l_count);
end;
/
2.3. dbms_output的綁定變量使用
Set echo on;
Set serveroutput on;
Set timming on;
declare
cursor_id integer;
i number;
xSql Varchar2(200);
xOut varchar2(200);
l_start number default dbms_utility.get_time;
xRow integer;
Begin
cursor_id:=DBMS_Sql.open_cursor;
For i in 1..1000 Loop
DBMS_Sql.parse(cursor_id,'insert into t values(:username,:user_id,Sysdate)',DBMS_SQL.V7);
DBMS_Sql.bind_variable(cursor_id,'username','test'||to_char(i));
DBMS_Sql.bind_variable(cursor_id,'user_id',i);
xRow:=DBMS_Sql.execute(cursor_id);
--insert into t values('test'||to_char(i),i,Sysdate);
--xSql:='insert into t values(:username,:user_id,Sysdate)';
--execute immediate xSql using 'test'||to_char(i),i;
End loop;
DBMS_sql.close_cursor(cursor_id);
dbms_output.put_line(round((dbms_utility.get_time-l_start)/100,2) ||'seconds...');
--xOut:=to_char(round((dbms_utility.get_time-l_start)/100,2)) ||'seconds...';
--xOut:='seconds...';
--return xout;
end;
這里強烈推薦使用靜態(tài)綁定變量,有興趣的話(huà)可以自己比較;
3. 我怎樣知道正在使用綁定變量的方法;
下面舉例說(shuō)明;
創(chuàng )建一個(gè)Table;
Create table t (xx int);
執行下面的語(yǔ)句;
Begin
For I in 1..100 loop
Execute immediate’insert into t values(‘|| t ||’)’;
End loop;
end;
現在準備好了腳本,開(kāi)始創(chuàng )建一個(gè)字符串中刪除常數的一個(gè)函數,它采用的是SQL語(yǔ)句為:
insert into t values(‘hello’,55);
insert into t values(‘world’,56);
將其轉換為
insert into t values(‘#’,@);
所有相同的語(yǔ)句很顯然是可見(jiàn)的(使用綁定變量);上述兩個(gè)獨特的插入語(yǔ)句經(jīng)過(guò)轉換后變成同樣的語(yǔ)句; 完成的轉換函數為:
create or replace function remove_constants(p_query in varchar2)
return varchar2 as
l_query long;
l_char varchar2(1);
l_in_quates boolean default false;
begin
for i in 1 .. length(p_query) loop
l_char := substr(p_query, i, 1);
if l_char = '''' and l_in_quates then
l_in_quates := False;
elsif l_char = '''' and not l_in_quates then
l_in_quates := true;
l_query := l_query || '#';
end if;
if not l_in_quates then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate(l_query, '0123456789',
'@@@@@@@@@');
for i in 1 .. 8 loop
l_query := replace(l_query, lpad(
'@', 10 - i,
'@'),
'@');
l_query := replace(l_query, lpad('', 10 - i, ''), '');
end loop;
return upper(l_query);
end;
/
接著(zhù)我們建立一個(gè)臨時(shí)表去保存V$SQLAREA里的語(yǔ)句,所有 Sql的執行結果都寫(xiě)在這里;
建立臨時(shí)表;
create global temporary table sql_area_tmp on commit preserve rows as
select sql_text,sql_text sql_text_wo_constants from
v$sqlarea where 1=0;
保存數據到臨時(shí)表上;
insert into sql_area_tmp(sql_text) select sql_text from v$sqlarea;
對臨時(shí)表中的數據進(jìn)行更新;刪除掉常數;
Update sql_area_tmp set SQL_TEXT_WO_CONSTANTS= remove_constants(sql_text);
現在我們要找到哪個(gè)糟糕的查詢(xún)
select SQL_TEXT_WO_CONSTANTS,count(*) from sql_area_tmp
group by SQL_TEXT_WO_CONSTANTS
having count(*)>10
order by 2;
SQL_TEXT_WO_CONSTANTS count(*)
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
INSERT INTO T VALUES(@) 100
另外, 設定如下參數
Alter session set sql_trace=true;
Alter session set timed_statictics=True;
Alter session set events ‘10046 trace name context forever,level <N>’;
這里的’N’ 表示的是1,4,8,12,詳細內容請參考相關(guān)文檔
Alter session set events ‘10046 trace name context off’;
可以用 TKPROF 工具查看綁頂變量執行的結果,更詳細內容請查看相關(guān)文檔;
4. 綁定變量在應用開(kāi)發(fā)環(huán)境下的使用;
4.1 在VB.Net or ASP.NET and VB中的的使用
建議用Oracleclient DB的連接方法,OleDB不支持;下面是使用
OracleClient連接的使用例子(這個(gè)代碼執行只需要2秒不到);
Begin
Dim cn01 As New OracleConnection
Dim CMD01 As New OracleCommand
Dim Cmd As New OleDbCommand
Dim i As Integer
Try
<add key="DBCONN_SFCFA" value="User ID=sfcfa;password=SFCFA;Data Source=CIM;" />
xConnStr = System.Configuration.ConfigurationSettings.AppSettings("DBCONN_SFCFA")
'cn01.ConnectionString()
cn01.ConnectionString = xConnStr
cn01.Open()
TextBox1.Text = Now
Application.DoEvents()
xSql = "insert into t values(:username,:userid,sysdate) "
For i = 1 To 1000
CMD01 = New OracleClient.OracleCommand(xSql, cn01)
CMD01.CommandType = CommandType.Text
CMD01.Parameters.Add("username", "test" + CStr(i))
CMD01.Parameters.Add("userid", i)
CMD01.ExecuteNonQuery()
CMD01.Parameters.Clear()
Next i
TextBox2.Text = Now
Catch ex As OleDbException
MsgBox(ex.Message)
Catch ex As Exception
MsgBox(ex.HelpLink + ex.Message)
End Try
End.
OleDB(VB,ASP等)不支持綁定變量,或者我沒(méi)有找到更好的方法去實(shí)現它;它有變量的概念但不支持綁定;網(wǎng)絡(luò )上,有很多帖子說(shuō);他實(shí)現了綁定變量用VB or ASP;我按照他們的方法去試,發(fā)現他們與單純傳參數沒(méi)有什么區別,請看下面的內容;
OleDB(這個(gè)執行需要5秒;) :
Dim xConnStr, xSql As String
Dim Cn As New OleDbConnection
Dim cn01 As New OracleConnection
Dim CMD01 As New OracleCommand
Dim Cmd As New OleDbCommand
Dim i As Integer
Try
<add key="DBCONN_SFCFA" value="Provider=MSDAORA.1;User ID=sfcfa;password=SFCFA;Data Source=CIM;"/>
xConnStr = System.Configuration.ConfigurationSettings.AppSettings("DBCONN_SFCFA")
'Cn.ConnectionString()
Cn.ConnectionString = xConnStr
Cn.Open()
TextBox1.Text = Now
Application.DoEvents()
xSql = "insert into t values(?,?,sysdate) "
For i = 1 To 1000
Cmd = New OleDbCommand(xSql, Cn)
Cmd.CommandType = CommandType.Text
Cmd.Parameters.Add("username", "test" + CStr(i))
Cmd.Parameters.Add("userid", i)
Cmd.ExecuteNonQuery()
Cmd.Parameters.Clear()
Next i
TextBox2.Text = Now
Catch ex As OleDbException
MsgBox(ex.Message)
Catch ex As Exception
MsgBox(ex.HelpLink + ex.Message)
End Try
VB or ASP(耗時(shí)也是5秒左右…):
Private Sub Command1_Click()
Dim sConn As String
Dim BVCS_CN As ADODB.Connection
'Dim BVCS as ADODB.
Dim xCMD As ADODB.Command
Dim xPre As ADODB.Parameter
Dim xSql As String
Dim xSql01 As String
Dim xRS As ADODB.Recordset
On Error GoTo 1
SetDBConnection = True
Set BVCS_CN = New ADODB.Connection
'BVCS_CN.Provider = "MSDAORA"
'sConn = "DATA SOURCE=" & ServerName & ";"
sConn = "Provider=MSDAORA.1;Password=sfcfa;User ID=sfcfa;Data Source=cim;"
With BVCS_CN
.Open sConn
End With
If BVCS_CN.State = 0 Then
MsgBox "DB Connection is error"
Exit Sub
End If
Text1.Text = Now
DoEvents
Set xCMD = New ADODB.Command
Dim xTest As String
Set xPre = New ADODB.Parameter
'BVCS_CN
For i = 1 To 1000
With xCMD
.ActiveConnection = BVCS_CN
.CommandText = " Insert into TT(username,userid) values(?,?) "
.CommandType = adCmdText
.Parameters.Append .CreateParameter("username", adBSTR, adParamInput, 30, "test" + CStr(i))
.Parameters.Append .CreateParameter("userid", adInteger, adParamInput, 4, i)
.Prepared = True
.Execute
End With
xCMD.Parameters.Delete 1
xCMD.Parameters.Delete 0
Next i
Set xCMD = Nothing
Text2.Text = Now
Exit Sub
1:
Set xCMD = Nothing
MsgBox Error$
For Each objErr In BVCS_CN.Errors
MsgBox objErr.Description
Next
BVCS_CN.Errors.Clear
Exit Sub
Resume Next
End Sub
4.2 在Delphi中的使用情況;
這里特殊說(shuō)明, Borland Delphi 4.0以上的版本已經(jīng)開(kāi)始完全支持綁定變量的概念,因此,它執行數據庫的查詢(xún)效率要好于其他開(kāi)發(fā)工具;執行的結果不到2秒;
procedure TForm1.Button1Click(Sender: TObject);
Var
i :Integer;
begin
edit1.text:=DatetimeToStr(now);
For i := 1 to 1000 do
//Begin
With Query1 do
Begin
close;
Sql.clear;
Sql.add('Insert into t Values(:username,:user_id,sysdate) ');
ParamByName('username').AsString :='test' ;
ParamByName('user_id').AsInteger :=i ;
execSql;
End;
//end;
//edit2.text:=DateToStr(now);
edit2.text:=DatetimetoStr(now);
end;
4.3. 在Java中的使用綁定變量
String v_id = 'xxxxx';
String v_sql = 'select name from table_a where id = ? '; //嵌入綁定變量
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //為綁定變量賦值
stmt.executeQuery();
在Java中，結合使用setXXX 系列方法，可以為不同數據類(lèi)型的綁定變量進(jìn)行賦值，從而大大優(yōu)化了SQL 語(yǔ)句的性能。
4.4 C#同VB.NET ,這里不在贅述;
5. 綁定變量使用限制條件是什么?
為了不重復解析相同的SQL語(yǔ)句,在第一次解析之后, ORACLE將SQL語(yǔ)句存放在內存中.這塊位于系統全局區域SGA(system global area)的共享池(shared buffer pool)中的內存可以被所有的數據庫用戶(hù)共享. 因此,當你執行一個(gè)SQL語(yǔ)句(有時(shí)被稱(chēng)為一個(gè)游標)時(shí),如果它和之前的執行過(guò)的語(yǔ)句完全相同, ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的執行路徑. ORACLE的這個(gè)功能大大地提高了SQL的執行性能并節省了內存的使用.可惜的是ORACLE只對簡(jiǎn)單的表提供高速緩沖(cache buffering) ,這個(gè)功能并不適用于多表連接查詢(xún)(這句并不完全可信,有興趣的可以自己琢磨).
數據庫管理員必須在init.ora中為這個(gè)區域設置合適的參數,當這個(gè)內存區域越大,就可以保留更多的語(yǔ)句,當然被共享的可能性也就越大了.
當你向ORACLE 提交一個(gè)SQL語(yǔ)句,ORACLE會(huì )首先在這塊內存中查找相同的語(yǔ)句.這里需要注明的是,ORACLE對兩者采取的是一種嚴格匹配,要達成共享,SQL語(yǔ)句必須完全相同(包括空格,換行等).
共享的語(yǔ)句必須滿(mǎn)足三個(gè)條件:
A. 字符級的比較:
當前被執行的語(yǔ)句和共享池中的語(yǔ)句必須完全相同.
例如:
SELECT * FROM EMP;
和下列每一個(gè)都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B. 兩個(gè)語(yǔ)句所指的對象必須完全相同:
例如:
用戶(hù) 對象名 如何訪(fǎng)問(wèn)
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
考慮一下下列SQL語(yǔ)句能否在這兩個(gè)用戶(hù)之間共享.
SQL 能否共享 原因
select max(sal_cap) from sal_limit; 不能 每個(gè)用戶(hù)都有一個(gè)private synonym - sal_limit , 它們是不同的對象
select count(*0 from work_city where sdesc like 'NEW%'; 能 兩個(gè)用戶(hù)訪(fǎng)問(wèn)相同的對象public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用戶(hù)jack 通過(guò)private synonym訪(fǎng)問(wèn)plant_detail 而jill 是表的所有者,對象不同.
C. 兩個(gè)SQL語(yǔ)句中必須使用相同的名字的綁定變量(bind variables)
例如：
第一組的兩個(gè)SQL語(yǔ)句是相同的(可以共享),而第二組中的兩個(gè)語(yǔ)句是不同的(即使在運行時(shí),賦于不同的綁定變量相同的值)
a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
6. 總結
不使用綁定變量是做著(zhù)等死,使用綁定變量不一定不會(huì )死;沒(méi)有任何的良藥會(huì )包治百病,所以在如何合理有效地使用綁定變量仍就需要大家去摸索;
[全文完]..
