Re: table doesn't get cached

From: Jonathan Lewis <>
Date: Fri, 18 Oct 2013 22:35:12 +0100
Message-ID: <>

"vsevolod afanassiev" <> wrote in message We have an application where many queries perform full table scan (for various reasons). Data size is small (tables and and indexes = 16 GB), so we configured db_cache_size = 7 GB and hoped that most frequently accessed data will get cached. This works for all tables but one, let's call it TABLE1. It is normal table, non-partitioned, table size is 818 MB. It has 16 columns including 2 LOB columns. LOBS have been defined with in-line storage, however some values exceed threshold (I think around 4000 bytes), so size of one lob segment = 203 MB and another = 1272 MB. We set CACHE property for the table (DBA_TABLES.CACHE='Y') and for the LOB columns (DBA_LOBS.CACHE='YES'). Sill during full table scans number of physical reads is almost equal to the number of buffer gets - example below. Number of INSERT/UPDATE/DELETE for this table is small.

Does the 818 MB include or exclude the LOB segments.

As a single segment 818MB is more than 10% of your cache, which places it in the size where the touch count won't get incremented on a tablescan, so it won't stay in the cache no matter how often you scan it:

It's also worth checking whether you physical reads are direct path or db file scattered reads - if Oracle starts doing the latter on this table that's another reason why it won't get cached, even if you work around the 10% by playing with hidden parameters.



Jonathan Lewis

Author: Oracle Core (Apress 2011)
Received on Fri Oct 18 2013 - 23:35:12 CEST

Original text of this message