Oracle documentation recommend about the candidate for keep pool that is smaller then 10% of the size of
Default buffer pool and has incurred at least 1% I/O in system. In other word highly accessed and small tables are candidates for keep pool. The goal is to locate those objects that are small and experience a disproportional amount of I/O activity
- Cache tables & indexes where the table is small (<100 blocks) and the table experiences frequent full-table scans.
- Cache any objects that consume more than 10% of the size of their data buffer.
1a) To find the tables which are highly executed (access is high) and Performing FTS and small tables are good candidate of keep pool
SELECT
p.owner,
p.NAME,
t.CACHE,
t.BUFFER_POOL,
s.blocks blocks,
SUM(sa.executions) FTS_NO
FROM
dba_tables t,
dba_segments s,
v$sqlarea sa,
(SELECT DISTINCT
sql_id stid,
object_owner owner,
object_name NAME
FROM
dba_hist_sql_plan
WHERE
object_owner NOT LIKE '%SYS%' AND
operation = 'TABLE ACCESS'
AND
options = 'FULL') p
WHERE
sa.sql_id = p.stid
AND
t.owner = s.owner
AND
t.table_name = s.segment_name
AND
t.table_name = p.NAME
AND
t.owner = p.owner
GROUP BY
p.owner, p.NAME, t.CACHE, t.BUFFER_POOL, s.blocks
HAVING
SUM(sa.executions) > 10
AND s.blocks < 100
ORDER BY
SUM(sa.executions) DESC;
1b) You can also find the frequency for FTS using below query
SELECT
b.owner,object_type mytype,
object_name myname,
blocks,
COUNT(1) buffers,
AVG(tch) avg_touches
FROM
sys. x$bh a,
dba_objects b,
dba_segments s
WHERE
a.obj = b.object_id
AND
b.object_name = s.segment_name
AND
b.owner NOT IN ('SYS','SYSTEM','SYSMAN')
GROUP BY
object_name,
object_type,
blocks,
obj,b.owner
HAVING
AVG(tch) > 5
AND
COUNT(1) > 20 ORDER BY 6 DESC
2a) Find the objects that consume more than 10% of the size of their data buffer
SELECT
s.segment_type,t1.owner,s.segment_name, (SUM(num_blocks)/GREATEST(SUM(blocks), .001))*100 AS "segment_%_in_sga"
FROM
(
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
)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 > 10
ORDER BY 4 DESC
;
2b) Below query helps you to find the size of objects in SGA and compare it with actual size of segments .And % of segment present in sga.
SELECT
s.segment_type,t1.owner,s.segment_name,S.BYTES/1024/1024/1024 total_size ,num_blocks*32/1024/1024 size_occup_In_SGA,
(num_blocks*32/1024/1024)*100/(S.BYTES/1024/1024/1024) SIZE_%_IN_SGA
FROM
( SELECT
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
COUNT(*) num_blocks
FROM
dba_objects o,
v$bh bh
WHERE
o.data_object_id = bh.objd
AND
o.owner NOT IN ('SYS','SYSTEM','SYSMAN')
AND
bh.status != 'free'
GROUP BY
o.owner,
o.object_name,
o.subobject_name,
o.object_type
) 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')
ORDER BY 6 DESC
On the basis of above results you can decide which tables are the
Good candidate for the keep pool cache.
First adjust the size of parameter db_keep_cache_size (dynamic parameter ) to ensure that cache has enough blocks to fully cache all of the segments that are assigned to the pool.
After choosing tables and indexes you can keep in pool using below commands
ALTER TABLE xyz STORAGE (BUFFER_POOL KEEP);
ALTER INDEX ind_xyz STORAGE (BUFFER_POOL KEEP);
No comments:
Post a Comment