Oracle Database - (Memory) Cache (Tables, ...)

> Database > Oracle Database

2 - Purge

Set the tablespace offline and online

ALTER TABLESPACE <tablespace_name> OFFLINE;
ALTER TABLESPACE <tablespace_name> ONLINE;

or flush the buffer cache

ALTER SYSTEM FLUSH BUFFER_CACHE;

or flush the shared pool

ALTER SYSTEM FLUSH SHARED_POOL;
Advertising

3 - Caching criteria

Small tables are automatically cached according to this criteria :

Table Caching Criteria

Table Size Size Criteria Caching
Small Number of blocks < 20 or 2% of total cached blocks, whichever is larger If STATISTICS_LEVEL is see to TYPICAL or higher, Oracle decides whether to cache a table depending on the table scan history. The table is cached only if a future table scan is likely to find the cached blocks. If STATISTICS_LEVEL is set to BASIC, the table is not cached.
Medium Larger than a small table, but < 10% of total cached blocks Oracle decides whether to cache a table based on its table scan and workload history. It caches the table only if a future table scan is likely to find the cached blocks.
Large > 10% of total cached blocks Not cached

4 - Configuration

4.1 - Attribute

Automatic caching of small tables is disabled for tables that are created or altered with the CACHE attribute.

4.2 - Hint

You can use the CACHE and NOCACHE hints to indicate where the retrieved blocks are placed in the buffer cache. The CACHE hint instructs the optimizer to place the retrieved blocks at the most recently used end of the LRU list in the buffer cache when a full table scan is performed.

4.3 - Parameter

If STATISTICS_LEVEL is see to TYPICAL or higher, Oracle decides whether to cache a table depending on the table scan history. The table is cached only if a future table scan is likely to find the cached blocks. If STATISTICS_LEVEL is set to BASIC, the table is not cached.

Advertising
db/oracle/cache.txt · Last modified: 2017/09/06 19:30 by gerardnico