Re: Query Performance issue

From: Lok P <>
Date: Mon, 28 Dec 2020 12:52:20 +0530
Message-ID: <>

As I can't try that on prod, so I have to test it on DEV by creating an additional index on column BI of table COX (Alias A5 here).

But for time being, as I saw there already exists a composite index on column (EID, BI,IOD, CT1), so i tried adding that additional filter on the column A5.EID of table COX (Alias A5) along with the BI filter , to see if the plan changes and its opting this Index. But I am still seeing the optimizer going for the same FTS on both the table COX and CS and a HASH JOIN OUTER even with "_optimizer_native_full_outer_join" = off;

Also I was thinking that projecting those(~50) columns out of the inline view is causing the HASH Table to spill to temp, is there any other way I can rewrite so that the projected column won't be taken into account while building the HASH table?

On Mon, Dec 28, 2020 at 1:31 AM Laurentiu Oprea <> wrote:

> 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 <> 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 <
>>> 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 ....
>>> "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 <> 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, <>
>>>> 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 <> 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, <> 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 <
>>>>>>>> wrote:
>>>>>>>> Also you need to help CBO with predicates like this:
>>>>>>>> SELECT ....~58 columns projected...
>>>>>>>> FROM (SELECT ....~60 columns projected
>>>>>>>> FROM "USER1"."BOS" "A2"
>>>>>>>> (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 <
>>>>>>>>> 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
>>>>>>>>> чт, 24 дек. 2020 г., 17:43 Lok P <>:
>>>>>>>>>> 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 <
>>>>>>>>>>> wrote:
>>>>>>>>>>> Can you show both original and modified queries?
>>>>>>>> --
>>>>>>>> Best regards,
>>>>>>>> Sayan Malakshinov
>>>>>>>> Oracle performance tuning engineer
>>>>>>>> Oracle ACE Associate

Received on Mon Dec 28 2020 - 08:22:20 CET

Original text of this message