Re: Locking issue

Lok P,

The 19.10 RU backports a parameter optimizer_real_time_statistics according to this note (
https://blogs.oracle.com/optimizer/real-time-statistics-parameter ) by Nigel Bayliss, with the default value of FALSE so on your next upgrade it seems likely that the feature will disable itself automatically - which suggests it would be safe to disable it manually.

I don't have access to a 19.9, so I assume that it is currently a hidden parameter but maybe it's more subtle than that. There's also the problem that your query is reporting the use of real-time statisics, but the collection of the statistics is from DML, so maybe you have to find a way to disable the entire realtime statistics feature to bypass your dictionary cache activity.

Regards
Jonathan Lewis

On Thu, 17 Jun 2021 at 06:10, Lok P <loknath.73_at_gmail.com> wrote:

> 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