在php中使用绑定变量的方法(Oracle SQL共享的机制)

在网站应用的开发中,一个较流行的方法便是使用 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 中查出未使用绑定变量的语句并进行修改。

发表评论

电子邮件地址不会被公开。 必填项已用*标注


*