2

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?

4
  • 3
    In a nutshell, "no" you can't measure SGA usage that on a per-user basis, as these system-level caches are meant to allow re-use of data between users (shared) and there is no dedicated per-user usage to measure there.
    – pmdba
    Aug 24, 2023 at 10:10
  • Why do you think that knowing how much space each "user's queries are occupying [in] the buffer cache" will be helpful?
    – mustaccio
    Aug 24, 2023 at 12:04
  • @mustaccio For a database monitoring project at my university. My professor asked for that data, but everything points to the fact that it is impossible.
    – Isaac PM
    Aug 24, 2023 at 16:32
  • Buffer cache is a shared resource, possibly for many users. No user is associated with any specific buffer cache. OS top results could be analyzed and if you know the useridenty you could observe VIRT RES SHR memory for each process of the user that happened to be recently active. Sep 5, 2023 at 0:35

1 Answer 1

4

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:

Oracle Database Memory Structures

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.

2
  • Hi, @Juan K.N., I thank you very much for your reply. And I understand the design decisions that prevent me from getting this data. However, I need this information (or an approximation of it), so I have done certain workarounds, but I am not sure if the methodology is correct. Basically, I can get from V$OPEN_CURSOR, what is the amount of cursors a user has in cache (as I understand, filtering by "SESSION CURSOR CACHED"). Is there any way to get how much memory each cursor occupies? Or are these cursors not related to the buffer cache?
    – Isaac PM
    Aug 24, 2023 at 16:28
  • 1
    @Isaac PM - v$open_cursor will only identify which cursors a user has open. While you can join from there to v$sql to get the amount of shared pool memory those cursors require, you cannot attribute any of that to any one session or user. The whole point of shared memory is that it's shared between lots of sessions and users. Even if you could, this would only give you shared memory for cursors, which is totally different than the buffers question you originally asked. The only memory statistic you can measure on a per-user basis is PGA usage (see v$process).
    – Paul W
    Aug 27, 2023 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.