Oracle优化之应用设计与实现

本篇博文将分为4个部分。如下：

一.  SQL语句管理
二.  批量接口

三.  事务设计

四.  使用存储过程降低网络流量

- - - - - - - - - - - - - - - - - - - - -  - - - - - - - - - - - - - - - - - - - - - --  - --  - - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - -

1 .  SQL语句管理

1.1  优化解析

     Oracle在共享池（share pool）中维护了一份SQL语句的缓存。如果在此共享池中发现匹配的SQL语句，就可以避免掉解析大部分的开销。在共享池中找到匹配的SQL所对应的解析被称为软解析（soft parse）。反之为硬解析（hard parse）。

     硬解析不仅耗费CPU的时间，在有大量会话想要同时缓存SQL到share  pool时还会造成争用。

a.  绑定变量与游标管理

      SQL语句在客户端被称为游标（cursor）或者上下文域（Context area）。在低级别的编程语言中（如C++）Oracle的API徐涛显式的创建游标。在更高级的编程语言中（如java或者C#）。游标表现为一个更加抽象的SQL语句对象。如在java中创建、执行并销毁了一个关联到某游标的SQL statement对象。如下（代码片段）：

         Statement   s = oracleConn.createStatement();

         s.execute(" UPDATE staff SET staff_name = 'zyj 1'  WHERE staffId = ' 2013060012'  ");

         s.close();

上面的语句执行后，如果在执行下面的片段：

         Statement   s = oracleConn.createStatement();

         s.execute(" UPDATE staff SET staff_name = 'zyj 2'  WHERE staffId = '2013060012'  ");

         s.close();

虽然只是 staff_name 赋值变化了，但在share pool中缓存的是不同的SQL语句。所以应该使用绑定变量,  如下：

       PreparedStatement stmt = oracleConn. preparestatement("  UPDATE   staff   SET    staff_name  = ?  WHERE staffId = ?  ");

         stmt.setstring(1 ," zyj 1 " );

         stmt.setint(2 , 2013060012 );

         stmt.execute();

 这样无论如何设置 staff_name  与 staffId    在share  pool 中都是同一条SQL，性能肯定会提升。

 使用绑定变量不仅可以降低单个应用的执行时间，也可以降低share pool中SQL语句上的闩锁与互斥争用出现的概率。

另，如果已有的程序使用了SQL 硬解析，这可以通过设置Oraac了的CURSOR_SHARING 参数来指示Oracle透明的将字面量替换为绑定变量，设置方法为：ALTER   SYSTEM   SET   cursor_sharing = FORCE . 还可以设置为其他值（EXACT、SIMILAR）设置后的效果如下：

  UPDATE staff SET staff_name = 'zyj1'  WHERE staffId = '2013060012'  替换为如下的SQL语句：

  UPDATE staff SET staff_name = : "SYS_B_0"  WHERE staffId = : "SYS_B_1"

1.2 避免执行不必要的SQL语句

      一句经典的SQL优化原则： 优化得最好的SQL语句是从不被执行的SQL语句。

下面用一个例子来说明。

 

   FOR   r   IN (  SELECT  *   

                                     FROM    emp   )

          LOOP

                 SELECT   depname  ----->  对于每个emp，这个SELECT都会被执行一次。

                       INTO  v_dep_name

                     FORM  department   

                   WHERE  depId  =  r. depId;

              IF v_dep_name  = 'IT'

             THEN   

                      NULL;

             END  IF;

         END LOOP;

可以通过如下优化：

SELECT  depId

     INTO   v_dep_id 

   FROM  department  

 WHERE depname = 'IT';

     

      FOR   r   IN (  SELECT  *   

                                     FROM    emp )

          LOOP        

              IF r.depId=v_dep_id ;

             THEN   

                      NULL;

             END  IF;

         END LOOP;

1.3  Oracle 11g 基于客户端的结果缓存（待研究）

2. 批量接口

    Oracle可以从DB中一次检索出一条记录，也可以一次检索出一批记录，批量检索可以减少发送到DB服务器的请求次数，也可以降低网络流量和逻辑IO的开销。

2.1  实现批量提取

       我们使用BULK   COLLECT   INTO子句来将记录提取到一个PL/SQL集合中，例如：

DECLARE

       TYPE   cust_id_type  IS TABLE  OF zyj.customers.cust_id%TYPE  INDEX   BY  BINARY_INTEGER ; ---声明集合变量

       TYPE   cust_last_name_type  IS  TABLE  OF   zyj.customers.cust_last_name%TYPE  INDEX  BY  BINARY_INTEGER; ---声明集合变量

   

       cust_id_list                    cust_id_type;

       cust_name_list              cust_last_name_type;

BEGIN

         SELECT   cust_id , cust_last_name

              BULK  COLLECT  INTO   cust_id_list  , cust_name_list 

              FROM  zyj.customers;

END;

在java中可以使用setFetchSize方法来设置批量的大小：

 PreparedStatement stmt = oracleConn. preparestatement(sql);

 stmt.setFetchSize(20);

ResultSet  r  =  stmt.executeQuery();

while(r.next()) {

       //other code

}

—— 使用批量提取在单词调用中从DB获取的记录，可以同时降低DB与网络的开销。对于批量查询来讲，批量提取大约可以带来一个数量级（10倍）的性能提升。

2.2  批量插入

这里使用FORALL 命令来通过一个操作插入一个集合的数据：

DECLARE

        TYPE   xtype   IS  TABLE  OF  mytable.x%TYPE  INDEX  BY  BINARY_INTEGER;   ---声明集合变量

        TYPE   ytype   IS  TABLE  OF  mytable.y%TYPE  INDEX BY   BINARY_INTEGER ;   ---声明集合变量

        xlist       xtype;

        ylist       ytype;

        BEGIN

              FOR  i   IN  1 .. 100

              LOOP 

                       xlist (i) := i;

                       ylist(i)  := 'This  is number ' || i;

              END LOOP;  

              FORALL   i   IN  1   ..   xlist.COUNT --如果用FOR则循环体内的媒体SQL或者DML语句会发送给SQL引擎执行。FORALL会一次性发送执行。

                      INSERT  INTO  mytable

                                                      (x,y) 

                                           VALUES (xlist(i) , ylist(i));

     

        END;

END;

在java中可以使用addBatch和executeBatch方法来通过一次操作插入多条记录。如下：

String sql = "INSERT INTO mytable (x,y) VALUES (:1 , :2)";

PreparedStatement insertStmt = oracleConn. preparestatement(sql);

int  batchSize = 10;

for (int i=1;i<1000;i++) {

   insertStmt  .setInt(1,i);

   insertStmt  .setString(2,"This is number "+i);

 insertStmt  .addBatch();

  if (i % batchSize == 0) {

       insertStmt  .executeBatch();

   }

}

  insertStmt  .executeBatch();

3  事务设计

3.1.  选择锁的策略：

  a.      两个会话同时尝试修改同一条记录的概率如何？ 如果可能性接近于0.选择乐观锁策略可能会更合适。

  b.      在交互应用中，让用户重试事务是否可以接受？ 如果不可接受，最好不要选择乐观锁策略。

  c.       在批量处理应用中，重试事务带来的开销是否比由其他绘画释放锁带来的延迟更大？ 如果是，使用悲观锁策略可能会更高效。

  d.       悲观锁倾向于持有更短时间的锁。从而降低了遭遇锁争用的可能性。

   e.     在交互应用中，悲观锁允许无限期地持有锁。交互应用中常常会出现这样的一种现象，比如提取并锁住数据后挂起，等待用户来点击OK按钮。如果用户出去开会了或者出去吃饭了.......没有意识到这条被显示的记录加了锁，则很可能会导致这条记录被锁住好几个小时。

——确定应用事务处理模型时，在乐观锁与悲观锁策略之间做一个正式的选择，并告知相关人员。

 

  3.2. 在实现锁的时候，可以考虑Oracle 10g R1后加入的一个伪列：ORA_ROWSCN 即： Oracle Syste Change  Number。这个自己没有实践，这里给出一个网站参考一下  http://oracle.chinaitlab.com/induction/776869.html   部分内容如下：

create   table  t1(i  int );
insert   into  t1  values ( 1 );
insert   into  t1  values ( 2 );
commit ;
create   table  t2 rowdependencies  as   select   *   from  t1;

4.使用存储过程来降低网络流量

- - - - - - - - -- - - - - - - - - - - - - - - - - - - - 2013-06-02 0:19。  终于写完了累啊这个周末。明天去打网球锻炼- - - - - - - - - - - - - -  - - - -