RE: Query Performance issue
Date: Mon, 28 Dec 2020 16:47:08 -0500
Message-ID: <035b01d6dd62$fe7ebaa0$fb7c2fe0$_at_rsiz.com>
One technique is to decompose each row source to the relevant rowids and use those rowids to populate a gtt from each base table with just the columns you need for the final projection. It is usually possible to do that in a “WITH” clause, or you can instantiate the gtt in the session.
Then run the original query, substituting the slenderized (just the rows you need and just the columns you need) gtt for each relevant base table.
Sometimes this if useful. Sometimes it still takes too long either to produce the gtts in the first place or in the now wide hash to produce the final result. IF there is a tendency for high selectivity of the pieces (such as losing all the COX rows where BI0 != :b1, leaving few, perhaps) with a big savings. When Oracle produces a gigantic memory footprint to hash out a small number of surviving rows, this tends to be a good strategy that is masked from the CBO by complex ORs, especially involving NULL values.
You might save time overall by redesigning your data model and completely rebuilding your database, now that you know the queries you are being asked to answer (or at least some of them that are clearly not a routine ask from the current data model.)
This will not be popular until after the dust has settled. If your boss designed the data model (or threw it together knowing roughly the attributes required sans relationships and likely queries, remember to refrain from being critical of the data model. Rather, it is obvious the AI nature of your required queries made it impossible to build performance into original data model (which is very good) but only now is it possible to improve it.
You must say this with a straight face.
Good luck
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lok P
Sent: Monday, December 28, 2020 2:22 AM
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 <laurentiu.oprea06_at_gmail.com> 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 <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) :
2.SELECT ....
Î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...
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,
чт, 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...
To: Laurentiu Oprea
Cc: Pap; Sayan Malakshinov; Jonathan Lewis; Oracle L
Subject: Re: Query Performance issue
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')
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')
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org
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 Mon Dec 28 2020 - 22:47:08 CET