Candidates for Keep POOL.

[tabreaz]

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.

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;

[tamilselvan]

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.

[tabreaz]

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.

[tamilselvan]

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.

[Bore]

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

[PAVB]

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.

[tamilselvan]

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.

[Bore]

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 :-)

[tamilselvan]

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.