KEEP Pool and full-table scans?

Hi Franklin,

By default, direct reads into the PGA are only used for parallel table scans (from 7.1.6).
Serial full table scans go through the cache by default unless event 10355 (from 7.3.4)
or the '_serial_direct_read' parameter (9.0.1) is set.

There is also an '_adaptive_direct_read' parameter (9.0.1) that by default ensures that small table scans are cached even if serial direct reads have been requested as above.

The default small table threshold is 2% of the number of buffers (not 20 buffers) but it can be set with _small_table_threshold. Tables with the CACHE keyword specified and/or tables cached in the KEEP buffer pool are also treated as small (from 8.1.5) but this is not reflected in the V$SYSSTAT statistics.

There is a bug with the touch count mechanism such that access via short scans is never counted, and so these buffers can age out if the KEEP buffer pool is oversubscribed, and that might result in buffer busy waits.

Despite all this, it is seldom a good idea (performance wise) to plan frequent short table scans. Index or hash based access is invariably much more efficient.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/ - For DBAs
@ http://www.christianity.net.au/ - For all

-----Original Message-----
From: Franklin [mailto:member29243_at_dbforums.com] Sent: Tuesday, 26 August 2003 11:13 AM
To: comp.databases.oracle.server
Subject: KEEP Pool and full-table scans?

I understand that Oracle will read full-table scan data blocks directly into the PGA, byassing the data buffer.

If this is true, is there a way to cache ( using the KEEP pool ), small tables (less than 20 data blocks) that experience frequent fulltable  scans?

If I KEEP the small table, will Oracle still read it from the data file into the PGA?

--
Posted via http://dbforums.com