Re: Cache a table

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 24 Oct 2003 06:00:08 +1000
Message-Id: <3f983313$0$21650$afc38c87@news.optusnet.com.au>

Richard Foote wrote:

[large snip]

>> [That's a big table now, with 49,680 rows, and approx. 10M in size]
>>
>> startup force;
>> select * from scott.emp [14 rows... a small table, as you specified]
>> select * from t1 (wait for the scrolling to stop)
>>
>> select count(*) from v$bh where file#=7;
>> COUNT(*)
>> --------
>>     2304
>>

[ditto]

Hi Richard,

I've not snipped any of your post, because it's a model of clarity and accuracy, and should stick around for a while for the benefit of newbies who might otherwise think the Buffer Cache is a waste of time!

Given that my buffer cache in the test above has about 6000 buffers, only 2304 of which were my T1 table at the end of the FTS, what you describe is, of course, quite correct.

If that's what "Geomancer" was trying to say, then fair enough. I think his talk of the PGA completely threw me, however!

Regards
HJR
>
> Hi Howard,
>
> Reading FTS data directly into the PGA and bypassing the buffer cache is
> news to me as well (and of course easily proven to be untrue by querying
> x$bh).
>
> However, just to perhaps explains things a little further, the algorithm
> used to determine how a block "ages" within the buffer cache is a little
> more complicated than a simple LRU list, although there is still the
> concept of an LRU. Oracle now splits the buffer caches into two sections,
> a "hot" and a "cold" section and Oracle determines which section a block
> belongs in by a combination of the number of times the blocks have been
> "touched" and the last time the block was touched (these values can be
> seen in the TCH and TIM columns in x$bh).
>
> Now if a block has been touched sufficient times, it earns the right to
> move to the "hot" end and head the MLU list but unless they keep getting
> touched start to move down and age . All these settings can be viewed and
> modified
> (not that I would recommend it) with the _DB_AGING_% and _DB_PERCENT_%
> list of parameters.
>
> The result of all this means that frequently accessed blocks can be
> protected from being "dislodged" (not sure if it's the term I would used
> but I know what you mean ;) by large FTS because it's only the cold
> portion of the buffer cache that is going to be initially affected.
>
> When a block is read in via an index, the block is kinda loaded into the
> MRU bit of the cold section of the buffer cache (roughly the "middle" of
> the LRU list so to speak). However, when a block is loaded via a FTS, this
> behaviour differs and the blocks are loaded into the LRU bit of the buffer
> cache. This is designed to prevent the "dislodgement" of potentially
> useful stuff at the colder end of the LRU. Therefore the effect of a FTS
> by nocached tables is minimal, an important point.
>
> Simple demo on 9.2, the Bowie table is approximately 13,000 blocks, small
> is 117 blocks:
>
> SQL> alter table bowie nocache;
>
> Table altered.
>
> SQL> select object_name, object_id, data_object_id from dba_objects where
> object
> _name in ('BOWIE', 'SMALL');
>
> OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
> --------------- ---------- --------------
> BOWIE 31379 31379
> SMALL 31457 31457
>
> SQL> select * from bowie; (run with autotrace traceonly)
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> ----------
> 18
>
> Note that only the last few blocks from the FTS actually remain in memory.
> If I repeat the select, I still have the same result from x$bh and the
> same number of *physical reads" occur each time.
>
> If I run the same thing with my "small" table which has about 117 blocks,
> the same thing happens ....
>
> SQL> alter table small nocache;
>
> Table altered.
>
> SQL> select * from small;
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> ----------
> 18
>
> Note that again only the last few blocks from the FTS actually remain in
> memory. If I repeat the select, I still have the same result from x$bh and
> again the same number of physical reads occur each time.
>
> OK, lets change my small table and cache the thing and see if I get a
> different result ...
>
> SQL> alter table small cache;
>
> Table altered.
>
> SQL> select * from small;
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> ----------
> 117
>
> I now see that all 117 blocks (that's all data blocks + segment header)
> are all now cached as expected. Repeated reruns of the select now generate
> *no* physical I/Os.
>
> But what if I now run a select on my "big" BOWIE table, what effect will
> this have on the SMALL cached blocks ?
>
> SQL> select * from bowie;
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> ----------
> 18
>
> Nothing new here, only the last few blocks again remain from the BOWIE
> table with the same physical I/Os generated.
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> ----------
> 117
>
> and thankfully nothing has changed with the SMALL table as a result. These
> blocks still remain cached and have not been "dislodged" as a result of
> the FTS on the big BOWIE table (as they sit safely somewhere near the
> middle, cold side of the LRU
>
> Finally, what if we play silly buggers and decide to cache the big BOWIE
> table ...
>
> SQL> alter table bowie cache;
>
> Table altered.
>
> SQL> select * from bowie;
>
> SQL> select count(*) from x$bh where obj=31379;
>
> COUNT(*)
> ----------
> 1338
>
> We now see that a whole heap of buffers have now been cached,
> approximately
> 10%. However, again the physical I/Os remain constant because we are
> still not effectively caching the table (the undocumented parameters
> behind the scene kick in to prevent the whole cache from flooding).
>
> But the effect on poor SMALL...
>
> SQL> select count(*) from x$bh where obj=31457;
>
> COUNT(*)
> ----------
> 1
>
> only one poor block (the header) has survived the experience :(
>
> So the touch aging LRU algorithm, the various hot/cold portions of the
> buffer cache and whether a table is cached or nocached all have an effect
> on how the end object is actually cached.
>
> It's only an intro but it's a start to any newbies listening in :)
>
> Cheers
>
> Richard
Received on Thu Oct 23 2003 - 15:00:08 CDT