Boa Tarde,
Dulima, vi seu post e estava preparando um script pra você verificar o mapeamento da sua SGA!
Selecionar tudo
set echo off
set feedback off
set linesize 512
prompt
prompt SGA Memory Map (Visao Geral)
prompt
column dummy noprint
column area format a20 heading 'Main SGA Areas'
column name format a20
column pool format a20
column bytes format 999,999,999,999
column sum(bytes) format 999,999,999,999
break on report
compute sum of sum(bytes) on report
SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes)
FROM v$sgastat WHERE pool is null and name = 'db_block_buffers'
group by name
union all
SELECT 2, 'Shared Pool', pool, sum(bytes)
FROM v$sgastat WHERE pool = 'shared pool'
group by pool
union all
SELECT 3, 'Large Pool', pool, sum(bytes)
FROM v$sgastat WHERE pool = 'large pool'
group by pool
union all
SELECT 4, 'Java Pool', pool, sum(bytes)
FROM v$sgastat WHERE pool = 'java pool'
group by pool
union all
SELECT 5, 'Redo Log Buffer', name, sum(bytes)
FROM v$sgastat WHERE pool is null and name = 'log_buffer'
group by name
union all
SELECT 6, 'Fixed SGA', name, sum(bytes)
FROM v$sgastat WHERE pool is null and name = 'fixed_sga'
group by name
ORDER BY 4 desc;
column area format a20 heading 'Shared Pool Areas'
prompt
prompt Mapeamento da memoria SGA (Shared pool)
prompt
SELECT 'Shared Pool' area, name, sum(bytes)
FROM v$sgastat WHERE pool = 'shared pool' and name in ('library cache','dictionary cache','free memory','sql area')
group by name
union all
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes)
FROM v$sgastat WHERE pool = 'shared pool' and name not in ('library cache','dictionary cache','free memory','sql area')
group by pool
order by 3 desc;
prompt
prompt Mapeamento da Cache SGA (DB cache size)
prompt
SELECT name, value
FROM v$spparameter
WHERE name = 'db_cache_size'
Qualquer duvida posta ai valeu?
[]'s
