This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jan 20, 2013 6:29 PM by 636309 Go to original post RSS
  • 15. Re: querying data from the buffer_cache
    636309 Newbie
    Currently Being Moderated
    JohnWatson wrote:
    You need to read up on the CACHE clause of CREATE TABLE (or ALTER TABLE) and the effect of setting CACHE or NOCACHE for large and small tables. Hint: by default, your tables are not being cached.
    Sorry John, but based on the article below, the CACHE option that you've described only determines if the data from a full table scan will be listed as most recently or least recently used blocks in the buffer. The hint that you've provided suggests that the CACHE option determines whether or not data will be cached.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:253415112676
  • 16. Re: querying data from the buffer_cache
    Girish Sharma Guru
    Currently Being Moderated
    I thought that once the data is loaded into the buffer_cache, the query wouldn't scan the hard disk for data. Could someone please help me understand what is happening?
    Query is doing huge reads on both the tables and and bringing huge number of blocks into buffer cache by doing full table scan (as your execution plan shows). If you thinks that once data s loaded into buffer cache, oracle will not physical read for the query output because data is in buffer cache; then I think its wrong. There is rule of small table scan whose result remains in buffer cache until a limited time period. The definition of a small table is the maximum of 2% of the buffer cache and 20 blocks, whichever is bigger. (http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm#i45097) in Oracle version 11g. In earlier version this 2% is the same while 20 blocks were reduced to 4 blocks.

    But, the buffer cache is fairly efficient. If it was meant to be cached, and the memory exists, it will be cached. small, frequently accessed lookup tables would be in the cache by "popular demand" 99% of the time already. (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:253415112676)

    If the table is small compared to the amount of memory available for the default buffer cache, and the blocks are frequently accessed, Oracle will automatically keep the blocks in default buffer cache without you having to make any changes (such as setting up a KEEP cache and altering the table to use the KEEP cache [buffer pool]).
    [url http://dbaspot.com/oracle-server/376495-how-keep-table-memory-oracle-10g.html]Charles Hooper

    Regards
    Girish Sharma
  • 17. Re: querying data from the buffer_cache
    JohnWatson Guru
    Currently Being Moderated
    arizona9952 wrote:
    JohnWatson wrote:
    You need to read up on the CACHE clause of CREATE TABLE (or ALTER TABLE) and the effect of setting CACHE or NOCACHE for large and small tables. Hint: by default, your tables are not being cached.
    Sorry John, but based on the article below, the CACHE option that you've described only determines if the data from a full table scan will be listed as most recently or least recently used blocks in the buffer. The hint that you've provided suggests that the CACHE option determines whether or not data will be cached.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:253415112676
    You have proved that full table scanned blocks of large tables do not remain in the cache. That article says why not: because they are put at the bottom of the LRU list, rater than in the middle. THat is what the CACHE clause, default NO, controls. As I said. All clear now?

    Trust me. I'm a DBA.
  • 18. Re: querying data from the buffer_cache
    Aman.... Oracle ACE
    Currently Being Moderated
    Girish,
    In earlier version this 2% is the same while 20 blocks were reduced to 4 blocks.
    In which release this was happening? I checked in 817 and it's not mentioned or may be I didn't look hard enough?

    Aman....
  • 19. Re: querying data from the buffer_cache
    Girish Sharma Guru
    Currently Being Moderated
    Aman,

    A "short" table is defined here as one with a high water mark lower than:

    4 blocks or 2% of the db block buffer cache for normal tables

    Tom Kyte answered for one question of OP for version 8.1.5
    http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:255215154182

    Regards
    Girish Sharma
  • 20. Re: querying data from the buffer_cache
    JohnWatson Guru
    Currently Being Moderated
    I've been trying to demonstrate the behaviour with code such as this on my 11.2.0.3
    alter session set "_serial_direct_read"=never;
    alter session set parallel_degree_policy=manual;
    alter session set "_small_table_threshold"=1;
    
    drop table tcache;
    drop table tnocache;
    
    create table tcache (c1 char (1000)) cache;
    create table tnocache (c1 char(1000)) nocache;
    insert into tcache (select 'a' from dual connect by level < 10000);
    insert into tnocache (select 'a' from dual connect by level < 10000);
    exec dbms_stats.gather_table_stats(user,'tcache')
    exec dbms_stats.gather_table_stats(user,'tnocache')
    
    alter system flush shared_pool;
    alter system flush buffer_cache;
    
    set autot trace stat
    select count(*) from tcache;
    select count(*) from tnocache;
    select count(*) from tcache;
    select count(*) from tnocache;
    but no matter what I do with parameters or the sizes of buffer cache and tables I'm getting both tables either cached or not. So I can't prove that I'm right. Or wrong.
  • 21. Re: querying data from the buffer_cache
    Aman.... Oracle ACE
    Currently Being Moderated
    Did you check the links that I had posted earlier John? The parameter's behavior is really complex!

    Regards
    Aman....
  • 22. Re: querying data from the buffer_cache
    Aman.... Oracle ACE
    Currently Being Moderated
    Thanks! So I guess, from 817(may be), the value is modified!

    Regards
    Aman....
  • 23. Re: querying data from the buffer_cache
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    JohnWatson wrote:
    I've been trying to demonstrate the behaviour with code such as this on my 11.2.0.3
    alter system flush buffer_cache;
    but no matter what I do with parameters or the sizes of buffer cache and tables I'm getting both tables either cached or not. So I can't prove that I'm right. Or wrong.

    John,

    One of the diffficulties we face when trying to work out how table size affects caching on full tablescans is that the algorithms exist to protect blocks from being discarded from the cache by excessive scanning - but if you flush the buffer cache before testing then there are no blocks needing protection, and the algorithm has a branch that takes advantage of that fact to load more blocks into the cache.

    When I want to (try to) work out the latest variations I run a routine that takes about 30 seconds to load the cache with a combination of (forced) very large index range scans with random I/Os to table, and repeated small index range scans to other tables. The large range scans give me lots of blocks with low touch counts that keep dropping off the LRU, the repeated smaller scans give me blocks with high touch counts that get promoted - but you have to keep repeating the range scans with at least a 3 second gap to make the count go up. The large range scans then push the popular data to the end of the LRU so that the popular blocks can be promoted.

    Major pain in the backside, and I find that I have to modify the code just about every time I revisit it.

    Regards
    Jonathan Lewis
  • 24. Re: querying data from the buffer_cache
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Girish Sharma wrote:

    Tom Kyte answered for one question of OP for version 8.1.5
    That's SO last century ;)
    Really, even the things that Tom Kyte said can go out of date - especially when they're three version and several years ago.
    Remember my rules for trust: http://jonathanlewis.wordpress.com/2006/11/13/trust/

    Regards
    Jonathan Lewis
  • 25. Re: querying data from the buffer_cache
    JohnWatson Guru
    Currently Being Moderated
    Jonathan Lewis wrote:
    One of the diffficulties we face when trying to work out how table size affects caching on full tablescans is that the algorithms exist to protect blocks from being discarded from the cache by excessive scanning - but if you flush the buffer cache before testing then there are no blocks needing protection, and the algorithm has a branch that takes advantage of that fact to load more blocks into the cache.
    Oracle is cleverer than I realized. Thank you for pointing this out, if I ever do get a repeatable test methodology for this I'll post it back here.

    And in the meantime:

    @arizona9952, I hope you see why it is more than likely that your repeated scans of large tables will always come from disc. The only exception to this that I can see would be if you were to cache the tables in a keep pool, as stefan suggested.
    (incidentally, I should warn you that the situation becomes more complicated in the current release, due to in part to changes in parallel processing and serial direct i/o)
  • 26. Re: querying data from the buffer_cache
    636309 Newbie
    Currently Being Moderated
    JohnWatson wrote:
    @arizona9952, I hope you see why it is more than likely that your repeated scans of large tables will always come from disc.
    Ok, I think I got the main idea here. Thanks!
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points