Oracle并行操作之并行查询实例解析

Oracle数据库的并行操作特性，其本质上就是强行榨取除数据库服务器空闲资源（主要是CPU资源），对一些高负荷大数据量数据进行分治处理。并行操作是一种非确定性的优化策略，在选择的时候需要小心对待。目前，使用并行操作特性的主要有下面几个方面：Parallel Query：并行查询，使用多个操作系统级别的Server Process来同时完成一个SQL查询；Parallel DML：并行DML操作。类似于Parallel Query。当要对大数据量表进行DML操作，如insert、update和delete的时候，可以考虑使用；Parallel DDL：并行DDL操作。如进行大容量数据表构建、索引rebuild等操作；Parallel Recovery，并行恢复。当数据库实例崩溃重新启动，或者进行存储介质恢复的时候，可以启动并行恢复技术。从而达到减少恢复时间的目的；Procedural Parallel，过程代码并行化。对我们编写的代码片段、存储过程或者函数，可以实现执行的并行化，从而加快执行效率；1、并行查询Parallel QueryOracle数据库的并行查询是比较基础的技术，也是OLAP和Oracle Data Warehouse经常使用的一种并行技术。同本系列前面一直强调的要素相同，在确定使用并行技术之前，要确定软硬件的一些先决条件：任务task必要条件。备选进行并行操作的任务task必须是一个大任务作业，比如，长时间的查询。任务时间通常可以以分钟、小时进行计数。只有这样的任务和需要，才值得让我们冒险使用并行操作方案；资源闲置条件。只有在数据库服务器资源存在闲置的时候，才可以考虑进行并行处理。如果经常性的繁忙，贸然使用并行只能加剧资源的争用。并行操作最大的风险在于并行争用引起的效率不升反降。所以，要在确定两个前提之后，再进行并行规划处理。2、环境准备首先，准备实验环境。由于笔者使用的一般家用PC虚拟机，所以并行度和存储量不能反映真实条件需求，见谅。SQL> select * from v$version where rownum<2;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionSQL> 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 2SQL> 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 tPlan 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 alteredSQL> 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_wilsonoracle 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_wilsonoracle 5778 1 0 17:43 ? 00:00:00 ora_p001_wilsonoracle 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 0Servers Available 0Servers Started 2Servers Shutdown 2Servers Highwater 2Servers Cleaned Up 0Server Sessions 6Memory Chunks Allocated 4Memory Chunks Freed 0Memory Chunks Current 4Memory Chunks HWM 4Buffers Allocated 30Buffers Freed 30Buffers Current 0Buffers HWM 815 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 tPlan 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会将一些要进行的并行操作进入等待状态，防止并行环境的恶化。当环境好转之后，等待队列中的并行语句就进入执行状态。这个特性就可以有效的防止并行滥用的出现。