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

Question

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?

Answer 1

You have an interesting question. However, as per my understanding of Oracle and the various caches that exist in the SGA and PGA, there are no real means to determine the usage of each individual user's cache usage, because that would defeat the concept of (efficient) caching.

The general idea behind caching (or Oracle's Memory Architecture) is to store information in RAM to allow for efficient retrieval and re-use of data, statements, query plans, etc. for each and every user querying an Oracle instance.

The basis for memory management in an Oracle instance is documented as follows:

_{(emphasis mine)}

Introduction to Oracle Database Memory Structures

When an instance is started, Oracle Database allocates a memory area and starts background processes.

The memory area stores information such as the following:

• Program code
• Information about each connected session, even if it is not currently active
• Information needed during program execution, for example, the current state of a query from which rows are being fetched
• Information such as lock data that is shared and communicated among processes
• Cached data, such as data blocks and redo records, that also exists on disk

This is split up into:

_{(emphasis mine)}

Basic Memory Structures

Oracle Database includes several memory areas, each of which contains multiple sub-components.

The basic memory structures associated with Oracle Database include:

• System global area (SGA)

  The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. All server and background processes share the SGA. Examples of data stored in the SGA include cached data blocks and shared SQL areas. (... and execution plans)

• Program global area (PGA)
  A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. Oracle Database creates the PGA when an Oracle process starts.
  One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.

• User global area (UGA)
  The UGA is memory associated with a user session.

• Software code areas
  Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.

^{Reference: Memory Architecture (Oracle | Docs)}

There is a really good picture in the above documentation, which displays the different components of Oracle's Memory Architecture:

Database Buffer Cache

The database buffer cache will contain data, that has already been retrieved from disk and which the Oracle Database Memory Manager deems worthy of being kept in memory for future retrieval from anybody querying the same Oracle instance. It wouldn't make sense to hold this data for each individual user, as it would take up too much space in RAM.

So you won't be able to determine to which user the data belongs.

Shared SQL Area

The same is also valid for the Shared SQL Area. Why store multiples of the same query, if it's more efficient to store only one version of the (same) query and then just run the query for anybody wanting to perform the same query?

Private SQL Area

Now this might look promising as an area to sum up each user's cache. However, the information stored in this area doesn't look very interesting:

_{(empasis mine)}

The private SQL area contains information unique to each user, such as bind variables and runtime buffers and is, in turn, associated with the shared SQL area that contains the actual parsed SQL code.

Answering Your Question

... I would like to know for each user how much the user's queries are occupying the buffer cache.

Due to the concept and nature of buffer cache, you will be unable to determine the user's queries occupying the buffer cache.

In a sense, everything that a user performs is "de-normalized" so that everybody can benefit from an efficient retrieval of data / statements / query execution plans.

I hope this information provides you with a good starting point.

By the way, a few of the well-known DBMS will manage memory according to similar principles. (Microsoft SQL Server, MySQL, PostgreSQL, ....) They won't all have the same Memory Architecture, but similar enough for you to understand how memory is managed in a DMBS (Database Management System).

Good luck.