Candidates for Keep POOL.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Candidates for Keep POOL.

  1. #1
    Join Date
    Sep 2005
    Posts
    278

    Candidates for Keep POOL.

    Hi Everyone,

    I have few question's related to Keep Pool,

    1) If I cache a object in KEEP pool, does Oracle prefers FULL table scans on that table?
    2) I wrote the following query to Identify the objects in Buffer Cache. Is it the perfect way to get the list of candidates for KEEP Pool.

    Code:
    select a.owner, a.object_name, b.sub_object, a.object_type,
    	b.num_rows, b.blocks obj_blks,
    	count(a.object_name) buff_blks, trunc(avg(a.touches)) touches
    from
    (
    select b.indx, b.hladdr,b.ts# tblspace, b.file# fileid, b.dbablk blockid,
    	b.obj objid, u.name owner, o.name object_name, o.subname subobject_name,
           decode (o.type#, 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 
                    19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 
                    34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 
                    39, 'LOB PARTITION', 40, 'LOB SUBPARTITION', 'UNDEFINED' 
                  ) object_type, b.tch touches
    from x$bh b, obj$ o, user$ u 
    where b.obj = o.dataobj# 
    and   o.owner# = u.user# 
    )a,
    (
    select owner object_owner, table_name object, null sub_object, buffer_pool, 
    	num_rows, blocks 
    from dba_tables 
    where buffer_pool is not null 
    union 
    select table_owner object_owner, table_name object, partition_name sub_object, buffer_pool, 
    	num_rows, blocks 
    from dba_tab_partitions 
    where buffer_pool is not null 
    union 
    select table_owner object_owner, table_name object, subpartition_name sub_object, buffer_pool, 
    	num_rows, blocks 
    from dba_tab_subpartitions 
    where buffer_pool is not null 
    union 
    select owner object_owner, index_name object, null sub_object, buffer_pool, 
    	num_rows, 1+LEAF_BLOCKS blocks 
    from dba_indexes 
    where buffer_pool is not null 
    union 
    select index_owner object_owner, index_name object, partition_name sub_object, buffer_pool, 
    	num_rows, 1+LEAF_BLOCKS blocks 
    from dba_ind_partitions 
    where buffer_pool is not null 
    union 
    select index_owner object_owner, index_name object, subpartition_name sub_object, buffer_pool, 
    num_rows, 1+LEAF_BLOCKS blocks 
    from dba_ind_subpartitions 
    where buffer_pool is not null 
    )b 
    where owner not in ( 'SYS', 'SYSTEM', 'PERFSTAT')
    and a.object_name = b.OBJECT AND a.owner = b.object_owner
    and nvl(a.subobject_name, 'NULL') = nvl(b.sub_object
    , 'NULL')
    group by b.buffer_pool, a.owner, b.sub_object, a.object_type, a.object_name, b.num_rows, b.blocks
    order by buff_blks desc, touches desc, obj_blks asc, num_rows desc;

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    1) If I cache a object in KEEP pool, does Oracle prefers FULL table scans on that table?

    No. The access path for a table is determined by its availble statistics, and expected number of rows from the table, the number of blocks to read and the CPU cycles.

    2) I wrote the following query to Identify the objects in Buffer Cache. Is it the perfect way to get the list of candidates for KEEP Pool.

    Since KEEP pool is always going to be smaller than DEFAULT pool, you may prefer to keep small reference tables and their indexes.

    Once Tom Kyte suggested not use KEEP pool at all. Define the buffer cache only DEFAULT pool, and let oracle manage it efficiently. Some of us may agree his point. I leave it to you.

    If you get good performance after defining the KEEP pool, then go for it.

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    Thanks for your reply,

    By reference tables, do u mean master tables?

    Do you any special case, where you used KEEP & LARGE pool. Your inputs are valuable. Thanks in advance.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Yes, Master tables.

    I have used KEEP and LARGE pool in the past. The LARGE pool is used for the objects where FULL TABLE SCAN occurs.

    Basically, you need to closely work with the dev team to know which objects are very important for acceptable performance from the end user.

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Hi,
    Personally I support Tom Kyte and Tamil but ... as Tamil said, it's up to you. By definition, KEEP pool should be used for small objects, which are accessed often. The second part (often accessed) should mean that their blocks will stay on the hot part of the LRU list with or without KEEP pool and this is the reason to support Tamil

    Regards

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I'm in full agreement with Tom, Tamil and Bore nevertheless a whole generation of data warehouse architects is out there devoting tons of hours to KEEP pool finetuning. I myself have to deal with one of these around here, the guy has a superpower, he's immune to logic and reasoning. Go figure.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    As I said earlier I used KEEP pool very cautiously. One incident happened few years ago I still remember even today. I had to rebuild 85 indexes one time. All those 85 indexes were created on one table - S_ORG_EXT, a Siebel table. The size of the table was 40GB that had 40 Millions rows.

    I had a powerful IBM UNIX servers at that time, and well striped disks on EMC DMX array. The index rebuild scripts (there are 3) was written by me and I used all the available features such as parallel, nologging etc. Each script had 30 indexes to be created. The SGA was increased to 9GB -only for this purpose. When I executed all the 3 scripts concurrently, they ran for 15 hours. I was not satisfied with the run time. Even though the SGA was set to 9GB, all are allocated to DEFAULT pool. What was happening was oracle flushed the buffer cache to accommodate index buffers during final phase of index built. The caused already table block buffers read by the other sessions were flushed, more IO was happening on the disks.

    The only way I could keep recently read table blocks into the buffer cache was to give some other area in the SGA for the index buffers.
    So I set KEEP pool in my index creation script, and allocated 500MB in the SGA.

    And this time, the total run time for the 3 scripts was around 5 hours.

    Once index was done, I reset the pool to DEFAULT.

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Tamil, this is a good story. I am just curios and would like to discuss with you ... in such case, what about building indexes with buffer pool recycle and setting default and recycle pool instead of default and keep pool? In the solution you have implemented, in fact you made your keep pool to operate as a default pool and your default pool to operate as a recycle pool. Practically these are just pools with associated objects to them, which competes to each other and do not interfere with the objects in the other pool, so you can call it keep or default or recycle... it is just about how you use it, not how you call it, but still it seems you used keep pool as a default pool and the default pool as a recycle pool :-)

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Yes, we can set RECYCLE pool for indexes and DEFAULT for tables. The main point is one set of buffers should not wipe out the other in a long running operation.

Posting Permissions

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


Click Here to Expand Forum to Full Width