Re: Query Performance issue

From: Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
Date: Sun, 27 Dec 2020 17:30:46 +0200
Message-ID: <CA+riqSU9myOgWW5a5qiYnqYGQOBH1vvtdmP+cK75yEgsqn=DJA_at_mail.gmail.com>



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_at_gmail.com> 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_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-l
Received on Sun Dec 27 2020 - 16:30:46 CET

Original text of this message