Re: Suggestion for application caching

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