Hello guys,
a little question about the buffer cache (of sga) and Full Table Scans / Parallel Queries.
Source:
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/memory.htm
When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.
Please correct me if i missunderstand this documention. A little example.
A table round about 2 GB and a buffer cache with about 500 MB. Now if i perform a full table scan on this table, all 2 GBs are loaded into the buffer cache? Is this right?
In this case it is irrelevant, if oracle puts the blocks on the lru end or on the mru end. The full table scan is eliminating all other blocks in the buffer cache - am i right?
Is it possible to setup oracle, that a full table scan is not putting the blocks into the buffer cache?
I mean like bypassing the buffer cache when using "parallel queries" - source:
http://www.dba-oracle.com/art_dbazine_oracle_10g_data_warehouse.htm
While a 30 GB db_cache_size might be appropriate for an OLTP shop or a shop that uses a large working set, a super-large SGA does not benefit data warehouses and decision support systems in which most data access is performed by a parallelized full-table scan. When Oracle performs a parallel full-table scan, the database blocks are read directly into the program global area (PGA), bypassing the data buffer RAM (refer to Figure 4).
Thanks and Regards
Stefan