Oracle数据库版本11.2.0.4,最近发现有一条SQL很慢,执行一次要20多s,GG_FUNCTION_LOCATION这张表很大,发现这张表有并行度,修改并行度之后,执行计划变了走索引了,执行时间为2s。说明一个问题,当表有并行度的时候,SQL倾向于走索引。
有并行度的执行计划:
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 608 (100)| | | | | | |
| 1 | MERGE | GG_FUNCTION_LOCATION | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 704 | 492K| 608 (1)| 00:00:08 | | | Q1,02 | P->S | QC (RAND) |
| 4 | VIEW | | | | | | | | Q1,02 | PCWP | |
|* 5 | HASH JOIN RIGHT OUTER | | 704 | 492K| 608 (1)| 00:00:08 | | | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 1 | 308 | 555 (1)| 00:00:07 | | | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 1 | 308 | 555 (1)| 00:00:07 | | | Q1,01 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 1 | 308 | 555 (1)| 00:00:07 | KEY | KEY | Q1,01 | PCWC | |
|* 9 | TABLE ACCESS FULL | GG_FUNCTION_LOCATION | 1 | 308 | 555 (1)| 00:00:07 | KEY | KEY | Q1,01 | PCWP | |
| 10 | BUFFER SORT | | | | | | | | Q1,02 | PCWC | |
| 11 | PX RECEIVE | | 704 | 280K| 53 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 12 | PX SEND HASH | :TQ10000 | 704 | 280K| 53 (0)| 00:00:01 | | | | S->P | HASH |
| 13 | PARTITION LIST SINGLE | | 704 | 280K| 53 (0)| 00:00:01 | KEY | KEY | | | |
| 14 | PARTITION LIST SINGLE| | 704 | 280K| 53 (0)| 00:00:01 | KEY | KEY | | | |
|* 15 | TABLE ACCESS FULL | GG_FUNCTION_LOCATION_TEMP | 704 | 280K| 53 (0)| 00:00:01 | KEY | KEY | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------
去掉表并行度之后的执行计划:
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 7 (100)| | | |
| 1 | MERGE | GG_FUNCTION_LOCATION | | | | | | |
| 2 | VIEW | | | | | | | |
| 3 | NESTED LOOPS OUTER | | 1 | 625 | 7 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| GG_FUNCTION_LOCATION_TEMP | 1 | 297 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX RANGE SCAN | IDX_GG_FL_TEMP_PART_ORDER_ID | 3 | | 3 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| GG_FUNCTION_LOCATION | 1 | 328 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 7 | INDEX UNIQUE SCAN | PK_GG_FUNCTION_LOCATION | 1 | | 2 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------