Oracle SQL共享的机制

1、Oracle SQL共享的机制-在php中使用绑定变量的方法在网站应用的研发中，一个较流行的方法便是使用php编程，php编程方法简单明了，直接在html中嵌 入php代码，对于研发基于数据库的动态应用十分方便。不过，许多研发员在用php研发基于Oracle数 据库的应用时，仍沿习研发基于Mysql的应用的方法，未使用绑定变量，使得Oracle SGA区中SQL语 句的重用性极低，浪费了内存，降低了系统性能。因而，在此，先简单介绍一下Oracle SQL共享的机制，再介绍怎么在php中使用绑定变量，从而实现 Oracle数据库中sql语句的共享。一、Oracle SQL语句共享区的机制1、SG

2、A区结构：Oracle数据库启动时，在内存中分配了一大片空间，为系统全局区(System Global Area)，其中包含Sql共 享池及数据缓存器(Data Buffer Cache)。SGA区的共享池部分主要由三个区域组成：库缓存，字典缓存， 控制结构。库缓存包括共享SQL区，私有SQL区，PL/SQL过程及包，及控制结构，如锁及库缓存 handles。用户执行过的Sql语句存放于Sql共享池中，以便能重用，提高其效率。2、SQL语句在内存中的分布：Oracle将其执行的每一条SQL语句存于共享SQL区及私有SQL区中。当Oracle发现两个用户执行 相同的SQL语句时，则为这些用户重用

3、SQL共享区。不过，每一用户必须在私有SQL区中拥有该语句的 一份独立拷贝。共享SQL区包含单一 SQL语句或相同的SQL语句的解析树及执行计划。通过为多个相 同的DML语句使用一个共享SQL区，Oracle节省了内存的使用，特别是当许多用户使用同一应用时。共 享SQL区永远驻留在共享池中。www.bitsCN.com3、SQL语句解析时进行的内存分配操作：当一个SQL语句被提交至Oracle去执行时，Oracle自动地执行以下内存分配步骤：Oracle检查共享池，看是否在共享SQL区中已存在相同的语句。若有，则该共享SQL区被用于执行该 语句的新实例的后续操作。相应地，若在共享池中无该语句，

4、则Oracle在共享池中分配一新的共享SQL 区，其尺寸决定于该语句的复杂性。若一个SQL语句需求新的共享SQL区而整个共享池已被分配完毕， 则Oracle可通过一个最近最少修改机理从共享池中释放部分项目，直至可为新语句的共享SQL区提供足 够的空间。若Oracle释放了一个共享SQL区，则和该区相关联的SQL语句在下次重执行时，须重新 解析并重新分配至另一共享SQL区。在两种情况下，用户专用SQL区和包含该语句的共享SQL区相关 联。因而，若能使语句得到共享，则其将减少内存的占用，同时，减少了 cpu的占用，加快了语句执行的速度。 即使一个光标仍处于打开状态，若其非常久未被使用了，则其共享区

5、也可能被从共享池中移出。若该光标 以后又被用于执行其语句，则Oracle重解析该语句并且在共享池中分配一新的共享SQL区。4、私有SQL区 私有SQL区包含绑定信息及运行时缓冲等数据。每一个提交一个SQL语句的会话均有一个私有SQL区。 每一提交相同SQL语句的用户有其使用单一共享SQL区的私有SQL区。许多私有SQL区能和同一共享 SQL区相关联一个私有SQL区包括一个永久区和一个运行时区：一个永久区包含在执行过程中保持的绑定信息，数据类型转换的代码（在定义的数据类型和查询列的数据类 型不一致时），及其他状态信息（比如递归或远程光标数或并行查询的状态）。永久区的尺寸决定于绑定变量 的数目及语

6、句中指定的列数。例如，若一个查询中指定了非常多列，则永久区要大一些。运行时区包含SQL语句被执行时使用的一些信息。运行时区的尺寸信赖于被执行的SQL语句的类型及其 复杂性及被该语句处理的行的尺寸。一般而言，用于INSERT, UPDATE,及DELETE的语句其运行区要 比SELECT语句所需的运行区尺寸要小。二、在php中不使用绑定变量和使用绑定变量的语法对比在php中，若不使用绑定变量，其对数据库的操作语法为：先解析已用变量值取代变量的语句，ora_parse（光标号，”包含变量的值的sql语句”）；再执行语句ora_exec（光标号）；使用绑定变量后，语法为先解析不含变量值的使用绑定变量

7、的语句，再将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（光标号）；三

8、、在 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共享区中便会存

9、在以下三条语句：select sid, serial#, machine from v$session where username=user1;select sid, serial#, machine from v$session where username=user2; bbs.bitsCN.comselect sid, serial#, machine from v$session where username=user3;由于每次执行时，语句中的var_username值不同，从而语句便相应地不同，使得其无法共享。使用绑定变量时，其语法为：先解析仅含绑定变量p_1(p: parame

10、ter，参数)，但无变量值的语句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共享区中只会存在以下一条语句：s

11、elect sid, serial#, machine from v$session where username=:p_1;而参数值user1, user2, user3则存放在执行该语句的用户会话的私有sql区此时，在系统sql共享区中，将该语句分两部分存储，一部分为前面仅含绑定变量的语句，为共享部分， 一部分为含有变量值的部分，为私有部分。由于共享部分不含值，因而，对于不同用户不同参数值的查询， 其语句为一致的，从而实现了共享，避免了不必要的解析。中国网管联盟2、示例2，在insert语句中使用绑定变量：语句：insert into test_table values(col1, col

12、2);假设执行三次，其参数值分别为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);使用绑定变量后，其语句为：首先在原放变量的地方放入绑定变量

13、，使其语句能共享，解析语句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_c

14、ursor) ； bbs.bitsCN.com内存中SQL共享区中只会存在以下一条语句：insert into test_table values(:p_col1,:p_col2);而参数值则存放在执行该语句的用户会话的私有sql区，从而由于共享部分为一致的，能在多用户中实现共 享。节约内存及cpu时间。若为通过数组进行多组值的插入，则可将ora_parse及ora_bind置于循环开始之前，因为语句在循环中 不会关闭，而且只是变量值变化，语句本身不变化，因而，只需一次解析及绑定。而将 赋值语句及ora_exec 语句置于循环中，由于减少了函数调用及网络传输的花费，更会大大提高速度。四、在其他

15、系统中使用绑定变量的方法：在PowerBuilder研发中，对于支持绑定变量的数据库系统，PowerBuilder的绑定开关缺省参数为打开， 从而其在系统中参数位置为?，实现了语句的共享。若在连接数据库时，将DBParm参数的DisableBind 设为1,则关闭绑定开关，不同参数值的同一语句无法共享。在Oracle Developer2000研发的应用中，系统也为默认使用绑定变量。不过，在二者中研发员自定义的 代码，便需研发员自己使用绑定变量，否则也会由于未使用绑定变量而影响性能。五、检查系统中sql语句共享程度及未使用绑定变量的语句的方法：bitsCN_com 在Oracle 8及以上版本

16、中，我们能通过查询视图v$sysstat获知系统中语句解析情况，从而了解绑定变 量的使用情况。select name , valuefrom v$sysstatwhere 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_CALLSfrom v$sqlareawhere限制条件；其只能查看sql语句的前1000个字节。若有超过1000字节