首先,准备实验环境。由于笔者使用的一般家用PC虚拟机,所以并行度和存储量不能反映真实条件需求,见谅。
SQL> select * from v$version where rownum<2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SQL> select count(*) from t; COUNT(*) ---------- 1160704
选择11gR2服务器环境,数据表T总数据量超过一百万。
首先,我们观察一下不使用并行的执行情况。
//提取出使用游标信息; SQL> select sql_text, sql_id, version_count from v$sqlarea where sql_text like 'select count(*) from t%'; SQL_TEXT SQL_ID VERSION_COUNT ------------------------------ ------------- ------------- select count(*) from t 2jkn7rpsbj64t 2 SQL> select * from table(dbms_xplan.display_cursor('2jkn7rpsbj64t',format => 'advanced', cursor_child_no => 0)); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID 2jkn7rpsbj64t, child number 0 ------------------------------------- select count(*) from t Plan hash value: 2966233522 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 4464 (100)| | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T | 1160K| 4464 (1)| 00:00:54 | -------------------------------------------------------------------
该执行计划中没有使用并行特性,进行全表扫描。执行时间为54s。
3、并行查询计划
首先,我们设置相应的并行度。设置并行度有两种方式,一种是使用hint加在特定的SQL语句上。另一种是对大对象设置并行度属性。
前者的优点是带有一定的强制性和针对性。就是指定特定的SQL语句进行并行处理。这样的优点是易于控制并行度,缺点是带有很强的强制力,当数据量偏小的时候,使用并行优势不大。而且如果是显示指定并行度,又会带来移植伸缩性差的缺点。
后者通过对象的属性指定并行度。就将并行作为一种执行手段,提供给优化器进行选择。这样,CBO会根据系统中资源的情况和数据的实际,进行执行计划生成。计划中可能是并行,也可能不是并行。这样的优点是将并行与否交予优化器CBO去判断,缺点是并行的滥用风险。
此处,笔者设置自动确定并行度的方式。
SQL> alter table t parallel; Table altered SQL> select count(*) from t; COUNT(*) ---------- 1160704
当启动查询时,Oracle中的并行伺候进程池会根据系统中的负荷和实际因素,确定分配出的并行进程数量。此时,我们可以通过视图v$px_process来查看进程池中的连接信息。
SQL> select * from v$px_process; SERVER_NAME STATUS PID SPID SID SERIAL# ----------- --------- ---------- ------------------------ ---------- ---------- P000 AVAILABLE 25 5776 P001 AVAILABLE 26 5778
注意,并行伺候进程是一种特殊的Server Process,本质上是一种可共享的slave进程。专用连接模式下,一般的Server Process与Client Process是“同生共死”的关系,终身服务于一个Client Process。而伺候slave进程是通过进程池进行管理的,一旦启动初始化,就会在一定时间内驻留在系统中,等待下次并行处理到来。
此时,我们检查v$process视图,也可以找到对应的信息。
SQL> select * from v$process; PID SPID PNAME USERNAME SERIAL# PROGRAM -------- ---------- ------------------------ ----- --------------- ---------- ------------------------------- 25 5776 P000 oracle 13 oracle@oracle11g (P000) 26 5778 P001 oracle 6 oracle@oracle11g (P001) (篇幅由于原因,予以省略……) 32 rows selected
对应的OS中,也存在相应的真实进程伺候。
[oracle@oracle11g ~]$ ps -ef | grep oracle (篇幅由于原因,予以省略……) oracle 5700 1 0 17:29 ? 00:00:02 oraclewilson (LOCAL=NO) oracle 5723 1 0 17:33 ? 00:00:00 ora_smco_wilson oracle 5764 1 2 17:40 ? 00:00:05 oraclewilson (LOCAL=NO) oracle 5774 1 0 17:42 ? 00:00:00 oraclewilson (LOCAL=NO) oracle 5776 1 0 17:43 ? 00:00:00 ora_p000_wilson oracle 5778 1 0 17:43 ? 00:00:00 ora_p001_wilson oracle 5820 1 1 17:44 ? 00:00:00 ora_w000_wilson
由于此时查询已经结束,对应的并行会话信息,已经消失不可见。
SQL> select * from v$px_session; SADDR SID SERIAL# QCSID QCSERIAL# -------- ---------- ---------- ---------- ----------
但是,如果任务的时间长,是可以捕获到对应信息的。
从上面的情况看,我们执行一个并行操作时,Oracle会从伺候进程池中获取到对应的并行进程,来进行操作。当操作完成后,伺候进程还会等待一定时间,之后回收。
并行操作进程的资源消耗,通过v$px_sysstat视图查看。
SQL> col statistic for a30; SQL> select * from v$px_process_sysstat; STATISTIC VALUE ------------------------------ ---------- Servers In Use 0 Servers Available 0 Servers Started 2 Servers Shutdown 2 Servers Highwater 2 Servers Cleaned Up 0 Server Sessions 6 Memory Chunks Allocated 4 Memory Chunks Freed 0 Memory Chunks Current 4 Memory Chunks HWM 4 Buffers Allocated 30 Buffers Freed 30 Buffers Current 0 Buffers HWM 8 15 rows selected
下面,我们检查一下执行计划信息。
SQL> set pagesize 10000; SQL> select * from table(dbms_xplan.display_cursor('2jkn7rpsbj64t',format => 'advanced',cursor_child _no => 1)); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 2jkn7rpsbj64t, child number 1 ------------------------------------- select count(*) from t Plan hash value: 3126468333 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 2478 (100)| | | | | 1 | SORT AGGREGATE | | 1 | | | | | | 2 | PX COORDINATOR | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) | 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | | 5 | PX BLOCK ITERATOR | | 1160K| 2478 (1)| 00:00:30 | Q1,00 | PCWC | |* 6 | TABLE ACCESS FULL| T | 1160K| 2478 (1)| 00:00:30 | Q1,00 | PCWP | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - access(:Z>=:Z AND :Z<=:Z)
从执行计划的条件(6 - access(:Z>=:Z AND :Z<=:Z))中,我们可以看到任务分配,之后分别进行全表扫描。最后排序计算count,合并结果的过程。
4、结论
Oracle Parallel Query是经常使用到的一种并行操作技术。相对于DDL、DML等类型操作,并行查询更可以作为系统功能的一个步骤来进行。
进行并行查询最大的风险就是并行滥用和失控的出现。这也是Oracle一直致力解决的问题。在Oracle11gR2中,引入了Parallel Statement Queuing(PSQ)技术特性。通常,只要并行伺候池允许,Oracle会引入尽可能多的并行进程进行操作。PSQ技术的出现,就是从资源角度加入了并行控制。
当系统繁忙的时候,PSQ会将一些要进行的并行操作进入等待状态,防止并行环境的恶化。当环境好转之后,等待队列中的并行语句就进入执行状态。这个特性就可以有效的防止并行滥用的出现。