Re: Suggestion for application caching
From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Sat, 19 Aug 2023 09:34:42 +0200
Message-ID: <2c9c1bfb-c888-96fa-50a5-d3c66d0932e0_at_bluewin.ch>
| Id | Operation | Name |
| | | | (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 |
| 1 | 11143 | 284 | 8MB | | | . | . |
| |
| 10 | JOIN FILTER USE |
| 1 | 481K | | | | | . | . |
| |
| 11 | TABLE ACCESS STORAGE FULL |
| 1 | 481K | | | | | . | . |
| |
Date: Sat, 19 Aug 2023 09:34:42 +0200
Message-ID: <2c9c1bfb-c888-96fa-50a5-d3c66d0932e0_at_bluewin.ch>
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.
- You can ask if the really want all these rows or if further filtering can be applied.
- Double check the join condition. Is it complete?
- You can try to substantially increase PGA ( about 10 - 20 x ) . Also consider MOS 2808761.1
- You might want to test parallelizing (like parallel 4) the first query in the union, that might get you little more PGA
- 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-lReceived on Sat Aug 19 2023 - 09:34:42 CEST