一步一步理解Oracle Parallel机制(1)

先从一个案例入手,开始深入理解学习Oracle的并行机制,并以此延申到其他数据库。
 
1系统持续报direct path read,导致IO短时间内变化较大,经过定位为sql_id为4j4g5wsd7nbjs导致,由于该sql中并无对表 dayureport(199G)的过滤条件,当前并无优化空间。
 
select 'I' || ' | ' || to_char(sysdate, 'yyyymmdd') || ' | ' ||
       car.car_caseid || ' | ' || car.car_txnseqno || ' | ' ||
       car.rei_reporttype || ' | ' || car.car_userid || ' | ' ||
       car.car_modifytime || ' | ' || car.car_modifyseq || ' | ' ||
       car.rei_filename || ' | ' || car.rei_batchno || ' | ' || ca r.rei_extdate || ' | ' || car.rei_reportdate || ' | ' || car.rei_cati_seqno || ' | ' || car.rei_htcr_seqno || ' | ' || car.rei_tsdt_seqno || ' | ' || car.rei_ctif_seqno || ' | ' || car.rei_atif_seqno || ' | ' || car.rei_rpdi_seqno || ' | ' || car.rei_dtdt_seqno || ' | ' || car.rei_receipttype || ' | ' || car.rei_subtype || ' | ' || car.rei_cifbranchcd || ' | ' || car.rei_transloc || ' | ' || car.ety_rgn_code || ' | ' || car.car_customertype || ' | ' || car.txn_rgn_code || ' | ||'
  from dayureport car
 where car_caseid in
       (select cad_caseid from cad_day_20180818 where ety_rgn_code = 42208);
 
2.在排查过程中发现表dayureport自带degree属性, 使sql即使没有加并行参数,但实际执行过程中也起了50个并行度,其执行计划如下,其中:TQ为开启并行标志。
 
Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  FINDNA (1751:64765)
 SQL ID              :  4j4g5wsd7nbjs
 SQL Execution ID    :  16777216
 Execution Started   :  08/23/2018 18:21:03
 First Refresh Time  :  08/23/2018 18:21:03
 Last Refresh Time   :  08/23/2018 18:22:43
 Duration            :  100s
 Module/Action       :  SQL*Plus/-
 Service             :  oraAMLM
 Program             :  sqlplus@pamlaeadb1 (TNS V1-V3)
 Fetch Calls         :  250

Global Stats
=======================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Calls |  Gets  | Reqs | Bytes |
=======================================================================================================
|    4632 |     241 |     4263 |        0.00 |        0.00 |      128 |   250 |    26M |   2M | 194GB |
=======================================================================================================

Parallel Execution Details (DOP=50 , Servers Allocated=50)   --degree of parallel
===========================================================================================================================================================
|      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency |  Other   | Buffer | Read  | Read  |      Wait Events         |
|                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs  | Bytes |      (sample #)          |
===========================================================================================================================================================
| PX Coordinator | QC    |         |    0.46 |    0.46 |          |        0.00 |             |          |    986 |       |     . |  |
| p000           | Set 1 |       1 |      54 |    3.25 |       49 |             |             |     1.67 |   353K | 21848 |   3GB | direct path read (48) |
| p001           | Set 1 |       2 |      94 |    4.86 |       87 |             |        0.00 |     2.67 |   530K | 32831 |   4GB | direct path read (89) |
| p002           | Set 1 |       3 |      95 |    4.93 |       87 |             |             |     2.54 |   535K | 33108 |   4GB | direct path read (88) |
| p003           | Set 1 |       4 |      94 |    4.92 |       86 |             |             |     2.70 |   531K | 32890 |   4GB | direct path read (88) |
| p004           | Set 1 |       5 |      56 |    3.44 |       51 |             |             |     1.88 |   376K | 23314 |   3GB | direct path read (47) |
| p005           | Set 1 |       6 |      94 |    4.83 |       87 |             |             |     2.48 |   526K | 32485 |   4GB | direct path read (83) |
| p006           | Set 1 |       7 |      94 |    4.86 |       86 |             |             |     2.52 |   525K | 32492 |   4GB | direct path read (85) |
| p007           | Set 1 |       8 |      94 |    4.88 |       87 |             |             |     2.67 |   527K | 32718 |   4GB | direct path read (88) |
| p008           | Set 1 |       9 |      94 |    4.81 |       86 |             |             |     2.62 |   525K | 32432 |   4GB | direct path read (86) |
| p009           | Set 1 |      10 |      94 |    4.91 |       86 |             |             |     2.68 |   528K | 32772 |   4GB | direct path read (84) |
| p00a           | Set 1 |      11 |      94 |    4.90 |       86 |             |             |     2.68 |   529K | 32716 |   4GB | direct path read (84) |
| p00b           | Set 1 |      12 |      94 |    4.81 |       87 |             |             |     2.47 |   530K | 32743 |   4GB | direct path read (86) |
| p00c           | Set 1 |      13 |      94 |    4.84 |       87 |             |             |     2.48 |   528K | 32769 |   4GB | direct path read (86) |
| p00d           | Set 1 |      14 |      95 |    4.85 |       87 |             |             |     2.64 |   533K | 33007 |   4GB | direct path read (85) |
| p00e           | Set 1 |      15 |      94 |    4.81 |       86 |             |             |     2.65 |   529K | 32649 |   4GB | direct path read (87) |
| p00f           | Set 1 |      16 |      94 |    4.85 |       87 |             |             |     2.50 |   531K | 32909 |   4GB | direct path read (86) |
| p00g           | Set 1 |      17 |      94 |    4.91 |       87 |             |             |     2.66 |   530K | 32889 |   4GB | direct path read (88) |
| p00h           | Set 1 |      18 |      94 |    4.87 |       87 |             |             |     2.50 |   534K | 32903 |   4GB | direct path read (80) |
| p00i           | Set 1 |      19 |      94 |    5.01 |       86 |             |             |     2.58 |   529K | 32691 |   4GB | direct path read (84) |
| p00j           | Set 1 |      20 |      94 |    4.83 |       86 |             |             |     2.64 |   528K | 32720 |   4GB | direct path read (86) |
| p00k           | Set 1 |      21 |      94 |    4.93 |       86 |             |             |     2.69 |   533K | 32979 |   4GB | direct path read (86) |
| p00l           | Set 1 |      22 |      94 |    4.82 |       87 |             |             |     2.49 |   526K | 32622 |   4GB | direct path read (87) |
| p00m           | Set 1 |      23 |      96 |    4.94 |       88 |             |             |     2.69 |   536K | 33214 |   4GB | direct path read (87) |
| p00n           | Set 1 |      24 |      94 |    4.86 |       86 |             |             |     2.68 |   533K | 32870 |   4GB | direct path read (87) |
| p00o           | Set 1 |      25 |      94 |    5.01 |       86 |             |             |     2.78 |   527K | 32605 |   4GB | direct path read (85) |
| p00p           | Set 1 |      26 |      95 |    4.89 |       88 |             |             |     2.68 |   536K | 33159 |   4GB | direct path read (89) |
| p00q           | Set 1 |      27 |      94 |    4.83 |       87 |             |             |     2.63 |   534K | 32984 |   4GB | direct path read (89) |
| p00r           | Set 1 |      28 |      95 |    4.89 |       88 |             |             |     2.52 |   535K | 33126 |   4GB | direct path read (87) |
| p00s           | Set 1 |      29 |      94 |    4.85 |       87 |             |             |     2.64 |   525K | 32614 |   4GB | direct path read (88) |
| p00t           | Set 1 |      30 |      94 |    4.85 |       87 |             |             |     2.50 |   529K | 32742 |   4GB | direct path read (84) |
| p00u           | Set 1 |      31 |      94 |    4.86 |       86 |             |             |     2.67 |   527K | 32587 |   4GB | direct path read (88) |
| p00v           | Set 1 |      32 |      94 |    4.83 |       87 |             |             |     2.46 |   530K | 32747 |   4GB | direct path read (83) |
| p00w           | Set 1 |      33 |      94 |    4.75 |       87 |             |             |     2.61 |   524K | 32413 |   4GB | direct path read (83) |
| p00x           | Set 1 |      34 |      94 |    4.91 |       87 |             |             |     2.69 |   532K | 32937 |   4GB | direct path read (86) |
| p00y           | Set 1 |      35 |      94 |    5.01 |       87 |             |             |     2.57 |   538K | 33278 |   4GB | direct path read (86) |
| p00z           | Set 1 |      36 |      94 |    4.91 |       87 |             |             |     2.52 |   533K | 32933 |   4GB | direct path read (84) |
| p010           | Set 1 |      37 |      94 |    4.77 |       87 |             |             |     2.62 |   523K | 32298 |   4GB | direct path read (90) |
| p011           | Set 1 |      38 |      94 |    4.85 |       87 |             |             |     2.50 |   529K | 32747 |   4GB | direct path read (81) |
| p012           | Set 1 |      39 |      95 |    4.94 |       87 |             |             |     2.71 |   531K | 32932 |   4GB | direct path read (87) |
| p013           | Set 1 |      40 |      94 |    4.88 |       86 |             |             |     2.51 |   528K | 32748 |   4GB | direct path read (89) |
| p014           | Set 1 |      41 |      94 |    4.82 |       86 |             |             |     2.47 |   529K | 32762 |   4GB | direct path read (87) |
| p015           | Set 1 |      42 |      95 |    4.95 |       87 |             |             |     2.74 |   536K | 33190 |   4GB | direct path read (86) |
| p016           | Set 1 |      43 |      94 |    4.86 |       87 |             |             |     2.50 |   533K | 33021 |   4GB | direct path read (89) |
| p017           | Set 1 |      44 |      94 |    4.88 |       87 |             |             |     2.67 |   532K | 32987 |   4GB | direct path read (82) |
| p018           | Set 1 |      45 |      94 |    4.86 |       86 |             |             |     2.50 |   527K | 32681 |   4GB | direct path read (87) |
| p019           | Set 1 |      46 |      94 |    4.88 |       86 |             |             |     2.53 |   529K | 32783 |   4GB | direct path read (85) |
| p01a           | Set 1 |      47 |      95 |    4.97 |       87 |             |             |     2.56 |   530K | 32894 |   4GB | direct path read (87) |
| p01b           | Set 1 |      48 |      94 |    4.94 |       86 |             |             |     2.70 |   528K | 32673 |   4GB | direct path read (86) |
| p01c           | Set 1 |      49 |      95 |    4.92 |       87 |             |             |     2.52 |   524K | 32512 |   4GB | direct path read (88) |
| p01d           | Set 1 |      50 |      95 |    4.83 |       87 |             |             |     2.62 |   531K | 32981 |   4GB | direct path read (86) |
===========================================================================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3281332499)
===============================================================================================================================================================================
| Id |           Operation            |       Name       |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity |        Activity Detail   |
|    |                                |                  | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |          (# samples)     |
===============================================================================================================================================================================
|  0 | SELECT STATEMENT               |                  |         |      |         5 |    +96 |    51 |     3721 |      |       |     . |          |                         |
|  1 |   PX COORDINATOR               |                  |         |      |         5 |    +96 |    51 |     3721 |      |       |     . |          |                         |
|  2 |    PX SEND QC (RANDOM)         | :TQ10001         |    208K | 111K |         3 |    +96 |    50 |     3721 |      |       |     . |          |                         |
|  3 |     HASH JOIN                  |                  |    208K | 111K |       100 |     +1 |    50 |     3721 |      |       |  59MB |     0.11 | Cpu (5)                 |
|  4 |      BUFFER SORT               |                  |         |      |         1 |     +2 |    50 |    72800 |      |       |   3MB |          |                         |
|  5 |       JOIN FILTER CREATE       | :BF0000          |    1551 |   10 |         1 |     +2 |    50 |    72800 |      |       |     . |          |                         |
|  6 |        PX RECEIVE              |                  |    1551 |   10 |         1 |     +2 |    50 |    72800 |      |       |     . |          |                         |
|  7 |         PX SEND BROADCAST      | :TQ10000         |    1551 |   10 |         1 |     +2 |     1 |    72800 |      |       |     . |          |                         |
|  8 |          PARTITION LIST SINGLE |                  |    1551 |   10 |         1 |     +2 |     1 |     1456 |      |       |     . |          |                         |
|  9 |           TABLE ACCESS FULL    | CAD_DAY_20180818 |    1551 |   10 |         1 |     +2 |     1 |     1456 |      |       |     . |          |                         |
| 10 |      JOIN FILTER USE           | :BF0000          |      1G | 111K |        99 |     +2 |    50 |      29M |      |       |     . |          |                         |
| 11 |       PX BLOCK ITERATOR        |                  |      1G | 111K |        99 |     +2 |    50 |      29M |      |       |     . |          |                         |
| 12 |        TABLE ACCESS FULL       | CAR_CASEREPORT   |      1G | 111K |       100 |     +1 | 20606 |      29M |   2M | 194GB |     . |    99.89 | Cpu (375)               |
|    |                                |                  |         |      |           |        |       |          |      |       |       |          | direct path read (4227) |
===============================================================================================================================================================================

 

 

 
 
3.表dayureport带有并行degree的原因是由于我们在建表的时候指定了并行参数,导致表的degree发生变化。如以下测试:
 
create table parallel_test1 parallel (degree 4)  as select * from dba_objects;
SQL> select DEGREE from dba_tables where table_name='PARALLEL_TEST1';
TABLE_NAME                               DEGREE
---------------------------------------- ------------------------------
PARALLEL_TEST1                           4                                   --可见degree受创建表时指定的degree影响。
create table parallel_test2 parallel (degree 8)  as select * from dba_objects;
SQL> select table_name,degree from dba_tables where table_name='PARALLEL_TEST2';
TABLE_NAME                               DEGREE
---------------------------------------- ------------------------------
PARALLEL_TEST2                           8
 
4.表自带并行度在对表的并发访问个数较多时,会造成系统瞬间并发激增,系统负载瞬时压力过大的情况。可通过以下方式调整并行度,见如下测试:
 
SQL> alter table PARALLEL_TEST2 parallel;  
Table altered.
SQL> select DEGREE from dba_tables where table_name='PARALLEL_TEST2';
DEGREE
------------------------------
DEFAULT
5.调整默认并行度为是不是DDL操作呢?
SQL> select LAST_DDL_TIME from dba_objects where object_name='PARALLEL_TEST2';

LAST_DDL_TIME
--------------
20180823 21:35

SQL> alter table PARALLEL_TEST2 parallel 1;  --此处建议将其默认并行度改为1,而不是默认值

Table altered.

SQL> select DEGREE from dba_tables where table_name='PARALLEL_TEST2';

DEGREE
------------------------------
DEFAULT                                      --

SQL> select LAST_DDL_TIME from dba_objects where object_name='PARALLEL_TEST2';

LAST_DDL_TIME
--------------
20180824 09:37
 
5.当degree的并行度为默认时,其default值为多少?
 
当采用默认值时,default=cpu_count*parallel_thread_per_cpu。也就是说当表的默认度为default时,其并行度为4所以设置这个参数的时候要谨慎!

SQL> show parameter cpu_count NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ cpu_count integer 2 SQL> show parameter parallel_thread NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ parallel_threads_per_cpu integer 2

 

 
 
 

转载于:https://www.cnblogs.com/dayu-liu/p/9528717.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值