Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cache a table

Re: Cache a table

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 23 Oct 2003 14:24:05 GMT
Message-ID: <9wRlb.162694$bo1.108850@news-server.bigpond.net.au>


"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:3f97c125$0$24515$afc38c87_at_news.optusnet.com.au...
> Howard J. Rogers wrote:
>
> > Geomancer wrote:
> >
> >> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> >> news:<3f9660e8$0$9554$afc38c87_at_news.optusnet.com.au>...
> >>
> >>> Disagree. The issue is not whether it would tend to want to stay in
the
> >>> buffer cache or not, but whether it is at risk of being dislodged by a
> >>> rogue, huge, tablescan.
> >>
> >> Howard, "Dislodged" is another of those Myths you hate so much!
> >>
> >> In Oracle9i, full-table scans changed to make FTS rows go directly
> >> into the PGA for the user, completely bypassing the block cache.
> >>
> >> Remember, back in Oracle7 there was a special recycle area at the LRU
> >> end of the cache for FTS, (which could NEVER page-out MRU blocks).
> >>
> >> This was changed in 9i to put FTS blocks directly into the PGA because
> >> Oracle knew that no other task would use them.
> >>
> >> In Oracle's OTN site we see the "Oracle Expert" article (your beloved
> >> Mr. Niemeic) state "a full table scan is put at the cold end of the
> >> LRU (Least Recently Used) list."
> >>
> >> I verified this with a simple experiment. Start a fresh instance,
> >> read a small table, then do an FTS gainist a huge tables and run a
> >> query against v$bh. You will NOTsee the FTS rows in the data buffer!
> >>
> >> It seems that this "dislodging" may be one of those urban myths!
>
> Having re-read your post several times, I still can't quite work out what
> you're on about, so here's a test I've just done on Oracle 9i Release 2,
> Red Hat 9:
>
> create tablespace bhtest
> datafile '/u01/app/oracle/oradata/lx92/bh01.dbf' size 10m;
>
> select file#, name from v$datafile;
> SQL> select file#,name from v$datafile;
>
> FILE# NAME
> --------------------------------------------------------------------------



> 1 /u01/app/oracle/oradata/lx92/system01.dbf
> 2 /u01/app/oracle/oradata/lx92/undotbs01.dbf
> 3 /u01/app/oracle/oradata/lx92/example01.dbf
> 4 /u01/app/oracle/oradata/lx92/indx01.dbf
> 5 /u01/app/oracle/oradata/lx92/tools01.dbf
> 6 /u01/app/oracle/oradata/lx92/users01.dbf
> 7 /u01/app/oracle/oradata/lx92/bh01.dbf
>
> [You might note the new datafile is number 7]
>
> create table t1 tablespace bhtest
> as select * from dba_objects; (7000+ rows inserted)
>
> insert into t1 select * from t1 (repeat until you run out of space)
> commit;
>
> [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
>
> Since datafile 7's only just been created, and since it only has one table
> in it, and since I did a small scan and then a FTS like you asked, what
> were you saying about blocks from a FTS not going into the buffer cache??
>
> By the way, the blocks from EMP were no longer in my (non-multi-pool)
cache:
> they'd been "dislodged" by the FTS.
>
> You might want to repeat your test.
>

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 - 09:24:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US