Problems with parallel and buffer sort in Oracle execution plan

Recently developed that a system has a SQL execution time is fast and slow, let me help to see, this SQL is 4 tables (2 million, 2 100,000) for the inner JOIN operation, the last count (*) aggregation operation, the execution time 1--10s unequal. View execution plan discovery uses PX parallel and buffer sort operations, no wonder it is fast and slow, but SQL does not explicitly add parallel, parameter parallel_server is not enabled, this parallel and buffer sort is from there?

The following experiment is done to reproduce the above situation:

1. px Parallel and Buffer SORT:

Select/*+ Parallel (E 4) Parallel (d 4) */E.ename, D.dname

From Scott.emp E, scott.dept d,scott.emp m

where E.deptno = D.deptno

and D.deptno = M.deptno

and E.deptno = 10;

Execution Plan:

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

| Id | Operation |    Name | TQ | In-out| PQ Distrib |

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

| 0 |          SELECT STATEMENT |        |      |            | |

|  1 |          PX Coordinator |        |      |            | |

|   2 | PX SEND QC (RANDOM) |  : TQ10003 | q1,03 | P->s | QC (RAND) |

|* 3 |          HASH JOIN BUFFERED |  | q1,03 |            PCWP | |

|     4 |          PX RECEIVE |  | q1,03 |            PCWP | |

|      5 | PX SEND Broadcast |  : TQ10001 | q1,01 | S->p | Broadcast |

|       6 |          PX SELECTOR |  | q1,01 |            SCWC | |

|        7 | TABLE ACCESS Full |  EMP | q1,01 |            SCWP | |

|* 8 |          HASH JOIN |  | q1,03 |            PCWP | |

|      9 | JOIN FILTER CREATE |  : BF0000 | q1,03 |            PCWP | |

|       10 |          BUFFER SORT |  | q1,03 |            PCWC | |

|        11 |          PX RECEIVE |  | q1,03 |            PCWP | |

|         12 | PX SEND HYBRID hash|        : TQ10000 | | S->p | HYBRID hash|

|* 13 | TABLE ACCESS Full |        DEPT |      |            | |

|      14 |          PX RECEIVE |  | q1,03 |            PCWP | |

|       15 | PX SEND HYBRID HASH |  : TQ10002 | q1,02 | P->p | HYBRID hash|

|        16 | JOIN FILTER Use |  : BF0000 | q1,02 |            PCWP | |

|         17 |          PX BLOCK ITERATOR |  | q1,02 |            PCWC | |

|* 18 | TABLE ACCESS Full |  EMP | q1,02 |            PCWP | |

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

2. BUFFER SORT (integrable product will produce this):

Select E.ename, D.dname

From Scott.emp E, scott.dept D;

Execution Plan:

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

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

| 0 |             SELECT STATEMENT |      |       |          |11 (100) | |

|  1 |             MERGE JOIN Cartesian |  | 95 | 57780 |11 (0) | 00:00:01 |

|   2 | TABLE ACCESS Full |    DEPT |   5 | 324 | 2 (0) | 00:00:01 |

|   3 |             BUFFER SORT |   |   19 | 856 | 9 (0) | 00:00:01 |

|    4 | INDEX FAST Full scan|   Pk_emp |   19 | 856 |          0 (0) | |

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

View Oracle's explanations:

The BUFFER SORT operation indicates that the database was copying the data blocks obtained by the scan of pk_emp from the S GA to the PGA. This strategy avoids multiple scans of the same blocks in the database buffer cache, which would generate many logical rea DS and Permit resource contention.

The Final Solution: to the 2 small table plus rowid >= ' 0 ' conditions, let the table through the index ROWID scan the hash join connection, stable within 1S return results.

Question: How does the px parallelism of the original SQL come into being and has not been reproduced.

This article is from the "srsunbing" blog, make sure to keep this source http://srsunbing.blog.51cto.com/3221858/1630138

Problems with parallel and buffer sort in Oracle execution plan