Re: query hanged in parsing
Date: Sun, 12 May 2024 16:05:52 +0200
Message-ID: <CAJu8R6iFdMkbY8VbhrTLnq-fvgkeChurDqHTbw8CHxeSzW9oOw_at_mail.gmail.com>
Hi
As already mentioned by Timur, what is the DOP (Degree Of Parallelism) of
this query?
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
I doubt that this will help because it will cancel (behind the scenes) many
Oracle transformations that appear after 12.1.
Best regards
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
insert /*+ opt_param('_optimizer_squ_bottomup' 'false') */
into t_demo
select * from t;
Mohamed Houri
> 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
>>>>>>> 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-lReceived on Sun May 12 2024 - 16:05:52 CEST