Problems of parallelism and BUFFER SORT in Oracle execution Plan

Recently, the developer said that there is a sql on a certain system whose execution time varies from fast to slow. Let me take a look. This sql consists of four tables (2 million, 200 thousand) for inner join operation, and finally for count (*) aggregation operation. The execution time varies from 1m to 10s. Looking at the execution plan, it is found that PX parallelism and BUFFER SORT operations are used, no wonder it is fast and slow, but sql does not explicitly add parallel, and the parameter parallel_server is not enabled, where does this parallelism and BUFFER SORT come from?

Here is an experiment to reproduce the above situation:

1. PX parallelism 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 | Q1Magin03 | P-> S | QC (RAND) |

| | * 3 | HASH JOIN BUFFERED | | Q1Power03 | PCWP |

| | 4 | PX RECEIVE | | Q1Person03 | PCWP |

| | 5 | PX SEND BROADCAST |: TQ10001 | Q1Magi 01 | S-> P | BROADCAST |

| | 6 | PX SELECTOR | | Q1Magol 01 | SCWC |

| | 7 | TABLE ACCESS FULL | EMP | Q1Magol 01 | SCWP |

| | * 8 | HASH JOIN | | Q1Power03 | PCWP |

| | 9 | JOIN FILTER CREATE |: BF0000 | Q1Power03 | PCWP |

| | 10 | BUFFER SORT | | Q1Person03 | PCWC |

| | 11 | PX RECEIVE | | Q1Person03 | PCWP |

| | 12 | PX SEND HYBRID HASH |: TQ10000 | | S-> P | HYBRID HASH |

| | * 13 | TABLE ACCESS FULL | DEPT |

| | 14 | PX RECEIVE | | Q1Person03 | PCWP |

| | 15 | PX SEND HYBRID HASH |: TQ10002 | Q1Power02 | P-> P | HYBRID HASH |

| | 16 | JOIN FILTER USE |: BF0000 | Q1Power02 | PCWP |

| | 17 | PX BLOCK ITERATOR | | Q1Power02 | PCWC |

| | * 18 | TABLE ACCESS FULL | EMP | Q1Power02 | PCWP |

2. BUFFER SORT (this is generated by the product of Karl product):

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) |

Check out Oracle's explanation:

The BUFFER SORT operation indicates that the database is copying the data blocks obtained by the scan of pk_emp from the SGA to the PGA. This strategy avoids multiple scans of the same blocks in the database buffer cache, which would generate many logical reads and permit resource contention.

The final solution: add the condition of rowid > ='0' to the two small tables, let the table scan the hash join connection through index rowid, and return the result steadily within 1s.

Question: how did the PX parallelism of the original sql come from? it has never been reproduced.