Re: Query Performance issue

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 <loknath.73_at_gmail.com> 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 <jlewisoracle_at_gmail.com>
> 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 <loknath.73_at_gmail.com> 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?
>>>
>>>
>>>

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