Re: Query Performance issue

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_at_gmail.com> 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_at_gmail.com>
> 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_at_gmail.com>:
>>
>>> 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_at_gmail.com>
>>> wrote:
>>>
>>>> Can you show both original and modified queries?
>>>>
>>>
>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>

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




text/plain attachment: Right_Outer_join_path.txt