PLSQL_性能优化系列10_Oracle Array数据组优化

 2014-09-25 Created By BaoXinjian

一、摘要

---

集合是Oracle开发中经常遇到的情况，Oracle集合分为三种情况：索引表集合(index by table)、嵌套表集合(nested table)、可变集合(varry table)。

PL/SQL中没有数组的概念，他的集合数据类型和数组是相似的。

• 其中varray table的元素是有数量限制的，index_by table和nexted table是没有这个限制的。
• index-by table是稀疏的，也就是说下标可以不连续，varray table则是紧密的，他的下标没有间隔。
• index_by table不能存储在数据库中，但是nexted table和varray table可以被存储在数据库中。

集合在使用时必须先使用type进行定义方可使用

 

二、集合的定义方式

---

1. 索引表集合 index by table

TYPE test_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;

遍历方式：这种数组不需要事先指定上限，下标可以不连续，可以是0或负数。

数据库存放：不可以存放在数据库中

2. 嵌套表集合 nested table

TYPE test_type IS TABLE OF VARCHAR2(10);

遍历方式：下标从1开始，不能超过数组所有元素的总和，当下标超出允许范围时

数据库存放：可以存放在数据库中

3. 可变集合 varry table

TYPE test_type IS ARRARY(5) OF VARCHAR2(10);

遍历方式：由于类型定义时的元素个数限制，所以TYPE3的变量在使用时最大的元素个数不能超过5个。

数据库存放：可以存放在数据库中

 

三、数组的存放在数据库方式

---

案例： 创建一个存在嵌套表集合类型栏位的表

Step1. 创建记录类型Record Type

CREATE OR REPLACE TYPE bxj_record
AS
   OBJECT (bxj_record_id NUMBER,
           bxj_record_date DATE,
           bxj_record_description VARCHAR2 (10));

Step2. 创建嵌套表集合类型nested table type

CREATE OR REPLACE TYPE bxj_nested_table AS TABLE OF bxj_record;

Step3. 创建测试表，用以存放集合

CREATE TABLE bxj_test_table
(
   invoice_id       NUMBER,
   invoice_num      VARCHAR2 (10),
   invoice_date     DATE,
   invoice_nested   bxj_nested_table
)
NESTED TABLE invoice_nested STORE AS bxj_record_table

Step4. 查看所建立的Object

Step5. 插入测试资料

DECLARE
   v_record1          bxj_record := bxj_record (1, SYSDATE, '2014/09/29');
   v_record2          bxj_record := bxj_record (2, SYSDATE, '2014/09/30');
   v_invoice_id       NUMBER;
   v_invoice_num      VARCHAR2 (10);
   v_invoice_date     DATE;
   v_invoice_nested   bxj_nested_table := bxj_nested_table ();
BEGIN
   v_invoice_id := 1;
   v_invoice_num := 'INVOICE_01';
   v_invoice_date := SYSDATE;

   v_invoice_nested.EXTEND;
   v_invoice_nested (1) := v_record1;

   v_invoice_nested.EXTEND;
   v_invoice_nested (2) := v_record2;

   INSERT INTO bxj_test_table
     VALUES   (v_invoice_id,
               v_invoice_num,
               v_invoice_date,
               v_invoice_nested);
               
   COMMIT;
END;

Step6. 查看表数据

 

四、数组优化方式 （arraysize / bulk collect）

---

客户端Arraysize的设置实际上可以理解为设置数组的大小来保存sql调用的返回值。设置适当的arraysize有几个好处：

1.减少读data block的次数，也就是consistent gets的次数。

假设一个block有n行，如果每次取一行，则对该block的逻辑读次数就是n，尽管逻辑读是内存读，速度很快，但并发量很大时也会使数据库的压力很大。

如果每次取>n行，则该block只需读一次。因此合理设置arraysize能大大减轻数据库压力。

2.在PLSQL中减少plsql引擎和sql引擎的切换次数，提高效率

3.通过数组存放结果，减少客户端和数据库服务器的调用次数，减少往返时间

 

五、PLSQL Bulk Collect设置数组访问的方法

---

案例：测试Bulk Collect访问数组的限制对consistent gets和script run time的影响

测试一：不使用bulk collect,一次fetch一条记录

Step2. 运行测试脚本

DECLARE
   CURSOR c1
   IS
      SELECT   object_name
        FROM   all_objects
       WHERE   ROWNUM < 100000;

   v_id   VARCHAR2 (30);
BEGIN
   OPEN c1;

   LOOP
      FETCH c1 INTO   v_id;

      EXIT WHEN c1%NOTFOUND;

      NULL;
   END LOOP;

   CLOSE c1;
END;
/

Step3. 查看运行后consisten gets变化 = 235622 -109441 = 126811

 

测试二：设置bulk collect，一次取100条

Step1. 运行测试脚本

DECLARE
   TYPE VarcharTab
   IS
      TABLE OF VARCHAR2 (30)
         INDEX BY BINARY_INTEGER;

   CURSOR c1
   IS
      SELECT   object_name
        FROM   all_objects
       WHERE   ROWNUM < 100000;

   v_id   VarcharTab;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1 BULK COLLECT INTO   v_id LIMIT 100;

      EXIT WHEN c1%NOTFOUND;
      NULL;
   END LOOP;

   CLOSE c1;
END;
/

Step2. 查看运行后consisten gets变化 = 284623 -235622= 49001

 

测试三：设置bulk collect，一次取5000条

Step1. 运行测试脚本

DECLARE
   TYPE VarcharTab
   IS
      TABLE OF VARCHAR2 (30)
         INDEX BY BINARY_INTEGER;

   CURSOR c1
   IS
      SELECT   object_name
        FROM   all_objects
       WHERE   ROWNUM < 100000;

   v_id   VarcharTab;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1 BULK COLLECT INTO   v_id LIMIT 5000;

      EXIT WHEN c1%NOTFOUND;
      NULL;
   END LOOP;

   CLOSE c1;
END;
/

Step2. 查看运行后consisten gets变化 = 332559 -284623= 47936

 

测试结果

• 测试一：consisten gets变化 = 235622 -109441= 126811
• 测试二：consisten gets变化 = 284623 -235622= 49001
• 测试三：consisten gets变化 = 332559 -284623= 47936

consistent_gets是从回滚段中读到的前映（或叫读取一致性影象）, 看见的数据是查询开始的时间点的，所以若存在block在查询开始后发生了变化的情况，则必须产生 before image 然后读数据，这就是一致读的含义

查询就是表示 consistent gets (query mode)，因为查询要保证所获取的数据的时间点的一致性，所以叫一致读，

即使是从当前 buffer 获得的数据，也叫 consistent gets ，这仅仅表达一种模式一种期望，并不表示真实的是从 当前buffer 获得 还是从回滚段获取数据产生的 bufore image 。

 

网络上的一个测试结果

 

六、SQLPLUS设置数组访问的方法

---

Sqlplus的arraysize参数表示数据库一次返回给客户端的行数，缺省是15。

如果返回数据量大，可以调大此参数:Set arraysize nnnn， 该参数最大为5000。

 

测试一：缺省arraysize  

select * from all_objects where rownum <100000;

查看执行时间

 

测试二：SQL> set arraysize 1000  

select * from all_objects where rownum <100000;

查看执行时间

 

Thanks and Regards

参考：http://www.jb51.net/article/35424.htm