Re: Query Performance issue
- From: Lok P <loknath.73@xxxxxxxxx>
- To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- Date: Thu, 24 Dec 2020 21:14:09 +0530
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
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
SQL Execution ID : 16777216
Execution Started : 12/24/2020 09:55:45
First Refresh Time : 12/24/2020 09:55:49
Last Refresh Time : 12/24/2020 10:02:56
Duration : 431s
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 |
=====================================================================================================
| 453 | 58 | 395 | 0.00 | 2 | 57455 | 365K | 42GB |
2980 | 349MB | -2.04% |
=====================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=308020963)
===========================================================================================================================================================================================================
| 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 | | | |
216 | +216 | 1 | 3 | | | | | |
| | |
| 1 | NESTED LOOPS OUTER | | 3 | 17611 |
216 | +216 | 1 | 3 | | | | | |
| | |
| 2 | FILTER | | | |
428 | +4 | 1 | 3 | | | | | |
| | |
| 3 | HASH JOIN RIGHT OUTER | | 3 | 17606 |
431 | +2 | 1 | 2M | 365K | 42GB | 384 | 45MB | 1M |
391M | 100.00 | Cpu (34) |
| | | | | |
| | | | | | | | |
| | direct path read temp (395) |
| | | | | |
| | | | | | | | |
| | direct path write temp (1) |
| 4 | TABLE ACCESS STORAGE FULL | BOS | 1M | 3274 |
1 | +4 | 1 | 1M | | | | | |
| | |
| 5 | TABLE ACCESS STORAGE FULL | COX | 2M | 3213 |
3 | +4 | 1 | 2M | | | | | |
| | |
| 6 | TABLE ACCESS BY INDEX ROWID | CS | 1 | 2 |
1 | +222 | 3 | 1 | | | | | |
| | |
| 7 | INDEX UNIQUE SCAN | CS_PK | 1 | 1 |
1 | +222 | 3 | 1 | 3 | 24576 | | | |
| | |
===========================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COX"."BI"=TO_NUMBER(:B1) OR "BOS"."BI"=TO_NUMBER(:B2) AND
"BOS"."I_DT" IS NULL AND "COX"."CT3"='XXX')
3 - access("BOS"."OID"(+)=TO_NUMBER("COX"."OID") AND
"BOS"."BI"(+)="COX"."BI")
7 - access("COX"."EID"="CS"."EID"(+))
Other related posts:
