Re: table doesn't get cached

"vsevolod afanassiev" <vsevolod.afanassiev_at_gmail.com> wrote in message news:f8b7567d-3259-442d-a855-9b7637f447f3_at_googlegroups.com... 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: http://jonathanlewis.wordpress.com/2011/03/24/small-tables/

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.

-- 

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543