Increased PGA requirements for SORTs in 19c?
Date: Fri, 20 Nov 2020 12:10:49 +0000
Message-ID: <1415532885fe44b2b72660cac05bf743_at_snapon.com>
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|
This query is using the same plan in all three settings. Same plan.
Trace/TKPROF shows these waits under 12.2:
Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 2 0.00 0.00 PGA memory operation 211 0.00 0.00 And these waits under 19.9: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ PGA memory operation 208 0.00 0.00 SQL*Net message to client 1 0.00 0.00 Disk file operations I/O 1 0.00 0.00 CSS initialization 1 0.00 0.00 CSS operation: query 6 0.00 0.00 CSS operation: action 1 0.00 0.00 ASM IO for non-blocking poll 16368 0.00 0.04direct path write temp 2067 0.36 2.71 direct path read temp 3144 0.61 96.20 SQL*Net message from client 1 0.00 0.00
We can also see from ASH and TKPROF that the time is dominated by the SORT operation - which of course aligns with the increased PGA and the TEMP usage and I/O.
I have an SR open but my confidence is low.
The Oracle analyst said "Version to version there will be need a more memory on SGA and PGA memory, So this is expected. Please increase the PGA_AGGREGATE_TARGET and check most of the sort operations are in memory." I asked for reference to documentation stating that, as I did not see anything like that in the Upgrade Guide, etc. And the 19c executions are clearly not being starved of PGA - they are getting 3x the PGA that the 12.2 executions got.
Has anyone seen an effect like this?
Thanks!
Mike Tefft
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 20 2020 - 13:10:49 CET