Menu de navegação

[jspaulonciParticipante]

Boa tarde pessoal, alguem teria uma query para verificar o quanto está em uso e o quanto tem livre para o parametro db_cache_size, estou em ASM com RAC 10.2.0.3.0

Abraços

João Paulo Spaulonci

[vieriParticipante]

show parameters db_cache_advice

*verifique se o advice tá ligado.

col size_est format 999,999,999,999 heading ‘Cache Size (m)’
col buf_est format 999,999,999 heading ‘Buffers’
col estd_rf format 999.90 heading ‘Estd Phys|Read Factor’
column estd_pr format 999,999,999 heading ‘Estd Phys| Reads’
SET LINES 80 PAGES 55
@title80 ‘DB Cache Advisor Report’
SELECT
size_for_estimate size_est,
buffers_for_estimate buf_est,
estd_physical_read_factor est_rf,
estd_physical_reads est_pr
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’;

posta o resultado da query ai pra galera debater, esse assunto parece simples mas envolve algumas variáveis.

[vieriParticipante]

posta dessas duas querys também.

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
1 – (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) “Hit Ratio”
FROM V$BUFFER_POOL_STATISTICS;

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN (‘db block gets from cache’,
‘consistent gets from cache’,
‘physical reads cache’);

Caso esteja procurando as campeãs de uso de buffer:

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN (‘db block gets from cache’,
‘consistent gets from cache’,
‘physical reads cache’);

[vieriParticipante]

ops.. query errada para as top no buffer cache.

a correta:

SQL> l
1 SELECT *
2 FROM (SELECT SQL_FULLTEXT, BUFFER_GETS
3 FROM V$SQL
4 ORDER BY BUFFER_GETS DESC)
5* WHERE ROWNUM <= 50

[jspaulonciParticipante]

Bom dia Vieri, primeiramente agradeço sua ajuda.
bom, vamos aos posts.

SQL> col size_est format 999,999,999,999 heading ‘Cache Size (m)’
SQL> col buf_est format 999,999,999 heading ‘Buffers’
SQL> col estd_rf format 999.90 heading ‘Estd Phys|Read Factor’
SQL> column estd_pr format 999,999,999 heading ‘Estd Phys| Reads’
SQL> SET LINES 80
Cannot SET LINES
SQL> Set PAGES 55
SQL> –@title80 ‘DB Cache Advisor Report’
SQL> SELECT
2 size_for_estimate size_est,
3 buffers_for_estimate buf_est,
4 estd_physical_read_factor est_rf,
5 estd_physical_reads est_pr
6 FROM V$DB_CACHE_ADVICE
7 WHERE name = ‘DEFAULT’
8 AND block_size = (SELECT value FROM V$PARAMETER
9 WHERE name = ‘db_block_size’)
10 AND advice_status = ‘ON’;

SIZE_EST BUF_EST EST_RF EST_PR

---

    80       9595     1,0412  150159936
   160      19190     1,0346  149209713
   240      28785     1,0285  148324563
   320      38380      1,023  147533966
   400      47975     1,0188  146917574
   480      57570     1,0151  146383711
   560      67165     1,0112  145824031
   640      76760     1,0083  145414879
   720      86355      1,006  145079813
   800      95950      1,004  144783181
   880     105545     1,0019  144484046
   944     113221          1  144212742
   960     115140     0,9995  144144908
  1040     124735     0,9848  142021554
  1120     134330      0,969  139735552
  1200     143925     0,9509  137137881
  1280     153520     0,9308  134234304
  1360     163115     0,9097  131189181
  1440     172710     0,8886  128143872
  1520     182305     0,8672  125064729
  1600     191900     0,7858  113322553

21 rows selected
Vieri, o que significa os campos abaixo ?

SIZE_EST
BUF_EST
EST_RF
EST_PR

[jspaulonciParticipante]

Segunda query

SQL> SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS,
2 1 – (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) “Hit Ratio”
3 FROM V$BUFFER_POOL_STATISTICS
4 ;

NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS Hit Ratio
DEFAULT 144213238 79514070 554634057 0,77258745

Meu coeficiente de acerto de cache de buffer é 77% a idéia é estar acima de 95% ?

[jspaulonciParticipante]

Query que retorna as sessões top no uso de cache

SQL> SELECT *
2 FROM (SELECT SQL_FULLTEXT, BUFFER_GETS
3 FROM V$SQL
4 ORDER BY BUFFER_GETS DESC)
5 WHERE ROWNUM sysdate) and (next_date < (sysd 50073
select /+ index(idl_ub1$ i_idl_ub11) +/ piece#,length,piece from idl_ub1$ wher 49340
call DECA.CNPJ_SINCRONISMO_MAINFRAME.EXECUTA() 41048
SELECT T1.ID_MAIN_SCR, T1.ID_SOLICITACAO,T1.ID_ESTABELECIMENTO, T1.ID_CONTRIBUIN 40499
select job, nvl2(last_date, 1, 0) from sys.job$ where next_date <= :1 and (field 38023
select u1.user#, u2.user#, u3.user#, failures, flag, interval#, what, nlsenv, 36973
UPDATE tb_dspj_incompatib_solicitacao SET dt_incompatibilidade = sysdate WHERE d 36925
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1, spare2 f 36856
select order#,columns,types from access$ where d_obj#=:1 36536
select /+ index(idl_ub2$ i_idl_ub21) +/ piece#,length,piece from idl_ub2$ wher 34709
select /+ index(idl_sb4$ i_idl_sb41) +/ piece#,length,piece from idl_sb4$ wher 34616
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro 32969
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj# 32795
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj# 28977
delete from smon_scn_time where thread=0 and scn = (select min(scn) from smon_s 28062
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc 23753
select /+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a 22930
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis 22443
select value$ from props$ where name = 'GLOBAL_DB_NAME' 21046
select metadata from kopm$ where name='DB_FDO' 20964
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (n 20761
select position#,sequence#,level#,argument,type#,charsetid,charsetform,propertie 19064
select /+ rule / bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ 18932
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco 18831
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w 16664
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w 16275
select /+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w 15022
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where 13831
select con#,obj#,rcon#,enabled,nvl(defer,0) from cdef$ where robj#=:1 13737
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where 13488
update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache= 9549
select local_tran_id, global_tran_fmt, global_oracle_id, global_foreign_id, 9482
insert into WRI$_ALERT_OUTSTANDING (reason_id, object_id, subobject_id, internal 8955
select o.name, u.name from obj$ o, user$ u where o.obj# = :1 and o.owner# = u.u 7944
select privilege#,level from sysauth$ connect by grantee#=prior privilege# and p 7502

50 rows selected

[vieriParticipante]

Sua taxa de acerto no buffer cache está muito baixa.

Seu buffer cache está com 944Mb confere ?
repare no buf_est = 1

SIZE_EST BUF_EST EST_RF EST_PR

---

80 9595 1,0412 150159936
160 19190 1,0346 149209713
240 28785 1,0285 148324563
320 38380 1,023 147533966
400 47975 1,0188 146917574
480 57570 1,0151 146383711
560 67165 1,0112 145824031
640 76760 1,0083 145414879
720 86355 1,006 145079813
800 95950 1,004 144783181
880 105545 1,0019 144484046
944 113221 1 144212742
960 115140 0,9995 144144908
1040 124735 0,9848 142021554
1120 134330 0,969 139735552
1200 143925 0,9509 137137881
1280 153520 0,9308 134234304
1360 163115 0,9097 131189181
1440 172710 0,8886 128143872
1520 182305 0,8672 125064729
1600 191900 0,7858 113322553

21 rows selected
Vieri, o que significa os campos abaixo ?

SIZE_EST => tamanho estimado
BUF_EST =>percentual % em cima do valor setado
EST_RF => Physical read factor for this cache size, which is the ratio of the number of estimated physical reads to the number of reads in the real cache. If there are no physical reads in the real cache, the value of this column is null. (média entre leituras físicas e leituras no cache)
EST_PR => Leituras físicas estimadas.

http://stanford.edu/dept/itss/docs/orac … s_1062.htm

Vc está com SGA automática(se tive da uma olhada
na v$sga_dynamic_components ) ? qtos gigas tem de SGA?
o db_cache_size está setado em qto ?

Acho que vale a pena aumentar ele para 1.6Gb,
irá diminir o número de physical reds de 14 para 11milhoes. em torno de 18%.

Ai verificar se a taxa de acerto (hit ratio) aumenta.

em um ambiente bastante porrado aqui da empresa.

NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS Hit Ratio

---

DEFAULT 66518673 561611139 1.1751E+10 .99459745

hit rario de 99,4% de acerto. isso não significa que não tenhu problemas de performance, mas abaixo dos 80%, acho dificil não ter “problemas”. Se a storage for muito boa , derrepente mascara isso.
Mas faz esse teste aumenta para 1.6G e roda as querys denovo, de preferência no mesmo horário.

[jspaulonciParticipante]

Bom dia Vieri, estou precisando saber quanto estou usando de cache para tirar o gerenciamento automático a SGA, foi por isso que pedi esta query .

Você usa SGA_TARGET e SGA_MAX_SIZE ? como que você deixa esses parametros no seu banco ? Em qual query você constatou que o meu CACHE estava com 944 mb ?

Abraços

Spaulonci

[vieriParticipante]

Vc deve ter seus motivos para desligar o gerênciamento automático,
eu particulamente gosto de deixar ele semi-automativo(tip-tronic)… hehhe

Exemplo.. se tenhu 8 Gb de SGA, e verifique que preciso de no minimo
2Gb de shared_pool, 1Gb de buffer e 500Mb de large_pool,
eu seto esses valores e deixo o Oracle gerênciar o resto.

Fico meio que na parceria com ele, não descarto e gerênciamento automático, mas digo limites minimos pra ele.

repare :

SQL> set lin 4000
SQL> col component for a20
SQL> select * from v$sga_dynamic_components ;

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPE GRANULE_SIZE

---

shared pool 2046820352 1207959552 0 0 18 GROW DEFERRED 01/09/09 16777216
large pool 16777216 0 0 0 1 GROW IMMEDIATE 23/08/09 16777216
java pool 33554432 16777216 0 0 2 SHRINK DEFERRED 30/08/09 16777216
streams pool 0 0 0 0 0 STATIC 16777216
DEFAULT buffer cache 5469372416 5469372416 0 16777216 21 SHRINK DEFERRED 01/09/09 16777216
KEEP buffer cache 0 0 0 0 0 STATIC 16777216
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 2K buffer ca 0 0 0 0 0 STATIC 16777216
che

DEFAULT 4K buffer ca 0 0 0 0 0 STATIC 16777216

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPE GRANULE_SIZE

---

che

DEFAULT 8K buffer ca 0 0 0 0 0 STATIC 16777216
che

DEFAULT 16K buffer c 0 0 0 0 0 STATIC 16777216
ache

DEFAULT 32K buffer c 0 0 0 0 0 STATIC 16777216
ache

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPE GRANULE_SIZE

---

ASM Buffer Cache 0 0 0 16777216 0 STATIC 16777216

O current_size é oque vc está buscando no momento.

acho que é so isso que vc precisa.

outra maneira de saber é com a query que passei.

Aonde o EST_RF estiver com valor 1, a coluna (Chache Size (m) )
é o seu valor alocado no buffer_cache, conforme esse percentual(EST_RF)
for aumentando ou diminuindo no result set da query, o tamanho do cache_size irá aumentando ou diminuindo proporcionalmente, e para saber da melhoria acompanhe na coluna (EST_PR), repare que conforme vc aumentá ( no seu caso), o buffer cache suas phisical reads diminuem,
no meu caso que já estou com 5,2Gb, não adianta eu aumentar que não tenho melhoria, o indicado pra mim segundo o advice é diminuir para 5,1Gb.

Cache Size (m) Buffers EST_RF EST_PR

---

         512       31,680     17,538  293984862
       1,024       63,360     5,5671   93319159
       1,536       95,040     2,1145   35443894
       2,048      126,720     1,3379   22426626
       2,560      158,400     1,1491   19262004
       3,072      190,080     1,0838   18167997
       3,584      221,760      1,051   17617358
       4,096      253,440     1,0304   17272831
       4,608      285,120     1,0151   17015651
       5,120      316,800     1,0023   16802006
       5,216      322,740          1   16762694
       5,632      348,480      ,9923   16633982
       6,144      380,160      ,9858   16525178
       6,656      411,840        ,98   16428244
       7,168      443,520      ,9756   16352947
       7,680      475,200      ,9709   16274291
       8,192      506,880      ,9658   16189065
       8,704      538,560      ,9613   16114550
       9,216      570,240      ,9572   16045351
       9,728      601,920      ,9523   15963647
      10,240      633,600      ,9409   15771915

Se vc quer simplesmente copiar oque o gerênciamento automático setou pra vc bastar setar o db_cache_size com o valor encontrado,
conforme explanei acima,caso queira fazer um ajuste , analisa as phisical reads , sempre batendo com o tamanho do buffer…

entendeu Paulo ?

[vieriParticipante]

retificando..

O indicado no meu caso é aumentar o buffer cache, mas repare que mesmo se eu aumentar ele em + 5GB o número de phisical reads, diminui muito pouco, pra mim não valendo a pena, porque meus outros 3Gb eu deixo na shared_pool.

Se eu tivesse com 1G de buffer cache, minhas phisical reads seria o dobro…. repare…

Cache Size (m) Buffers EST_RF EST_PR

---

         512       31,680     17,538  293984862
       1,024       63,360     5,5671   93319159
       1,536       95,040     2,1145   35443894
       2,048      126,720     1,3379   22426626
       2,560      158,400     1,1491   19262004
       3,072      190,080     1,0838   18167997
       3,584      221,760      1,051   17617358
       4,096      253,440     1,0304   17272831
       4,608      285,120     1,0151   17015651
       5,120      316,800     1,0023   16802006
       5,216      322,740          1   16762694
       5,632      348,480      ,9923   16633982
       6,144      380,160      ,9858   16525178
       6,656      411,840        ,98   16428244
       7,168      443,520      ,9756   16352947
       7,680      475,200      ,9709   16274291
       8,192      506,880      ,9658   16189065
       8,704      538,560      ,9613   16114550
       9,216      570,240      ,9572   16045351
       9,728      601,920      ,9523   15963647
      10,240      633,600      ,9409   15771915

[]s

[jspaulonciParticipante]

Bom dia Vieri, você está certo, o que eu preciso é do conteúdo da v$sga_dynamic_components, assim como você vou deixar as minhas SGAs meio automático , quero deixar fixo pelo menos a large_pool, o db_cache size e o shared_pool.

Mas me diga uma coisa, como que você deixa os parametros SGA_TARGET e a SGA_MAX_SIZE ?

Obrigado

[vieriParticipante]

deixo eles com mesmo valor…

[jspaulonciParticipante]

Mas qual valor ? Zero ou diferente de Zero ?

Spaulonci

[vieriParticipante]

Exemplo…

tenho um server com 8Gb de RAM e quero que o Oracle use 4GB.

eu seto o SGA_MAX_SIZE=4G e SGA_TARGET=4G .

assim sendo estou usando gerênciamento automático… após isso monitoro meus buffer’s e seto um valor minimo pra eles,
mas este valor minimo eu deixo sobrar uns 30% para o gerênciamento automático escolher para qual pool de memôria que vai alocar.

entendido ?

[]s..
😉

[Autor]

Posts