Oracle数据库中的并行执行技术解析

### Oracle 数据库中的并行执行技术解析 在数据库操作中，尤其是处理大量数据时，串行执行往往效率低下，耗费大量时间。本文将介绍两种实现并行执行的方法：并行流水线函数和 DIY 并行执行，帮助你提升数据库处理性能。 #### 1. 过程并行性概述 在数据库应用中，尤其是批量处理过程，通常设计为串行执行。例如以下过程： ```sql Create procedure process_data As Begin For x in ( select * from some_table ) Perform complex process on X Update some other table, or insert the record somewhere else End loop end ``` 在这种情况下，Oracle 的并行查询或并行数据操作语言（PDML）可能并无帮助，甚至可能导致数据库消耗更多资源且处理时间更长。虽然可以使用数组处理来优化 UPDATE/INSERT 操作，但这通常无法将运行时间减少 50% 以上。 当遇到需要大幅减少运行时间（如加快四到八倍）的情况时，可以考虑以下两种方法： - **并行流水线函数**：Oracle 会根据可用资源决定合适的并行度，创建并协调会话进行并行处理。 - **DIY 并行执行**：通过手动或使用 Oracle 提供的内置包（如 DBMS_PARALLEL_EXECUTE）将数据分割成多个小流进行并行处理。 #### 2. 并行流水线函数 并行流水线函数可以将原本串行的过程并行化执行。具体步骤如下： 1. **创建示例表**： ```sql EODA@ORA12CR1> create table t1 2 as 3 select object_id id, object_name text 4 from all_objects; Table created. EODA@ORA12CR1> begin 2 dbms_stats.set_table_stats 3 ( user, 'T1', numrows=>10000000,numblks=>100000 ); 4 end; 5 / PL/SQL procedure successfully completed. EODA@ORA12CR1> create table t2 2 as 3 select t1.*, 0 session_id 4 from t1 5 where 1=0; Table created. ``` 这里使用 DBMS_STATS 模拟一个大表，T2 表在 T1 表结构基础上增加了 SESSION_ID 列，用于观察并行执行情况。 2. **创建对象类型**： ```sql EODA@ORA12CR1> CREATE OR REPLACE TYPE t2_type 2 AS OBJECT ( 3 id number, 4 text varchar2(30), 5 session_id number 6 ) 7 / Type created. EODA@ORA12CR1> create or replace type t2_tab_type 2 as table of t2_type 3 / Type created. ``` 对象类型 t2_type 定义了输出的结构，t2_tab_type 是 t2_type 的表类型。 3. **创建并行流水线函数**： ```sql EODA@ORA12CR1> create or replace 2 function parallel_pipelined( l_cursor in sys_refcursor ) 3 return t2_tab_type 4 pipelined 5 parallel_enable ( partition l_cursor by any ) 6 7 is 8 l_session_id number; 9 l_rec t1%rowtype; 10 begin 11 select sid into l_session_id 12 from v$mystat 13 where rownum =1; 14 loop 15 fetch l_cursor into l_rec; 16 exit when l_cursor%notfound; 17 -- complex process here 18 pipe row(t2_type(l_rec.id,l_rec.text,l_session_id)); 19 end loop; 20 close l_cursor; 21 return; 22 end; 23 / Function created. ``` 该函数接受一个 ref 游标作为输入，返回 t2_tab_type 类型。使用 PARALLEL_ENABLE 声明为并行函数，通过 partition l_cursor by any 让 Oracle 以最佳方式分割数据。 4. **执行并行处理**： ```sql EODA@ORA12CR1> alter session enable parallel dml; Session altered. EODA@ORA12CR1> insert /*+ append */ 2 into t2(id,text,session_id) 3 select * 4 from table(parallel_pipelined 5 (CURSOR(select /*+ parallel(t1) */ * 6 from t1 ) 7 )) 8 / 17914 rows created. EODA@ORA12CR1> commit; Commit complete. ``` 通过查询新插入的数据并按 SESSION_ID 分组，可以查看使用的并行执行服务器数量和每个服务器处理的行数： ```sql EODA@ORA12CR1> select session_id, count(*) 2 from t2 3 group by session_id; ``` | SESSION_ID | COUNT(*) | | --- | --- | | 198 | 2166 | | 11 | 2569 | | 13 | 2493 | | 185 | 1865 | | 95 | 2613 | | 17 | 2377 | | 256 | 2331 | | 103 | 1500 | 并行流水线函数虽然能实现并行处理，但可能需要对原过程进行较大的重写。如果无法进行大规模重写，可以考虑 DIY 并行执行。 #### 3. DIY 并行执行 从 Oracle Database 11g Release 2 及更高版本开始，可以使用 DBMS_PARALLEL_EXECUTE 内置包实现并行执行。步骤如下： 1. **修改串行过程**： ```sql EODA@ORA12CR1> create or replace 2 procedure serial( p_lo_rid in rowid, p_hi_rid in rowid ) 3 is 4 begin 5 for x in ( select object_id id, object_name text 6 from big_table 7 where rowid between p_lo_rid 8 and p_hi_rid ) 9 loop 10 -- complex process here 11 insert into t2 (id, text, session_id ) 12 values ( x.id, x.text, sys_context( 'userenv', 'sessionid' ) ); 13 end loop; 14 end; 15 / Procedure created. ``` 主要修改是添加了 ROWID 输入和谓词，使用 SYS_CONTEXT 获取 SESSIONID 以便监控每个并行会话的工作量。 2. **分割表数据**： ```sql EODA@ORA12CR1> begin 2 dbms_p ```