ORACLE执行计划-SQL语句开并行与不开并行在执行计划中的体现_sql语句是串行执行还是并行执行-优快云博客

本文链接：https://blog.youkuaiyun.com/xiangmin_meng/article/details/5785285

本文探讨了在Oracle中，SQL语句开启并行与不开启并行的执行计划差异。通过示例展示了当对不同表开启并行时，执行计划的选择及其对性能的影响，包括CPU成本、时间消耗和物理/逻辑读取操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL语句开并行与不开并行在执行计划中的体现

ENODS.WS_JIAOYI 数据量小于ENODS.WS_OPER_LOG 数据量，且ENODS.WS_JIAOYI 有唯一索引

SQL>set autot on;

SQL> SELECT COUNT(distinct A.MJ_LOGIN_ID) AS SEND_GOODS_MEMBER_CNT
      FROM ENODS.WS_JIAOYI A, ENODS.WS_OPER_LOG B
     WHERE A.ID = B.TRADE_ID
       AND B.ACTION_TYPE IN
           ('front_seller_send_all_goods', 'front_sellerAnnounceSendGoods')
       AND TRUNC(B.GMT_CREATE) = trunc(sysdate)-2;

SEND_GOODS_MEMBER_CNT
---------------------
833

1 row selected.

Elapsed: 00:00:12.64

Execution Plan
----------------------------------------------------------
Plan hash value: 1358353880

------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |     1 |    71 | 17371   (1)| 00:05:13 |
|   1 | SORT GROUP BY                |                      |     1 |    71 |            |          |
|   2 |   NESTED LOOPS                |                      |   190 | 13490 | 17371   (1)| 00:05:13 |
|* 3 |    TABLE ACCESS FULL          | WS_OPER_LOG |   190 | 7600 | 17181   (1)| 00:05:10 |
|   4 |    TABLE ACCESS BY INDEX ROWID| WS_JIAOYI         |     1 |    31 |     1   (0)| 00:00:01 |
|* 5 |     INDEX UNIQUE SCAN         | PK_WS_JIAOYI      |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("B"."ACTION_TYPE"='front_sellerAnnounceSendGoods' OR
              "B"."ACTION_TYPE"='front_seller_send_all_goods') AND
              TRUNC(INTERNAL_FUNCTION("B"."GMT_CREATE"))=TRUNC(SYSDATE@!)-2)
   5 - access("A"."ID"="B"."TRADE_ID")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      12847 consistent gets
       8507 physical reads
        548 redo size
        529 bytes sent via SQL*Net to client
        488 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

解释

执行路径：3->2->5->4->1->0

CUP消耗代价: 17371

执行时间：00:00:12.64

物理读：8507

逻辑读：12847+0 一般来说逻辑读越大，说明从内存读的越多，可以减少IO

Cost (%CPU)：17371 (1) 消耗CPU代价17371，(1)表示占CUP的1%，一般来说控制在2%内为最佳

---------------------------------------------------------------------------------------------------------------------------------

开启并行

SQL> SELECT/*+ parallel(b 4)*/ COUNT(distinct A.MJ_LOGIN_ID) AS SEND_GOODS_MEMBER_CNT
      FROM ENODS.WS_JIAOYI A, ENODS.WS_OPER_LOG B
     WHERE A.ID = B.TRADE_ID
       AND B.ACTION_TYPE IN
           ('front_seller_send_all_goods', 'front_sellerAnnounceSendGoods')
       AND TRUNC(B.GMT_CREATE) = trunc(sysdate)-2;

SEND_GOODS_MEMBER_CNT
---------------------
833

1 row selected.

Elapsed: 00:00:08.18

Execution Plan
----------------------------------------------------------
Plan hash value: 1023787221

-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                           | Name                 | Rows | Bytes | Cost (%CPU)| Time     |    TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |     1 |    71 | 4820   (1)| 00:01:27 |        |      ||
|   1 | SORT GROUP BY                      |                      |     1 |    71 |            |          |        |      ||
|   2 |   PX COORDINATOR                    |                      |       |       |            |          |        |      ||
|   3 |    PX SEND QC (RANDOM)              | :TQ10001             |     1 |    71 |            |          | Q1,01 | P->S | QC (RAND)      |
|   4 |     SORT GROUP BY                   |                      |     1 |    71 |            |          | Q1,01 | PCWP ||
|   5 |      PX RECEIVE                     |                      |     1 |    71 |            |          | Q1,01 | PCWP ||
|   6 |       PX SEND HASH                  | :TQ10000             |     1 |    71 |            |          | Q1,00 | P->P | HASH|
|   7 |        SORT GROUP BY                |                      |     1 |    71 |            |          | Q1,00 | PCWP ||
|   8 |         NESTED LOOPS                |                      |   190 | 13490 | 4820   (1)| 00:01:27 | Q1,00 | PCWP ||
|   9 |          PX BLOCK ITERATOR          |                      |       |       |            |          | Q1,00 | PCWC ||
|* 10 |           TABLE ACCESS FULL         | WS_OPER_LOG |   190 | 7600 | 4767   (1)| 00:01:26 | Q1,00 | PCWP ||
| 11 |          TABLE ACCESS BY INDEX ROWID| WS_JIAOYI         |     1 |    31 |     1   (0)| 00:00:01 | Q1,00 | PCWP ||
|* 12 |           INDEX UNIQUE SCAN         | PK_WS_JIAOYI      |     1 |       |     0   (0)| 00:00:01 | Q1,00 | PCWP ||
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

10 - filter(("B"."ACTION_TYPE"='front_sellerAnnounceSendGoods' OR "B"."ACTION_TYPE"='front_seller_send_all_goods') AND
TRUNC(INTERNAL_FUNCTION("B"."GMT_CREATE"))=TRUNC(SYSDATE@!)-2)
12 - access("A"."ID"="B"."TRADE_ID")

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         39 recursive calls
          3 db block gets
     434329 consistent gets
       8568 physical reads
        628 redo size
        529 bytes sent via SQL*Net to client
        488 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          8 sorts (memory)
          0 sorts (disk)
          1 rows processed

解释

执行路径：10->9->12->11->8->7->6->5->4->3->2->1->0

CUP消耗代价: 4820

执行时间：00:00:08.18

物理读：8568

逻辑读：434329+3 一般来说逻辑读越大，说明从内存读的越多，可以减少IO

Cost (%CPU)：4820 (1) 消耗CPU代价4820，(1)表示占CUP的1%，一般来说控制在2%内为最佳

PX BLOCK ITERATOR：表示开了并行

---------------------------------------------------------------------------------------------------------------------------------

对a表开启并行

SQL> SELECT/*+ parallel(a 4)*/ COUNT(distinct A.MJ_LOGIN_ID) AS SEND_GOODS_MEMBER_CNT
2    FROM ENODS.WS_JIAOYI A, ENODS.WS_OPER_LOG B
3   WHERE A.ID = B.TRADE_ID
4     AND B.ACTION_TYPE IN
5         ('front_seller_send_all_goods', 'front_sellerAnnounceSendGoods')
6     AND TRUNC(B.GMT_CREATE) = trunc(sysdate)-2;

SEND_GOODS_MEMBER_CNT
---------------------
833

1 row selected.

Elapsed: 00:00:11.82

Execution Plan
----------------------------------------------------------
Plan hash value: 1358353880

Predicate Information (identified by operation id):
---------------------------------------------------

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      12847 consistent gets
       8547 physical reads
        548 redo size
        529 bytes sent via SQL*Net to client
        488 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

解释

这个执行计划为什么没有并行了？我的理解是这样的，等待高手解答

可以看出，虽然开了并行，但是oracle任然会优先执行唯一索引扫描

a表数据量小于b表，此时对b表开并行是最佳的。