Re: query hanged in parsing

THanks to all, your feedback is highly appreciated.

I think this function where parsing is hanged is responsible for computing plan signatures which are then compared between coordinator and slaves to agree on joining the execution.

I finally observed that the problem is for local partitioned indexes on tables with a very large number of partitions. Issue seems to have been introduced by Jul patch for 12.1 Same behavior don`t seem to be replicated on 19C.

THe workaround was to push hints using profiles to full scan the tables + px_join_filter ... Although tables are huge, it seems like exa is handling them in decent time.

În dum., 12 mai 2024 la 17:06, Mohamed Houri <mohamed.houri_at_gmail.com> a scris:

> Hi
>
> As already mentioned by Timur, what is the DOP (Degree Of Parallelism) of
> this query?
>
>
> - Are you using bind variables?
> - Is it an insert/select or a plain select
> - Are you using a view in your SELECT part
>
> Since you are using 12.1 I remember back in 2015 that I have suffered a
> parsing issue with a parallel insert/select statement. However, my main
> issue then was that parallel slaves were refusing to share the execution
> plan optimized by the QC (Query Coordinator) which then triggered a bunch
> of Library cache locks and Cursor Pin S Wait on X. This looks not to be
> your case. By that time and at that Oracle release (12.1) my workaround was
> to reduce the DOP of the insert/select and to use Literal instead of bind
> variables. In and other similar case (Cursor Pin S wait on X during a
> parallel insert/select, we enabled parallel dml to help reducing the
> parsing effect
>
> Since you look to be facing a purely parsing issue (burning CPU before the
> execution plan is generated), could you try the following (adapt to your
> query)
>
> explain plan for
> insert /*+ opt_param('_optimizer_squ_bottomup' 'false') */
> into t_demo
> select * from t;
>
> I doubt that this will help because it will cancel (behind the scenes)
> many Oracle transformations that appear after 12.1.
>
> Best regards
> Mohamed Houri
>
>
>
> Le dim. 12 mai 2024 à 13:05, Timur Akhmadeev <timur.akhmadeev_at_gmail.com>
> a écrit :
>
>> 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.
>>>>>>>>
>>>>>>>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

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