Re: Query Performance issue
- From: Lok P <loknath.73@xxxxxxxxx>
- To: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
- Date: Thu, 24 Dec 2020 12:45:42 +0530
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?
*************** Original Query sql monitor***********************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL ID : 0a4xhjbhdfjbk
SQL Execution ID : 16777216
Execution Started : 12/24/2020 00:49:28
First Refresh Time : 12/24/2020 00:49:32
Last Refresh Time : 12/24/2020 01:03:37
Duration : 849s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
=====================================================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
Reqs | Bytes | Offload |
=====================================================================================================
| 926 | 196 | 730 | 0.22 | 2 | 81196 | 1M | 155GB |
7111 | 833MB | -1.01% |
=====================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2475526405)
==============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost |
Time | Start | Execs | Rows | Read | Read | Write | Write | Mem |
Temp | Activity | Activity Detail |
| | | | (Estim) | |
Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) |
(Max) | (%) | (# samples) |
==============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | |
846 | +4 | 1 | 1 | | | | | |
| | |
| 1 | VIEW | VW_FOJ_0 | 2M | 43492 |
846 | +4 | 1 | 1 | | | | | |
| 0.12 | Cpu (1) |
| 2 | HASH JOIN FULL OUTER | | 2M | 43492 |
849 | +1 | 1 | 2M | 1M | 147GB | 2943 | 345MB | 1M |
560M | 94.45 | Cpu (102) |
| | | | | |
| | | | | | | | |
| | direct path read temp (698) |
| 3 | TABLE ACCESS STORAGE FULL | BOS | 1M | 3274 |
1 | +4 | 1 | 1M | | | | | |
| | |
| 4 | VIEW | | 2M | 16676 |
45 | +4 | 1 | 2M | | | | | |
| | |
| 5 | VIEW | VW_FOJ_1 | 2M | 16676 |
45 | +4 | 1 | 2M | | | | | |
| | |
| 6 | HASH JOIN FULL OUTER | | 2M | 16676 |
47 | +2 | 1 | 2M | 66255 | 8GB | | | 2M |
373M | 5.43 | Cpu (6) |
| | | | | |
| | | | | | | | |
| | direct path read temp (39) |
| | | | | |
| | | | | | | | |
| | direct path write temp (1) |
| 7 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683 |
1 | +4 | 1 | 948K | | | | | |
| | |
| 8 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 |
1 | +4 | 1 | 2M | | | | | |
| | |
==============================================================================================================================================================================================================
*************** Original Query with PARALLEL(4) hints sql
monitor***********************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777533
Execution Started : 12/23/2020 22:15:10
First Refresh Time : 12/23/2020 22:15:10
Last Refresh Time : 12/23/2020 22:15:23
Duration : 13s
Fetch Calls : 2
Global Stats
======================================================================================================
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read |
Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
Reqs | Bytes | Offload |
======================================================================================================
| 53 | 20 | 32 | 0.32 | 2 | 82861 | 54778 | 7GB |
7335 | 860MB | -21.95% |
======================================================================================================
Parallel Execution Details (DOP=4 , Servers Allocated=8)
====================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Cluster |
Buffer | Read | Read | Write | Write | Cell | Wait Events
|
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) |
Gets | Reqs | Bytes | Reqs | Bytes | Offload | (sample #)
|
====================================================================================================================================================================
| PX Coordinator | QC | | 0.02 | 0.02 | | |
15 | | . | | . | NaN% |
|
| p000 | Set 1 | 1 | 8.48 | 3.06 | 5.33 | 0.08 |
12945 | 9176 | 1GB | 1047 | 123MB | -19.05% | direct path read temp (6)
|
| p001 | Set 1 | 2 | 8.71 | 3.11 | 5.52 | 0.08 |
13975 | 9310 | 1GB | 1067 | 125MB | -20.48% | direct path read temp (5)
|
| p002 | Set 1 | 3 | 8.39 | 3.05 | 5.25 | 0.08 |
13302 | 9155 | 1GB | 1060 | 124MB | -20.48% | direct path read temp (5)
|
| p003 | Set 1 | 4 | 8.76 | 3.26 | 5.42 | 0.08 |
13107 | 9440 | 1GB | 1223 | 143MB | -21.95% | direct path read temp (7)
|
| p004 | Set 2 | 1 | 4.64 | 1.97 | 2.68 | |
7589 | 4422 | 567MB | 735 | 86MB | -26.58% | cell multiblock physical read
(1) |
| | | | | | | |
| | | | | | direct path read temp (3)
|
| p005 | Set 2 | 2 | 4.69 | 1.95 | 2.74 | |
7206 | 4425 | 564MB | 734 | 86MB | -26.58% | direct path read temp (3)
|
| p006 | Set 2 | 3 | 4.60 | 1.95 | 2.65 | |
7177 | 4417 | 563MB | 734 | 86MB | -26.58% | cell multiblock physical read
(1) |
| | | | | | | |
| | | | | | direct path read temp (2)
|
| p007 | Set 2 | 4 | 4.66 | 1.97 | 2.69 | |
7545 | 4433 | 566MB | 735 | 86MB | -26.58% | direct path read temp (2)
|
====================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3711507738)
==========================================================================================================================================================================================================================
| Id | Operation | Name | Rows |
Cost | Time | Start | Execs | Rows | Read | Read | Write | Write |
Mem | Temp | Activity | Activity Detail |
| | | | (Estim) |
| Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes |
(Max) | (Max) | (%) | (# samples) |
==========================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | |
| 1 | +13 | 9 | 1 | | | | |
| | | |
| 1 | PX COORDINATOR | | |
| 1 | +13 | 9 | 1 | | | | |
| | | |
| 2 | PX SEND QC (RANDOM) | :TQ10004 | 2M |
2547 | 3 | +11 | 4 | 1 | | | | |
| | | |
| 3 | VIEW | VW_FOJ_0 | 2M |
2547 | 8 | +6 | 4 | 1 | | | | |
| | | |
| 4 | HASH JOIN FULL OUTER BUFFERED | | 2M |
2547 | 13 | +1 | 4 | 2M | 36772 | 4GB | 4397 | 515MB |
9M | 578M | 63.27 | Cpu (8) |
| | | | |
| | | | | | | | |
| | | direct path read temp (23) |
| 5 | PX RECEIVE | | 1M |
908 | 2 | +1 | 4 | 1M | | | | |
| | 2.04 | Cpu (1) |
| 6 | PX SEND HASH | :TQ10002 | 1M |
908 | 2 | +1 | 4 | 1M | | | | |
| | 2.04 | Cpu (1) |
| 7 | PX BLOCK ITERATOR | | 1M |
908 | 1 | +2 | 4 | 1M | | | | |
| | | |
| 8 | TABLE ACCESS STORAGE FULL | BOS | 1M |
908 | 2 | +1 | 50 | 1M | 337 | 226MB | | |
| | 6.12 | Cpu (1) |
| | | | |
| | | | | | | | |
| | | cell multiblock physical read (2) |
| 9 | PX RECEIVE | | 2M |
1637 | 5 | +2 | 4 | 2M | | | | |
| | 2.04 | Cpu (1) |
| 10 | PX SEND HASH | :TQ10003 | 2M |
1637 | 4 | +2 | 4 | 2M | | | | |
| | | |
| 11 | VIEW | | 2M |
1637 | 4 | +2 | 4 | 2M | | | | |
| | | |
| 12 | VIEW | VW_FOJ_1 | 2M |
1637 | 4 | +2 | 4 | 2M | | | | |
| | | |
| 13 | HASH JOIN FULL OUTER BUFFERED | | 2M |
1637 | 4 | +2 | 4 | 2M | 17360 | 2GB | 2938 | 344MB |
9M | 386M | 24.49 | Cpu (2) |
| | | | |
| | | | | | | | |
| | | direct path read temp (10) |
| 14 | PX RECEIVE | | 948K |
744 | 1 | +2 | 4 | 948K | | | | |
| | | |
| 15 | PX SEND HASH | :TQ10000 | 948K |
744 | 1 | +2 | 4 | 948K | | | | |
| | | |
| 16 | PX BLOCK ITERATOR | | 948K |
744 | 1 | +2 | 4 | 948K | | | | |
| | | |
| 17 | TABLE ACCESS STORAGE FULL | CS | 948K |
744 | 1 | +2 | 54 | 948K | | | | |
| | | |
| 18 | PX RECEIVE | | 2M |
891 | 1 | +2 | 4 | 2M | | | | |
| | | |
| 19 | PX SEND HASH | :TQ10001 | 2M |
891 | 1 | +2 | 4 | 2M | | | | |
| | | |
| 20 | PX BLOCK ITERATOR | | 2M |
891 | 1 | +2 | 4 | 2M | | | | |
| | | |
| 21 | TABLE ACCESS STORAGE FULL | COX | 2M |
891 | 1 | +2 | 49 | 2M | 309 | 222MB | | |
| | | |
==========================================================================================================================================================================================================================
Other related posts:
