Re: Query Performance issue

Hi Mark,

I wonder if Björn and mine "backbone join" technique can be used. Stefan describes it in short here:
https://community.oracle.com/tech/apps-infra/discussion/4386851/implement-index-backbone-join-transformation-rewrite-in-cbo-code

The bottom line is to rewrite the query such that the join is made only with the columns essentially needed.
In my example you need to put essential columns into indexes. On an Exadata that might not even be necessary. The rest of the columns is picked up later using the rowids of the rows that make it into the final result.

Regards

Lothar

Am 28.12.2020 um 22:47 schrieb Mark W. Farnham:
>
> 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
> *To:* Laurentiu Oprea
> *Cc:* Pap; Sayan Malakshinov; Jonathan Lewis; Oracle L
> *Subject:* Re: Query Performance issue
>
> 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 <mailto: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
> <mailto: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
> <mailto: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 <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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 <http://orasql.org>
>
> чт, 24 дек. 2020 г., 17:43 Lok P
> <loknath.73_at_gmail.com
> <mailto: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
> <mailto: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://orasql.org>
>

--
http://www.freelists.org/webpage/oracle-l