Re: Locking issue

Actually I am not sure if we can relate our issue to exactly the same one which you pointed above. Our Db version is 19.9.0.0.0. In our case it is a simple SELECT query ( SELECT /*+ FULL(P) +*/ * FROM P ) doing FTS on a table (holding 700million rows) from multiple sessions(20+ sessions) and were experiencing DC lock on 'dc_realtime_colst/tabst' and all the other new session accessing same object 'P' were gets locked in parse stage(cursor pin s wait on X) there after creating a big storm of concurrency. Support is pointing to the below bug and suggesting the patch to install to fix this bug.

32042352
<https://support.oracle.com/epmos/faces/PatchDetail?requestId=24138539&_afrLoop=252185249564103&patchId=32042352&_afrWindowMode=0&_adf.ctrl-state=168q4vx617_93#> HIGH
ROW CACHE MUTEX WAITS ON 19.8 WITH HIGH CONCURRENCY <https://support.oracle.com/epmos/faces/PatchDetail?requestId=24138539&_afrLoop=252185249564103&patchId=32042352&_afrWindowMode=0&_adf.ctrl-state=168q4vx617_93#>

I had one related question, as this all seems to be related to 'dynamic statistics/stats on conventional DML option' as that is visible in the note section in the SELECT query plan. So is it advisable to switch that off for this sql/table someway without negatively impacting it anything?

On Wed, Jun 9, 2021 at 9:21 PM Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> Out of interest, but off on a tangent from original thread, I am also
> hitting what seems to be some oddities around this dc_realtime_colst.
>
>
>
> Trying to drop a 230GB partitioned table it’s taking hours with notable
> waits on DLM cross inst call completion and notable row cache lock waits on
> dc_realtime_colst (and dc_histogram_defs).
>
> For my issue, investigation is pointing towards two notes which point at
> same bug:
>
> *Drop User Cascade Command Hang On "DLM cross inst call completion" (Doc
> ID 2671064.1)*
>
> *Bug 31208287 - Deleting Enqueues For A Large Partitioned Table Hangs In
> KJCI_WAIT/KJCI_PROCESSCRQ (Doc ID 31208287.8)*
>
>
>
> WORKAROUND:
>
> Drop in smaller portions. For instance first remove stats/histograms from
> the
>
> columns.
>
>
>
>
>
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
> *From: *Noveljic Nenad <nenad.noveljic_at_vontobel.com>
> *Sent: *28 May 2021 08:57
> *To: *Lok P <loknath.73_at_gmail.com>
> *Cc: *ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *Subject: *RE: Locking issue
>
>
>
> Yes, this bug should be fixed in 19.9 in the base release. I can tell from
> my installation that it’s definitely fixed in 19.9.0.0.201020.
>
>
>
> You can verify it for your patch level:
>
>
>
> $ORACLE_HOME/OPatch/opatch lsinventory | grep 31414023
>
>
>
> We still don’t know the reason for row cache lock id=62/63 allocations.
> Unfortunately, the 10222 trace doesn’t spool the call stacks for cid=62/63,
> only for cid=16/8.
>
>
>
> We’d need to capture the call stacks for 62 or 63 to find out more.
>
>
>
> If you want to go down that rabbit hole, I can prepare a DTrace script if
> you’re on Solaris or gdb if you’re on Linux. The later would be better to
> run on a test system if you’re unexperienced with gdb.
>
>
>
> Let me know.
>
>
>
> Best regards,
>
>
>
> Nenad
>
>
>
>
>
> *From:* Lok P <loknath.73_at_gmail.com>
> *Sent:* Freitag, 28. Mai 2021 06:05
> *To:* Noveljic Nenad <nenad.noveljic_at_vontobel.com>
> *Cc:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
> *Subject:* Re: Locking issue
>
>
>
> Thank you Nenad. We have oracle version 19.9.0.0. Need to check patch
> details. But it seems from the bug details , this bug should have been
> fixed by this version. correct me if wrong.
>
>
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
>
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> Without prejudice to any contractual agreements between you and us which
> shall prevail in any case, we take it as your authorization to correspond
> with you by e-mail if you send us messages by e-mail. However, we reserve
> the right not to execute orders and instructions transmitted by e-mail at
> any time and without further explanation.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of
> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
> referred to as "Vontobel Group") for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version.
> Please note that all e-mail communications to and from the Vontobel Group
> are subject to electronic storage and review by Vontobel Group. Unless
> stated to the contrary and without prejudice to any contractual agreements
> between you and Vontobel Group which shall prevail in any case,
> e-mail-communication is for informational purposes only and is not intended
> as an offer or solicitation for the purchase or sale of any financial
> instrument or as an official confirmation of any transaction.
> The legal basis for the processing of your personal data is the legitimate
> interest to develop a commercial relationship with you, as well as your
> consent to forward you commercial communications. You can exercise, at any
> time and under the terms established under current regulation, your rights.
> If you prefer not to receive any further communications, please contact
> your client relationship manager if you are a client of Vontobel Group or
> notify the sender. Please note for an exact reference to the affected group
> entity the corporate e-mail signature. For further information about data
> privacy at Vontobel Group please consult www.vontobel.com
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.vontobel.com%2F&data=04%7C01%7C%7C189508006f924ce0cea708d921ae308b%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637577854510426506%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=ZE7KqZKMzLkdVtuhs0YEeLXiR03X9JErkDnKtaK0XNA%3D&reserved=0>
> .
>
>
>

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