Re: Query Performance issue
- From: Lok P <loknath.73@xxxxxxxxx>
- To: Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
- Date: Sun, 27 Dec 2020 23:48:24 +0530
I tried running the queries individually as you suggested. And also tried
by doing UNION ALL within those two, but it was giving 5 rows(instead of
~3rows) , so then tried replacing UNION ALL with UNION and I saw the result
set matched. I had attached the sql monitor for the individual queries
along with the UNION one. Yet to see if this UNION is going to give us
exactly the same result as the original query every time without removing
any legitimate rows from the resultset.
But ,It looks like the UNION does come fast(within ~45 seconds) as
compared to the original query. Again, the major amount of time seems to be
consumed in the HASH JOIN of table COX and CS step itself, because of temp
spill, and one other HASh JOIN with BOS is eliminated in this UNION path.
Is this the recommended approach, as Oracle is not doing such OR expansion
on its own? But again, as here too the same HASH JOIN FULl OUTER between
COX and CS appears taking a major amount of time, and again it is going to
be a bottleneck in future with some more rows added to these tables. So it
seems not sustainable. Please correct me if I am wrong.
On Sun, Dec 27, 2020 at 9:00 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
Can you try to do a test run with 2 separate queries(scope is to split the
or condition into 2 queries then concatenate them later using union all) :
1. SELECT ....
WHERE "A1"."BI0" = :b1
2.SELECT ....
WHERE
"A1"."COl1" = :b2
AND "A1"."I_DT" IS NULL
AND (
"A1"."BI0" IS NOT NULL
AND "A1"."CT1" = 'XXX'
OR "A1"."BI0" IS NULL
AND "A1"."CT1" = 'YYY')
În dum., 27 dec. 2020 la 14:48, Lok P <loknath.73@xxxxxxxxx> a scris:
Thank you. The gv$sql_workarea does show the reduction of tempspace
usage for parallel-4 execution ,which means the SQL monitor was not giving
correct info for the parallel execution path temp usage.
And it looks odd that full outer join of two table with less than
2million rows taking so much time because of temp spill.
Is there any possible design changes which we can work on to achieve the
same business logic done in 15seconds, which is more sustainable wrt future
data volume growth.
On Sat, 26 Dec 2020, 11:09 pm Pap, <oracle.developer35@xxxxxxxxx> wrote:
Yes probably that's true, as Jonathan mentioned the only choice here
would be to avail more memory to have the hash table in memory as much as
possible so that temp read would be minimized.
Additionally the overall organic growth in data volume seems low but the
amount/number of columns which gets exposed/projected out of the inline
view(which is ~40-50 columns) may be causing the memory consumption growing
so fast making the query suffer because of temp spill.
For the parallel hint sql monitor may be really hiding the exact
consumption, so the real figures should be logged in gv$sql_workarea which
can be searched for the sql_id.
On Fri, Dec 25, 2020 at 2:22 PM Lok P <loknath.73@xxxxxxxxx> wrote:
Is it correct to say that the full outer join logic implementation just
can't be rewritten to make it any better path. And then it looks like usage
of full outer join is riskier.
So the only way would be make available required amount of memory so
that temp/disk read won't impact the run time. Or else ensure same amount
of rows to persist on the base table all the time and not let them grow
overtime , so that the hash join will happen fully in memory?
On Thu, 24 Dec 2020, 9:14 pm Lok P, <loknath.73@xxxxxxxxx> wrote:
Attached is the sql monitor with Right Outer join path. And I do see
the estimation of plan_line_id- 3 is deviating by a large number.
But the issue , i see is the number of rows does match with the
original one, but i see the values getting mismatched, some of the columns
are coming as null in the modified query whereas those are having not null
values in the original query result for those three rows. And also the
total execution time is close to ~5minutes .
On Thu, Dec 24, 2020 at 8:24 PM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Also you need to help CBO with predicates like this:
SELECT ....~58 columns projected...
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
RIGHT OUTER JOIN
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
RIGHT OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID"
-- added a copy of the predicates:
where
"A5"."BI0" = :b1
OR "A5"."BI0" IS NOT NULL AND "A5"."CT1" =
'XXX'
OR "A5"."BI0" IS NULL AND "A5"."CT1" =
'YYY'
-- end
) "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2"))
"A1"
WHERE "COX"."BI0" = :b1
OR "BOS"."COl1" = :b2
AND "BOS"."I_DT" IS NULL
AND ( "COX"."BI0" IS NOT NULL
AND "COX"."CT1" = 'XXX'
OR "COX"."BI0" IS NULL AND "COX"."CT1" = 'YYY')
On Thu, Dec 24, 2020 at 5:46 PM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Ok, I see the problem, you just need to replace both left joins to
'right join', because as I said previously, all rows of the final
resultset
should contain rows from COX.
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org
чт, 24 дек. 2020 г., 17:43 Lok P <loknath.73@xxxxxxxxx>:
I have just simply , replaced the FULL OUTER join with LEFT OUTER
Join , something as below(with actual aliases) and ran it.
I am sensing like, i did something wrong , and not the way which
you thought of perhaps. Can you guide me here please, how you want me
to
test it.
SELECT ....~58 columns projected...
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
*LEFT OUTER JOIN*
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
*LEFT OUTER JOIN* "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID") "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2"))
"A1"
WHERE "COX"."BI0" = :b1
OR "BOS"."COl1" = :b2
AND "BOS"."I_DT" IS NULL
AND ( "COX"."BI0" IS NOT NULL
AND "COX"."CT1" = 'XXX'
OR "COX"."BI0" IS NULL AND "COX"."CT1" = 'YYY')
On Thu, Dec 24, 2020 at 8:03 PM Sayan Malakshinov <
xt.and.r@xxxxxxxxx> wrote:
Can you show both original and modified queries?
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org
*********** First part of UNION query***************
SELECT ....~58 columns projected...
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
FULL OUTER JOIN
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
FULL OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID") "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1"
WHERE "A1"."BI0" = :b1
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 12/27/2020 12:37:03
First Refresh Time : 12/27/2020 12:37:03
Last Refresh Time : 12/27/2020 12:37:24
Duration : 21s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
===========================================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read | Write | Write
| Cell |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes
| Offload |
===========================================================================================
| 23 | 7.12 | 16 | 2 | 52223 | 33366 | 4GB | 2843 | 333MB
| -16.28% |
===========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=1688253567)
================================================================================================================================================================================================================
| 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 | | |
| 15 | +3 | 1 | 3 | | | | |
| | | |
| 1 | VIEW | VW_FOJ_0 | 2M | 16685
| 15 | +3 | 1 | 3 | | | | |
| | | |
| 2 | HASH JOIN RIGHT OUTER | | 2M | 16685
| 16 | +2 | 1 | 3 | | | | | 447K
| | | |
| 3 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 4
| 1 | +2 | 1 | 3 | | | | |
| | | |
| 4 | INDEX RANGE SCAN | BOS_PK | 1 | 3
| 1 | +2 | 1 | 3 | | | | |
| | | |
| 5 | VIEW | | 2M | 16678
| 15 | +3 | 1 | 3 | | | | |
| | | |
| 6 | VIEW | VW_FOJ_1 | 2M | 16678
| 20 | +2 | 1 | 3 | | | | |
| | | |
| 7 | HASH JOIN RIGHT OUTER | | 2M | 16678
| 22 | +0 | 1 | 2M | 33366 | 4GB | 2843 | 333MB | 2M
| 373M | 100.00 | Cpu (3) |
| | | | |
| | | | | | | | |
| | | direct path read temp (18) |
| 8 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683
| 1 | +2 | 1 | 948K | | | | |
| | | |
| 9 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213
| 1 | +2 | 1 | 2M | | | | |
| | | |
================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A2"."BI"(+)="A3"."BID1" AND
"A2"."OID"(+)=TO_NUMBER("A3"."OID2"))
4 - access("A2"."BI"(+)=TO_NUMBER(:B1))
6 - filter("A5"."BI"=TO_NUMBER(:B1))
7 - access("A5"."EID"="A4"."EID"(+))
**************** Second part of UNION query*******************
SELECT ....~58 columns projected...
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
FULL OUTER JOIN
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
FULL OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID") "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1"
WHERE "A1"."COl1" = :b2
AND "A1"."I_DT" IS NULL
AND ( "A1"."BI0" IS NOT NULL
AND "A1"."CT1" = 'XXX'
OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 12/27/2020 12:38:56
First Refresh Time : 12/27/2020 12:38:56
Last Refresh Time : 12/27/2020 12:39:37
Duration : 41s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
=========================================================================================================
| Elapsed | Cpu | IO | Concurrency | Fetch | Buffer | Read | Read |
Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
Reqs | Bytes | Offload |
=========================================================================================================
| 45 | 13 | 32 | 0.00 | 2 | 52223 | 66713 | 8GB |
2843 | 333MB | -8.70% |
=========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=926058519)
===============================================================================================================================================================================================================
| 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 | | | |
36 | +6 | 1 | 2 | | | | | |
| | |
| 1 | FILTER | | | |
36 | +6 | 1 | 2 | | | | | |
| | |
| 2 | HASH JOIN OUTER | | 1 | 16685 |
40 | +2 | 1 | 3 | | | | | 429K |
| | |
| 3 | TABLE ACCESS BY INDEX ROWID | BOS | 1 | 4 |
1 | +2 | 1 | 3 | | | | | |
| | |
| 4 | INDEX RANGE SCAN | BOS_PK | 1 | 3 |
1 | +2 | 1 | 3 | | | | | |
| | |
| 5 | VIEW | VW_FOJ_1 | 2M | 16678 |
40 | +2 | 1 | 3 | | | | | |
| | |
| 6 | HASH JOIN FULL OUTER | | 2M | 16678 |
41 | +1 | 1 | 2M | 66713 | 8GB | 2843 | 333MB | 2M |
373M | 100.00 | Cpu (11) |
| | | | | |
| | | | | | | | |
| | direct path read temp (29) |
| | | | | |
| | | | | | | | |
| | direct path write temp (1) |
| 7 | TABLE ACCESS STORAGE FULL | CS | 948K | 2683 |
1 | +2 | 1 | 948K | | | | | |
| | |
| 8 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 |
1 | +2 | 1 | 2M | | | | | |
| | |
===============================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A5"."CT1"='XXX' AND "A5"."BI" IS NOT NULL OR "A5"."BI" IS
NULL AND "A5"."CT1"='YYY')
2 - access("A2"."BI"="A5"."BI"(+) AND "A2"."OID"=TO_NUMBER("A5"."OID"(+)))
3 - filter("A2"."I_DT" IS NULL)
4 - access("A2"."BI"=TO_NUMBER(:B2))
5 - filter("A5"."BI"(+)=TO_NUMBER(:B2))
6 - access("A5"."EID"="A4"."EID")
*********** With UNION ******************
SELECT ....~58 columns projected...
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
FULL OUTER JOIN
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
FULL OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID") "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1"
WHERE "A1"."BI0" = :b1
UNION
SELECT ....~58 columns projected...
FROM (SELECT ....~60 columns projected
FROM "USER1"."BOS" "A2"
FULL OUTER JOIN
(SELECT ...~41 columns projected from A4 and A5
FROM "USER1"."CS" "A4"
FULL OUTER JOIN "USER1"."COX" "A5"
ON "A5"."EID" = "A4"."EID") "A3"
ON "A2"."BI" = "A3"."BID1"
AND "A2"."OID" = TO_NUMBER ("A3"."OID2")) "A1"
WHERE "A1"."COl1" = :b2
AND "A1"."I_DT" IS NULL
AND ( "A1"."BI0" IS NOT NULL
AND "A1"."CT1" = 'XXX'
OR "A1"."BI0" IS NULL AND "A1"."CT1" = 'YYY')
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 12/27/2020 12:21:08
First Refresh Time : 12/27/2020 12:21:12
Last Refresh Time : 12/27/2020 12:21:50
Duration : 42s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 2
Global Stats
===========================================================================================
| Elapsed | Cpu | IO | Fetch | Buffer | Read | Read | Write | Write
| Cell |
| Time(s) | Time(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes
| Offload |
===========================================================================================
| 46 | 13 | 33 | 2 | 80710 | 66713 | 8GB | 2843 | 333MB
| -8.70% |
===========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3655576753)
==================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost
| Time | Start | Execs | Rows | Read | Read | Mem | Temp |
Activity | Activity Detail |
| | | | (Estim) |
| Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (Max) |
(%) | (# samples) |
==================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | |
| 39 | +4 | 1 | 3 | | | | |
| |
| 1 | SORT UNIQUE | | 3 |
19901 | 39 | +4 | 1 | 3 | | | 2048 | |
| |
| 2 | UNION-ALL | | |
| 31 | +4 | 1 | 5 | | | | |
| |
| 3 | NESTED LOOPS OUTER | | 2 |
3214 | 1 | +4 | 1 | 3 | | | | |
| |
| 4 | VIEW | VW_FOJ_1 | 2 |
3210 | 1 | +4 | 1 | 3 | | | | |
| |
| 5 | NESTED LOOPS OUTER | | 2 |
3210 | 1 | +4 | 1 | 3 | | | | |
| |
| 6 | TABLE ACCESS STORAGE FULL | COX | 2 |
3206 | 1 | +4 | 1 | 3 | | | | |
| |
| 7 | TABLE ACCESS BY INDEX ROWID | CS | 1 |
2 | 1 | +4 | 3 | 1 | | | | |
| |
| 8 | INDEX UNIQUE SCAN | CS_PK | 1 |
1 | 1 | +4 | 3 | 1 | | | | |
| |
| 9 | TABLE ACCESS BY INDEX ROWID | BOS | 1 |
2 | 1 | +4 | 3 | 2 | | | | |
| |
| 10 | INDEX UNIQUE SCAN | BOS_PK | 1 |
1 | 1 | +4 | 3 | 2 | | | | |
| |
| 11 | FILTER | | |
| 35 | +8 | 1 | 2 | | | | |
| |
| 12 | HASH JOIN OUTER | | 1 |
16685 | 39 | +4 | 1 | 3 | | | 434K | |
| |
| 13 | TABLE ACCESS BY INDEX ROWID | BOS | 1 |
4 | 1 | +4 | 1 | 3 | | | | |
| |
| 14 | INDEX RANGE SCAN | BOS_PK | 1 |
3 | 1 | +4 | 1 | 3 | | | | |
| |
| 15 | VIEW | VW_FOJ_3 | 2M |
16678 | 39 | +4 | 1 | 3 | | | | |
| |
| 16 | HASH JOIN FULL OUTER | | 2M |
16678 | 42 | +1 | 1 | 2M | 63108 | 7GB | 2M | 373M |
100.00 | Cpu (11) |
| | | | |
| | | | | | | | |
| direct path read temp (31) |
| 17 | TABLE ACCESS STORAGE FULL | CS | 948K |
2683 | 1 | +4 | 1 | 948K | | | | |
| |
| 18 | TABLE ACCESS STORAGE FULL | COX | 2M |
3213 | 1 | +4 | 1 | 2M | | | | |
| |
==================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage("A5"."BI"=TO_NUMBER(:B1))
filter("A5"."BI"=TO_NUMBER(:B1))
8 - access("A5"."EID"="A4"."EID"(+))
10 - access("A2"."BI"(+)=TO_NUMBER(:B1) AND
"A2"."OID"(+)=TO_NUMBER("A5"."OID"))
11 - filter("A5"."CT1"='XXX' AND "A5"."BI" IS NOT NULL OR "A5"."BI" IS NULL
AND "A5"."CT1"='YYY')
12 - access("A2"."BI"="A5"."BI"(+) AND "A2"."OID"=TO_NUMBER("A5"."OID"(+)))
13 - filter("A2"."I_DT" IS NULL)
14 - access("A2"."BI"=TO_NUMBER(:B2))
15 - filter("A5"."BI"(+)=TO_NUMBER(:B2))
16 - access("A5"."EID"="A4"."EID")
Other related posts:
