Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, raja.

Asked: December 01, 2020 - 7:57 am UTC

Last updated: December 01, 2020 - 10:23 am UTC

Version: 12c

Viewed 100+ times

You Asked

Hello Team,

does SGA allocate individual buffer pool to each sql statement or it is for set of sql statements?
if one session overloades the pool, how does it effect other sessions querying same table or different tables?

and we said...

There are many parts to the SGA.

The buffer cache is one of these. This is a shared area that many sessions can access. The point of this is to keep frequently read data in memory and avoid physical (disk) reads.

The first time a session reads rows from a table, the database reads them from disk and copies them into the buffer cache (there are exceptions when using full table scans - these can bypass the buffer cache). So when one session reads rows, other sessions reading the same blocks can benefit because they can read the data straight from memory, avoiding disk access.

If every session reads different data, all have to go to disk because the data aren't cached. As each session reads new data, it'll push existing buffers out of the cache (assuming it's full). The database has internal algorithms for deciding which buffers it keeps in the cache.

You can split the buffer cache into several pools (default, keep, recycle) to help manage this case. You do this on an object basis (e.g. read_always_tab goes in the keep pool, read_rarely_tab goes in the recycle pool). In most cases, this is a lot of work for minimal benefit.

For more details, read the SGA overview in the concepts guide:

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/memory-architecture.html#GUID-24EDB8CD-8279-4CED-82AF-642FC01A4A73

These interactive architecture diagrams can also help you understand how the memory areas fit together:

https://www.oracle.com/webfolder/technetwork/tutorials/architecture-diagrams/18/technical-architecture/database-technical-architecture.html

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database