在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论坛】