Why have you added ‘X’ as an extra column in the return list?
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxx> 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@xxxxxxxxx> wrote:
Where are you seeing the temp spill?
On Sat, Aug 19, 2023 at 2:52 AM yudhi s <learnerdatabase99@xxxxxxxxx> 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
