Articles Related
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.