Re: Increased PGA requirements for SORTs in 19c?

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 20 Nov 2020 17:32:53 -0500

Hi MIchael,

That doesn't allow you to make any conclusions. Yes, 19.9 was devouring more temp & PGA, but you will need to figure out what did do that. You will have to go into ASH and see what processes were waiting for direct read/write temp events. Maybe there is a changed plan in the new version? That would be rather normal.  Maybe the new instance was collecting stats? Do you have an engineered system with real time statistics turned on? Buffer gets are also noticeably larger for the 19.9 which would lead to conclusion that something else was going on here. Average elapsed seconds is so much larger for 19.9 that it cannot be explained away by bad sort. Sorting algorithms haven't changed since the time of the Bible, king Donald Knuth's edition. It is unlikely that Oracle would suddenly start changing the implementation of the sorting algorithms.

Do you have any other dissimilar settings? Flashback? Undo for temp?

Regards

On 11/20/20 7:10 AM, Tefft, Michael J wrote:


We are upgrading some of our 12.2 databases to 19c (19.9) and we are seeing significantly degraded sort performance in several queries. Here are some ‘fun facts’ collected from AWR and ASH for one of these queries, comparing our production 12.2 database, our test database (as 12.2, prior to upgrade), and our test database (as 19.9, after upgrade):

   |Database |Prod 12.2      |Test 12.2      |Test 19.9      |

 
|------------------------------|---------------|---------------|---------------|

 |Executions |           1193|            567|             93|

 |*Avg Elapsed sec |           0.45|           0.49|          34.11*|

 |Avg CPU sec                   |           0.45| 0.44|           5.16|

 |Avg rows processed            |           6.00| 6.00|           6.06|

 |Avg IOWAIT sec                |           0.00| 0.01|          28.95|

 |*Avg Buffer Gets               | 987.09|         880.93|       1,071.20| *

**

* |Avg Physical Reads            |           0.01| 11.96|     171,222.48| *

**

* |Avg PGA MB                    |          13.40| 14.29|          41.04| *

**

* |Avg TEMP MB |           3.14|           0.07|       1,303.78|*

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217

Other related posts: