big table - cheeyoung/sqlplus-public GitHub Wiki
| size | |
|---|---|
| big | |
| medium | |
| small | not tracked |
Views
8.109 V$BT_SCAN_OBJ_TEMPS
8.108 V$BT_SCAN_CACHE
Parameters
2.71 DB_BIG_TABLE_CACHE_PERCENT_TARGET
If a large table is about the size of the combined size of the big table cache of all instances, the table will be partitioned and cached or mostly cached on all instances.
Wait Events
C.3.36 direct path read
C.3.38 direct path write
C.3.37 direct path sync
References
8.3.1 Buffer Cache Usage in Parallel Execution
By default parallel execution does not use the SGA (buffer cache) to cache the scanned blocks unless the object is very small or is declared as CACHE.
In-Memory Parallel Execution, enabled by setting the parameter PARALLEL_DEGREE_POLICY is set to AUTO, enables parallel statements to leverage the SGA to cache object blocks. Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA. The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle Real Applications Cluster (Oracle RAC) environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. It does this by using PX servers on the instances where the blocks are cached.
If the size of the object is larger than a specific threshold value based on the total size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads and not cached in the SGA.
8.3.2 Automatic Big Table Caching
1.4 Automatic Big Table Caching
This big table cache provides significant performance improvements for full table scans on tables that do not fit entirely into the buffer cache.