公司级做宣传图的网站,中国拟在建项目网官网,广州建网站腾虎,网站做好了该怎么做在Oracle中#xff0c;PARALLEL#xff08;并行#xff09;方式最大化调用计算机资源来成倍提高数据分析效率。
1#xff0e; 用途
强行启用并行度来执行当前SQL。这个在Oracle 9i之后的版本可以使用#xff0c;之前的版本现在没有环境进行测试。也就是说#xff0c;加…在Oracle中PARALLEL并行方式最大化调用计算机资源来成倍提高数据分析效率。
1 用途
强行启用并行度来执行当前SQL。这个在Oracle 9i之后的版本可以使用之前的版本现在没有环境进行测试。也就是说加上这个说明可以强行启用Oracle的多线程处理功能。举例的话就像电脑装了多核的CPU但大多情况下都不会完全多核同时启用2核以上的比较明显使用parallel说明就会多核同时工作来提高效率。 但本身启动这个功能也是要消耗资源与性能的。所有一般都会在返回记录数大于100万时使用效果也会比较明显。
2 语法
/*parallel(table_short_name,cash_number)*/ 这个可以加到insert、delete、update、select的后面来使用和rule的用法差不多有机会再分享rule的用法 开启parallel功能的语句是 alter session enable parallel dml; 这个语句是DML语句哦如果在程序中用用execute的方法打开。 一、 并行查询
并行查询允许将一个sql select语句划分为多个较小的查询每个部分的查询并发地运行然后将各个部分的结果组合起来提供最终的结果多用于全表扫描索引全扫描等大表的扫描和连接、创建大的索引、分区索引扫描、大批量插入更新和删除 1. 启用并行查询 SQL ALTER TABLE T1 PARALLEL; 告知oracle对T1启用parallel查询但并行度要参照系统的资源负载状况来确定。 利用hints提示启用并行同时也可以告知明确的并行度否则oracle自行决定启用的并行度这些提示只对该sql语句有效。 SQL select /* parallel(t1 8) */ count(*)from t1; SQL select degree from user_tables where table_nameT1; DEGREE -------------------- DEFAULT 并行度为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 wheretable_nameT1; DEGREE ---------------------------------------- 1 3. 数据字典视图 v$px_session sid各个并行会话的sid qcsidquery coordinator sid,查询协调器sid 二、 并行dml 并行dml包括insertupdatedeletemerge在pdml期间oracle可以使用多个并行执行服务器来执行insertupdatedeletemerge多个会话同时执行同时每个会话(并发进程)都有自己的undo段都是独立的一个事务这些事务要么由pdml协调器进程提交要么都rollback。 在一个有充足I/o带宽的多cpu主机中对于大规模的dml速度可能会有很大的提升尤其是在大型的数据仓库环境中。 并行dml需要显示的启用 SQL alter session enable parallel dml; Disable并行dml SQL alter session disable parallel dml; 三、 并行ddl 并行ddl提供了dba使用全部机器资源的能力常用的pddl有 create table as select …… create index alter index rebuild alter table move alter table split 在这些sql语句后面加上parallel子句 例 SQL alter table t1 move parallel; Table altered SQL create index T1_IDX on T1 (OWNER,OBJECT_TYPE) 2 tablespace SYSTEM 3 parallel; 4
并行的实现机制是 首先Oracle 会创建一个进程用于协调并行服务进程之间的信息传递这个协调进程将需要操作的数据集比如表的数据块分割成很多部分称为并行处理单元然后并行协调进程给每个并行进程分配一个数据单元。比如有四个并行服务进程他们就会同时处理各自分配的单元当一个并行服务进程处理完毕后协调进程就会给它们分配另外的单元如此反复直到表上的数据都处理完毕最后协调进程负责将每个小的集合合并为一个大集合作为最终的执行结果返回给用户。 并行处理的机制实际上就是把一个要扫描的数据集分成很多小数据集Oracle 会启动几个并行服务进程同时处理这些小数据集最后将这些结果汇总作为最终的处理结果返回给用户。 这种数据并行处理方式在OLAP系统中非常有用OLAP系统的表通常来说都是非常大如果系统的CPU比较多让所有的CPU共同来处理这些数据效果就会比串行执行要高的多。 然而对于OLTP系统通常来讲并行并不合适原因是OLTP系统上几乎在所有的SQL操作中数据访问路劲基本上以索引访问为主并且返回结果集非常小这样的SQL 操作的处理速度一般非常快不需要启用并行。 二 并行处理的机制 当Oracle 数据库启动的时候实例会根据初始化参数 PARALLEL_MIN_SERVERSn 的值来预先分配n个并行服务进程当一条SQL 被CBO判断为需要并行执行时发出SQL的会话进程变成并行协助进程它按照并行执行度的值来分配进程服务器进程。 首先协调进程会使用ORACLE 启动时根据参数 parallel_min_serversn的值启动相应的并行服务进程如果启动的并行服务器进程数不足以满足并行度要求的并行服务进程数则并行协调进程将额外启动并行服务进程以提供更多的并行服务进程来满足执行的需求。 然后星星协调进程将要处理的对象划分成小数据片分给并行服务进程处理并行服务进程处理完毕后将结果发送给并行协调进程然后由并行协调进程将处理结果汇总并发送给用户。 刚才讲述的是一个并行处理的基本流程。 实际上在一个并行执行的过程中还存在着并行服务进程之间的通信问题。 在一个并行服务进程需要做两件事情的时候它会再启用一个进程来配和当前的进程完成一个工作比如这样的一条SQL语句 Select * from employees order by last_name; 假设employees表中last_name 列上没有索引并且并行度为4此时并行协调进程会分配4个并行服务进程对表employees进行全表扫描操作因为需要对结果集进行排序所以并行协调进程会额外启用4个并行服务进程用于处理4个进程传送过来的数据这新启用的用户处理传递过来数据的进程称为父进程用户传出数据最初的4个并行服务进程成为子进程这样整个并行处理过程就启用了8个并行服务进程。 其中每个单独的并行服务进程的行为叫作并行的内部操作而并行服务进程之间的数据交流叫做并行的交互操作。 这也是有时我们发现并行服务进程数量是并行度的2倍就是因为启动了并行服务父进程操作的缘故。
三. 读懂一个并行处理的执行计划 CREATE TABLE emp2 AS SELECT * FROM employees;
ALTER TABLE emp2 PARALLEL 2; EXPLAIN PLAN FOR SELECT SUM(salary) FROM emp2 GROUP BY department_id;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); --------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 2782 | 3 (34) | | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 107 | 2782 | 3 (34) | Q1,01 | P-S | QC (RAND) |
| 3 | HASH GROUP BY | | 107 | 2782 | 3 (34) | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 107 | 2782 | 3 (34) | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 107 | 2782 | 3 (34) | Q1,00 | P-P | HASH |
| 6 | HASH GROUP BY | | 107 | 2782 | 3 (34) | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 107 | 2782 | 2 (0) | Q1,00 | PCWP | |
| 8 | TABLE ACCESS FULL| EMP2 | 107 | 2782 | 2 (0) | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------- The table EMP2 is scanned in parallel by one set of slaves while the aggregation for the GROUP BY is done by the second set. The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces so as to divide the scan workload between the parallel scan slaves. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two slave sets as rows flow up from the parallel scan, get repartitioned through the HASH table queue, and then read by and aggregated on the top slave set. The PX SEND QC row source represents the aggregated values being sent to the QC in random (RAND) order. The PX COORDINATOR row source represents the QC or Query Coordinator which controls and schedules the parallel plan appearing below it in the plan tree. 上面这段文字是从Oracle 联机文档上荡下来的。
http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/ex_plan.htm#PFGRF94687 通过执行计划我们来看一下它的执行步骤 1并行服务进程对EMP2表进行全表扫描。 2并行服务进程以ITERATOR迭代方式访问数据块也就是并行协调进程分给每个并行服务进程一个数据片在这个数据片上并行服务进程顺序地访问每个数据块Iterator所有的并行服务进程将扫描的数据块传给另一组并行服务进程父进程用于做Hash Group操作。 3并行服务父进程对子进程传递过来的数据做Hash Group操作。 4并行服务进程子进程将处理完的数据发送出去。 5并行服务进程父进程接收到处理过的数据。 6合并处理过的数据按照随即的顺序发给并行协调进程QCQuery Conordinator。 7并行协调进程将处理结果发给用户。 当使用了并行执行SQL的执行计划中就会多出一列in-out。 该列帮助我们理解数据流的执行方法。 它的一些值的含义如下
Parallel to SerialP-S: 表示一个并行操作发送数据给一个串行操作通常是并行incheng将数据发送给并行调度进程。
Parallel to ParallelP-P表示一个并行操作向另一个并行操作发送数据疆场是两个从属进程之间的数据交流。
Parallel Combined with parent(PCWP): 同一个从属进程执行的并行操作同时父操作也是并行的。
Parallel Combined with Child(PCWC): 同一个从属进程执行的并行操作子操作也是并行的。
Serial to ParallelS-P: 一个串行操作发送数据给并行操作如果select 部分是串行操作就会出现这个情况。
如何启用并行 可以用hint、alter session或者设置对象并行属性三种方式设置启用并行。三种方式任意一种就可以使并行生效如果多种方式同时存在的话则优先级顺序是hint - alter session - table/index degree。 1 用途 强行启用并行度来执行当前SQL。这个在Oracle 9i之后的版本可以使用之前的版本现在没有环境进行测试。也就是说加上这个说明可以强行启用Oracle的多线程处理功能。举例的话就像电脑装了多核的CPU但大多情况下都不会完全多核同时启用2核以上的比较明显使用parallel说明就会多核同时工作来提高效率。 但本身启动这个功能也是要消耗资源与性能的。所有一般都会在返回记录数大于100万时使用效果也会比较明显。 2 语法 /*parallel(table_short_name,cash_number)*/ 这个可以加到insert、delete、update、select的后面来使用和rule的用法差不多有机会再分享rule的用法 开启parallel功能的语句是 alter session enable parallel dml; 这个语句是DML语句哦如果在程序中用用execute的方法打开。 3 实例说明 用ERP中的transaction来说明下吧。这个table记录了所有的transaction而且每天数据量也算相对比较大的根据企业自身业务量而定。假设我们现在要查看对比去年一年当中每月的进、销情况所以一般都会写成 select to_char(transaction_date,yyyymm) txn_month, sum( decode( sign(transaction_quantity),1,transaction_quantity,0 ) ) in_qty, sum( decode( sign(transaction_quantity),-1,transaction_quantity,0 ) ) out_qty from mtl_material_transactions mmt where transaction_date add_months( to_date( to_char(sysdate,yyyy)||0101,yyyymmdd), -12) and transaction_date add_months( to_date( to_char(sysdate,yyyy)||1231,yyyymmdd), -12) group by to_char(transaction_date,yyyymm) 这个SQL执行起来如果transaction_date上面有加index的话效率还算过的去但如果没有加index的话估计就会半个小时内都执行不出来。这是就可以在select 后面加上parallel说明。例如 select /*parallel(mmt,10)*/ to_char(transaction_date,yyyymm) txn_month, ... 这样的话会大大提高执行效率。如果要将检索出来的结果insert到另一个表tmp_count_tab的话也可以写成 insert /*parallel(t,10)*/ into tmp_count_tab ( txn_month, in_qty, out_qty ) select /*parallel(mmt,10)*/ to_char(transaction_date,yyyymm) txn_month, ... 插入的机制和检索机制差不多所以在insert后面加parallel也会加速的。关于insert机制这里暂不说了。 Parallel后面的数字越大执行效率越高。不过貌似跟server的配置还有oracle的配置有关增大到一定值效果就不明显了。所以一般用8,10,12,16的比较常见。我试过用30发现和16的效果一样。不过数值越大占用的资源也会相对增大的。如果是在一些package、function or procedure中写的话还是不要写那么大免得占用太多资源被DBA开K。 4 Parallel也可以用于多表 多表的话就是在第一后面加入其他的就可以了。具体写法如下 /*parallel(t,10) (b,10)*/ 5 小结 关于执行效率建议还是多按照index的方法来提高效果。Oracle有自带的explan road的方法在执行之前先看下执行计划路线对写好的SQL tuned之后再执行。实在没办法了再用parallel方法。Parallel比较邪恶对开发者而言不是好东西会养成不好习惯导致很多bad SQL不会暴漏SQL Tuning的能力得不到提升。我有见过某些人create table后从不create index或primary key认为写SQL时加parallel就可以了。 Oracle Parallel Execution(并行执行) 关于Oracle 的并行执行Oracle 官方文档有详细的说明 Using Parallel Execution http://download.oracle.com/docs/cd/E11882_01/server.112/e10837/parallel.htm#VLDBG010 This chapter covers tuning in a parallel execution environment and discusses the following topics: Introduction to Parallel ExecutionHow Parallel Execution WorksTypes of ParallelismInitializing and Tuning Parameters for Parallel ExecutionTuning General Parameters for Parallel ExecutionMonitoring Parallel Execution PerformanceMiscellaneous Parallel Execution Tuning Tips 一 并行Parallel和OLAP系统 并行的实现机制是 首先Oracle 会创建一个进程用于协调并行服务进程之间的信息传递这个协调进程将需要操作的数据集比如表的数据块分割成很多部分称为并行处理单元然后并行协调进程给每个并行进程分配一个数据单元。比如有四个并行服务进程他们就会同时处理各自分配的单元当一个并行服务进程处理完毕后协调进程就会给它们分配另外的单元如此反复直到表上的数据都处理完毕最后协调进程负责将每个小的集合合并为一个大集合作为最终的执行结果返回给用户。 并行处理的机制实际上就是把一个要扫描的数据集分成很多小数据集Oracle 会启动几个并行服务进程同时处理这些小数据集最后将这些结果汇总作为最终的处理结果返回给用户。 这种数据并行处理方式在OLAP系统中非常有用OLAP系统的表通常来说都是非常大如果系统的CPU比较多让所有的CPU共同来处理这些数据效果就会比串行执行要高的多。 然而对于OLTP系统通常来讲并行并不合适原因是OLTP系统上几乎在所有的SQL操作中数据访问路劲基本上以索引访问为主并且返回结果集非常小这样的SQL 操作的处理速度一般非常快不需要启用并行。 二 并行处理的机制 当Oracle 数据库启动的时候实例会根据初始化参数 PARALLEL_MIN_SERVERSn 的值来预先分配n个并行服务进程当一条SQL 被CBO判断为需要并行执行时发出SQL的会话进程变成并行协助进程它按照并行执行度的值来分配进程服务器进程。 首先协调进程会使用ORACLE 启动时根据参数 parallel_min_serversn的值启动相应的并行服务进程如果启动的并行服务器进程数不足以满足并行度要求的并行服务进程数则并行协调进程将额外启动并行服务进程以提供更多的并行服务进程来满足执行的需求。 然后星星协调进程将要处理的对象划分成小数据片分给并行服务进程处理并行服务进程处理完毕后将结果发送给并行协调进程然后由并行协调进程将处理结果汇总并发送给用户。 刚才讲述的是一个并行处理的基本流程。 实际上在一个并行执行的过程中还存在着并行服务进程之间的通信问题。 在一个并行服务进程需要做两件事情的时候它会再启用一个进程来配和当前的进程完成一个工作比如这样的一条SQL语句 Select * from employees order by last_name; 假设employees表中last_name 列上没有索引并且并行度为4此时并行协调进程会分配4个并行服务进程对表employees进行全表扫描操作因为需要对结果集进行排序所以并行协调进程会额外启用4个并行服务进程用于处理4个进程传送过来的数据这新启用的用户处理传递过来数据的进程称为父进程用户传出数据最初的4个并行服务进程成为子进程这样整个并行处理过程就启用了8个并行服务进程。 其中每个单独的并行服务进程的行为叫作并行的内部操作而并行服务进程之间的数据交流叫做并行的交互操作。 这也是有时我们发现并行服务进程数量是并行度的2倍就是因为启动了并行服务父进程操作的缘故。 三. 读懂一个并行处理的执行计划 CREATE TABLE emp2 AS SELECT * FROM employees; ALTER TABLE emp2 PARALLEL 2; EXPLAIN PLAN FOR SELECT SUM(salary) FROM emp2 GROUP BY department_id; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU) | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 2782 | 3 (34) | | | | | 1 | PX COORDINATOR | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 107 | 2782 | 3 (34) | Q1,01 | P-S | QC (RAND) | | 3 | HASH GROUP BY | | 107 | 2782 | 3 (34) | Q1,01 | PCWP | | | 4 | PX RECEIVE | | 107 | 2782 | 3 (34) | Q1,01 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 107 | 2782 | 3 (34) | Q1,00 | P-P | HASH | | 6 | HASH GROUP BY | | 107 | 2782 | 3 (34) | Q1,00 | PCWP | | | 7 | PX BLOCK ITERATOR | | 107 | 2782 | 2 (0) | Q1,00 | PCWP | | | 8 | TABLE ACCESS FULL| EMP2 | 107 | 2782 | 2 (0) | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------- The table EMP2 is scanned in parallel by one set of slaves while the aggregation for the GROUP BY is done by the second set. The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces so as to divide the scan workload between the parallel scan slaves. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two slave sets as rows flow up from the parallel scan, get repartitioned through the HASH table queue, and then read by and aggregated on the top slave set. The PX SEND QC row source represents the aggregated values being sent to the QC in random (RAND) order. The PX COORDINATOR row source represents the QC or Query Coordinator which controls and schedules the parallel plan appearing below it in the plan tree. 上面这段文字是从Oracle 联机文档上荡下来的。 http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/ex_plan.htm#PFGRF94687 通过执行计划我们来看一下它的执行步骤 1并行服务进程对EMP2表进行全表扫描。 2并行服务进程以ITERATOR迭代方式访问数据块也就是并行协调进程分给每个并行服务进程一个数据片在这个数据片上并行服务进程顺序地访问每个数据块Iterator所有的并行服务进程将扫描的数据块传给另一组并行服务进程父进程用于做Hash Group操作。 3并行服务父进程对子进程传递过来的数据做Hash Group操作。 4并行服务进程子进程将处理完的数据发送出去。 5并行服务进程父进程接收到处理过的数据。 6合并处理过的数据按照随即的顺序发给并行协调进程QCQuery Conordinator。 7并行协调进程将处理结果发给用户。 当使用了并行执行SQL的执行计划中就会多出一列in-out。 该列帮助我们理解数据流的执行方法。 它的一些值的含义如下 Parallel to SerialP-S: 表示一个并行操作发送数据给一个串行操作通常是并行incheng将数据发送给并行调度进程。 Parallel to ParallelP-P表示一个并行操作向另一个并行操作发送数据疆场是两个从属进程之间的数据交流。 Parallel Combined with parent(PCWP): 同一个从属进程执行的并行操作同时父操作也是并行的。 Parallel Combined with Child(PCWC): 同一个从属进程执行的并行操作子操作也是并行的。 Serial to ParallelS-P: 一个串行操作发送数据给并行操作如果select 部分是串行操作就会出现这个情况。 四并行执行等待事件 在做并行执行方面的性能优化的时候可能会遇到如下等待时间 PX Deq Credit: send blkd 这是一个有并行环境的数据库中从statspack 或者AWR中经常可以看到的等待事件。 在Oracle 9i 里面 这个等待时间被列入空闲等待。 关于等待时间参考 Oracle 常见的33个等待事件 http://blog.csdn.net/tianlesoftware/archive/2010/08/12/5807800.aspx 一般来说空闲等待可以忽略它但是实际上空闲等待也是需要关注的因为一个空闲的等待它反映的是另外的资源已经超负荷运行了。 基于这个原因在Oracle 10g里已经把PX Deq Credit: send blkd等待时间不在视为空闲等待而是列入了Others 等待事件范围。 PX Deq Credit: send blkd 等待事件的意思是 当并行服务进程向并行协调进程QC也可能是上一层的并行服务进程发送消息时同一时间只有一个并行服务进程可以向上层进程发送消息这时候如果有其他的并行服务进程也要发送消息就只能等待了。 知道获得一个发送消息的信用信息Credit这时候会触发这个等待事件这个等待事件的超时时间为2秒钟。 如果我们启动了太多的并行进程实际上系统资源CPU或者QC 无法即时处理并行服务发送的数据那么等待将不可避免。 对于这种情况我们就需要降低并行处理的并行度。 当出现PX Deq Creditsend blkd等待的时间很长时我们可以通过平均等待时间来判断等待事件是不是下层的并行服务进程空闲造成的。该等待事件的超时时间是2秒如果平均等待时间也差不多是2秒就说明是下层的并行进程“无事所做”处于空闲状态。 如果和2秒的差距很大就说明不是下层并行服务超时导致的空闲等待而是并行服务之间的竞争导致的因为这个平均等待事件非常短说明并行服务进程在很短时间的等待之后就可以获取资源来处理数据。 所以对于非下层的并行进程造成的等待解决的方法就是降低每个并行执行的并行度比如对象表索引上预设的并行度或者查询Hint 指定的并行度。 五 并行执行的使用范围 Oracle的并行技术在下面的场景中可以使用 1 Parallel Query并行查询 2 Parallel DDL并行DDL操作如建表建索引等 3 Parallel DML并行DML操作如insertupdatedelete等 5.1 并行查询 并行查询可以在查询语句子查询语句中使用但是不可以使用在一个远程引用的对象上如DBLINK。 一个查询能够并行执行需要满足一下条件 1 SQL语句中有Hint提示比如Parallel 或者 Parallel_index. 2 SQL语句中引用的对象被设置了并行属性。 3 多表关联中至少有一个表执行全表扫描Full table scan或者跨分区的Index range SCAN。 如 select /*parallel(t 4) * from t; 5.2 并行DDL 操作 5.2.1 表操作的并行执行 以下表操作可以使用并行执行 CREATE TABLE … AS SELECT ALTER TABLE … move partition Alter table … split partition Alter table … coalesce partition DDL操作我们可以通过trace 文件来查看它的执行过程。 示例 查看当前的trace 文件 /* Formatted on 2010/8/31 23:33:00 (QP5 v5.115.810.9015) */ SELECT u_dump.VALUE || / || db_name.VALUE || _ora_ || v$process.spid || NVL2 (v$process.traceid, _ || v$process.traceid, NULL) || .trc Trace File FROM v$parameter u_dump CROSS JOIN v$parameter db_name CROSS JOIN v$process JOIN v$session ON v$process.addr v$session.paddr WHERE u_dump.name user_dump_dest AND db_name.name db_name AND v$session.audsid SYS_CONTEXT (userenv, sessionid); Trace File ------------------------------------------------------------------------------ d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_5836.trc d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trc SQL alter session set events 10046 trace name context forever,level 12; 会话已更改。 SQL create table 怀宁 parallel 4 as select * from dba_objects; 表已创建。 SQL alter session set events 10046 trace name context off ; 会话已更改。 这里用到了ORACLE的event 时间。 10046事件是用来跟踪SQL语句的。开启事件后相关的信息会写道trace 文件中这也是之前我们查看trace 文件名的原因。 关于event事件参考我的blog Oracle 跟踪事件 set event http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspx 有了trace文件 我们可以用tkprof 工具来查看trace 文件的内容。 关于tkprof 工具介绍参考blog 使用 Tkprof 分析 ORACLE 跟踪文件 http://blog.csdn.net/tianlesoftware/archive/2010/05/29/5632003.aspx 进入trace 目录用tkprof命令生成txt 文件然后查看txt 文件。 d:/app/Administrator/diag/rdbms/orcl/orcl/tracetkprof orcl_ora_3048.trc 安庆.txt sysno TKPROF: Release 11.2.0.1.0 - Development on 星期二 8月 31 23:45:25 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. d:/app/Administrator/diag/rdbms/orcl/orcl/trace 5.2.2 创建索引的并行执行 创建索引时使用并行方式在系统资源充足的时候会使性能得到很大的提高特别是在OLAP系统上对一些很大的表创建索引时更是如此。 以下的创建和更改索引的操作都可以使用并行 Create index Alter index … rebuild Alter index … rebuild partition Alter index … split partition 一个简单的语法create index t_ind on t(id) parallel 4; 监控这个过程和5.2.1 中表一样需要通过10046事件。 这里就不多说了。 有关减少创建时间方法参考blog 如何加快建 index 索引 的时间 http://blog.csdn.net/tianlesoftware/archive/2010/07/11/5664019.aspx 总结 使用并行方式不论是创建表修改表创建索引重建索引他们的机制都是一样的那就是Oracle 给每个并行服务进程分配一块空间每个进程在自己的空间里处理数据最后将处理完毕的数据汇总完成SQL的操作。 5.3 并行DML 操作 Oracle 可以对DML操作使用并行执行但是有很多限制。 如果我们要让DML 操作使用并行执行必须显示地在会话里执行如下命令 SQL alter session enable parallel dml; 会话已更改。 只有执行了这个操作Oracle 才会对之后符合并行条件的DML操作并行执行如果没有这个设定即使SQL中指定了并行执行Oracle也会忽略它。 5.3.1 deleteupdate和merge 操作 Oracle 对Delete,update,merge的操作限制在只有操作的对象是分区表示Oracle 才会启动并行操作。原因在于对于分区表Oracle 会对每个分区启用一个并行服务进程同时进行数据处理这对于非分区表来说是没有意义的。 5.3.2 Insert 的并行操作 实际上只有对于insert into … select … 这样的SQL语句启用并行才有意义。 对于insert into .. values… 并行没有意义因为这条语句本身就是一个单条记录的操作。 Insert 并行常用的语法是 Insert /*parallel(t 2) */ into t select /*parallel(t1 2) */ * from t1; 这条SQL 语句中可以让两个操作insert 和select 分别使用并行这两个并行是相互独立互补干涉的也可以单独使用其中的一个并行。 六 并行执行的设定 6.1 并行相关的初始话参数 6.1.1 parallel_min_serversn 在初始化参数中设置了这个值Oracle 在启动的时候就会预先启动N个并行服务进程当SQL执行并行操作时并行协调进程首先根据并行度的值在当前已经启动的并行服务中条用n个并行服务进程当并行度大于n时Oracle将启动额外的并行服务进程以满足并行度要求的并行服务进程数量。 6.1.2 parallel_max_serversn 如果并行度的值大于parallel_min_servers或者当前可用的并行服务进程不能满足SQL的并行执行要求Oracle将额外创建新的并行服务进程当前实例总共启动的并行服务进程不能超过这个参数的设定值。 6.1.3 parallel_adaptive_multi_usertrue|false Oracle 10g R2下并行执行默认是启用的。 这个参数的默认值为true它让Oracle根据SQL执行时系统的负载情况动态地调整SQL的并行度以取得最好的SQL 执行性能。 6.1.4 parallel_min_percent 这个参数指定并行执行时申请并行服务进程的最小值它是一个百分比比如我们设定这个值为50. 当一个SQL需要申请20个并行进程时如果当前并行服务进程不足按照这个参数的要求这个SQL比如申请到20*50%10个并行服务进程如果不能够申请到这个数量的并行服务SQL 将报出一个ORA-12827的错误。 当这个值设为Null时表示所有的SQL在做并行执行时至少要获得两个并行服务进程。 6.2 并行度的设定 并行度可以通过以下三种方式来设定 1使用Hint 指定并行度。 2使用alter session force parallel 设定并行度。 3使用SQL中引用的表或者索引上设定的并行度原则上Oracle 使用这些对象中并行度最高的那个值作为当前执行的并行度。 示例 SQLSelect /*parallel(t 4) */ count(*) from t; SQLAlter table t parallel 4; SQLAlter session force parallel query parallel 4; Oracle 默认并行度计算方式 1Oracle 根据CPU的个数RAC实例的个数以及参数parallel_threads_per_cpu的值计算出一个并行度。 2对于并行访问分区操作取需要访问的分区数为并行度。 并行度的优先级别从高到低 Hint-alter session force parallel-表索引上的设定- 系统参数 实际上并行只有才系统资源比较充足的情况下才会取得很好的性能如果系统负担很重不恰当的设置并行反而会使性能大幅下降。 七 直接加载 在执行数据插入或者数据加载的时候可以通过append hint的方式进行数据的直接加载。 在insert 的SQL中使用APPEND如 Insert /*append */ into t select * from t1; 还可以在SQL*LOADER里面使用直接加载 Sqlldr useriduser/pwd controlload.ctl directtrue Oracle 执行直接加载时数据直接追加到数据段的最后不需要花费时间在段中需找空间数据不经过data buffer直接写到数据文件中效率要比传统的加载方式高。 示例 SQL create table t as select * from user_tables; 表已创建。 SQL select segment_name,extent_id,bytes from user_extents where segment_nameT; SEGMENT_NA EXTENT_ID BYTES ---------- ---------- ---------- T 0 65536 T 1 65536 T 2 65536 T 3 65536 T 4 65536 这里我们创建了一张表分配了5个extents。 SQL delete from t; 已删除979行。 SQL select segment_name,extent_id,bytes from user_extents where segment_nameT; SEGMENT_NA EXTENT_ID BYTES ---------- ---------- ---------- T 0 65536 T 1 65536 T 2 65536 T 3 65536 T 4 65536 这里删除了表里的数据但是查询依然占据5个extents。因为delete不会收缩表空间不能降低高水位。 SQL insert into t select * from user_tables; 已创建980行。 SQL commit; 提交完成。 SQL select segment_name,extent_id,bytes from user_extents where segment_nameT; SEGMENT_NA EXTENT_ID BYTES ---------- ---------- ---------- T 0 65536 T 1 65536 T 2 65536 T 3 65536 T 4 65536 用传统方式插入数据被分配到已有的空闲空间里。 SQL delete from t; 已删除980行。 SQL commit; 提交完成。 SQL select segment_name,extent_id,bytes from user_extents where segment_nameT; SEGMENT_NA EXTENT_ID BYTES ---------- ---------- ---------- T 0 65536 T 1 65536 T 2 65536 T 3 65536 T 4 65536 删除数据用append直接插入看一下。 SQL insert /*append */ into t select * from user_tables; 已创建980行。 SQL commit; 提交完成。 SQL select segment_name,extent_id,bytes from user_extents where segment_nameT; SEGMENT_NA EXTENT_ID BYTES ---------- ---------- ---------- T 0 65536 T 1 65536 T 2 65536 T 3 65536 T 4 65536 T 5 65536 T 6 65536 T 7 65536 T 8 65536 T 9 65536 已选择10行。 从结果可以看出直接加载方式时虽然表中有很多空的数据块Oracle 仍然会额外的分配4个extent用于直接加载数据。 直接加载的数据放在表的高水位High water Markhwm以上当直接加载完成后Oracle 将表的高水位线移到新加入的数据之后这样新的数据就可以被用户使用了。 Oracle 高水位(HWM) http://blog.csdn.net/tianlesoftware/archive/2009/10/22/4707900.aspx 7.1 直接加载和REDO 直接加载在logging模式下与传统加载方式产生的redo 日志差别不大因为当一个表有logging属性时即使使用直接加载所有改变的数据依然要产生redo实际上是所有修改的数据块全部记录redo以便于以后的恢复这时候直接加载并没有太大的优势。 直接加载最常见的是和nologging一起使用这时候可以有效地减少redo 的生成量。 注意的是在这种情况下直接加载的数据块是不产生redo的只有一些其他改变的数据产生一些redo比如表空间分配需要修改字典表或者修改段头数据块这些修改会产生少量的redo。 实际上对于nologging 方式的直接加载undo 的数据量也产生的很少因为直接加载的数据并不会在回滚段中记录这些记录位于高水位之上在事务提交之前对于其他用户来说是不可见的所以不需要产生undo事务提交时Oracle 将表的高水位线移到新的数据之后如果事务回滚只需要保持高水位线不动即可就好像什么都没有发生一样。 注意由于在nologging模式下redo 不记录数据修改的信息所以直接加载完后需要立即进行相关的备份操作因为这些数据没有记录在归档日志中一旦数据损坏只能用备份来恢复而不能使用归档恢复。 Logging模式下示例 SQL set autot trace stat; SQL insert /*append */ into t select * from user_tables; 已创建980行。 统计信息 ---------------------------------------------------------- 132 recursive calls 87 db block gets 8967 consistent gets 0 physical reads 286572 redo size 911 bytes sent via SQL*Net to client 1017 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processed SQL rollback; 回退已完成。 SQL insert into t select * from user_tables; 已创建980行。 统计信息 ---------------------------------------------------------- 0 recursive calls 144 db block gets 9027 consistent gets 0 physical reads 267448 redo size 927 bytes sent via SQL*Net to client 1004 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processed Nologging模式下示例 SQL alter table t nologging; 表已更改。 SQL insert into t select * from user_tables; 已创建980行。 统计信息 ---------------------------------------------------------- 239 recursive calls 132 db block gets 9061 consistent gets 0 physical reads 262896 redo size 927 bytes sent via SQL*Net to client 1004 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 980 rows processed SQL rollback; 回退已完成。 SQL insert /*append */ into t select * from user_tables; 已创建980行。 统计信息 ---------------------------------------------------------- 8 recursive calls 40 db block gets 8938 consistent gets 0 physical reads 340 redo size -- redo 减少很多 911 bytes sent via SQL*Net to client 1017 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processed 这部分内容也可参考Blog Oracle DML NOLOGGING http://blog.csdn.net/tianlesoftware/archive/2010/07/11/5701596.aspx 7.2 直接加载和索引 如果直接加载的表上有索引Oracle不会像加载数据的方式那样来处理索引的数据但是它同样需要维护一个索引这个成本很高同时会生成很多的redo。 所以当使用直接加载时通常是针对一些数据量非常大的表。如果这些表存在索引将会带来很大的性能影响这时可以考虑先将索引disable或者drop掉等加载数据后之后在重新建立索引。 nologging示例 SQL insert /*append */ into t select * from user_tables; 已创建980行。 统计信息 ---------------------------------------------------------- 0 recursive calls 40 db block gets 8936 consistent gets 0 physical reads 384 redo size 911 bytes sent via SQL*Net to client 1017 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processed SQL rollback; 回退已完成。 SQL create index t_ind on t(table_name); 索引已创建。 SQL insert /*append */ into t select * from user_tables; 已创建980行。 统计信息 ---------------------------------------------------------- 40 recursive calls 170 db block gets 8955 consistent gets 4 physical reads 149424 redo size 911 bytes sent via SQL*Net to client 1017 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 980 rows processed SQL rollback; 回退已完成。 SQL insert into t select * from user_tables; 已创建980行。 统计信息 ---------------------------------------------------------- 8 recursive calls 828 db block gets 9037 consistent gets 0 physical reads 382832 redo size 927 bytes sent via SQL*Net to client 1005 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 980 rows processed SQL rollback; 回退已完成。 7.3 直接加载和并行 直接加载可以和并行执行一同使用这样可以并行地向表中插入数据。 如 SQLalter session enable parallel dml; -- 这里必须显示的申明 SQLinsert /*append parallel(t,2) */ into t select * from t1; SQLinsert /*append */ into t select * from t1; 注在对insert 使用并行时Oracle自动使用直接加载的方式进行数据加载所以在这种情况下append是可以省略的。 当使用并行加载时Oracle 会按照并行度启动相应数量的并行服务进程像串行执行的直接加载的方式一样每个并行服务进程都单独分配额外的空间用于加载数据实际上Oracle 为每个并行服务进程分配了一个临时段每个并行服务进程将数据首先加载到各自的临时段上当所有的并行进程执行完毕后将各自的数据块合并到一起放到高水位之后如果事务提交则将高水位移到新加载的数据之后。 7.4 直接加载和SQL*LOADER 在SQL*LOADER中也可以使用直接加载它比传统方式效率更高因为它绕开了SQL的解析和数据缓冲区直接将数据加载到数据文件这对OLAP或者数据仓库系统非常有用。 指定加载 Sqlldr useriduser/pwd controlcontrol.ctl directtrue 指定并行和加载 Sqlldr useriduser/pwd controlcontrol.ctl directtrue paralleltrue SQL*LOADER直接加载对索引的影响 1索引为非约束性直接加载可以在加载完毕后维护索引的完整性。 2索引为约束性索引比如主键直接加载仍然会将数据加载入库但是会将索引置为unusable. 如果使用SQL*LOADER的并行直接加载选项并且表上有索引将导致加载失败这是我们可以在sqlloader中指定skip_index_maintenancetrue, 来允许加载完成但是索引状态会变成unusable需要手工rebuild. 关于SQL*LOADER的更多内容参考blog Oracle SQL Loader http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4674063.aspx zhaizi:ttp://blog.csdn.net/tianlesoftware/article/details/5854583 Oracle并行FAQ 关于Oracle并行的一些简单小结。 Ø 什么是并行 并行是Oracle为了提高大数据量的运算效率而提供多进程协作技术它可以让多个CPU同时处理一个计算任务充分使用系统资源提高计算效率。 Ø 什么操作支持并行 大部分的DML(insert/update/delete/merge)、DDL、Query都支持并行操作。 Ø 什么情况下需要启用并行 并不是所有的SQL都应该使用并行。要使用并行需满足以下两个条件否则结果可能适得其反 1 机器有充分的空闲资源(CPU、内存等) 2 参与运算的数据量大。在当前系统初定于参与运算数据量大于10GB或者SQL运行时间超过30分钟可考虑使用并行。 Ø 如何启用并行 可以用hint、alter session或者设置对象并行属性三种方式设置启用并行。三种方式任意一种就可以使并行生效如果多种方式同时存在的话则优先级顺序是hint - alter session - table/index degree。 hint alter session table/index degree Query select /* parallel(a,8) */count(1) from table_name a; alter session force query parallel 8; select count(1) from table_name a; alter table table_name parallel 8; select count(1) from table_name a; DML 默认情况下parallel dml是禁用的需要先用alter session启用 alter session enable parallel dml; update /* parallel(a,8) */ table_name a set col11; alter session force parallel dml parallel 8; update table_name a set col11; alter session enable parallel dml; alter table table_name parallel 8; update table_name a set col11; 以上三步缺一不可。 DDL 无 alter session force parallel ddl parallel 8; create table table_name as select * from …… create table table_name parallel 8 as select * from …… create index index_name …… parallel 8; 注意 1) 上述的alter session enable只是表示让当前会话支持并行最终并行需要通过hint或者table/index degree来实现而alter session force表示强制并行无需hint等配合使用。 2) 建议在hint或者alter session中控制并行不要通过修改表或者索引的属性(degree)来控制。在查询频繁的情况下把表或者索引的并行度改大可能会导致严重的性能问题。 Ø 对于insert ……select ……如何设置并行 insert……select….包含两个部分query和dml可以为这两个部分分别设置并行度。因为insert操作是dml因此还需要通过alter session方式把pdml启用如 alter session enable parallel dml; insert /* append parallel(a,4) */ into table_a a select /* parallel(b,8) */ * from table_b b where ……; Ø 在存储过程/包中如何启用并行 参考上文所述。唯一的区别是如果需要执行alter session则需要用动态语句执行如 execute immediate ‘alter session enable parallel dml’; Ø 并行度该设置多大 一般来说并行度越大SQL的执行效率越高但是不建议设置超过CPU核数的并行度。在当前的RAC中考虑到同时会有多个任务在跑为了不影响其他任务并行度需要严格控制在32个以下一般的建议值是8和16。 另外建议并行度设置为2的n次方如2/4/8/16/32等。 Ø 既有DML又有query的SQL如何设置并行度如insert …..select….. 1) 如果写入量大则在insert上加并行会明显提升性能否则在insert上加并行基本没有什么意义。如 insert into t(game_name,num) select game_name,count(1) cnt from popt_total_login_all_his group by game_name; 这种SQL主要瓶颈在查询上写入量很少因此只需要在查询部分设置并行即可。 2) 如果查询量大则在查询上加并行会明显提升性能 3) 如果写入和查询量都大则在两个部分都要加并行不要让其中一方成为瓶颈。 总结起来就是瓶颈在部分就在这部分上加并行如果都有瓶颈则都加并行。 对于pdml建议 1) 由于并行dml有诸多限制和弊端因此在写入量不大的情况下尽量不启用并行dml。 2) insert和query的并行度不一定要一致可根据实际调整一般设置query并行度大于等于insert并行度。并行度最好设置为2的n次方。 3) 并行度不要设置超过CPU的个数 Ø 如何查询并行是否起作用 1) 在SQL执行的时候在PL/SQL DEV看是否有多个活动会话执行一个SQL 2) SQL执行完了以后在同一个会话查询v$pq_sesstat SQL select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL ------------------------------ ---------- ------------- Queries Parallelized 1 1 DML Parallelized 0 0 DDL Parallelized 0 0 上面的结果中如果last_query有非0的值表示并行起作用了。 其他方法先不告诉你了…… Ø 如何控制并行度 可以用hint、alter session以及table/index degree指定并行度详细请参考上文【如何启用并行】部分。 Ø 如何跨实例并行 在当前的RAC环境为了减少cache fusion提高效率默认情况把SQL并行限制在同一个节点执行。如果有超超大的SQL需要多个节点同时并行则可以用如下语句来控制并行可跨越的实例 alter session set parallel_instance_groupdw; --可跨越4个节点 alter session set parallel_instance_groupdw1; --限制在节点1执行 alter session set parallel_instance_groupdw12; --限制在节点1和2执行 alter session set parallel_instance_groupdw124; --限制在节点1/2/4三个节点执行 以此类推。 原则上不允许跨实例并行如果必须跨实例则使用前需征得DBA同意。 Ø 为什么我的SQL达不到我设定的并行度 并行度受以下(不限于)条件限制 1) 系统的session、process参数的设定(一般不会超过) 2) parallel_max_serversx限制。这个限定目前是256意味着一个节点最多启动256个并行从属进程。在多用户多SQL同时执行的情况下很容易达到这个上限。 3) 对于个人用户当前最多只允许4个或者10个连接同时访问数据库。这意味着个人用户下并行度不能超过4或者10。(systemuser等程序账号不在此限制范围之内) Ø 并行有什么弊端 1 并行dml会浪费空间并行度越高浪费越厉害 2 被并行dml影响的表需要提交或者回滚后才能被后续的SQL使用否则会报错这可能会影响事务的一致性。 3 并行容易触发异常或者bug降低系统和程序的稳定性 一、Parallel 在Oracle中PARALLEL并行方式最大化调用计算机资源来成倍提高数据分析效率。 1 用途 强行启用并行度来执行当前SQL。这个在Oracle 9i之后的版本可以使用之前的版本现在没有环境进行测试。也就是说加上这个说明可以强行启用Oracle的多线程处理功能。举例的话就像电脑装了多核的CPU但大多情况下都不会完全多核同时启用2核以上的比较明显使用parallel说明就会多核同时工作来提高效率。 但本身启动这个功能也是要消耗资源与性能的。所有一般都会在返回记录数大于100万时使用效果也会比较明显。 2 语法 /*parallel(table_short_name,cash_number)*/ 这个可以加到insert、delete、update、select的后面来使用和rule的用法差不多有机会再分享rule的用法 开启parallel功能的语句是 alter session enable parallel dml; 这个语句是DML语句哦如果在程序中用用execute的方法打开。 一、 并行查询 并行查询允许将一个sql select语句划分为多个较小的查询每个部分的查询并发地运行然后将各个部分的结果组合起来提供最终的结果多用于全表扫描索引全扫描等大表的扫描和连接、创建大的索引、分区索引扫描、大批量插入更新和删除 1. 启用并行查询 SQL ALTER TABLE T1 PARALLEL; 告知oracle对T1启用parallel查询但并行度要参照系统的资源负载状况来确定。 利用hints提示启用并行同时也可以告知明确的并行度否则oracle自行决定启用的并行度这些提示只对该sql语句有效。 SQL select /* parallel(t1 8) */ count(*)from t1; SQL select degree from user_tables where table_nameT1; DEGREE -------------------- DEFAULT 并行度为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 wheretable_nameT1; DEGREE ---------------------------------------- 1 3. 数据字典视图 v$px_session sid各个并行会话的sid qcsidquery coordinator sid,查询协调器sid 二、 并行dml 并行dml包括insertupdatedeletemerge在pdml期间oracle可以使用多个并行执行服务器来执行insertupdatedeletemerge多个会话同时执行同时每个会话(并发进程)都有自己的undo段都是独立的一个事务这些事务要么由pdml协调器进程提交要么都rollback。 在一个有充足I/o带宽的多cpu主机中对于大规模的dml速度可能会有很大的提升尤其是在大型的数据仓库环境中。 并行dml需要显示的启用 SQL alter session enable parallel dml; Disable并行dml SQL alter session disable parallel dml; 三、 并行ddl 并行ddl提供了dba使用全部机器资源的能力常用的pddl有 create table as select …… create index alter index rebuild alter table move alter table split 在这些sql语句后面加上parallel子句 例 SQL alter table t1 move parallel; Table altered SQL create index T1_IDX on T1 (OWNER,OBJECT_TYPE) 2 tablespace SYSTEM 3 parallel; 4 1 用途 强行启用并行度来执行当前SQL。这个在Oracle 9i之后的版本可以使用之前的版本现在没有环境进行测试。也就是说加上这个说明可以强行启用Oracle的多线程处理功能。举例的话就像电脑装了多核的CPU但大多情况下都不会完全多核同时启用2核以上的比较明显使用parallel说明就会多核同时工作来提高效率。 但本身启动这个功能也是要消耗资源与性能的。所有一般都会在返回记录数大于100万时使用效果也会比较明显。 2 语法 /*parallel(table_short_name,cash_number)*/ 这个可以加到insert、delete、update、select的后面来使用和rule的用法差不多有机会再分享rule的用法 开启parallel功能的语句是 alter session enable parallel dml; 这个语句是DML语句哦如果在程序中用用execute的方法打开。 4 Parallel也可以用于多表 多表的话就是在第一后面加入其他的就可以了。具体写法如下 /*parallel(t,10) (b,10)*/ 5 小结 关于执行效率建议还是多按照index的方法来提高效果。Oracle有自带的explan road的方法在执行之前先看下执行计划路线对写好的SQL tuned之后再执行。实在没办法了再用parallel方法。Parallel比较邪恶对开发者而言不是好东西会养成不好习惯导致很多bad SQL不会暴漏SQL Tuning的能力得不到提升。我有见过某些人create table后从不create index或primary key认为写SQL时加parallel就可以了。 注意
1) 上述的alter session enable只是表示让当前会话支持并行最终并行需要通过hint或者table/index degree来实现而alter session force表示强制并行无需hint等配合使用。
2) 建议在hint或者alter session中控制并行不要通过修改表或者索引的属性(degree)来控制。在查询频繁的情况下把表或者索引的并行度改大可能会导致严重的性能问题。
Ø 对于insert ……select ……如何设置并行
insert……select….包含两个部分query和dml可以为这两个部分分别设置并行度。因为insert操作是dml因此还需要通过alter session方式把pdml启用如 alter session enable parallel dml; insert /* append parallel(a,4) */ into table_a a
select /* parallel(b,8) */ * from table_b b where ……;
Ø 在存储过程/包中如何启用并行
参考上文所述。唯一的区别是如果需要执行alter session则需要用动态语句执行如
execute immediate ‘alter session enable parallel dml’;
Ø 并行度该设置多大
一般来说并行度越大SQL的执行效率越高但是不建议设置超过CPU核数的并行度。在当前的RAC中考虑到同时会有多个任务在跑为了不影响其他任务并行度需要严格控制在32个以下一般的建议值是8和16。
另外建议并行度设置为2的n次方如2/4/8/16/32等。
Ø 既有DML又有query的SQL如何设置并行度如insert …..select…..
1) 如果写入量大则在insert上加并行会明显提升性能否则在insert上加并行基本没有什么意义。如
insert into t(game_name,num) select game_name,count(1) cnt from popt_total_login_all_his group by game_name;
这种SQL主要瓶颈在查询上写入量很少因此只需要在查询部分设置并行即可。
2) 如果查询量大则在查询上加并行会明显提升性能
3) 如果写入和查询量都大则在两个部分都要加并行不要让其中一方成为瓶颈。 总结起来就是瓶颈在部分就在这部分上加并行如果都有瓶颈则都加并行。 对于pdml建议
1) 由于并行dml有诸多限制和弊端因此在写入量不大的情况下尽量不启用并行dml。
2) insert和query的并行度不一定要一致可根据实际调整一般设置query并行度大于等于insert并行度。并行度最好设置为2的n次方。
3) 并行度不要设置超过CPU的个数
Ø 如何查询并行是否起作用
1) 在SQL执行的时候在PL/SQL DEV看是否有多个活动会话执行一个SQL
2) SQL执行完了以后在同一个会话查询v$pq_sesstat
SQL select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 0 上面的结果中如果last_query有非0的值表示并行起作用了。 其他方法先不告诉你了…… Ø 如何控制并行度
可以用hint、alter session以及table/index degree指定并行度详细请参考上文【如何启用并行】部分。 Ø 如何跨实例并行
在当前的RAC环境为了减少cache fusion提高效率默认情况把SQL并行限制在同一个节点执行。如果有超超大的SQL需要多个节点同时并行则可以用如下语句来控制并行可跨越的实例
alter session set parallel_instance_groupdw; --可跨越4个节点
alter session set parallel_instance_groupdw1; --限制在节点1执行
alter session set parallel_instance_groupdw12; --限制在节点1和2执行
alter session set parallel_instance_groupdw124; --限制在节点1/2/4三个节点执行 以此类推。 原则上不允许跨实例并行如果必须跨实例则使用前需征得DBA同意。 Ø 为什么我的SQL达不到我设定的并行度
并行度受以下(不限于)条件限制
1) 系统的session、process参数的设定(一般不会超过)
2) parallel_max_serversx限制。这个限定目前是256意味着一个节点最多启动256个并行从属进程。在多用户多SQL同时执行的情况下很容易达到这个上限。
3) 对于个人用户当前最多只允许4个或者10个连接同时访问数据库。这意味着个人用户下并行度不能超过4或者10。(systemuser等程序账号不在此限制范围之内) Ø 并行有什么弊端
1 并行dml会浪费空间并行度越高浪费越厉害
2 被并行dml影响的表需要提交或者回滚后才能被后续的SQL使用否则会报错这可能会影响事务的一致性。
3 并行容易触发异常或者bug降低系统和程序的稳定性
