Re: query hanged in parsing

If it’s hanging for a long time, can you share a few typical stack traces from oradebug short_stack while parsing hangs from QC and PX slaves.

What is the value of degree of parallelism?

Regards
Timur Akhmadeev

On Sat, 11 May 2024 at 18:08, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> 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