Oracle Database - (Memory) Cache (Tables, )

Card Puncher Data Processing

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;

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

Configuration

Attribute

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

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.

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.





Discover More
Card Puncher Data Processing
Oracle Database - Data Files

Every Oracle database has one or more physical datafiles (OS File), which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the...
Card Puncher Data Processing
Oracle Database - Full Table Scans

in Oracle During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause. When Oracle...



Share this page:
Follow us:
Task Runner