Professional Web Applications Themes

Cache a table - Oracle Server

Hi all, when you would cache a table. what are the general rules if the amount of memory isn't a problem... tia Kalle...

  1. #1

    Default Cache a table

    Hi all,

    when you would cache a table. what are the general rules if the amount
    of memory isn't a problem...

    tia
    Kalle

    Kalle Guest

  2. #2

    Default Re: Cache a table

    Kalle wrote:
     

    The general rule is don't bother.

    Well, OK... what I mean is that if you are talking about the 'CACHE' clause
    of the alter table statement, or create table, statement, don't bother. It
    is a pretty poor way of achieving better performance, and has long since
    been superceded by the multiple buffer pools feature (first introduced in
    Oracle 8.0).

    And the rules of thumb for those multiple buffer pools are:

    tables smaller than 10% of your default pool are candidates for housing in
    the keep pool

    Tables bigger than 200% of your keep pool are candidates for housing in the
    recycle pool.

    But those are only rules of thumb.

    Regards
    HJR
    --
    --------------------------------------------
    See my brand new website, soon to be full of
    new articles: www.dizwell.com.
    Nothing much there yet, but give it time!!
    --------------------------------------------

    Howard Guest

  3. #3

    Default Re: Cache a table

    "Howard J. Rogers" <com> wrote in
    news:3f95066c$0$10411$optusnet.com.au:
     
    >
    > The general rule is don't bother.
    >
    > Well, OK... what I mean is that if you are talking about the 'CACHE'
    > clause of the alter table statement, or create table, statement, don't
    > bother. It is a pretty poor way of achieving better performance, and
    > has long since been superceded by the multiple buffer pools feature
    > (first introduced in Oracle 8.0).
    >
    > And the rules of thumb for those multiple buffer pools are:
    >
    > tables smaller than 10% of your default pool are candidates for
    > housing in the keep pool
    >
    > Tables bigger than 200% of your keep pool are candidates for housing
    > in the recycle pool.
    >
    > But those are only rules of thumb.
    >
    > Regards
    > HJR[/ref]

    And I would add that for the keep pool, you should only include tables
    that are frequently scanned. No point in wasting memory on a table that
    isn't, even though it meets the ROT for size.

    I can think of one case where using the pre-8.0 style of caching might
    apply. That would be a small table that is only used in a long running
    batch job that does frequent scans of that table. It would end up being
    cached in memory for the duration of the batch job, but eventually
    dropped out of memory when the job finishes.
    Chuck Guest

  4. #4

    Default Re: Cache a table


    "Chuck" <net> wrote in message news:133.1.4... 
    > >
    > > The general rule is don't bother.
    > >
    > > Well, OK... what I mean is that if you are talking about the 'CACHE'
    > > clause of the alter table statement, or create table, statement, don't
    > > bother. It is a pretty poor way of achieving better performance, and
    > > has long since been superceded by the multiple buffer pools feature
    > > (first introduced in Oracle 8.0).
    > >
    > > And the rules of thumb for those multiple buffer pools are:
    > >
    > > tables smaller than 10% of your default pool are candidates for
    > > housing in the keep pool
    > >
    > > Tables bigger than 200% of your keep pool are candidates for housing
    > > in the recycle pool.
    > >
    > > But those are only rules of thumb.
    > >
    > > Regards
    > > HJR[/ref]
    >
    > And I would add that for the keep pool, you should only include tables
    > that are frequently scanned. No point in wasting memory on a table that
    > isn't, even though it meets the ROT for size.
    >
    > I can think of one case where using the pre-8.0 style of caching might
    > apply. That would be a small table that is only used in a long running
    > batch job that does frequent scans of that table. It would end up being
    > cached in memory for the duration of the batch job, but eventually
    > dropped out of memory when the job finishes.[/ref]

    If a table is frequently scanned (i.e. very hot), it is going to remain in the
    buffer pool no matter what. Its no good use putting it in the KEEP pool.

    The tables which are medium-high in scans are better candidates
    for the KEEP pool.

    Anurag


    Anurag Guest

  5. #5

    Default Re: Cache a table

    Anurag Varma wrote:
     

    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. By putting even a frequently-accessed table into
    the keep pool, you ensure it can't be dislodged by scans against large
    tables (assuming you haven't been daft enough to ask for them to go into
    the keep pool as well, of course!!).

    Such cache 'partitioning' has got to be a good thing in its own right.

    Regards
    HJR
    --
    --------------------------------------------
    See my brand new website, soon to be full of
    new articles: www.dizwell.com.
    Nothing much there yet, but give it time!!
    --------------------------------------------

    Howard Guest

  6. #6

    Default Re: Cache a table


    "Howard J. Rogers" <com> wrote in message news:3f9660e8$0$9554$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. By putting even a frequently-accessed table into
    > the keep pool, you ensure it can't be dislodged by scans against large
    > tables (assuming you haven't been daft enough to ask for them to go into
    > the keep pool as well, of course!!).
    >
    > Such cache 'partitioning' has got to be a good thing in its own right.
    >
    > Regards
    > HJR
    > --
    > --------------------------------------------
    > See my brand new website, soon to be full of
    > new articles: www.dizwell.com.
    > Nothing much there yet, but give it time!!
    > --------------------------------------------
    >[/ref]

    Howard,

    In your argument I would ask why that big table is not put in the recycle pool.
    If a segment is extremely hot, then random access to large segments should
    not displace its cached buffer. After all a buffer block earns its place by
    touch count.

    However, warm-hot segments can be in danger of their blocks being displaced
    by a large segment read.

    Now in a datawarehouse, pretty much most of the dimension tables and especially
    their indexes should probably be in the Keep Pool.
    While the fact tables probably should be kept in the recycle pool.

    Although, I would agree that if the database shows unpredictable activity of
    ad-hoc users making large/medium table scans on a regular basis, then
    the advice of putting extremely hot segments in the keep pool makes more sense.

    Anurag

    Anurag


    Anurag Guest

  7. #7

    Default Re: Cache a table

    "Howard J. Rogers" <com> wrote in
    news:3f9660e8$0$9554$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. By putting even a frequently-accessed
    > table into the keep pool, you ensure it can't be dislodged by scans
    > against large tables (assuming you haven't been daft enough to ask for
    > them to go into the keep pool as well, of course!!).
    >
    > Such cache 'partitioning' has got to be a good thing in its own right.
    >
    > Regards
    > HJR[/ref]

    Or whether it will dislodge other things in the buffer cache that would
    otherwise still be there if it were placed in the keep pool to start
    with.
    Chuck Guest

  8. #8

    Default Re: Cache a table

     


    Excuse my ignorance, but what is ROT?

    Rene


    --
    Rene Nyffenegger
    http://www.adp-gmbh.ch
    Rene Guest

  9. #9

    Default Re: Cache a table

    Rule of Thumb?

    Brian

    Rene Nyffenegger wrote: 
    >
    > Excuse my ignorance, but what is ROT?
    >
    > Rene
    >
    >
    > --
    > Rene Nyffenegger
    > http://www.adp-gmbh.ch[/ref]

    --
    ================================================== =================

    Brian Peasland
    peasland.com

    Remove the "remove_spam." from the email address to email me.


    "I can give it to you cheap, quick, and good. Now pick two out of
    the three"
    Brian Guest

  10. #10

    Default Re: Cache a table

     

    Yes, that makes sense.
    Thanks.
     
    >>
    >> Excuse my ignorance, but what is ROT?
    >>
    >> Rene
    >>
    >>
    >> --
    >> Rene Nyffenegger
    >> http://www.adp-gmbh.ch[/ref]
    >[/ref]


    --
    Rene Nyffenegger
    http://www.adp-gmbh.ch
    Rene Guest

  11. #11

    Default Re: Cache a table

    "Howard J. Rogers" <com> wrote in message news:<3f9660e8$0$9554$optusnet.com.au>...
     

    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!
    Geomancer Guest

  12. #12

    Default Re: Cache a table

    Geomancer wrote:
     
    >
    > 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![/ref]


    I'm not enitrely sure what you're getting at, but none of this makes much
    sense. They invented the recycle buffer precisely so that full table scans
    can find a home in the Buffer Cache which wouldn't 'dislodge' other buffers
    of more long-lasting use.

    The idea that Oracle refuses to cache blocks read via FTS is just silly: how
    could Oracle "know no other task would use them"? We might just as well
    abolish the buffer cache entirely and have done with it, if that were
    really true.

    There *are* direct reads which by-pass the buffer cache, but a full table
    scan against a regular table wouldn't be one of them.

    If you could be more precise about your sources, or what you are suggesting,
    I'll happily discuss it, but generally FTSes go via the buffer cache.

    Regards
    HJR
    --
    --------------------------------------------
    See my brand new website, soon to be full of
    new articles: www.dizwell.com.
    Nothing much there yet, but give it time!!
    --------------------------------------------

    Howard Guest

  13. #13

    Default Re: Cache a table

    Howard J. Rogers wrote:
     
    >>
    >> 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![/ref][/ref]

    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.

    Regards
    HJR
    --
    --------------------------------------------
    See my brand new website, soon to be full of
    new articles: www.dizwell.com.
    Nothing much there yet, but give it time!!
    --------------------------------------------

    Howard Guest

  14. #14

    Default Re: Cache a table

    "Howard J. Rogers" <com> wrote in message
    news:3f97c125$0$24515$optusnet.com.au... [/ref][/ref]
    the [/ref]
    >
    > 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
    > --------------------------------------------------------------------------[/ref]
    ------ 
    cache: 

    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 undoented 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


    Richard Guest

  15. #15

    Default Re: Cache a table

    Howard,

    Could you elaborate 'direct read' more? I know reading LOB is a kind of 'direct read', anything
    else?

    Thanks.
    Jack

    "Howard J. Rogers" <com> wrote in message
    news:3f979d65$0$24515$optusnet.com.au... 
    > >
    > > 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![/ref]
    >
    >
    > I'm not enitrely sure what you're getting at, but none of this makes much
    > sense. They invented the recycle buffer precisely so that full table scans
    > can find a home in the Buffer Cache which wouldn't 'dislodge' other buffers
    > of more long-lasting use.
    >
    > The idea that Oracle refuses to cache blocks read via FTS is just silly: how
    > could Oracle "know no other task would use them"? We might just as well
    > abolish the buffer cache entirely and have done with it, if that were
    > really true.
    >
    > There *are* direct reads which by-pass the buffer cache, but a full table
    > scan against a regular table wouldn't be one of them.
    >
    > If you could be more precise about your sources, or what you are suggesting,
    > I'll happily discuss it, but generally FTSes go via the buffer cache.
    >
    > Regards
    > HJR
    > --
    > --------------------------------------------
    > See my brand new website, soon to be full of
    > new articles: www.dizwell.com.
    > Nothing much there yet, but give it time!!
    > --------------------------------------------
    >[/ref]


    Jack Guest

  16. #16

    Default Re: Cache a table

    Richard Foote wrote:

    [large snip] [/ref]
    [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


     


    Howard Guest

  17. #17

    Default Re: Cache a table

    VERY impressive examples.

    I got the PGA thing at OracleWorld from someone in the Oracle
    real-rorld performance group, but I must have misunderstood it.

    Let me check my notes and get back to you.
    Geomancer Guest

  18. #18

    Default Re: Cache a table


    It is possible that the person was
    talking about a feature for
    'serial reads direct'
    which can be enabled through a
    hidden parameter.


    --
    Regards

    Jonathan Lewis
    http://www.jlcomp.demon.co.uk

    The educated person is not the person
    who can answer the questions, but the
    person who can question the answers -- T. Schick Jr


    One-day tutorials:
    http://www.jlcomp.demon.co.uk/tutorial.html
    ____Belgium__November (EOUG event - "Troubleshooting")
    ____UK_______December (UKOUG conference - "CBO")


    Three-day seminar:
    see http://www.jlcomp.demon.co.uk/seminar.html
    ____UK___November


    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html


    "Geomancer" <com> wrote in message
    news:google.com... 


    Jonathan Guest

  19. #19

    Default Re: Cache a table


    Note in-line

    --
    Regards

    Jonathan Lewis
    http://www.jlcomp.demon.co.uk

    The educated person is not the person
    who can answer the questions, but the
    person who can question the answers -- T. Schick Jr


    One-day tutorials:
    http://www.jlcomp.demon.co.uk/tutorial.html
    ____Belgium__November (EOUG event - "Troubleshooting")
    ____UK_______December (UKOUG conference - "CBO")


    Three-day seminar:
    see http://www.jlcomp.demon.co.uk/seminar.html
    ____UK___November


    The Co-operative Oracle Users' FAQ
    http://www.jlcomp.demon.co.uk/faq/ind_faq.html


    "Anurag Varma" <com> wrote in message
    news:E7plb.14636$roc.ny... 
    remain in the 
    pool. 

    A little known detail about tablescans in 8.1 (and probably 9.2 but I
    don't recall confirming it) is that the touch count on blocks subject
    to tablescans is NOT increased - so even if a "small" table is loaded
    into the middle of the LRU chain and repeatedly scanned, it will
    always end up falling off the end of the LRU chain as other blocks
    are read into the buffer. It never gets promoted to the hot end unless
    it is also subject to indexed accesses.








    Jonathan Guest

  20. #20

    Default Re: Cache a table

    "Jonathan Lewis" <demon.co.uk> wrote in message
    news:bne9sm$kog$2$demon.co.uk... 
    > remain in the 
    > pool. 
    >
    > A little known detail about tablescans in 8.1 (and probably 9.2 but I
    > don't recall confirming it) is that the touch count on blocks subject
    > to tablescans is NOT increased - so even if a "small" table is loaded
    > into the middle of the LRU chain and repeatedly scanned, it will
    > always end up falling off the end of the LRU chain as other blocks
    > are read into the buffer. It never gets promoted to the hot end unless
    > it is also subject to indexed accesses.
    >[/ref]

    Hi Jonathan,

    That's a good point. Only the segment header which is read via a single
    block read is subject to a touch count increment during a tablescan.

    And yes, it's still correct in 9.2.

    Cheers

    Richard


    Richard Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 0
    Last Post: September 16th, 04:37 PM
  2. Replies: 0
    Last Post: September 15th, 05:39 AM
  3. Replies: 0
    Last Post: September 10th, 10:49 PM
  4. Replies: 0
    Last Post: September 10th, 05:29 PM
  5. Replies: 2
    Last Post: August 12th, 07:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139