Oracle数据库并行的是是非非与新特性.pptx

1、并行不悖Oracle数据库并行的是是非非并行的基础概念并行的发展演进并行的不只是SQL并行的常见问题并行概述 单进程操作分拆由多个进程同时运行 充分利用主机CPU、IO能力 适用于OLAP系统 适用于OLTP的后台批处理 优化的最后手段并行概述SELECT * FROM sales s ORDER BY name;Parallel SortFull ScanPQ1PQ2PQ3PQ4PQ5PQ6PQ7PQ8Sales tableA - CD - FG - MN - OP - RSQueryCoordinatorT - VW - ZConsumerProducerdistribute并行概述-|

2、Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|-TQ |IN-OUT| PQ Distrib |0 | SELECT STATEMENT1 | PX COORDINATOR2 | PX SEND QC (ORDER)| 99772 | 9840K|26(4)| 00:00:01 | :TQ10001 | 99772 | 9840K| :TQ10000 | 99772 | 9840K| 99772 | 9840K| 99772 | 9840K|26 (4)| 00:00:01 | Q1,01 | P-S | QC (ORDER) |

3、3 |4 |5 |6 |7 |SORT ORDER BYPX RECEIVEPX SEND RANGE| 99772 | 9840K| 99772 | 9840K|2625252525(4)| 00:00:01 | Q1,01 | PCWP |(0)| 00:00:01 | Q1,01 | PCWP |(0)| 00:00:01 | Q1,00 | P-P | RANGE(0)| 00:00:01 | Q1,00 | PCWC |(0)| 00:00:01 | Q1,00 | PCWP |PX BLOCK ITERATOR |TABLE ACCESS FULL| SALES-| Id | Op

4、eration| Name | Rows | Bytes | Cost (%CPU)| Time|-|0 | SELECT STATEMENT1 | SORT ORDER BY| 99772 | 9840K| 99772 | 9840K|181181178(3)| 00:00:01 |(3)| 00:00:01 |(1)| 00:00:01 |2 |TABLE ACCESS FULL| SALES | 99772 | 9840K|-并行概述-| Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|-TQ |IN-OUT| PQ Dist

5、rib |0 | SELECT STATEMENT1 | PX COORDINATOR| 99772 | 9840K|26(4)| 00:00:01 | 2 | PX SEND QC (ORDER) | :TQ10001 | 99772 | 9840K| 26 (4)| 00:00:01 | Q1,01 | P-S | QC (ORDER) |3 |4 |5 |6 |7 |SORT ORDER BYPX RECEIVEPX SEND RANGE| 99772 | 9840K| 99772 | 9840K|2625252525(4)| 00:00:01 | Q1,01 | PCWP |(0)|

6、00:00:01 | Q1,01 | PCWP |(0)| 00:00:01 | Q1,00 | P-P | RANGE(0)| 00:00:01 | Q1,00 | PCWC |(0)| 00:00:01 | Q1,00 | PCWP | :TQ10000 | 99772 | 9840K| 99772 | 9840K| 99772 | 9840K|PX BLOCK ITERATOR |TABLE ACCESS FULL| SALES-Q1: Parallel Slave Group 100: Parallel Slave Sets 101: Parallel Slave Sets 2并行概述

7、-| Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|-TQ |IN-OUT| PQ Distrib |0 | SELECT STATEMENT1 | PX COORDINATOR2 | PX SEND QC (ORDER)| 99772 | 9840K|26(4)| 00:00:01 | :TQ10001 | 99772 | 9840K| :TQ10000 | 99772 | 9840K| 99772 | 9840K| 99772 | 9840K|26 (4)| 00:00:01 | Q1,01 | P-S | QC (ORDER

8、) |3 |4 |5 |6 |7 |SORT ORDER BYPX RECEIVEPX SEND RANGE| 99772 | 9840K| 99772 | 9840K|2625252525(4)| 00:00:01 | Q1,01 | PCWP |(0)| 00:00:01 | Q1,01 | PCWP |(0)| 00:00:01 | Q1,00 | P-P | RANGE(0)| 00:00:01 | Q1,00 | PCWC |(0)| 00:00:01 | Q1,00 | PCWP |PX BLOCK ITERATOR |TABLE ACCESS FULL| SALES-P-S: P

9、aralle to SerialP-P: Parallel to ParallelS-P: Serial to ParallelPCWP: Parallel Combined With ParentPCWC: Parallel Combined With ChildSCWP: Serial Combined With ParentSCWC: Serial Combined With ChildR-S: Remote to Serial并行概述-| Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|-TQ |IN-OUT| PQ Dis

10、trib |0 | SELECT STATEMENT1 | PX COORDINATOR2 | PX SEND QC (ORDER)| 99772 | 9840K|26(4)| 00:00:01 | :TQ10001 | 99772 | 9840K| :TQ10000 | 99772 | 9840K| 99772 | 9840K| 99772 | 9840K|26 (4)| 00:00:01 | Q1,01 | P-S | QC (ORDER) |3 |4 |5 |6 |7 |SORT ORDER BYPX RECEIVEPX SEND RANGE| 99772 | 9840K| 99772

11、| 9840K|2625252525(4)| 00:00:01 | Q1,01 | PCWP |(0)| 00:00:01 | Q1,01 | PCWP |(0)| 00:00:01 | Q1,00 | P-P | RANGE(0)| 00:00:01 | Q1,00 | PCWC |(0)| 00:00:01 | Q1,00 | PCWP |PX BLOCK ITERATOR |TABLE ACCESS FULL| SALES-并行概述 HASH 利用HASH算法进行数据打散 BROADCAST 将数据集广播方式发给所有消费者 RANGE 对数据范围打散配合GROUP BY和ORDER BY

12、操作 KEY 针对键值的逻辑聚集 ROUND ROBIN 将数据循环发送给所有消费者并行的基础概念并行的发展演进并行的不只是SQL并行的常见问题跨节点并行 RAC环境下，并行自动在多个节点同时执行 充分利用多个主机的CPU内存资源 通过参数parallel_force_local来避免跨节点并行并行队列 传统方式: 并行进程数量不足导致并行操作串行执行 并行进程数量太多导致主机资源耗尽 11g特性： 并行进程不足时进入队列没有足够的并行进程，SQL请求进入队列排队SQL语句语句解析，确定并行度643216队列当并行进程充足后，队列中第一个排队并行任务出队列并执行并行进程数量充足语句立即执行12

13、88并行与IN MEMORYRAC下的INMEMORY采用SHARE NOTHING架构并行与IN MEMORY 表数据分布方式 分区SQL alter table t_part inmemory distribute by partition;Table altered. 范围SQL alter table t inmemory distribute by rowid range;Table altered. 自动 启用INMEMORY的前提条件 alter system set parallel_degree_policy = AUTO alter system set parallel_

14、force_local = TRUE一体机对并行的支撑 PCI Flash提供高速低延迟IO能力 存储节点提供IO动态扩展能力 InfiniBand提供高速心跳网络带宽 RDMA协议进一步降低心跳网络延迟 zData一体机解决了并行扩展能力的瓶颈并行的基础概念并行的发展演进并行的不只是SQL并行的常见问题统计信息并行收集SQL exec dbms_stats.delete_database_statsPL/SQL procedure successfully completed.SQL exec dbms_stats.gather_database_statsPL/SQL procedure

15、successfully completed.Elapsed: 00:47:41.54SQL exec dbms_stats.delete_database_statsPL/SQL procedure successfully completed.SQL exec dbms_stats.gather_database_stats(degree = 8)PL/SQL procedure successfully completed.Elapsed: 00:39:07.87统计信息并行收集SQL exec dbms_stats.delete_database_statsPL/SQL procedu

16、re successfully completed.SQL alter system set parallel_adaptive_multi_user = false;System altered.SQL exec dbms_stats.set_global_prefs(CONCURRENT, TRUE)PL/SQL procedure successfully completed.SQL exec dbms_stats.gather_database_stats(degree = 8)PL/SQL procedure successfully completed.Elapsed: 00:36

17、:09.94数据泵并行testDEVDB $ expdp yangtk directory=d_temp dumpfile=user20190425.dpExport: Release 11.2.0.4.0 - Production on Thu Apr 25 14:41:58 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

18、 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting YANGTK.SYS_EXPORT_SCHEMA_01: yangtk/* directory=d_temp dumpfile=user20190425.dpEstimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using

19、 BLOCKS method: 3.682 GB.Master table YANGTK.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded*Dump file set for YANGTK.SYS_EXPORT_SCHEMA_01 is:/tmp/user20190425.dpJob YANGTK.SYS_EXPORT_SCHEMA_01 successfully completed at Thu Apr 25 14:45:23 2019 elapsed 0 00:03:15数据泵并行testDEVDB $ expdp yangtk direc

20、tory=d_temp dumpfile=user20190425_para%U.dp parallel=4Export: Release 11.2.0.4.0 - Production on Thu Apr 25 14:46:02 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith

21、 the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting YANGTK.SYS_EXPORT_SCHEMA_01: yangtk/* directory=d_temp dumpfile=user20190425_para%U.dp parallel=4Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOC

22、KS method: 3.682 GB.Dump file set for YANGTK.SYS_EXPORT_SCHEMA_01 is:/tmp/user20190425_para01.dp/tmp/user20190425_para02.dp/tmp/user20190425_para03.dp/tmp/user20190425_para04.dpJob YANGTK.SYS_EXPORT_SCHEMA_01 successfully completed at Thu Apr 25 14:48:12 2019 elapsed 0 00:01:59RMAN并行RMAN run2 3 allo

23、cate channel c1 device type disk format /u02/rman/%U;4 allocate channel c2 device type disk format /u02/rman/%U;5 backup tablespace system;6 backup tablespace sysaux;7 backup tablespace undotbs1;8 backup tablespace users;9 RMAN并行.input datafile file number=00001 name=/u01/app/oracle/oradata/DB18C/da

24、tafile/o1_mf_system_f9ovorc1_.dbfchannel c1: starting piece 1 at 2019-04-25 15:41:12channel c1: finished piece 1 at 2019-04-25 15:41:27piece handle=/u02/rman/19tvsrgo_1_1 tag=TAG20190425T154112 comment=NONEchannel c1: backup set complete, elapsed time: 00:00:15Finished backup at 2019-04-25 15:41:27.

25、input datafile file number=00003 name=/u01/app/oracle/oradata/DB18C/datafile/o1_mf_sysaux_f9ovq5gy_.dbfchannel c1: starting piece 1 at 2019-04-25 15:41:28channel c1: finished piece 1 at 2019-04-25 15:42:13piece handle=/u02/rman/1atvsrh8_1_1 tag=TAG20190425T154128 comment=NONE.input datafile file num

26、ber=00004 name=/u01/app/oracle/oradata/DB18C/datafile/o1_mf_undotbs1_f9ovqyl3_.dbfchannel c1: starting piece 1 at 2019-04-25 15:42:13channel c1: finished piece 1 at 2019-04-25 15:42:14piece handle=/u02/rman/1btvsril_1_1 tag=TAG20190425T154213 comment=NONE.input datafile file number=00007 name=/u01/a

27、pp/oracle/oradata/DB18C/datafile/o1_mf_users_f9ovqznq_.dbfchannel c1: starting piece 1 at 2019-04-25 15:42:14channel c1: finished piece 1 at 2019-04-25 15:43:19.RMAN并行RMAN run2 3 allocate channel c1 device type disk format /u02/rman/%U;4 allocate channel c2 device type disk format /u02/rman/%U;5 bac

28、kup tablespace system, sysaux, undotbs1, users;6 RMAN并行.channel c1: SID=290 device type=DISKStarting backup at 2019-04-25 15:30:54channel c1: starting full datafile backup setchannel c1: specifying datafile(s) in backup setinput datafile file number=00007 name=/u01/app/oracle/oradata/DB18C/datafile/

29、o1_mf_users_f9ovqznq_.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/DB18C/datafile/o1_mf_undotbs1_f9ovqyl3_.dbfchannel c1: starting piece 1 at 2019-04-25 15:30:55channel c2: starting full datafile backup setchannel c2: specifying datafile(s) in backup setinput datafile file number

30、=00003 name=/u01/app/oracle/oradata/DB18C/datafile/o1_mf_sysaux_f9ovq5gy_.dbfinput datafile file number=00001 name=/u01/app/oracle/oradata/DB18C/datafile/o1_mf_system_f9ovorc1_.dbfchannel c2: starting piece 1 at 2019-04-25 15:30:55channel c1: finished piece 1 at 2019-04-25 15:32:30piece handle=/u02/

31、rman/16tvsqtf_1_1 tag=TAG20190425T153054 comment=NONEchannel c1: backup set complete, elapsed time: 00:01:36channel c2: finished piece 1 at 2019-04-25 15:32:31piece handle=/u02/rman/17tvsqtf_1_1 tag=TAG20190425T153054 comment=NONEchannel c2: backup set complete, elapsed time: 00:01:36Finished backup

32、 at 2019-04-25 15:32:31released channel: c1released channel: c2SQLLDR并行$ sqlldr yangtk/yangtk control=control1.ctl direct=true parallel=trueSQL*Loader: Release 11.2.0.4.0 - Production on Wed May 1 22:35:47 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Load completed

33、 - logical record count 1000000.$ sqlldr yangtk/yangtk control=control2.ctl direct=true parallel=trueSQL*Loader: Release 11.2.0.4.0 - Production on Wed May 1 22:35:48 2019Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Load completed - logical record count 1000000.升级并行ora

34、cleDEVDB admin$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 -l /home/oracle catupgrd.sqlNumber of CpusDatabase NameDataBase Version= 4= DEVDB_N= 11.2.0.4.0Parallel SQL Process Count= 8-Phases 0-108 Start Time:2019_05_06 16:11:08-* Executing Change Scripts *Serial Phase #:0DEVDB_N Files:1Time: 870s* Ca

35、talog Core SQL *Serial Phase #:1Restart Phase #:2DEVDB_N Files:5DEVDB_N Files:1Time: 51sTime: 1s* Catalog Tables and Views *Parallel Phase #:3Restart Phase #:4DEVDB_N Files:19 Time: 10sDEVDB_N Files:1 Time: 0sSerial Phase #:107 DEVDB_N Files:1Serial Phase #:108 DEVDB_N Files:1Time: 0sTime: 29s-Phase

36、s 0-108 End Time:2019_05_06 17:10:12-Grand Total Upgrade Time: 0d:0h:59m:7s升级并行testDEVDB db18c$ ps -ef|grep 4629oracleoracleoracleoracleoracleoracleoracleoracleoracletest4629 31265 0 16:11 pts/04712 4629 0 16:11 pts/04713 4629 0 16:11 pts/04714 4629 0 16:11 pts/04715 4629 0 16:11 pts/04716 4629 0 16

37、:11 pts/04720 4629 0 16:11 pts/04733 4629 0 16:11 pts/04739 4629 0 16:11 pts/05446 1361 0 16:12 pts/400:00:00 /u02/db18c/perl/bin/perl catctl.pl -n 8 -l /home/oracle catupgrd.sql00:00:00 /u02/db18c/bin/sqlplus00:00:00 /u02/db18c/bin/sqlplus00:00:00 /u02/db18c/bin/sqlplus00:00:00 /u02/db18c/bin/sqlpl

38、us00:00:00 /u02/db18c/bin/sqlplus00:00:00 /u02/db18c/bin/sqlplus00:00:00 /u02/db18c/bin/sqlplus00:00:00 /u02/db18c/bin/sqlplus00:00:00 grep 4629人工并行 数据泵大表导出expdp query对大表同时分片导出 SQLLOADER导入启动多个SQLLDR命令同时导入表中，利用PARTITION EXCHANGE交换到目标表 多分区/子分区DDL操作多会话同时对多个分区进行DDL操作 大批量更新或删除使用DBMS_PARALLEL_EXECUTE包人工并行

39、SQL DECLARE23V_SQL VARCHAR2(4000);V_STATUS NUMBER;4 BEGIN56789101112131415DBMS_PARALLEL_EXECUTE.CREATE_TASK(T_PARALLEL_UPDATE);DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(T_PARALLEL_UPDATE, YANGTK, T_RECORD, TRUE, 32);V_SQL := UPDATE /*+ ROWID(A) */ T_RECORD A SET NAME = LOWER(NAME) WHERE ROWID BET

40、WEEN :START_ID AND :END_ID;DBMS_PARALLEL_EXECUTE.RUN_TASK(T_PARALLEL_UPDATE, V_SQL, DBMS_SQL.NATIVE, PARALLEL_LEVEL = 8);V_STATUS := DBMS_PARALLEL_EXECUTE.TASK_STATUS(T_PARALLEL_UPDATE);WHILE (V_STATUS != DBMS_PARALLEL_EXECUTE.FINISHED) LOOPDBMS_OUTPUT.PUT_LINE(PARALLEL EXECUTE STATUS: | V_STATUS);D

41、BMS_LOCK.SLEEP(5);V_STATUS := DBMS_PARALLEL_EXECUTE.TASK_STATUS(T_PARALLEL_UPDATE);END LOOP;DBMS_PARALLEL_EXECUTE.DROP_TASK(T_PARALLEL_UPDATE);16 EXCEPTION1718WHEN OTHERS THENRAISE;19 END;20 /PL/SQL procedure successfully completed.Elapsed: 00:04:41.50并行的基础概念并行的发展演进并行的不只是SQL并行的常见问题资源耗尽 并行本质是资源换时间 并行

42、不会降低系统资源消耗 并行是不具备伸缩性的 前台应用调用并行是灾难资源耗尽 CPU耗尽 IO耗尽 内存耗尽 心跳网络耗尽 Process进程耗尽资源耗尽资源耗尽资源耗尽INSERT INTO MID_PH_W_USER_FEE NOLOGGINGSELECT /*+PARALLEL(A, 8)*/ NVL(B.S_USER_NO, A.USER_NO) USER_NO, NVL(B.AREA_NO, A.AREA_NO) AREA_NO,NVL(B.CITY_NO, A.CITY_NO) CITY_NO, NVL(B.SVC_ID, A.SVC_ID) SVC_ID, NVL(B.BRAND_

43、ID, A.BRAND_ID) BRAND_ID,A.DEVICE_NO, SUM(A.ALL_FEE) ALL_FEE, SUM(A.RENT_FEE) RENT_FEE, SUM(A.LOCAL_FEE) LOCAL_FEE,SUM(A.LONG_FEE) LONG_FEE, SUM(A.ROAM_FEE) ROAM_FEE, SUM(A.ICR_FEE) ICR_FEE, SUM(A.OTHER_FEE) OTHER_FEE,SUM(SF_ALL_FEE - ALL_FEE) FAVOR_FEE, 0, 0 IS_CHANGE, 0, 0, NVL(B.OFFICE_NO, A.OFFI

44、CE_NO) OFFICE_NOFROM MID_M_C_USER_4G A,(SELECT /*+PARALLEL(4, 8)*/ S_USER_NO, C_USER_NO, AREA_NO, CITY_NO, SVC_ID, BRAND_ID, DEVELOP_CHANNEL OFFICE_NOFROM MID_M_B_C_USER AWHERE ACCT_MONTH = :B1AND CHANGE_MONTH = SUBSTR(:B1 , 1, 4) | 01) BWHERE ACCT_MONTH = :B1AND A.USER_NO = B.C_USER_NO(+)GROUP BY N

45、VL(B.S_USER_NO, A.USER_NO), NVL(B.AREA_NO, A.AREA_NO), NVL(B.CITY_NO, A.CITY_NO), NVL(B.SV C_ID,A.SVC_ID), NVL(B.BRAND_ID, A.BRAND_ID), A.DEVICE_NO, NVL(B.OFFICE_NO, A.OFFICE_NO)资源耗尽资源耗尽SQL select * from v$px_process_sysstat where statistic like Buffers%;STATISTICVALUE- -Buffers AllocatedBuffers Fre

46、edBuffers CurrentBuffers HWMSQL show parameter parallel_execution_message1991710199169317114212NAME- - -parallel_execution_message_size integer 16384TYPEVALUESQL select 114212 * 16384 /1024/1024 from dual;114212*16384/1024/1024-1784.5625ALTER SYSTEM SET _PX_use_large_pool = TRUE SCOPE = SPFILE;未启用并行

47、 未设置会话参数 并行代价高 并行进程资源不足 无法并行执行未启用并行SQL UPDATE /*+ PARALLEL(8) */ T_RECORD SET NAME = UPPER(NAME);24832512 rows updated.Elapsed: 00:06:23.17Execution Plan-Plan hash value: 2336775929-| Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|-TQ |IN-OUT| PQ Distrib |0 | UPDATE STATEMENT1 | UPDATE2 | PX

48、 COORDINATOR| T_RECORD |24M|568M| 6550|(1)| 00:00:03 |3 |4 |5 |PX SEND QC (RANDOM)| :TQ10000 |PX BLOCK ITERATOR |TABLE ACCESS FULL| T_RECORD |24M|24M|24M|568M| 6550568M| 6550568M| 6550(1)| 00:00:03 | Q1,00 | P-S | QC (RAND) |(1)| 00:00:03 | Q1,00 | PCWC |(1)| 00:00:03 | Q1,00 | PCWP |-未启用并行SQL ALTER

49、 SESSION FORCE PARALLEL DML PARALLEL 8;Session altered.Elapsed: 00:00:00.00SQL UPDATE /*+ PARALLEL(8) */ T_RECORD SET NAME = UPPER(NAME);24832512 rows updated.Elapsed: 00:04:16.67SQL COMMIT;Commit complete.Elapsed: 00:00:00.08未启用并行SQL EXPLAIN PLAN FOR UPDATE /*+ PARALLEL(8) */ T_RECORD SET NAME = UP

50、PER(NAME);Explained.SQL SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT-Plan hash value: 2898626402-| Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|-TQ |IN-OUT| PQ Distrib |0 | UPDATE STATEMENT1 | PX COORDINATOR2 | PX SEND QC (RANDOM) | :TQ10000 |24M|568M| 6550|(1)| 00:00:03 |24M|