Oracle Database Discussions

We are experiencing slowness in our database and I suspect that the reason might be incorrect usage of RESULT_CACHE in functions in our plsql packages.

We are on Oracle 12.1:

select * from v$version;

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

Running the SQL-statement below returns the dataset further down.

SELECT   namespace,
         status,
         name,
         COUNT(*) number_of_results,
         ROUND(AVG(scan_count)) avg_scan_cnt,
         ROUND(MAX(scan_count)) max_scan_cnt,
         ROUND(SUM(block_count)) tot_blk_cnt
    FROM v$result_cache_objects
   WHERE TYPE = 'Result'
GROUP BY namespace, name, status
ORDER BY namespace, tot_blk_cnt;

Can anyone please advice if any of the functions listed in the result set below should not be using RESULT_CACHE?

To me it seems like the last functions, for instance PKG_W.GETBYPK should not be using RESULT_CACHE as there are too many results in the cache. Am I right?

Please let me know if you need more information from me.

As you probably can see from the package names in the result set below, I have renamed them for the purpose of this forum post.

Also, is it possible to flush just parts of the result cache as opposed to flushing the entire cache?