Re: Huge DBF sequential reads by KTSJ while updating securefile LOBS.

Hi Jonathan,

I have attached the p1 ,p2, p3 values which the KTSJ module was doing the single block reads, and all are pointing to the LOB segment. The main issue here is goldengate replication latency. This we are observing when multiple concurrent sessions are updating the securefile LOB's. So to isolate the issue and to see whether the space management slaves are contributing to this behaviour i turned off that feature using the below parameter.

ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0; Post which i don't see single block reads from KTSJ module but still we had replication latency. We use synchronous IO for the NAS file system where the data resides. It appears the "direct path write" itself is taking less time however it appears the module is waiting for CPU (wait event null as per ASH).

SQL_ID      EVENT
                  MODULE            COUNT(*)
-------------
----------------------------------------------------------------
------------------------------------ ----------

             OGG-R3_896B-OPEN_            160  <<<<<<

                 DATA_SOURCE

dw4d2s82txm97 direct path write
OGG-R3_896B-OPEN_            46

               DATA_SOURCE

https://gist.github.com/aryangoti/588cc507d222930a6f906683c8115f3b

The replication lag just keeps increasing and it just comes down once we stop the application.

Thanks,

Goti

On Fri, Apr 1, 2022 at 4:19 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> I was reading the pre-alloc time as time spent by the space management
> slaves allocating space from the tablespace to the LOB segment, not time
> spent extending the files in the tablespace. But that might be a
> simultaneous issue. I think the information you get from the ASH data
> might help identify that - the blocks might be the start of file blocks, or
> near the start of LOB segment blocks - or possibly near the start of newer
> extents in the LOB segment blocks.
>
> Regards
> Jonathan Lewis
>
>
> On Fri, 1 Apr 2022 at 02:29, Goti <aryan.goti_at_gmail.com> wrote:
>
>> Thanks Jonathan for the details.
>>
>> I will check ASH for further details. Additionally , I did try to add
>> more space to the respective LOB table space and we do have enough free
>> space now. But one thing I am not getting like why SMCO still need to watch
>> out for extending the space ?
>>
>> On Thu, 31 Mar 2022 at 10:31 PM, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>>
>>> It's not your version, of course, but I was looking at a similar issue
>>> on 19c a little while ago:
>>> https://jonathanlewis.wordpress.com/2022/01/10/control-file-waits/
>>>
>>> Some work of this type has to be done by some process - it's just a
>>> question of how visible it becomes to end-user run-time
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>> On Thu, 31 Mar 2022 at 12:35, Goti <aryan.goti_at_gmail.com> wrote:
>>>
>>>> Hi All,
>>>>
>>>> Envt :11.2.0.4 on RHEL 6.5
>>>>
>>>> We are observing slowness in OGG replication on the target side. The
>>>> replication itself is updating a table which has a secure file LOB. There
>>>> are a lot of updates performed during this time (Close to 25K in an hour).
>>>> The FG session waits for "direct path write" as it was created with the
>>>> NOCACHE attribute. However, the ASH shows that this is internally
>>>> triggering some space management slaves to do many single block reads.
>>>> There are no trace files also associated with the W00* processes.
>>>>
>>>> Please find the TKPROF report for the OGG replicat OS process.
>>>> https://gist.github.com/aryangoti/f49660a4bbb23c58a9f403ac9270eb7a
>>>>
>>>> Snapper for W0003 process.
>>>> https://gist.github.com/aryangoti/f2cf46ebcec3920d79f4fb719c01f309
>>>>
>>>> ASH details (5 minutes) when the replication was slow.
>>>> https://gist.github.com/aryangoti/6674691d7770b6eb667718589633aec5
>>>>
>>>> Please let me know how to troubleshoot this further.,
>>>>
>>>> Thanks,
>>>>
>>>> Goti
>>>>
>>> --
>> Thanks,
>>
>> Goti
>>
>

--
http://www.freelists.org/webpage/oracle-l




application/vnd.ms-excel attachment: ktsj.xls