Re: query hanged in parsing

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