Re: KEEP Pool and full-table scans?

Hi Steve,

When I query my NT 9iR2 db for the parameter _small_table_threshold, the value is 95.

I didn't understand the value of 2% of the number of buffers.

Thanks

Dias

"Steve Adams" <steve.adams_at_ixora.com.au> wrote in message news:<005201c36ba5$30690270$ae8735cb_at_ixora.com.au>...
> 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 full-
> table scans?
>
>
>
> If I KEEP the small table, will Oracle still read it from the data file
> into the PGA?
Received on Wed Aug 27 2003 - 07:24:08 CDT