Rac/Exa yes.
Thanks.
Dominic
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
From: Ls Cheng<mailto:exriscer@xxxxxxxxx>
Sent: 15 July 2021 13:27
To: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Cc: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: KTSJ / Wnnn
Hi
Is this RAC? If it is then most probably you need to apply this patch
Bug 32245850 - txtsdan : dml operations hung on "gc current request" waits
We had quite a few problems with KTSJ in a 19.10 database running in Exadata
Full Rack and after SR'ing was told to apply this patch which fixed the
problems.
BR
On Thu, Jul 15, 2021 at 1:02 PM Dominic Brooks
<dombrooks@xxxxxxxxxxx<mailto:dombrooks@xxxxxxxxxxx>> wrote:
I was observing a foreground process yesterday which was running a series of
batched updates from Java in a single thread and was running very slowly.
Each element in the batch was updating a single row via a unique scan.
The execution time of this feed was reported as having tripled since moving to
19.6.
Performance was atrocious. For example, from AWR over a period of 15-16 hours,
an average size batch of a couple of hundred elements was averaging anywhere
between 8 and over 100 seconds per execution per hour, the vast majority of
time in cluster related waits. Averages hide a whole bunch of detail of course
but a useful indicator.
I was observing from GV$SESSION and GV$ASH and the source of the cluster waits
seems to be related to KTSJ slave activity and there was strong correlation of
the “two” (java update plus multiple active KTSJ slaves) working on the same
datafile/blocks – series of the two doing gc buffer busy release, gc buffer
busy acquire, gc current block busy with the occasional cell single block
physical read. Blocking session information on some of the gc waits
occasionally pointing at the other (update blocked by KTSJ or vice versa)
Reading all the responses oracle-l thread from May 2020 on KTSJ was the best
source of information I could find:
https://www.freelists.org/post/oracle-l/reads-by-KTSJ,17<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.freelists.org%2Fpost%2Foracle-l%2Freads-by-KTSJ%2C17&data=04%7C01%7C%7Cdd9bb4f911924b21425208d9478be3b4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619488405969724%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=R0ROqnCkMW1%2BwRzX7010%2Bro6U8ckKDF%2B66M2bTQTPMY%3D&reserved=0>
And a couple of bug references leading from there, not all of which relevant to
my version (19.6) but giving indications of what might be going on:
* blocks are not marked as free in assm after delete - 12.2 and later (Doc
ID 30265523.8)
* performance degradation by w00 processes after applying july 2020 dbru
(Doc ID 32075777.8) superseded by
* force full repair enabled by fix control and populate repair list even if
_assm_segment_repair_bg=false (Doc ID 32234161.8)
With mention of the parameter _assm_segment_repair_bg.
Per the explanations in the oracle-l thread, seems to be foreground session
doing something which then prompts background session to check/fix the ASSM
information. But in my case, this fixing is causing significant contention back
to the foreground session.
I ran snapper on some of the KTSJ slaves and of the ASSM fix related stats,
ASSM bg: slave fix state was consistently around 5000 in a 5 second period.
That is not a statistic I have any context to judge the value of.
This is a monthly feed so doesn’t happen every day but when it does it sits in
a critical path. It’s finished now so there’s not a lot I can look at now if
it’s not in ASH. Obviously a next step is to try to reproduce this in a test
environment.
I just wondered whether anyone had done any further investigation into this
behaviour.
Cheers,
Dominic
Sent from
Mail<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgo.microsoft.com%2Ffwlink%2F%3FLinkId%3D550986&data=04%7C01%7C%7Cdd9bb4f911924b21425208d9478be3b4%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637619488405974701%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=%2FRVwTTlQlNbpW%2B0B5vO9O5mYeFuH7DNOd1xjfiSiyB0%3D&reserved=0>
for Windows 10
