PLSQL_性能优化系列03_Oracle Parallel并发

2022-10-12 14:47:13

2014-09-25 BaoXinjian

PLSQL_性能优化系列03_Oracle Parallel并发一、摘要

对于一个大的任务，一般的做法是利用一个进程，串行的执行，如果系统资源足够，可以采用parallel技术，把一个大的任务分成若干个小的任务，同时启用n个进程/线程，并行的处理这些小的任务，这些并发的进程称为并行执行服务器(parallel executeion server)，这些并发进程由一个称为并发协调进程的进程来管理。

1. 启用Parallel前的忠告：

只有在需要处理一个很大的任务，如需要几十分钟，几个小时的作业中，并且要有足够的系统资源的情况下(这些资源包括cpu，内存，io),您才应该考虑使用parallel。

否则，在一个多并发用户下，系统本身资源负担已经很大的情况下，

启用parallel，将会导致某一个会话试图占用了所有的资源，其他会话不得不去等待，从而导致系统系能反而下降的情况，

一般情况下，oltp系统不要使用parallel，oltp系统中可以考虑去使用。

Oracle的并发技术可以将一个大任务分解为多个小任务由多个进程共同完成。合理地使用并发可以充分利用系统资源，提高效率。

2. Parallel分类

并行查询：parallel query

并行DML: parallel dml pdml

并行DDL: parallel ddl pddl

3. 适用场合

3.1 适用parallel的两个条件

(1). 大的任务，如全表扫描大表这和日常生活中的经验是一样的，小任务自己完成都比派发任务省事

(2).系统有足够的资源(cpu/io)

3.2 换句话说，并发是在系统资源充足、用户少的系统上，为了充分利用系统资源以提高任务处理速度而设计的一种技术。以下是几种场景：

(1). OLTP系统 有大量用户和session，如果每个session使用并发查询将导致系统崩溃。但也有例外例如计费系统月底或下班后没有或用户很少访问，运行批处理程序，此时可使用并发提高速度

(2). 数据仓库系统 通常可使用并发查询、PDML等并发，注意有些数据仓库系统也提供给大量用户访问，这种系统有某些OLTP特性，应慎用并发

(3). 无论是OLTP还是数据仓库，维护期间使用parallel ddl和PDML对管理员来说是非常有用的

 

PLSQL_性能优化系列03_Oracle Parallel并发二、并行查询

并行查询允许将一个sql select语句划分为多个较小的查询，每个部分的查询并发地运行，然后将各个部分的结果组合起来，提供最终的结果，

多用于全表扫描，索引全扫描等，大表的扫描和连接、创建大的索引、分区索引扫描、大批量插入更新和删除

1. 启用并行查询

Setup1. 启用并行查询

SQL> ALTER TABLE T1 PARALLEL;

告知oracle，对T1启用parallel查询，但并行度要参照系统的资源负载状况来确定。

 

Setup2. 利用hints提示，启用并行，同时也可以告知明确的并行度，否则oracle自行决定启用的并行度，这些提示只对该sql语句有效。

SQL> select /*+ parallel(t1 8) */ count(*) from t1;

Step1. 查询并行度

SQL> select degree from user_tables where table_name=‘T1‘;

DEGREE =  DEFAULT

Step2. 并行度为Default，其值由下面2个参数决定

SQL> show parameter cpu

NAME                                         TYPE       VALUE
cpu_count                                    integer    2
parallel_threads_per_cpu                     integer    2

cpu_count表示cpu数

parallel_threads_per_cpu表示每个cpu允许的并行进程数

default情况下，并行数为cpu_count*parallel_threads_per_cpu

 

2. 取消并行查询

SQL> alter table t1 noparallel;
SQL> select degree from user_tables where table_name=‘T1‘;

DEGREE
1

 

3. 数据字典视图

select * from v$px_session

sid：各个并行会话的sid

qcsid：query coordinator sid,查询协调器sid

 

PLSQL_性能优化系列03_Oracle Parallel并发三、并行DML

并行DML包括insert，update，delete，merge，在pdml期间，oracle可以使用多个并行执行服务器来执行insert，update，delete，merge，多个会话同时执行，同时每个会话(并发进程)都有自己的undo段，都是独立的一个事务，这些事务要么由parallel dml协调器进程提交，要么都rollback。

 

1. 在一个有充足I/o带宽的多cpu主机中，对于大规模的dml，速度可能会有很大的提升，尤其是在大型的数据仓库环境中。

Steup1. 并行dml需要显示的启用

SQL> alter session enable parallel dml;

Setup2. Disable并行dml

SQL> alter session disable parallel dml;

 

2. 一个例子 Parallel DML

ALTER TABLE emp PARALLEL (10);
ALTER SESSION ENABLE PARALLEL DML;

INSERT INTO emp
SELECT * FROM t_emp;

COMMIT;



ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ PARALLEL(emp,12) */ INTO emp
SELECT /*+ PARALLEL(t_emp,12) */ * FROM t_emp;

COMMIT;

注意：使用parallel后，insert select * 语句自动就使用direct-load了，此时不再需要使用append hint（ /*+APPEND */）

 

3. Parallel DML的限制：

  • 不支持有trigger的表,在上面做PDML，能成功，但忽略了并发性；
  • 不支持某些约束，例如self-referential integrity。原因是PDML分为多个独立的session去修改数据，无法保证某些完整性；容易引起死锁已经其他锁问题；
  • 一个session使用了PDML，在commit/rollback之前，另一个session无法再使用PDML；
  • Advanced replication不支持（因为使用了trigger）；
  • Deferred constraints（约束的deferred模式指修改操作在提交时才去验证是否满足约束条件）不支持；
  • 分布式事务不支持；
  • Clustered tables不支持；
  • 当违反这些限制，PDML要么报错，要么忽略并行度；

 

PLSQL_性能优化系列03_Oracle Parallel并发四、并行DDL

并行DDL提供了DBA使用全部机器资源的能力，常用的Parallel命令

  • create table as select ……
  • create index
  • alter index rebuild
  • alter table move
  • alter table split

在这些sql语句后面加上parallel子句

例子1. Alter Table

SQL> alter table t1 move parallel;

Table altered

例子2. Alter Index

SQL> create index T1_IDX on T1 (OWNER, OBJECT_TYPE)
     tablespace SYSTEM
     parallel;

 

PLSQL_性能优化系列03_Oracle Parallel并发五、并发与空间浪费

Parallel DDL以及某些PDML依赖于direct path load，即绕过databuffer直接写数据文件。

例如，create table as select ，insert /*+APPEND */,

这会形成空间浪费

例如倒入1010M数据,每个extent 100m，direct path load会新分配100m 的extent来存放数据（如果有小于100m的extent，常规insert可以用这些空间）。

假设10个并发，每个并发倒入101M数据，会创建2个extent,则总共会创建20个extent,则形成990m空间浪费。

一方面浪费了空间（如果表创建之后有常规insert，则能使用这些空间），另一方面全表扫描时会搜索这些空的extent，这也降低了全表扫描的速度。

表空间的extent管理有两种方式，

unform size，则每个extent大小相同，

autoallocate是oracle根据内部机制决定extent大小，

更灵活Uniform 方式不支持extent trimming，而autoallocate在parallel ddl中用到extent trimming，减少了空间浪费。

因此在频繁使用parallel DDL操作的表空间上，要么减少uniform size每个extent的大小，要么使用autoallocate ，以减少空间浪费。

 

PLSQL_性能优化系列03_Oracle Parallel并发六、并发存储过程开发

以下是一个常见任务：扫描全表，修改数据，再写入新的表

如果一个进程处理太慢，我们通常会自己将数据划分，然后开多个进程调用。

使用11G R2 内置的并发包：DBMS_PARALLLEL_EXECUTE，大大简化了这一过程（11G R2之前，没有内置的并发程序包，需要手工按照rowid或主键划分大表，然后通过dbms_job或dbms_schedule并发调用。）

程序的目的是删除bmf中orig_bill_ref_no like ‘18%‘的记录，本来一句sql可以完成，由于数据量太大,系统回滚段不足。因此开发人员准备分多个进程运行

DECLARE
   CURSOR c1
   IS
      SELECT   orig_bill_ref_no
        FROM   bmf
       WHERE   orig_bill_ref_no LIKE ‘18%‘ AND MOD (account_no, 5) = 0;
BEGIN
   FOR r1 IN c1
   LOOP
      DELETE FROM   bmf
            WHERE   orig_bill_ref_no = r1.orig_bill_ref_no;
      COMMIT;
   END LOOP;
   COMMIT;
END;
/

这样的写法会有什么问题呢，很快就遇到snapshot too old错误了。原因是select打开bmf游标，同时修改bmf并commit数据，由于查询一致性要求，打开的游标要看到的是bmf修改之前的情况，这是从undo去读的，因此一旦时间超出undo_retention，undo信息过期，就报snapshot too old了。

 

使用Oracle并发包进行测试

Step1. 创建过程serial过程，用来被多个并发线程调用

/* Formatted on 2014/9/25 12:02:02 (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE serial (p_lo_rid IN ROWID, p_hi_rid IN ROWID)
IS
BEGIN
   DELETE FROM   bmf
         WHERE   ROWID BETWEEN p_lo_rid AND p_hi_rid
                 AND orig_bill_ref_no LIKE ‘15%‘;
END;
/

 

Step2. 按照rowid将表划分为多个chunk，供线程调用

BEGIN
   dbms_parallel_execute.create_task (‘PROCESS BIG TABLE‘);

   dbms_parallel_execute.create_chunks_by_rowid (
      task_name     => ‘PROCESS BIG TABLE‘,
      table_owner   => ‘LUW‘,
      table_name    => ‘BMF‘,
      by_row        => FALSE,                        --不按行记录数而按block数
      chunk_size    => 2000
   );
END;
/
SELECT   *
  FROM   (  SELECT   chunk_id,
                     status,
                     start_rowid,
                     end_rowid
              FROM   dba_parallel_execute_chunks
             WHERE   task_name = ‘PROCESS BIG TABLE‘
          ORDER BY   chunk_id)
 WHERE   ROWNUM <= 5
/

 

Step3. 发起并发任务，按照第2步对表的划分来分配并运行任务

BEGIN
   dbms_parallel_execute.run_task (
      task_name        => ‘PROCESS BIG TABLE‘,
      sql_stmt         => ‘begin serial( :start_id, :end_id ); end;‘,
      language_flag    => DBMS_SQL.NATIVE,
      parallel_level   => 4
   );
END;
/

Step4. 删除并发作业

BEGIN
   dbms_parallel_execute.drop_task (‘process big table‘);
END;
/

Step5 性能比较

那么使用并发和简单的delete相比，速度怎样呢

1. 使用并发：

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.07

2. 直接delete:

delete from bmf where orig_bill_ref_no like ‘15%‘;

403525 rows deleted.

Elapsed: 00:00:08.12

3. 这说明使用并发提高了速度，更别说对回滚段的空间要求也少了。

 

********************作者：鲍新建********************

PLSQL_性能优化系列03_Oracle Parallel并发

 

参考： http://blog.itpub.net/18474/viewspace-1060729/

PLSQL_性能优化系列03_Oracle Parallel并发

上一篇：MySQL 系统变量(system variables)


下一篇：Socket Error # 10013 Access denied