ORACLE学习笔记（四）——数据库优化

一、ORACLE数据库的数据操作优化（批量绑定Bulk Binding）

      绑定变量包扩下面两个部分：

      1) 输入集合（collections），使用FORALL语句，一般用来改善DML（INSERT、UPDATE和DELETE) 操作的性能；

      2) 输出集合（collections），使用BULK COLLECT子句；一般用来提高查询（SELECT）的性能。

（1）数据查询的优化（BULK COLLECT）

    Create Or Replace Procedure SELECT_BULK_TEST AS
        TYPE curtime IS TABLE OF Strainformation.Curtime%TYPE;
        TYPE position IS TABLE OF Strainformation.Position%TYPE;

      TYPE Strain IS TABLE OF Strainformation.Strain%TYPE;
      ctime curtime; -- no need to initialize
      pos position;
      str strain;
  BEGIN
      SELECT Curtime,Position,Strain BULK COLLECT INTO ctime,pos,str FROM Strainformation;
      FOR i in 1..pos.count LOOP   
          DBMS_OUTPUT.PUT_LINE(enums(i) || ' ' ||names(i));
      END LOOP;
  END;
  /

（2）删除优化

          DELETE  /*+ RULE */  from  Test_Table;

（3）插入优化输入集合（FORALL）

        顾名思义，插入数据时使用批量绑定，就是要通过存储过程，自定义数组的方式，一次绑定一个完整的集合。如：

create or replace PROCEDURE insert_test AS
  TYPE curtime ISTABLE OF Strainformation.Curtime%TYPE INDEXBY BINARY_INTEGER;
  TYPE position ISTABLE OF Strainformation.Position%TYPE INDEX BY BINARY_INTEGER;
  TYPE Strain ISTABLE OF Strainformation.Strain%TYPE INDEXBY BINARY_INTEGER;
  i Integer;
  pos Position;
  str Strain;
  ctime curtime;
  -- 该过程为通过使用批量绑定实现的数据插入，插入速度相当快
BEGIN
  --首先使用批量绑定查询，把信息表中的数据绑定到设定的数组变量中
  Select Curtime,Position,Strain bulk collect into ctime,pos,str from Strainformation;
  FORALL i IN 1..pos.count    -- use FORALL statement
      insert /*+ append */ into STRAININFO_ALERT(ctime(i),pos(i),s(i),str(i));
  commit;
  exception
      when others then
      --出现错误，提示失败
      dbms_output.put_line('数据插入失败！');
  rollback;
END insert_test;

 

 

二、ORACLE性能优化

 1、buffer_cache大小的设置及依据

      （1）buffercache大小的查询和修改

         buffercache的设置和sharedpool的设置很相似，一种情况我们单独的去设置，如buffercache设置1个g或2个g，另外可以把buffercache的设置放在SGA target里面去，oracle自动去设置buffercache大小，在实际的生产中我们还是经常的去手工设buffercache大小，下面这句是查一下当前我们sga里面的各个组件到底是多大。

         SELECT component,current_size,min_size FROM v$sga_dynamic_components;

         buffer设置大小

         alter system set db_cache_size=20M scope=memory;(也可以scope=both，设置buffercache，大小这里设的是20M)

     一般的有一个原则：DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3，dbcache设的大小一般是SGA_MAX_SIZE的二分之一到三分之二，也就是整个的SGA的一半到三分之二的空间。

      （2）查看sga状态

           SELECT component,current_size,min_size FROM v$sga_dynamic_components; 

           其中DEFAULTbuffer cache  是buffercache的大小

      （3）仔细查一下设置的值：  show parameter sga；

      （4）show parameter db_cache_size；

           如果db_cache_size的设置的值为默认值0，说明没有设置大小，用的全是由SGA自动调整

           如何改呢，如下：altersystem set db_cache_size=200M scope=both;

      （5）查看spfile文件的位置：show parameter spfile；

      （6）当前数据库设置的块的大小：show parameter db_block_size

 

 

2、CLOB性能调优

      1）Alter Table test_table_info Modify (position_clob Clob store as securefile(cache));

            //强制将所有LOB 创建为SecureFileLOB

           Alter system set db_securefile = 'FORCE';

      2）Alter Table test_table_info Add (

            position_clob clob lob(position_clob) store as securefile(cache);

            strain_clob clob lob(strain_clob) store as securefile(cache));

      3）使用DB_SECUREFILE 初始化参数，数据库管理员(DBA) 可确定SecureFiles 的使用情况，其中有效值为：

            (1)  ALWAYS：尝试将ASSM表空间上的所有LOB 创建为SecureFile LOB，但是仅可将自动段空间管理(ASSM) 表空间外的任何LOB 创建为BasicFile LOB；
            (2)  FORCE：强制将所有LOB 创建为SecureFileLOB；
            (3)  PERMITTED：允许创建SecureFiles（默认值）；
            (4)  NEVER：禁止创建SecureFiles；
            (5)  IGNORE：禁止创建SecureFiles，并忽略使用SecureFiles 选项强制创建BasicFiles 而导致的任何错误

 

 

注：内容非完全原创，是由我个人在学习的过程中整理和练习而作。