|
Automating KEEP Pool Assignment
The Oracle documentation states:
A good candidate for a segment to put into the KEEP pool is
a segment that is smaller than 10% of the size of the
DEFAULT buffer pool and has incurred at least 1% of the
total I/Os in the system.
It is easy to locate segments that are less
than 10% of the size of their data buffer, but Oracle only has
v$segstat to track I/O at the segment level. To get around this
issue, some DBAs place each segment into an isolated tablespace
so that the AWR can show the total I/O, but you can also use
STATSPACK (with level 7 snapshots) and the AWR both capture and
report segment level statistics. These statistics include the
physical reads in 9i, and the number of segment scans in 10g.
Since the idea of the KEEP is to fully cache the object, the
goal is to locate those objects that are small and experience a
disproportional amount of I/O activity. Using this guideline,
there are two approaches. Unlike the recommendation from the
Oracle documentation, these approaches can be completely
automated:
§
Cache tables & indexes where the table is small
(<50 blocks) and the table experiences frequent full-table
scans.
§
Cache any objects that consume more than 10% of
the size of their data buffer.
The first method that uses
v$sql_plan to examine all
execution plans, searching for small-table, full-table scans,
and is
found in get_keep_pool.sql.
This can automatically generate the KEEP syntax for any small
table, with the DBA adjusting the table size threshold, for
tables that have many full-table scans.
-- *************************************************
-- Copyright © 2010 by Rampant TechPress
-- *************************************************
select
'alter table '||p.owner||'.'||p.name||' storage
(buffer_pool keep);'
from
dba_tables t,
dba_segments s,
dba_hist_sqlstat a,
(select distinct
pl.sql_id,
pl.object_owner owner,
pl.object_name name
from
dba_hist_sql_plan pl
where
pl.operation = 'TABLE ACCESS'
and
pl.options = 'FULL') p
where
a.sql_id = p.sql_id
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
and
t.buffer_pool <> 'KEEP'
having
s.blocks < 50
group by
p.owner, p.name, t.num_rows, s.blocks
UNION
--
***********************************************************
-- Next, get the index names
--
***********************************************************
select
'alter index '||owner||'.'||index_name||' storage
(buffer_pool keep);'
from
dba_indexes
where
owner||'.'||table_name in
(
select
p.owner||'.'||p.name
from
SEE CODE DEPOT FOR FULL SCRIPTS
dba_tables t,
dba_segments s,
dba_hist_sqlstat
a,
(select distinct
pl.sql_id,
pl.object_owner owner,
pl.object_name name
from
dba_hist_sql_plan pl
where
pl.operation = 'TABLE ACCESS'
and
pl.options = 'FULL') p
where
a.sql_id = p.sql_id
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.owner not in ('SYS','SYSTEM')
and
t.buffer_pool <> 'KEEP'
having
s.blocks < 50
group by
p.owner, p.name, t.num_rows, s.blocks
)
By running this script, the Oracle10g
v$ views can be used
to generate suggestions for the KEEP syntax, based on the number
of blocks in the object.
alter index DING.PK_BOOK storage (buffer_pool
keep);
alter table DING.BOOK storage (buffer_pool
keep);
alter table DING.BOOK_AUTHOR storage (buffer_pool
keep);
alter table DING.PUBLISHER storage (buffer_pool
keep);
alter table DING.SALES storage (buffer_pool keep);
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 or index would
meet this criterion. Of course, this script would need to be
run numerous times during the day because the buffer contents
change very rapidly.
The script in keep_syn.sql
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.
-- *************************************************
-- Copyright © 2010 by Rampant TechPress
--- *************************************************
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
SEE CODE DEPOT FOR FULL SCRIPTS
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
SEE CODE DEPOT FOR FULL SCRIPTS
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
;
The following is a sample of the output from this script:
alter TABLE IS.GL_JE_BATCHES storage (buffer_pool keep);
alter INDEX IS.GL_JE_BATCHES_U2 storage (buffer_pool keep);
alter TABLE IS.GL_JE_HEADERS storage (buffer_pool keep);
Once the segments for assignment to the KEEP pool have been
identified, the DBA will need to adjust the
db_keep_cache_size
parameter to ensure that it has enough blocks to fully
cache all of the segments that are assigned to the pool.
Of course, there are many exceptions to this automated
approach. For example, these scripts do not handle table
partitions and other object types. Hence, these scripts should
be used as a framework for a KEEP pool caching strategy, and
should not be run as is.
The next section provides information on scripts that can be
used to automate the identification of objects for the RECYCLE
pool. The identification of candidates for
the RECYCLE pool is very similar to the KEEP pool process.
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
|
