Sample Header Ad - 728x90

How to know how much memory is used by each DB user in the cache buffer?

2 votes
1 answer
1163 views
Using this query:
SELECT
        A.CACHE_BUFFER_TOTAL,
        B.CACHE_BUFFER_USED,
        A.CACHE_BUFFER_TOTAL - B.CACHE_BUFFER_USED AS CACHE_BUFFER_FREE
    FROM
        (SELECT BYTES / 1024 / 1024 AS CACHE_BUFFER_TOTAL FROM V$SGAINFO WHERE NAME = 'Buffer Cache Size') A,
        (SELECT (SELECT COUNT(*) FROM V$BH) * (SELECT BLOCK_SIZE FROM V$BUFFER_POOL) / 1024 / 1024 AS CACHE_BUFFER_USED FROM DUAL) B;
... I can determine the total size of the buffer cache and the total used; however, I would like to know for each user how much the user's queries are occupying the buffer cache. Is there a simple way to achieve this in Oracle 21C?
Asked by Isaac PM (23 rep)
Aug 24, 2023, 05:04 AM
Last activity: Aug 24, 2023, 08:57 AM