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 <loknath.73@xxxxxxxxx> 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 <jlewisoracle@xxxxxxxxx>
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 <loknath.73@xxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxx> 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?
