Re: Query Performance issue

In theory if is not doing on his own (and not doing with the hint use_concat as well) it might be the case that the result is affected and I advised completely wrong.
Are you able to create an index on the column: "A5"."BI" from filter( *"A5"."BI"*=TO_NUMBER(:B1)) and give a try using the suggested : alter session set "_optimizer_native_full_outer_join" = off;

În dum., 27 dec. 2020 la 20:18, Lok P <loknath.73_at_gmail.com> a scris:

> 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