This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Dec 7, 2012 9:32 AM by rp0428 RSS

scripts to identify small tables for keep pool

ji li Pro
Currently Being Moderated
I am using Oracle 11.2 EE and was wondering if anyone would know of or be abvle share some scripts to identify small tables and/or indexes that frequently get full table scans to put in the keep pool.

I've seen a few web pages that discuss this, but really haven't seen any good scripts that are ready to run without having to buy their book.

e.g., http://www.dba-oracle.com/bp/teaser_sga_keep_pool.htm

Thanks in advance.

ji li
  • 1. Re: scripts to identify small tables for keep pool
    936749 Newbie
    Currently Being Moderated
    ji li wrote:
    I am using Oracle 11.2 EE and was wondering if anyone would know of or be abvle share some scripts to identify small tables and/or indexes that frequently get full table scans to put in the keep pool.

    I've seen a few web pages that discuss this, but really haven't seen any good scripts that are ready to run without having to buy their book.

    e.g., http://www.dba-oracle.com/bp/teaser_sga_keep_pool.htm

    Thanks in advance.

    ji li
    Why would you even consider to pin tables in memory? Which version of database are you using?
  • 2. Re: scripts to identify small tables for keep pool
    Marcus Rangel Journeyer
    Currently Being Moderated
    If they are small and frequently used, Oracle will keep them in memory for you. No need to worry about that.
  • 3. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    To answer your question, here is one of many similar quotes I find all over the internet.

    Examples:
    ======

    If there are certain segments in your application that are referenced frequently, then store the blocks from those segments in a separate cache called the KEEP buffer pool. Memory is allocated to the KEEP buffer pool by setting the parameter DB_KEEP_CACHE_SIZE to the required size. The memory for the KEEP pool is not a subset of the default pool. Typical segments that can be kept are small reference tables that are used frequently.

    The KEEP Buffer Pool

    A CACHE option is available on database tables. This option causes database blocks that are read in from a table during a full table scan to be put onto the MRU (most recently used) end of the LRU (least recently used) list, as opposed to the LRU end. Thus, the data blocks associated with that table remain in the database buffer cache for a longer period of time before being aged out. The idea is to allow for as close to a 100 percent cache hit ratio as possible on these often-hit, smaller tables. The KEEP buffer pool gives you a dedicated segment of database buffer cache memory in which to load the blocks of these tables. Typically, these are smaller tables, such as lookup tables.

    Another method for identifying tables and indexes for the KEEP pool examines the current blocks in the data buffer. For this query, the rules are simple. Any object that has more than 80% of its data blocks in the data buffer should probably be fully cached.
  • 4. Re: scripts to identify small tables for keep pool
    Girish Sharma Guru
    Currently Being Moderated
    May be below two threads :

    Re: Full table scan

    Full table scan

    But, if you are doing SQL tuning just because of full table scan, then its horrible wrong. Full table scans do not necessarily indicate a problem. Example, if my house (table) is big and I wish to walk in each room (full table scan), then definately I will have to walk more, but question is why I am moving in all rooms? If I am looking where last night I kept my sql tuning book, so rather than finding book in like kitchen, bed room, guest room etc.. I should search in study / computer room i.e. something like index scan, because I (CBO) knows where that book (rowid in index) may be. But, if I am looking where last night my daughter kept her study book, then I (CBO) am not sure where she can kept her book (rowid in index), so rather than just searching in her study room (index scan), I think its best to search in all rooms (full table scan) of the house (table).

    Regards
    Girish Sharma
  • 5. Re: scripts to identify small tables for keep pool
    vlethakula Expert
    Currently Being Moderated
    check

    http://www.remote-dba.net/oracle_10g_tuning/t_oracle_keep_pool.htm
  • 6. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Thank you Girish. That was very useful, especially the second link. I like how it actually shows the number of FTSs on particular tables and how big the tables are. It is a perfect script for my purpose.

    In our environment, we have a lot of small tables, and a few large tables that are constantly being pulled into buffer cache thereby causing the smaller tables to be pushed out. If the oracle has to go back to disk frequently to do a full table scan on small tables, then I'm thinking we can improve performance by putting them in the keep pool cache.

    I liked your analogy of your walking around the house. :0)

    I'm actually trying to improve several things, including low library cache_hit ratio, poor cursor_sharing (due to lack of bind variables althought setting cursor_sharing to FORCE might help), and just general performance tuning. For our LC hit ratio, I'm going to try increasing my shared_pool_reserve_size, but I'm wondering if I set shared_pool_size to some value, isn't this doing the same thing? (We are using Automatic Memory Management with memory_target and currently shared_pool_size is 0, but shared_pool_reserve_size is about 600M.)
  • 7. Re: scripts to identify small tables for keep pool
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    ji li wrote:
    I am using Oracle 11.2 EE and was wondering if anyone would know of or be abvle share some scripts to identify small tables and/or indexes that frequently get full table scans to put in the keep pool.
    There was a bug in 8i fixed by 10g (and might even have been fixed in 9i - without looking for my notes I can't be sure) which meant that short tables didn't get their "keep" bit set when scanned. This was the only reason why you might want to identify small tables that were subject to tablescan and allocate them to the keep pool. Even then, it was sensible to check that the tablescans were the sensible access path first.

    Regards
    Jonathan Lewis
  • 8. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Hi Jonathan. Well, I certainly have respect for your insights, but with all due respect, why would you make this statement that goes against so many other sources that recommend caching small tables that are frequently full scanned?

    I'm just curious what rationale you using to contradict what Oracle and so many others recommend.
  • 9. Re: scripts to identify small tables for keep pool
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    ji li wrote:
    Hi Jonathan. Well, I certainly have respect for your insights, but with all due respect, why would you make this statement that goes against so many other sources that recommend caching small tables that are frequently full scanned?

    I'm just curious what rationale you using to contradict what Oracle and so many others recommend.
    The first quote you've given a little further up the thread is from the 11.2 manuals, and says nothing about tablescans, it's talking about reference tables - and reference tables are usually things you access by index. However, having said that, you might note that a little further down the 11.2 manual pages (Chapter 7 of the performance tuning guide) it makes a statement which seems to be far more appropriate to your system: if you have large segments that are subject to large amounts of random I/O then some of your smaller segments that could usefully be kept cached may suffer and need to be protected - the manual then suggests that you could protect such objects by either (a) putting the large segments in the recycle cache, or (b) putting the smaller segments in the keep cache.

    The second quote is from Toadworld and is about Oracle 8i - so you should be apply a little caution to it when considering what to do about 11g. Moreover, as I noted above, 8i was subject to a bug which meant that the touch count for small tablescans.

    The third quote seems to be from one of the Burleson websites (which I tend to filter out when I'm doing a google search); since the bit you've selected was derived from a comment in an Oracle manual it might be worth addressing - but again it says nothing about small tablescans.


    Since you've said that I am contradicting "so many others" you might try to find a couple of quotes from reputable websites that refer to recent versions of Oracle, and if you can supply the URLs I'll take a look at what they say.


    On the other hand, you might like to look at a couple of articles I've written in the past about the keep cache and tablescans that might suggest I know a little more than most about the topic: http://jonathanlewis.wordpress.com/2011/03/14/buffer-states/ http://jonathanlewis.wordpress.com/2011/03/16/buffer-flush/ http://jonathanlewis.wordpress.com/2011/03/24/small-tables/

    I've just done a quick google search to see if I could find when I first mentioned the touch count / keep bit bug - and there's one reference on this Ask Tom thread where I point out it's not fixed until 10g. (And Tom produced a little code to demonstrate how to see it.) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255215154182#35607463711315


    Regards
    Jonathan Lewis
  • 10. Re: scripts to identify small tables for keep pool
    sb92075 Guru
    Currently Being Moderated
    without stating it explicitly, it appears that the basic underlying assumption is that you know better than Oracle implementation how best to manage data within the SGA.

    If the content of the small tables really are used "frequently",
    then Oracle won't remove them from the SGA.

    post SQL & results that actually demonstrate any problem really exists (except between your ears).
  • 11. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Wow you're good! yes, I research as much as I can before I post a question.
    The problem for all of us is that a lot of blogs seem to contradict each other, and we have to resort to going with the most commonly accepted or published practices.
    In reality, of course, we know that there is no better way to find out these things than by testing and proving to ourselves one way or another.
    But, setting up a test environment, especially for testing memory tuning, is near impossible to mimic a real life production database.

    I did read one of Tom's pages that he advised against pinning except in certain cases.

    BTW: I also read DB's blogs with a limited amount of credence.

    Thanks for your links to your paper and I will read it.

    One thing is that when I run the query here (Full table scan I do see a lot of objects that have numerous FTSs.
    These are the top ten in the list which I thought might at least be worth looking into.
    OWNER                    NAME                        NUM_ROWS K          BLOCKS         NBR_FTS
    ------------------------ -------------------- --------------- - --------------- ---------------
    ARADMIN                  SERVGRP_BOARD                      5 N               8           29059
    ARADMIN                  T2127                       12644912 N          245888            6079
    ARADMIN                  CONTROLRECORDIDS                   1 N               8            3263
    SYSMAN                   MGMT_VERSIONS                      5 N               8            2872
    ARADMIN                  CHAR_MENU                       2255 N             256            2331
    SYSMAN                   MGMT_NOTIFY_DEVICES                2 N               8            1607
    ARADMIN                  CONTROL                            1 N               8            1590
    ARADMIN                  GROUP_CACHE                      320 N               8            1498
    ARADMIN                  T384                             145 N              40            1375
    ARADMINE                 GROUP_CACHE                       13 N               8            1333
    ARADMIN                  T2119                           4925 N             256            1243
    ARADMIN                  T2154                             96 N               8            1238
  • 12. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    You may be right SB, but with the number of small tables we have, and the number of FTSs that i see, along with large FTSs, I'm thinking they would get flushed out a lot.
    By the way SB, I never implied or said I know better than Oracle implementation how best to manage data within the SGA.

    But if you look at the Oracle 11GR2 Tuning Guide, there is a whole section that starts off with: (on page 7-15)

    KEEP Pool

    If there are certain segments in your application that are referenced frequently, then store the blocks from those segments in a separate cache called the KEEP buffer pool. Memory is allocated to the KEEP buffer pool by setting the parameter DB_KEEP_CACHE_SIZE to the required size. The memory for the KEEP pool is not a subset of the default pool. Typical segments that can be kept are small reference tables that are used frequently.
  • 13. Re: scripts to identify small tables for keep pool
    sb92075 Guru
    Currently Being Moderated
    Full Table Scan are not inherently evil.

    In some cases FTS is most efficient way to obtain result set.

    It appears that you have a solution, in search of a problem.
  • 14. Re: scripts to identify small tables for keep pool
    ji li Pro
    Currently Being Moderated
    Great articles Jonathan.
    Makes me want to know more about automatic direct path serial tablescans.
1 2 Previous Next

Legend

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