scott@TEST>selecttable_name,degreefromuser_tableswheretable_name='EMP'; TABLE_NAME DEGREE ---------------------------------------- EMP 1@H_502_0@想用默认的并行度去访问表EMP
scott@TEST>altertableempparallel; Tablealtered. scott@TEST>selecttable_name,degreefromuser_tableswheretable_name='EMP'; TABLE_NAME DEGREE ---------------------------------------- EMP DEFAULT scott@TEST>setautotracetraceonly scott@TEST>select*fromemp; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2873591275 -------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Rows |Bytes|Cost(%cpu)|Time |TQ|IN-OUT|PQDistrib| -------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT| |14|1218|2(0)|00:00:01| | | | |1|PXCOORDINATOR| | | | | | | | | |2|PXSENDQC(RANDOM)|:TQ10000|14|1218|2(0)|00:00:01|Q1,00|P->S|QC(RAND)| |3|PXBLOCKITERATOR| |14|1218|2(0)|00:00:01|Q1,00|PCWC| | |4|TABLEACCESSFULL|EMP |14|1218|2(0)|00:00:01|Q1,00|PCWP| | -------------------------------------------------------------------------------------------------------------- .....@H_502_0@从上面的执行计划中可以看出,走的是对表EMP的全表扫描,PX...表示的就是走的并行 @H_502_0@默认并行度的算法如下:
@H_502_0@默认并行度=parallel_threads_per_cpu*cpu_count @H_502_0@如果想对表开启8个并行度则执行:alter table emp parallel 8;
scott@TEST>selecttable_name,degreefromuser_tableswheretable_name='EMP'; TABLE_NAME DEGREE ---------------------------------------- EMP DEFAULT scott@TEST>altertableempparallel8; Tablealtered. scott@TEST>selecttable_name,degreefromuser_tableswheretable_name='EMP'; TABLE_NAME DEGREE ---------------------------------------- EMP 8@H_502_0@2、使用并行Hint @H_502_0@有如下一些并行Hint可以用来控制是否启用并行及指定并行度 @H_502_0@1) /*+ parallel(table[,degree]) */ #用于指定并行度去访问指定表,如果没有指定并行度degree,则使用Oracle默认并行度 @H_502_0@2) /*+ noparallel(table) */ #对指定表不使用并行访问 @H_502_0@3) /*+ parallel_index(table[,index[,degree]]) */ #对指定的分区索引以指定的并行度去做并行范围扫描 @H_502_0@4) /*+ no_parallel_index(table[,index]) */ #对指定的分区索不使用并行访问 @H_502_0@5) /*+ pq_distribute(table,out,in) */ #对指定表以out/in所指定的方式来传递数据,这里out/in的值可以是HASH/NONE/BROADCAST/PARTITION中的任意一种如/*+ pq_distribute(table,none,partition) */ @H_502_0@把表EMP修改回并行度为1
scott@TEST>altertableempnoparallel; Tablealtered. scott@TEST>selecttable_name,degreefromuser_tableswheretable_name='EMP'; TABLE_NAME DEGREE ---------------------------------------- EMP 1@H_502_0@使用并行Hint执行上之前的sql
scott@TEST>select/*+parallel(emp)*/*fromemp; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2873591275 -------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Rows |Bytes|Cost(%cpu)|Time |TQ|IN-OUT|PQDistrib| -------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT| |14|1218|2(0)|00:00:01| | | | |1|PXCOORDINATOR| | | | | | | | | |2|PXSENDQC(RANDOM)|:TQ10000|14|1218|2(0)|00:00:01|Q1,00|PCWP| | --------------------------------------------------------------------------------------------------------------@H_502_0@从上面的执行计划中可以看出,走的是并行 @H_502_0@3、使用alter session命令 @H_502_0@使用alter session命令,可以在当前session中强制启用并行查询或并行DML。如果强制启用了并行查询或者并行DML,那就意味着从执行alter session命令强制开启并行的那个时间点开始,在这个session中随后执行的所有sql都将以并行的方式执行,有如下四种方法在当前session中强制开启并行 @H_502_0@1) alter session parallel query @H_502_0@在当前session中强制开启并行查询,没有指定并行度,Oracle使用默认并行度 @H_502_0@2) alter session parallel query parallel n @H_502_0@在当前session中强制开启并行查询,并且指定并行度为n @H_502_0@3) alter session parallel dml @H_502_0@在当前session中强制开启并行DML,没有指定并行度,Oracle使用默认并行度 @H_502_0@4) alter session parallel dml parallel n @H_502_0@在当前session中强制开启并行DML,并且指定并行度为n @H_502_0@表EMP并行度仍为1,在session中强制开启并行:
scott@TEST>selecttable_name,degreefromuser_tableswheretable_name='EMP'; TABLE_NAME DEGREE ---------------------------------------- EMP 1 scott@TEST>setautotracetraceonly scott@TEST>altersessionforceparallelquery; Sessionaltered. scott@TEST>select*fromemp; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2873591275 -------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Rows |Bytes|Cost(%cpu)|Time |TQ|IN-OUT|PQDistrib| -------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT| |14|1218|2(0)|00:00:01| | | | |1|PXCOORDINATOR| | | | | | | | | |2|PXSENDQC(RANDOM)|:TQ10000|14|1218|2(0)|00:00:01|Q1,00|PCWP| | -------------------------------------------------------------------------------------------------------------- ......@H_502_0@从执行计划中可以看出走的是并行。 @H_502_0@取消当前session并行使用如下语句alter session disable parallel query;
scott@TEST>altersessiondisableparallelquery; Sessionaltered. scott@TEST>select*fromemp; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:3956160932 -------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | -------------------------------------------------------------------------- |0|SELECTSTATEMENT| |14|1218|3(0)|00:00:01| |1|TABLEACCESSFULL|EMP|14|1218|3(0)|00:00:01| -------------------------------------------------------------------------- ......@H_502_0@4、11gR2的自动并行 @H_502_0@Oracle在11gR2中引入了自动并行(Auto DOP),自动并行的开启受参数parallel_degree_policy的控制,其默认值为MANUAL,即自动并行在默认情况下并没有开启。如果通过更改PARALLEL_DEGREE_POLICY的值而开启了自动并行,那么后面执行的sql的执行方式是串行还是并行,以及并行执行的并行度是多少等,就都是由Oracle自动来决定了。
scott@TEST>selecttable_name,degreefromuser_tableswheretable_namein('EMP','EMP_TEMP'); TABLE_NAME DEGREE ------------------------------------------------------------------------------------------------------------------------------------------------------ EMP 1 EMP_TEMP 1 scott@TEST>altersessionsetparallel_degree_policy=AUTO; Sessionaltered. scott@TEST>setautotracetraceonly scott@TEST>select*fromemp; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:3956160932 -------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | -------------------------------------------------------------------------- |0|SELECTSTATEMENT| |14|1218|3(0)|00:00:01| |1|TABLEACCESSFULL|EMP|14|1218|3(0)|00:00:01| -------------------------------------------------------------------------- ...... scott@TEST>select*fromemp_temp; 1835008rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2661083444 -------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Rows |Bytes|Cost(%cpu)|Time |TQ|IN-OUT|PQDistrib| -------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT| |1835K|66M|1683(1)|00:00:21| | | | |1|PXCOORDINATOR| | | | | | | | | |2|PXSENDQC(RANDOM)|:TQ10000|1835K|66M|1683(1)|00:00:21|Q1,00|P->S|QC(RAND)| |3|PXBLOCKITERATOR| |1835K|66M|1683(1)|00:00:21|Q1,00|PCWC| | |4|TABLEACCESSFULL|EMP_TEMP|1835K|66M|1683(1)|00:00:21|Q1,00|PCWP| | -------------------------------------------------------------------------------------------------------------- ......@H_502_0@从上面的输出可以看出表EMP和EMP_TEMP的并行度都为1,但是两个表的数据量相关很大,EMP只有14条数据,EMP_TEMP有1835008条数据。在执行时Oracle选择的执行方式就有不同,EMP是串行执行,而EMP_TEMP为并行执行。
@H_502_0@
@H_502_0@参考《基于Oracle的sql优化》 @H_502_0@官方文档:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2013.htm#i2231814 原文链接:https://www.f2er.com/oracle/210084.html