Re: query hanged in parsing

I did some tracing and observed that is hanging is below step:

qkaOpnPlanSignature: opnp = 0x7fd4c336c118 seed = 41a2bc8d

qkaOpnHashCompute: hashing on: opntyp = 1 seed = 7f9f1e43

qkaOpnHashCompute: hashing on: opndty = 1 seed = 55651e66

qkaOpnHashCompute: hashing on: kccoid = 9 seed = 1d479011

qkaOpnHashCompute: hashing on: froaid = EBAL seed = 8769362b

qkaOpnPlanSignature: final seed = 8769362b

qkaPlanSignatureCB: opc:10, seed:2271819307

qkaPlanSignatureCB: +GRANULE_ITERATOR_QKNTYP(45) (nil) qkn_qksctxPlanSig seed = 586bc363

*qkaPlanSignatureCB: +SCAN_INDEX_QKNTYP(44) (nil) qkn_qksctxPlanSig seed = 586bc363 -.-> hangs for minutes sometimes *

What is particular about this :SCAN_INDEX_QKNTYP(44) is an index with an interval table with over 65K partitions.

Any hint on how I can overcome this problem will be highly appreciated. Or maybe how I can dig more into what that step qkaPlanSignatureCB is doing (tracing I did is SQL_Compile* and PART* disk high)

THank you.

În vin., 15 mar. 2024 la 22:49, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> a scris:

> Something to mention, eliminating the parallel hint query will parse and
> start execution in just few seconds, so something is going crazy due to
> parallel hint
>
> Playing around with optimizer_features_enable (until 9.2) didn't help..
>
> În vin., 15 mar. 2024 la 21:13, Laurentiu Oprea <
> laurentiu.oprea06_at_gmail.com> a scris:
>
>> thanks for feedback.
>>
>> When I say hanging indeed is basically bruning CPU.
>> 10053 for coordinator is telling me tha TIMER : SQL optimization overall
>> around 1minute, while in fact is spending about 25 minutes to finish
>> parsing and invite parallel slaves for execution . THen again one slaves is
>> taking same amount of time (is creating a new cursor ) and finally all
>> other slaves just soft parse this cursor created by first slave and
>> execution starts (sssuming it starts and not faill with ORA-04021
>>
>> So I assume in 10053 I need to trace something more maybe...no idea
>> hontely
>>
>> În vin., 15 mar. 2024 la 20:16, Jonathan Lewis <jlewisoracle_at_gmail.com>
>> a scris:
>>
>>> When you say "hanging" to you mean it's blocked and waiting, or is it
>>> burning CPU?
>>>
>>> Although you've used the "Outline Information into SQL Plan Profile"
>>> trick, SQL Plan Profiles (and SQL Plan Baselines) don't always manage to
>>> reproduce the plan (especially in the earlier releases). Do you have
>>> adaptive plans enabled, and have you tried running the 10053 trace? It's
>>> possible given the symptoms you've described that Oracle is spending a huge
>>> amount of time calculating inflections points for adaptive joins.
>>>
>>> Here's an example I wrote about a couple of years ago: Adaptive Joins |
>>> Oracle Scratchpad (wordpress.com)
>>> <https://jonathanlewis.wordpress.com/2022/04/13/adaptive-joins/>
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>> On Fri, 15 Mar 2024 at 17:49, Laurentiu Oprea <
>>> laurentiu.oprea06_at_gmail.com> wrote:
>>>
>>>> Thanks a lot Mark. Release is 12.1. Query is running on the same node
>>>> (parallel force local is true). I tried to block every decision of the
>>>> optimizer by creating a profile containing a full outline of a known good
>>>> plan. But parsing can either fail with ORA-04021 (one of the slaves raises
>>>> this error) or it completes and the query starts to run after all after
>>>> about 40 minutes.
>>>>
>>>> În vin., 15 mar. 2024 la 19:05, Mark W. Farnham <mwf_at_rsiz.com> a scris:
>>>>
>>>>> For this one precise release and patch information will be at least
>>>>> useful (if not essential).
>>>>>
>>>>>
>>>>>
>>>>> There have been several bugs serially killed where the plans generated
>>>>> for different parallel children didn’t match up. There was a failure mode
>>>>> where it just kept trying to come up with a matched plan until it timed out
>>>>> (which could be very long).
>>>>>
>>>>>
>>>>>
>>>>> Running with parallel forced to a single node may avoid some of those
>>>>> that were rooted in different node/instance specific plan differences.
>>>>>
>>>>>
>>>>>
>>>>> I thought that the last vampire version of this bug had been staked in
>>>>> the heart circa 2018, but I could be wrong.
>>>>>
>>>>>
>>>>>
>>>>> mwf
>>>>>
>>>>>
>>>>>
>>>>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>>>>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Laurentiu Oprea
>>>>> *Sent:* Friday, March 15, 2024 11:58 AM
>>>>> *To:* ORACLE-L (oracle-l_at_freelists.org)
>>>>> *Subject:* query hanged in parsing
>>>>>
>>>>>
>>>>>
>>>>> Dear all,
>>>>>
>>>>>
>>>>>
>>>>> Maybe you can give me an idea, I have a query running in parallel
>>>>> hanging in parsing phase for very long time, I did a flamegraph and seems
>>>>> like stack is looking like this (qesmaUpdatePlanSig is in top of flamegraph)
>>>>>
>>>>>
>>>>>
>>>>> qkaComputePlanSignature
>>>>>
>>>>> qkaTraverseQknDescendents
>>>>>
>>>>> qknApplyCallback
>>>>>
>>>>> qkaPlanSignatureCB
>>>>>
>>>>> qesmaUpdatePlanSig
>>>>>
>>>>>
>>>>>
>>>>> Any ideas what could be the reason for this??
>>>>>
>>>>>
>>>>>
>>>>> Appreciate your help.
>>>>>
>>>>

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