Re: Query Performance issue
Date: Sun, 27 Dec 2020 18:17:47 +0530
Message-ID: <CAKna9VY=LYXSq1q7Us3dCtRWisR6BN_qrieN+VS=QKdCzcbCcA_at_mail.gmail.com>
On Sat, 26 Dec 2020, 11:09 pm Pap, <oracle.developer35_at_gmail.com> 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_at_gmail.com> 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_at_gmail.com> 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_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-lReceived on Sun Dec 27 2020 - 13:47:47 CET