Oracle Database Result Cache TroubleshootingBy Martin | February 20th, 2017 | Category: 12c, Oracle Database | No Comments »
I was troubleshooting an issue where the Oracle Database Result Cache did not get used when the RESULT_CACHE hint was specified inside a subquery. As the main query has a sysdate inside, the RESULT_CACHE Hint can not be specified for the main query, but only for the subquery.
SELECT SQ.*, sysdate FROM (SELECT * FROM tableX, ....) SQ;
When using “/*+ RESULT_CACHE */ inside of Subquery, result cache was not enabled.
1 2 3
SELECT SQ.*, sysdate FROM (SELECT /*+ RESULT_CACHE */ * FROM tableX, ....) SQ;
I found out that there is a trace event which can be set for diagnostics.
ALTER SESSION SET EVENTS '43905 trace name context forever, level 1';
This generated a trace file containing a clue to the reason:
QKSRC: ViewText[ecode=942] = SELECT /*+ RESULT_CACHE */ ......
So, it seemed that there was an internal ORA-942 somewhere that prevented result caching from being used. The solution was to qualify the objects inside the subquery. Now the result cache was used correctly:
SELECT SQ.*, sysdate FROM (SELECT /*+ RESULT_CACHE */ * FROM ownerY.tableX, ....)