Dear Afandi,
in version 9i :
1. set init parameter db_cache_advice = 'ON' if we want to see;
2. run this query to check db_cache_size:
column cache_size format a25 heading 'Cache Size (mb)'
column BFE format 999,999,999 heading 'Buffers'
column eprf format 999,90 heading 'Estd Phys | Read Factor'
column epr format 999,999,999,999 heading 'Estd Phys | Reads'
select size_for_estimate||
decode((select value/(1024*1024)
from v$parameter
where name='db_cache_size'),
size_for_estimate,' (Curent Size)')
as Cache_Size,
buffers_for_estimate as bfe,
estd_physical_read_factor as eprf,
estd_physical_reads as epr
from v$db_cache_advice
where name = 'DEFAULT'
and block_size = (select value from v$parameter
where name = 'db_block_size')
and advice_status = 'ON'
/
3. run this query to check shared_pool_size :
set lin 200
set pages 100
col spsfe format a20 heading "SHARED POOL|FOR|ESTIMATE"
col spsf format 990.99 heading "SHARED POOL|FACTOR"
col els format a14 heading "ESTIMATED|MEMORY SIZE|USED BY|LIBRARY CACHE"
col elts format a15 heading "ESTIMATED|TIME SAVED"
select shared_pool_size_for_estimate||' MB'||decode((select
value/(1024*1024)
from v$parameter
where name = 'shared_pool_size'),
shared_pool_size_for_estimate,' (Current Size)') spsfe,
shared_pool_size_factor spsf,
estd_lc_size||' MB' els,
estd_lc_memory_objects elmo,
estd_lc_time_saved||' Sec' elts,
estd_lc_time_saved_factor eltsf,
estd_lc_memory_object_hits elmoh from v$shared_pool_advice
/
regards,
Fahmi
[Non-text portions of this message have been removed]
