RE: query hanged in parsing
Date: Thu, 16 May 2024 07:01:16 -0400
Message-ID: <16b501daa780$604b6fe0$20e24fa0$_at_rsiz.com>
What do you mean by “orphan entries in indexes?”
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Laurentiu Oprea
Sent: Thursday, May 16, 2024 2:44 AM
Cc: Oracle L
Subject: Re: query hanged in parsing
For info if someone hit a similar problem, it seems like the cause for parsing are the orphan entries in indexes...which also explain why avoiding indexes in the execution plan mitigated the problem. And the problem seems to replicate for both 12 and 19C versions.
În lun., 13 mai 2024 la 08:36, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> a scris:
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 <https://twitter.com/MohamedHouri> Twitter - MohamedHouri <https://twitter.com/MohamedHouri> -- http://www.freelists.org/webpage/oracle-lReceived on Thu May 16 2024 - 13:01:16 CEST