1 2 Previous Next 25 Replies Latest reply: Dec 7, 2012 11:32 AM by rp0428 RSS

    scripts to identify small tables for keep pool

    ji li
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    Great articles Jonathan.
                                    Makes me want to know more about automatic direct path serial tablescans.
                                    1 2 Previous Next