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

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

Hi Gotti,

You haven't sorted the tkprof output by exeela and you didn't use explain=yes argument, so you don't have execution plans. You may want to use orasrp instead of tkprof.

http://oracledba.ru/orasrp/

Orasrp is by far the best plug-in replacement for tkprof that I have ever seen. I hope that Egor Starostin is safe and sound in these testing times.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com