Re: Query Performance issue

Thank you so much. I will try to analyze this and see if we can go this route as a new design to fix this.

However, with respect to root cause , I was wondering how come such a large deviation in datavolume can happen such that the temp spill just immediately goes so high and cause the query to go from ~10 seconds to ~15minutes. But then i saw the pga_allocated in dba_hist_active_sess_history has also dropped from ~.4GB to ~.005GB during the same time. Which means it can't be just the volume in the base table increased , as that would not have minimized the pga_allocated for that session to drop at same time.

 Now, we saw that in this database, we are having a hash_area_size set as ~1.5GB in gv$parameter along with pga_aggregate_target as 15GB and workarea_size_policy as AUTO. So in this situation if anybody just set the workarea_size_policy as 'Manual" in session level Oracle will try to allocate the set hash_area_size i.e. ~1.5GB to that session and that's why if i just set the workarea_size_policy to Manual and hit the query it's making it finish in ~10seconds. So it means , it may be that somehow (may be alter session in the code or through trigger etc) , the workarea_size_policy was getting set as Manual earlier but now its not happening and going for all AUTO, so making this query to crawl.

On Tue, Dec 29, 2020 at 3:17 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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> 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) :
>
> 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