Re: Increased PGA requirements for SORTs in 19c?

From: Jonathan Lewis <jlewisoracle_at_gmail.com>
Date: Fri, 20 Nov 2020 12:40:30 +0000
Message-ID: <CAGtsp8nEQkH2DidtwGsUHP1LwzHsjNtjmYaQNH+-7uuCaxCO3Q_at_mail.gmail.com>



I take it you're not posting the execution plan for confidentiality reasons. A text version of the SQL Monitor report might be helpful if you can hack it consistently.

The thought that crosses my mind is that the number of PGA allocation operations is the same in 12c and 19c, which suggests (in the absence if any testing) that the memory allocated to do the sort is the same and the baseline PGA is much higher. You may have discounted this already in the PGA numbers you report, of course, but I would start a session and check v$process_memory. I would also check v$pgastat before you start the test, looking at:

aggregate PGA target parameter          -- pga_aggregate_target setting
aggregate PGA auto target                   -- currently free pga memory
(i.e. target - currently allocated to processes)
global memory bound                           -- available limit for a
process, a % of the above, NOT of the pga_aggregate_target

It may be that with many more processes in 19c the global memory bound is much lower for the same pga_aggregate_target, leading to a spill to disc. (NB - this is a hypothesis, not a description of previous observations).

Regards
Jonathan Lewis

On Fri, 20 Nov 2020 at 12:11, Tefft, Michael J <Michael.J.Tefft_at_snapon.com> 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|
>
>
>
> 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
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 20 2020 - 13:40:30 CET

Original text of this message