Re: Query Performance issue

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_at_gmail.com> 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_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




text/plain attachment: sql_monitor_UNION_Breakup.txt