Re: Suggestion for application caching

Hello Mark,

That query(which consumed high tempspace) is just one part of the main query which consists of five different "union all". So the " X" In the last position Is getting union with other values of other "union all" Queries at same position. But I hope that won't make much difference in the tempspace consumption.

Also as I mentioned, converting that query using an exists clause is making the query run without any temp spill. Hope the modified query will be logically return same as original query. Not sure if any other options possible here.

On Sat, 19 Aug, 2023, 10:25 pm Mark W. Farnham, <mwf_at_rsiz.com> wrote:

> Why have you added ‘X’ as an extra column in the return list?
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *yudhi s
> *Sent:* Saturday, August 19, 2023 8:38 AM
> *To:* Lothar Flatz
> *Cc:* Lok P; Oracle L
> *Subject:* Re: Suggestion for application caching
>
>
>
> Thank you so much Lothar.
>
> So , it's the first query which is causing the temp spill and must be
> where the majority of time is spent. So I tried multiple options as you
> suggested. Like Running it with higher PGA(setting 2GB at session level) ,
> with parallel-4 but not seeing any reduction in the temp space consumption
> and also in run time. Though i killed the queries halfway while they were
> generating rows, because a lot of rows had to be passed back to the client.
>
> Below the github link is the sql monitor and predicate information for the
> first query without any change and with higher pga and with parallel(4)
> hints. Basically I tried the option 3 and 4 as you suggested. And I am not
> sure how I can hint the query to switch to "sort unique" in place of "hash
> unique", so I have not tried that option yet.
>
> And as per the development team, the JOIN conditions are all there and are
> not missing , but there exists one to many relationship between table M and
> A that's why the resultset are getting increased. "M"-kind of customer
> table and "A" is holding a different IP address for the same customer
> transactions.
>
>
>
> But then I noticed the projected columns are only from table M and I. None
> of the columns were projected from table A. So I tried putting table 'X' in
> the exists clause and the query is not using any temp space now. Below link
> also has the sql monitor and predicate section after tweaking the query
> using EXISTS clause. Hopefully I am not doing it wrong here.
>
>
>
> https://gist.github.com/databasetech0073/de859a7c24f610bf7db5190f48fa3ddf
>
>
>
> Existing query:-
>
>
> SELECT DISTINCT M.MID, I.AD1, I.AD2, I.AD3, I.AD4, m.abi
> FROM A , M , I
> WHERE A.abi = M.abi
> AND A.AID = M.AID
> AND M.ACT = 1
> AND A.ISQ = I.ISQ
> AND I.AIND = 'X'
> AND I.AIND = A.AIND
>
> Modified to as below:-
>
> SELECT DISTINCT
> M.MID, I.I.AD1, I.I.AD2, I.I.AD3, I. I.AD4, m.abi, 'X'
> FROM M , I
> WHERE M.ACT = 1
> AND I.AIND = 'X'
> aND exists ( select 1 from A where A.abi = M.abi
> AND A.AID = M.AID
> AND A.ISQ = I.ISQ
> AND I.AIND = A.AIND )
>
>
> Monitoring plan for modified query:-
>
> SQL Plan Monitoring Details (Plan Hash Value=2400668331)
>
> =================================================================================================================================================================================
> | Id | Operation | Name | Rows | Cost
> | Time | Start | Execs | Rows | Mem | Activity | Activity
> Detail | Progress |
> | | | | (Estim) |
> | ACT(s) | ACT | | (Actual) | | (%) | (# samples)
> | |
>
> =================================================================================================================================================================================
> | -> 0 | SELECT STATEMENT | | |
> | 1937 | +0 | 1 | 145M | . | 2.78 | Cpu (1)
> | |
> | -> 1 | HASH JOIN | | 512M | 4693
> | 1937 | +0 | 1 | 145M | 4MB | 97.22 | Cpu (31)
> | |
> | | | | |
> | | | | | | | SQL*Net more
> data to client (4) | |
> | 2 | JOIN FILTER CREATE | :BF0000 | 5694 | 231
> | 1 | +0 | 1 | 5891 | . | |
> | |
> | 3 | HASH JOIN | | 5694 | 231
> | 1 | +0 | 1 | 5891 | . | |
> | |
> | 4 | JOIN FILTER CREATE | :BF0001 | 5694 | 12
> | 1 | +0 | 1 | 5894 | . | |
> | |
> | 5 | SORT UNIQUE | | 5694 | 12
> | 1 | +0 | 1 | 5894 | . | |
> | |
> | 6 | TABLE ACCESS STORAGE FULL | A | 5694 | 12
> | 1 | +0 | 1 | 5894 | . | |
> | |
> | 7 | JOIN FILTER USE | :BF0001 | 140K | 219
> | 1 | +0 | 1 | 11161 | . | |
> | |
> | 8 | TABLE ACCESS STORAGE FULL | I | 140K | 219
> | 1 | +0 | 1 | 11161 | . | |
> | |
> | 9 | JOIN FILTER USE | :BF0000 | 481K | 4297
> | 1936 | +0 | 1 | 202K | . | |
> | |
> | -> 10 | TABLE ACCESS STORAGE FULL | M | 481K | 4297
> | 1937 | +0 | 1 | 202K | . | |
> | 41% |
>
> =================================================================================================================================================================================
>
>
>
> On Sat, Aug 19, 2023 at 1:04 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
>
> Hi,
>
> Ok, relevant is this part:
>
> ============================================================================================================================================================================================================================
> | Id | Operation |
> Name | Rows | Cost | Time | Start | Execs | Rows
> | Read | Read | Write | Write | Mem | Temp | Activity | Activity
> Detail |
> | |
> | | (Estim) | | Active(s) | Active
> | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) |
> (%) | (# samples) |
>
> ============================================================================================================================================================================================================================
> | 2 | HASH UNIQUE
> | | 512M | 7M | 904 | +1
> | 1 | 357M | 17152 | 17GB | 17152 | 17GB | 1GB | 17GB
> | | |
> | 3 | HASH JOIN
> | | 512M | 4693 | 146 | +2
> | 1 | 357M | | | | | 4MB | .
> | | |
> | 4 | JOIN FILTER CREATE |
> :BF0000 | 5694 | 231 | 1 | +2 |
> 1 | 5888 | | | | | . | . |
> | |
> | 5 | HASH JOIN
> | | 5694 | 231 | 1 | +2
> | 1 | 5888 | | | | | 4MB | .
> | | |
> | 6 | JOIN FILTER CREATE |
> :BF0001 | 5694 | 12 | 1 | +2 |
> 1 | 5891 | | | | | . | . |
> | |
> | 7 | TABLE ACCESS STORAGE FULL |
> A | 5694 | 12 | 1 | +2 |
> 1 | 5891 | | | | | . | . |
> | |
> | 8 | JOIN FILTER USE |
> :BF0001 | 140K | 219 | 1 | +2 |
> 1 | 11143 | | | | | . | . |
> | |
> | 9 | TABLE ACCESS STORAGE FULL |
> I | 140K | 219 | 1 | +2 |
> 1 | 11143 | 284 | 8MB | | | . | . |
> | |
> | 10 | JOIN FILTER USE |
> :BF0000 | 481K | 4297 | 146 | +2 |
> 1 | 481K | | | | | . | . |
> | |
> | 11 | TABLE ACCESS STORAGE FULL |
> M | 481K | 4297 | 146 | +2 |
> 1 | 481K | | | | | . | . |
> | |
>
> Or in as text:
> SELECT DISTINCT ....
> FROM A, M , I
> WHERE ...
>
> Now: the hash join is proliferating the number of rows by a lot. The
> extent of increase seems unusual. It is possible that a part of the join
> condition is missing .
>
> So what could you do:
> You can not reduce the number of rows before the Hash join, otherwise the
> distinct operation would have shown a drop in actual rows.
>
> 1. You can ask if the really want all these rows or if further
> filtering can be applied.
> 2. Double check the join condition. Is it complete?
> 3. You can try to substantially increase PGA ( about 10 - 20 x ) .
> Also consider MOS 2808761.1
> 4. You might want to test parallelizing (like parallel 4) the first
> query in the union, that might get you little more PGA
> 5. You might experiment hinting a sort unique, don't know if it helps
>
>
> Thanks
>
> Lothar
>
> Am 18.08.2023 um 23:45 schrieb yudhi s:
>
> Below is the part in bold showing ~17GB tempspill. Also , I have posted
> the full sql monitor in the github link in the start of this thread.
>
>
>
> SQL Plan Monitoring Details (Plan Hash Value=1390517125)
>
> ============================================================================================================================================================================================================================
> | Id | Operation | Name
> | Rows | Cost | Time | Start | Execs | Rows | Read
> | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
> | | |
> | (Estim) | | Active(s) | Active | | (Actual) | Reqs
> | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
>
> ============================================================================================================================================================================================================================
> | 0 | SELECT STATEMENT |
> | | | 917 | +11 | 1 | 366M |
> | | | | . | . | | |
> | 1 | UNION-ALL |
> | | | 781 | +147 | 1 | 366M |
> | | | | . | . | | |
>
> *| 2 | HASH UNIQUE |
> | 512M | 7M | 904 | +1 | 1 | 357M | 17152
> | 17GB | 17152 | 17GB | 1GB | 17GB | | |*|
> 3 | HASH JOIN |
> | 512M | 4693 | 146 | +2 | 1 | 357M | |
> | | | 4MB | . | | |
> | 4 | JOIN FILTER CREATE | :BF0000
> | 5694 | 231 | 1 | +2 | 1 | 5888 |
> | | | | . | . | | |
> | 5 | HASH JOIN |
> | 5694 | 231 | 1 | +2 | 1 | 5888 |
> | | | | 4MB | . | | |
>
>
>
> On Sat, Aug 19, 2023 at 3:12 AM Lok P <loknath.73_at_gmail.com> wrote:
>
> Where are you seeing the temp spill?
>
>
>
> On Sat, Aug 19, 2023 at 2:52 AM yudhi s <learnerdatabase99_at_gmail.com>
> wrote:
>
> Hello Listers,
>
> It's Oracle 19C. We have one third party app query in which the results
> out of the query(which is ~300million rows) gets fully cached by the app
> servers for certain requirements. (Mostly because the other queries on top
> of those cached result sets are required to be having a very fast response
> as per business requirement).
>
> And that query executes multiple times each day i.e. once for each app
> server to cache the result sets. So currently during cache
> refresh/invalidation, four app servers executing the query each once i.e.
> four instances of the same query fired on the database simultaneously from
> four different sessions. The number of app servers is going to be increased
> to ~12 in future, which means this query is going to be executed 12 times.
> For each execution , this query takes ~20GB temp space and runs for
> ~20minutes(~5minutes in database and rest of the time for fetching the rows
> to the application/client).
>
> The issue which we are trying to address is , This query consumes ~20GB of
> tempspace for one execution and for 12 servers we will need ~240GB of temp
> space for those ~20 minutes of duration. Are there any other options out
> there to handle such a scenario?
>
> Below is the sample query and its sql monitor(in the github link). I have
> replaced the actual names with dummy names and also just posted
> the basic skeleton to keep it simple here.
>
> This query has five "UNION ALL" conditions and the TEMP SPACE is spilling
> while applying the DISTINCT clause mostly. But the application team is
> resisting that they can't get rid of the "DISTINCT" clause because of
> current design and also the UNION ALL is from multiple tables with
> different Join criteria. Also only the specific columns are exposed out of
> the query but not all. Is there any other way to minimize the tempspill
> here?
>
>
>
> https://gist.github.com/databasetech0073/ab498814ee88538378097f3d12fca705
>
>
> SQL Text
> ------------------------------
> SELECT DISTINCT ....
> FROM A, M , I
> WHERE ...
> UNION ALL
> SELECT DISTINCT ...
> FROM C , M, I
> WHERE ....
> UNION ALL
> SELECT...
> FROM M, I, MI
> WHERE ....
> UNION ALL
> SELECT ....
> FROM MSX , M, S, SIP, IPS
> WHERE ...
> UNION ALL
> SELECT ....
> FROM CSX ,M,S,SIP,IPS
> WHERE ....
> UNION ALL
> SELECT ...
> FROM ASX, M,S,SIP,IPS
> WHERE ....
>
>
>
> Regards
>
> Yudhi
>
>
>
>

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