Re: Query Performance issue

From: Lok P <>
Date: Thu, 24 Dec 2020 15:55:25 +0530
Message-ID: <>

Attached is the sql monitor with setting these session level parameters. I killed it after 15minutes.

Yes, i see the estimation is accurate in all the steps except the final one i.e. plan_line_id-1 i.e. VIEW, do you mean to say by some way we can rewrite the query(may be using UNION etc) without hampering the result set/functionality Or say there is other transformation/path possible which optimizer is not doing here , to come up with a better possible path for the same query?
Is it possible, to hint it someway(using cardinality hint) to let the optimizer know there are actually going to be ~2rows in the final result set, and then see what path the optimizer is taking?

On Thu, Dec 24, 2020 at 3:14 PM Laurentiu Oprea <> wrote:

> From my perspective there are multiple suspicious things
> First is the estimated number of returned rows. The optimizer is very
> wrong on this. Based on the fact that your query returns 1 or 2 rows, I
> think that OR conditions should be transformed in concatenation and
> predicates pushed at table level as filters . It doesn't make sense to scan
> everything and filter at the end .
> Out of curiosity can you run a serial execution with these at session
> level:
> alter session set "_serial_direct_read"=ALWAYS ;
> alter session set "cell_offload_processing"=TRUE;
> alter session set "_kcfis_cell_passthru_enabled"=FALSE;
> alter session set "_kcfis_storageidx_disabled"=FALSE;
> alter session set "cell_offload_plan_display"=ALWAYS';
> I`ll not set parallel based on the fact that you mentioned this query
> might be executed by 50 concurrent sessions , you might kill your box.
> (of course I`m miles away from experience perspective compared with people
> who answered you, I might be very wrong... so threat everything I say with
> care :) )
> În joi, 24 dec. 2020 la 09:17, Lok P <> a scris:
>> Thank You Jonathan.
>> I see the projected columns in the inline view are around ~40 to 50 but
>> are all referred to in the outer query so they are needed.
>> As we see the comparison of parallel path vs original query sql monitor(
>> as attached) for the same set of binds, I do see the execution finished in
>> <~15 seconds incase of parallel(4) VS ~15minutes for the original query.
>> The parallel path does take the HASH HASH distribution for the JOINS, and
>> the memory has been increased from ~1-2MB to ~9MB for the HASH join
>> operations, but i am seeing the temp spill has been on the higher side in
>> case of the parallel execution path i.e. ~578MB VS ~560MB in original
>> query. I was expecting it to be lesser in case of parallel execution. So is
>> the execution finished fast , just because the more number of threads
>> helped in reading the HASH table data from disk faster? And there is no
>> benefit with respect to overall temp spill, or the sql monitor is really
>> hiding something/logging something wrong in case of parallel execution sql
>> monitor report?
>> As it rightly happened , it was running in a few seconds in the past but
>> started running longer suddenly. And i suspect this might be because of
>> some slight organic increase in data volume in the base tables caused to
>> break that max session level memory limit oracle allocated to hold the HASH
>> table and thus spilled to temp causing this multifold run duration, so we
>> were kind of running on edge since a few days may be.
>> So Is it correct to think that if the data volume will increase in the
>> base table further a bit(due to normal organic growth) , the parallel
>> path(/*+parallel(4)*/) is also going to slow down after some days? What
>> should be the long term approach to cater this query if we want it to
>> finish in the same time around <15-20 seconds duration?
>> On Thu, Dec 24, 2020 at 1:41 AM Jonathan Lewis <>
>> wrote:
>>> Forcing it parallel might be sufficient - I haven't checked to see how
>>> well a Full outer join would behave in parallel, I imagine it would to a
>>> hash/hash distribution, which would tend to be even shares with the effect
>>> of a much larger total memory allocation for the same amount of data.
>>> Another thing to check is that you don't specify any columns in the
>>> inline view that aren't needed - the optimizer might be unable to project
>>> them out until it's carried them through the join.
>>> The drawback to usnig parallelism as a workaround is that you might
>>> starve other processes of CPU, or get unstable response time if too many
>>> queries try to go parallel at the same time.
>>> If you're trying to get parallelism through an SQL profile/baseline or
>>> SQL Patch you might have to use the /*+ shared() */ hint rather than the
>>> /*+ parallel() */ hint.
>>> Regards
>>> Jonathan Lewis
>>> On Wed, 23 Dec 2020 at 19:53, Lok P <> wrote:
>>>> When you said "* but it's possible that the only option is to ensure
>>>> that you can get enough memory to avoid the spill to disc*. ", are you
>>>> pointing towards setting manual work area size and hash_area=2GB in session
>>>> level? or some other way?
>>>> I was thinking if driving the query in parallel -4 is a sensible way.
>>>> What's your thought on this?
>>>> On Thu, Dec 24, 2020 at 1:03 AM Jonathan Lewis <>
>>>> wrote:
>>>>> Almost certainly correct.
>>>>> A (native) full outer join can't use in index "into" a table because
>>>>> the full outer join needs to be an outer join in both directions.
>>>>> You could try disabling the full outer join - as a test you could
>>>>> 'alter session set "_optimizer_native_full_outer_join" = off' to see if
>>>>> that makes enough of a difference to performance; but it's possible that
>>>>> the only option is to ensure that you can get enough memory to avoid the
>>>>> spill to disc. (In your version of Oracle/Exadata a hash join spill to
>>>>> disc can be a huge fraction of the total run-time because the I/O can't
>>>>> "cheat".)
>>>>> Regards
>>>>> Jonathan Lewis
>>>>> On Wed, 23 Dec 2020 at 18:22, Lok P <> wrote:
>>>>>> Table COX has PK on column (EID,BID,OID,ETYP) and table CS has index
>>>>>> on column (EID) and table BOS has index on (BID, OID). But none of the
>>>>>> index is getting used in the query path, is it because the FULL OUTER JOIN
>>>>>> cant use index+ nested loop path here. Is this understanding correct?


Received on Thu Dec 24 2020 - 11:25:25 CET

Original text of this message