SGA TUNING INFORMATION

Description
General guidelines and definitions are as follows:

Data Block Cache – > .90 .85
Highly application dependent, increase
the instance parameter SHARED_POOL_SIZE
to increase hit ratio.
– Avg. Users Per Stmt. – The average number of users who execute
a SQL statement.
– Avg. Executes Per Stmt.
– The average number of times that each
statement gets executed.

– Data Dictionary Cache Hit Ratio
– > .95 – Increase the instance parameter
SHARED_POOL_SIZE to increase hit ratio.

References
Note:106285.1 – TROUBLESHOOTING GUIDE: Common Performance Tuning Issues
Note:62161.1 – Systemwide Tuning using UTLESTAT Reports in Oracle7/8

Script
ttitle –
center ‘SGA Cache Hit Ratios’ skip 2

set pagesize 60
set heading off
set termout off

col lib_hit format 999.999 justify right
col dict_hit format 999.999 justify right
col db_hit format 999.999 justify right
col ss_share_mem format 999.99 justify right
col ss_persit_mem format 999.99 justify right
col ss_avg_users_cursor format 999.99 justify right
col ss_avg_stmt_exe format 999.99 justify right

col val2 new_val lib noprint
select 1-(sum(reloads)/sum(pins)) val2
from v$librarycache
/
col val2 new_val dict noprint
select 1-(sum(getmisses)/sum(gets)) val2
from v$rowcache
/
col val2 new_val phys_reads noprint
select value val2
from v$sysstat
where name = ‘physical reads’
/
col val2 new_val log1_reads noprint
select value val2
from v$sysstat
where name = ‘db block gets’
/
col val2 new_val log2_reads noprint
select value val2
from v$sysstat
where name = ‘consistent gets’
/
col val2 new_val chr noprint
select 1-(&phys_reads / (&log1_reads + &log2_reads)) val2
from dual
/

col val2 new_val avg_users_cursor noprint
col val3 new_val avg_stmts_exe noprint
select sum(users_opening)/count(*) val2,
sum(executions)/count(*) val3
from v$sqlarea
/

set termout on
set heading off

select ‘Data Block Buffer Hit Ratio : ‘||&chr db_hit_ratio,
‘Shared SQL Pool ‘,
‘ Dictionary Hit Ratio : ‘||&dict dict_hit,
‘ Shared SQL Buffers (Library Cache) ‘,
‘ Cache Hit Ratio : ‘||&lib lib_hit,
‘ Avg. Users/Stmt : ‘||
&avg_users_cursor||’ ‘,
‘ Avg. Executes/Stmt : ‘||
&avg_stmts_exe||’ ‘
from dual
/

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em PLSQL SCRIPTS, SGA e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s