Re: Query Performance issue

From: Laurentiu Oprea <>
Date: Thu, 24 Dec 2020 11:44:09 +0200
Message-ID: <>

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 - 10:44:09 CET

Original text of this message