Hi all, I've the table DEFINITIVI (2GB) and a developer suggested me to keep it in Buffer cache to improve database performance.
Our database is a 11.2g RAC (2 instances) with automatic shared memory management
This is my sga values:
| NAME | BYTES
|
|---|
| Fixed SGA Size | 2241264 |
| Redo Buffers | 10027008 |
| Buffer Cache Size | 10200547328 |
| Shared Pool Size | 6845104128 |
| Large Pool Size | 402653184 |
| Java Pool Size | 67108864 |
| Streams Pool Size | 134217728 |
| Shared IO Pool Size | 536870912 |
| Granule Size | 67108864 |
| Maximum SGA Size | 27191361536 |
| Startup overhead in Shared Pool | 898199880 |
| Free SGA Memory Available | 9529458688 |
and this are my sga parameter:
| NAME | VALUE |
|---|
| sga_target | 8G |
| sga_max_size | 26048M |
| db_cache_size | 0 |
| shared_pool_size | 0 |
| large_pool_size | 0 |
| java_pool_size | 0 |
| streams_pool_size | 0 |
For know which table is in the sga i fount the query:
select
s.segment_type TYPE
, t1.owner SCHEMA
, s.segment_name NAME
, ROUND((sum(num_blocks)/greatest(sum(blocks), .001))*100,2) PERC_IN_SGA
, 'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);' QUERY
, ROUND(SUM(BYTES)/1024/1024/1024,2) DIM_OBJ
, ROUND((SUM(BYTES)/1024/1024/1024)*(sum(num_blocks)/greatest(sum(blocks), .001)),2) GB_OBJ_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,
gv$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
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 object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
ORDER BY
4 DESC
;
I run in the first instance:
alter TABLE DEFINITIVI storage (buffer_pool keep);
The query:
select BUFFER_POOL from SYS.DBA_SEGMENTS where SEGMENT_NAME = 'DEFINITIVI';
now return KEEP
If i do a full scan on DEFINITIVI (SELECT * FROM DEFINITIVI) the blocks in cache don't grow up.
If do an update on all rows they grow (not 100%) but after 10 minutes they are about 0%.
What is wrong?