I've a situation where data caching has resulted into wrong results in the reports because the data has been updated in a table due to online transactions, but the report is showing previously cached data.
The reason for implementing caching - We have 3rd party SQLs which use a costly inline function and this function is used multiple times in a single SQL with different parameters. Also, this function is designed in such a way that it fetches all the info in each call and returns only what was requested based on input parameter.
So, to improve performance, a package collection was introduced to store all the fetched data in first inline function execution itself and then return data from that cached collection itself for all subsequent inline function calls.
Example:
SELECT
pack_test.fn_costly('Person','Name'),
pack_test.fn_costly('Person','Age'),
pack_test.fn_costly('Person', 'Gender'),
pack_test.fn_costly('Person', 'Address')
FROM DUAL;
In this example, fn_costly is designed to fetch all the info related to Person, but it returns only the value which is requested in 2nd Parameter. So, a package level collection is added to store all the info related to Person in first execution for 'Name' and 2nd,3rd,4th calls were returned from Cache.
In this approach, my assumption was, when SQL will come in, this collection will always be empty but looks like, since connection pooling is enabled, previous execution data is already present in the collection, due to which, current execution has ended up using the previously cached data, which is wrong.
Is there any way to make sure packaged collection is empty before each independent SQL execution happens?
PS: I can only make changes in the package (where function exists) or the SQL which is being fired by third parties.
Thanks
Additional Info (16/5): To address this issue temporarily, I've appended 'HH24MI' to the cached collection index, meaning if Sql is executing in the same minute as cached data, cache is valid and can be used.. Otherwise, reload the cache with current HH24MI appended to indexed columns and use new data. I know this is not a 100% solid approach but can hold the fort for now.. If anyone has a better solution, please share.
Package variables remain in memory for the lifetime of the session, so yes, connection pooling can cause this behavior. Because there is no concept of execution order in SELECT clauses, you can't guarantee the order in which Oracle will call these, so you would have to have a way of the function identifying itself as the first call. That could get tricky (you could perhaps use the combination of SQL_ID
and SQL_EXEC_ID
from v$session
but that's getting ugly).
Perhaps the approach is wrong. If it were me, I'd start with tuning the underlying function, to get it to run in a reasonable time so no caching is needed. If you get it fast enough, you're done.
If you still need to consolidate, rewrite the function to have an object return type so you can return all the fields at once. There are multiple ways to do this. Here's one:
CREATE OR REPLACE TYPE myobjectype AS OBJECT (name varchar2(128), age integer, gender char(1), address varchar2(256))
/
CREATE OR REPLACE FUNCTION FN_COSTLY
RETURN myobjecttype
AS
myobject myobjecttype;
BEGIN
-- expensive stuff to get data
myobject := myobjecttype('My name',13,'M','123 Main St');
dbms_output.put_line('got here');
RETURN myobject;
END;
/
SELECT TREAT(result AS myobjecttype).name name,
TREAT(result AS myobjecttype).age age,
TREAT(result AS myobjecttype).gender gender,
TREAT(result AS myobjecttype).address address
FROM (SELECT /*+ NO_MERGE */ FN_COSTLY result FROM dual)
Then make sure you get only one "got here" message (which is why the NO_MERGE
hint is used, to prevent Oracle from merging the query block and calling the function four times), after which comment out or remove the dbms_output
call.
Result: