Re: query hanged in parsing

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