| |
 |
|
Oracle automating script for keep pool caching tables & indexes
db_keep_cache_size
Oracle Tips by Burleson Consulting
|
This is an excerpt from my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts. You can immediately download the working scripts from the book
code depot.
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.
It is highly unlikely that an
undeserving table of index would meet this criterion. Of course,
you would need to run this script at numerous times during the day
because the buffer contents change very rapidly.
The following script can be run
every hour via dbms_job, and automate the monitoring of KEEP
pool
candidates. Every
time it finds a candidate, the DBA will execute the syntax and
adjust the total KEEP pool size to accommodate the new object.
set
pages 999
set
lines 92
spool
keep_syn.lst
drop
table t1;
create
table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status
!= 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||'
storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks),
.001))*100 > 80
;
spool
off;
alter
TABLE BOM.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);
alter
TABLE BOM.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter
INDEX BOM.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter
TABLE APPLSYS.FND_CONCURRENT_PROGRAMS storage (buffer_pool
keep);
alter
TABLE APPLSYS.FND_CONCURRENT_REQUESTS storage (buffer_pool
keep);
alter
TABLE GL.GL_JE_BATCHES storage (buffer_pool keep);
alter
INDEX GL.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter
TABLE GL.GL_JE_HEADERS storage (buffer_pool keep);
alter
TABLE INV.MTL_DEMAND_INTERFACE storage (buffer_pool keep);
alter
INDEX INV.MTL_DEMAND_INTERFACE_N10 storage (buffer_pool keep);
alter
TABLE INV.MTL_ITEM_CATEGORIES storage (buffer_pool keep);
alter
TABLE INV.MTL_ONHAND_QUANTITIES storage (buffer_pool keep);
alter
TABLE INV.MTL_SUPPLY_DEMAND_TEMP storage (buffer_pool keep);
alter
TABLE PO.PO_REQUISITION_LINES_ALL storage (buffer_pool keep);
alter
TABLE AR.RA_CUSTOMER_TRX_ALL storage (buffer_pool keep);
alter
TABLE AR.RA_CUSTOMER_TRX_LINES_ALL storage (buffer_pool keep);
alter
INDEX WIP.WIP_REQUIREMENT_OPERATIONS_N3 storage (buffer_pool
keep);
In sum, there are two ways to
identify tables and indexes for full caching in the KEEP pool. We start by explaining all of
the SQL in the databases looking for small-table, full-table scans.
Next, we repeatedly examine the data buffer cache, seeing any
objects that have more than 80% of their blocks in RAM. Next, let’s
finish the job and see how to re-size the KEEP pool to accommodate
your new objects.
The following script can be run
every hour via dbms_job, and automate the monitoring of KEEP
pool
candidates.
Every time it finds a candidate, the DBA will execute the syntax and
adjust the total KEEP pool size to accommodate the new object.
set
pages 999
set
lines 92
spool
keep_syn.lst
drop
table t1;
create
table t1 as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status
!= 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||'
storage (buffer_pool keep);'
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks),
.001))*100 > 80
;
spool
off;
alter
TABLE BOM.BOM_DELETE_SUB_ENTITIES storage (buffer_pool keep);
alter
TABLE BOM.BOM_OPERATIONAL_ROUTINGS storage (buffer_pool keep);
alter
INDEX BOM.CST_ITEM_COSTS_U1 storage (buffer_pool keep);
alter
TABLE APPLSYS.FND_CONCURRENT_PROGRAMS storage (buffer_pool
keep);
alter
TABLE APPLSYS.FND_CONCURRENT_REQUESTS storage (buffer_pool
keep);
alter
TABLE GL.GL_JE_BATCHES storage (buffer_pool keep);
alter
INDEX GL.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter
TABLE GL.GL_JE_HEADERS storage (buffer_pool keep);
alter
TABLE INV.MTL_DEMAND_INTERFACE storage (buffer_pool keep);
 |
For more on KEEP pool caching, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
