Performance Problem After Installing 11.2.0.4.180116 DBPSU (26925576)

(Doc ID 2403687.1)

Last updated on MAY 29, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

If INSERT sql or Select becomes much slower after applying Jan 2018 PSU 26925576, then you have hit this bug.

Analyzing the execution plan, before and after Jan 2018 PSU, the plan outlines are same. However after Jan 2018 PSU, part of the plan is not parallelized any more.

In the bad execution plan, this part of the plan is not parallelized.


Plan hash value: 1790828553 (bad plan)

...
|  76 |             TABLE ACCESS BY LOCAL INDEX ROWID| GL_TRXN                        |    13 |   338 |       |     4   (0)| 00:00:01 |     1 |     1 |  Q7,02 | PCWP |            |
|  77 |     VIEW                                     |                                |   431M|    28G|       |  3258K  (1)| 10:51:41 |       |       |        |      |            |
|  78 |      UNION-ALL                               |                                |       |       |       |            |          |       |       |        |      |            |
|  79 |       SORT GROUP BY                          |                                |   272M|    17G|    20G|  2070K  (1)| 06:54:09 |       |       |        |      |            |
|  80 |        SORT GROUP BY                         |                                |   272M|    17G|    20G|  279 |       VIEW                                  |                                |    416M|    37G|       |  3202K  (1)| 00:00:37 |       |       |  Q8,15 | PCWP |            |       |       |          |
|  80 |        VIEW                                 |                                |    416M|    27G|       |  3198K  (1)| 00:00:37 |       |       |  Q8,15 | PCWP |            |       |       |          |
|              |     73M|  2440M|       |   164K  (1)| 00:00:02 |       |       |  Q8,12 | PCWP |            |       |       |          |
| 118 |                PX SEND HASH                 | :TQ80005                       |     73M|  2440M|       |   164K  (1)| 00:00:02 |       |       |  Q8,05 | P->P | HASH       |       |       |          |
| 119 |                 PX BLOCK ITERATOR           |                                |     73M|  2440M|       |   164K  (1)| 00:00:02 |   KEY |   KEY |  Q8,05 | PCWC |            |       |       |          |
|*120 |                  TABLE ACCESS FULL          | table            |     73M|  2440M|       |   164K  (1)| 00:00:02 |   KEY |   KEY |  Q8,05 | PCWP |            |       |       |          |
| 121 |          SORT GROUP BY                      |                                |     15M|   914M|  1129M|   105K  (1)| 00:00:02 |       |       |  Q8,15 | PCWP |            |  1028M|    10M|          |
 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

plan 2735881061 ( good ):

| 75 | TABLE ACCESS BY LOCAL INDEX ROWID| GL_TRXN | 14 | 364 | | 4 (0)| 00:00:01 | 1 | 1 | Q7,02 | PCWP | | | | |
| 76 | PX COORDINATOR | | | | | | | | | | | | | | |
| 77 | PX SEND QC (RANDOM) | :TQ80015 | 416M| 37G| | 3202K (1)| 00:00:37 | | | Q8,15 | P->S | QC (RAND) | | | |
| 78 | BUFFER SORT | | 213M| 10G| | | | | | Q8,15 | PCWP | | 12G| 34M| |

79 | VIEW | | 416M| 37G| | 3202K (1)| 00:00:37 | | | Q8,15 | PCWP | | | | |
| 80 | VIEW | | 416M| 27G| | 3198K (1)| 00:00:37 | | | Q8,15 | PCWP | | | | |
| 81 | UNION-ALL | | | | | | | | | Q8,15 | PCWP | | | | |
| 82 | SORT GROUP BY | | 256M| 16G| 19G| 2009K (1)| 00:00:23 | | | Q8,15 | PCWP | | 18G| 42M| |
| 83 | PX RECEIVE | | 256M| 16G| | 2009K (1)| 00:00:23 | | | Q8,15 | PCWP | | | | |
| 84 | PX SEND HASH | :TQ80010 | 256M| 16G| | 2009K (1)| 00:00:23 | | | Q8,10 | P->P | HASH | | | |
| 85 | SORT GROUP BY | | 256M| 16G| 19G| 2009K (1)| 00:00:23 | | | Q8,10 | PCWP | | 18G| 42M| |

 




You may also notice a higher dop than before for the parts of the Sql that are running in parallel.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms