在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 中查出未使用绑定变量的语句并进行修改。


摘自:http://www.fanqiang.com/a4/b4/20011110/0810001555.html

作者：赵华良