RE: Suggestion for application caching

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 19 Aug 2023 12:55:04 -0400
Message-ID: <143001d9d2bd$e8c02a20$ba407e60$_at_rsiz.com>

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)

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

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

| 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

Received on Sat Aug 19 2023 - 18:55:04 CEST

This message: [ Message body ]
Next message: Mladen Gogala: "Re: Suggestion for application caching"
Previous message: Pap: "Re: [External] : Re: Open cursor error"
In reply to yudhi s: "Re: Suggestion for application caching"
Next in thread: yudhi s: "Re: Suggestion for application caching"
Reply: yudhi s: "Re: Suggestion for application caching"
Reply: yudhi s: "Re: Suggestion for application caching"
Reply: yudhi s: "Re: Suggestion for application caching"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message