Re: Should we use Subquery caching or result cache here
Date: Fri, 29 Oct 2021 21:41:11 -0400
Message-ID: <9cf3e432-4e0c-43f4-af19-f6eca1afba19_at_gmail.com>
SELECT............
pkg.fun1 (RFFT.AMT, RTFX.STS, RTFX.PDT, :B1, ( :B1 + 1), 'X'),
pkg.fun1 (RFFT.AMT, RTFX.STS, RTFX.PDT, :B1, ( :B1 + 1), 'Y'),
TRUNC ( :B1)....
FROM RTFX RTFX, RFFT RFFT, ND ND, RDC RDC
WHERE RTFX.FFXID = RFFT.FXID
AND RTFX.AC_Key = RFFT.C_Key
AND RFFT.CT_Key = ND.N_Key
AND ND.N_ETY IN ('XX', 'YY', 'ZZ')...;
If the entries in the tables are not
changing very rapidly, cursor cache makes sense. Bear in mind
that cursor cache is protected by latches. Updating any cached
row will invalidate the cache entry and will require obtaining a
latch. Frequent updates may cause latch storms. Cursor cache is
used where result remains the same and can be reused. One of my
favorite tricks is to change "keep alive" application server
query from "SELECT 1 FROM DUAL" into "SELECT /*+ CURSOR_CACHE */
1 FROM DUAL". The value of "1" is unlikely to change, unless
you're a treasury secretary, so the execution path is much
shorter. You would be surprised but that query spends
significant amount of CPU with any application server.
-- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com-- http://www.freelists.org/webpage/oracle-l Received on Sat Oct 30 2021 - 03:41:11 CEST