Gerando Estatísticas da SGA do Oracle
CooperaTIGalera, tudo bem?
Hoje nós vamos falar de uma estatística, que pode nos ajudar muito na hora de liberação de memória para a execução de tarefas no banco de dados. Estamos falando da SGA(System Global Area).
Dentro do Oracle, podemos executar o script abaixo como administrador.
DECLARE
libcac NUMBER(10, 2);
rowcac NUMBER(10, 2);
bufcac NUMBER(10, 2);
redlog NUMBER(10, 2);
spsize NUMBER;
blkbuf NUMBER;
logbuf NUMBER;
BEGIN
SELECTVALUE
INTO redlog
FROM v$sysstat
WHEREname = ‘redo log space requests’;
SELECT100 * (SUM(pins) – SUM(reloads)) / SUM(pins)
INTO libcac
FROM v$librarycache;
SELECT100 * (SUM(gets) – SUM(getmisses)) / SUM(gets)
INTO rowcac
FROM v$rowcache;
SELECT 100 * (cur.VALUE + con.VALUE – phys.VALUE) /(cur.VALUE + con.VALUE)
INTO bufcac
FROM v$sysstat cur, v$sysstat con, v$sysstat phys, v$statname ncu, v$statname nco, v$statname nph
WHERE cur.statistic# = ncu.statistic#
AND ncu.name = ‘db block gets’
AND con.statistic# = nco.statistic#
AND nco.name = ‘consistent gets’
AND phys.statistic# = nph.statistic#
AND nph.name = ‘physical reads’;
SELECTVALUE
INTO spsize
FROM v$parameter
WHERE name = ‘shared_pool_size’;
SELECTVALUE
INTO blkbuf
FROM v$parameter
WHEREname = ‘db_block_buffers’;
SELECT VALUE INTO logbuf FROM v$parameter WHERE name = ‘log_buffer’;
DBMS_OUTPUT.put_line(‘> SGA CACHE STATISTICS’);
DBMS_OUTPUT.put_line(‘> ********************’);
DBMS_OUTPUT.put_line(‘> SQL Cache Hit rate = ‘ || libcac);
DBMS_OUTPUT.put_line(‘> Dict Cache Hit rate = ‘ || rowcac);
DBMS_OUTPUT.put_line(‘> Buffer Cache Hit rate = ‘ || bufcac);
DBMS_OUTPUT.put_line(‘> Redo Log space requests = ‘ || redlog);
DBMS_OUTPUT.put_line(‘> ‘);
DBMS_OUTPUT.put_line(‘> INIT.ORA SETTING’);
DBMS_OUTPUT.put_line(‘> ****************’);
DBMS_OUTPUT.put_line(‘> Shared Pool Size = ‘ || spsize || ‘ Bytes’);
DBMS_OUTPUT.put_line(‘> DB Block Buffer = ‘ || blkbuf || ‘ Blocks’);
DBMS_OUTPUT.put_line(‘> Log Buffer = ‘ || logbuf || ‘ Bytes’);
DBMS_OUTPUT.put_line(‘> ‘);
IF libcac < 99THEN
DBMS_OUTPUT.put_line(‘*** HINT: Library Cache too low! Increase the Shared Pool Size.’);
ENDIF;
IF rowcac < 85 THEN
DBMS_OUTPUT.put_line(‘*** HINT: Row Cache too low! Increase the Shared Pool Size.’);
ENDIF;
IF bufcac < 90THEN
DBMS_OUTPUT.put_line(‘*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.’);
ENDIF;
IF redlog > 100THEN
DBMS_OUTPUT.put_line(‘*** HINT: Log Buffer value is rather low!’);
ENDIF;
END;
/
Font: http://psoug.org/snippet/Display-database-SGA-statistics_521.htm
Galera, espero ter ajudado vocês!!!