AW: Increased PGA requirements for SORTs in 19c?
- From: "Willy Klotz" <willyk@xxxxxxxxxxx>
- To: <Michael.J.Tefft@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 21 Nov 2020 09:09:12 +0100
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."
That's the old and never changing bla from support. Every time I hear "this
is expected" I know that they do not know what they are talking about ..
Has anyone seen an effect like this?
In one word, YES.
Give it more pga_aggregate_target, until direct path write temp and direct
path read temp returns to acceptable values (you may have to increase
pga_aggregate_limit also).
Regards and good luck
Wilhelm Klotz
Von: oracle-l-bounce@xxxxxxxxxxxxx [
mailto:oracle-l-bounce@xxxxxxxxxxxxx] Im
Auftrag von Tefft, Michael J
Gesendet: Freitag, 20. November 2020 13:11
An: 'oracle-l@xxxxxxxxxxxxx' <oracle-l@xxxxxxxxxxxxx>
Betreff: Increased PGA requirements for SORTs in 19c?
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.04
direct 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
Other related posts:
