在php中使用绑定变量的方法(Oracle SQL共享的机制)
本文出自:http://www.computerworld.com.cn作者:赵华良(2002-01-29 20:34:43)
在网站应用的开发中,一个较流行的方法便是使用php编程,php编程方法简单明了,直接在html中嵌入php代码,对于开发基于数据库的动态应用十分方便。但是,许多开发员在用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中查出未使用绑定变量的语句并进行修改。
(http://www.fanqiang.com)进入【UNIX论坛】