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
Last activity: Aug 24, 2023, 08:57 AM