Re: Suggestion for application caching
From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Sat, 19 Aug 2023 03:15:23 +0530
Message-ID: <CAEzWdqe8MC2_h5E88SeZR21ttYgGYPNdaqz_wyuc+6apv2imAA_at_mail.gmail.com>
| Id | Operation | Name
| 0 | SELECT STATEMENT |
Date: Sat, 19 Aug 2023 03:15:23 +0530
Message-ID: <CAEzWdqe8MC2_h5E88SeZR21ttYgGYPNdaqz_wyuc+6apv2imAA_at_mail.gmail.com>
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-lReceived on Fri Aug 18 2023 - 23:45:23 CEST