Índice do fórum Treinamentos Avançados Treinamento Oracle Performance Diagnostics & Tuning Análise Statspack

Análise Statspack

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Performance Diagnostics & Tuning.

Mensagem Sex Fev 22, 2019 4:39 pm

Mensagens: 0
Olá Portilho,

Fiz uma análise de um ambiente e queria sua opnião para saber se estou no caminho certo.

Cabeçalho

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1673119734 nbs 1 13-Jan-19 19:15 12.1.0.1.0 NO

Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
bancora.**** Linux x86 64-bit 32 32 2 42.2

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 789 13-Fev-19 09:00:02 1,111 26.0
End Snap: 792 13-Fev-19 12:00:03 1,499 29.4
Elapsed: 180.02 (mins) Av Act Sess: 22.1
DB time: 3,979.08 (mins) DB CPU: 2,038.90 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 9,024M 9,152M Std Block Size: 8K
Shared Pool: 15,232M 15,104M Log Buffer: 9,088K

Podemos notar que temos um problema generalizado pois o DB time está muito maior que Elapsed time. O cálculo por cores é de 5760; Então o banco ficou por volta de 70% da carga total nesse período.
Além disso a Shared pool está quase o dobro do Cache, que pode ser um problema.

Passei para a Time Model.

Time Model System Stats DB/Inst: NBS/nbs Snaps: 789-792
-> Ordered by % of DB time desc, Statistic name

Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 231,356.2 ########
DB CPU 122,334.1 ########
parse time elapsed 12,568.8 ########
hard parse elapsed time 12,183.0 ########
PL/SQL execution elapsed time 3,284.8 ########
hard parse (sharing criteria) elaps 2,304.0 ########
connection management call elapsed 604.3 ########
hard parse (bind mismatch) elapsed 74.9 ########
failed parse elapsed time 35.9 ########
PL/SQL compilation elapsed time 8.0 ########
sequence load elapsed time 3.3 ########
repeated bind elapsed time 0.8 ########
DB time 238,745.0
background elapsed time 1,257.1
background cpu time


Converti os itens em porcentagem para ficar mais fácil, que ficou assim:

sql execute elapsed time: 96%
DBCPU: 54.24%

Então, a porcentagem de wait foi de : 45.66%. Quase metade do tempo com wait.

Então, vou para Foreground Wait Events.

Foreground Wait Events

Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
read by other session 14,631,823 0 54,839 4 200.8 23.6
db file sequential read 21,456,182 0 33,782 2 294.5 14.5

Aqui temos as duas principais waits, que pelo que entendi tem soluções em comum. Ataquei primeiro a "read by other session". Essa wait pode ser causada por pressão de espaço no Cache, que me remeteu ao tamanho da Shared Pool no cabeçalho.

Passei pelas waits de background e não vi nada que pudesse ajudar.

Parti para o Advise do Cache.

Buffer Pool Advisory DB/Inst: NBS/nbs End Snap: 792
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Pool, Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D 896 .1 110 4.9 54,293,223 35,004,803 190.2
D 1,792 .2 221 3.4 37,969,452 24,224,548 131.6
D 2,688 .3 331 2.6 28,553,938 18,006,518 97.9
D 3,584 .4 441 2.1 23,366,823 14,580,936 79.2
D 4,480 .5 551 1.8 20,177,550 12,474,733 67.8
D 5,376 .6 662 1.6 17,882,675 10,959,193 59.6
D 6,272 .7 772 1.4 16,022,929 9,731,013 52.9
D 7,168 .8 882 1.3 14,394,234 8,655,419 47.0
D 8,064 .9 992 1.2 12,885,608 7,659,118 41.6
D 8,960 1.0 1,103 1.0 11,435,355 6,701,368 36.4
D 9,152 1.0 1,126 1.0 11,133,438 6,501,981 35.3
D 9,856 1.1 1,213 0.9 10,026,390 5,770,885 31.4
D 10,752 1.2 1,323 0.8 8,677,453 4,880,044 26.5
D 11,648 1.3 1,433 0.7 7,434,588 4,059,252 22.1
D 12,544 1.4 1,544 0.6 6,318,787 3,322,375 18.1
D 13,440 1.5 1,654 0.5 5,326,958 2,667,369 14.5
D 14,336 1.6 1,764 0.4 4,448,001 2,086,903 11.3
D 15,232 1.7 1,874 0.3 3,589,957 1,520,249 8.3
D 16,128 1.8 1,985 0.2 2,651,490 900,484 4.9
D 17,024 1.9 2,095 0.2 1,802,904 443,849 2.4
D 17,920 2.0 2,205 0.1 1,127,093 349,881 1.9
-------------------------------------------------------------

Percebi que se eu dobrasse o tamanho do db_cache_size eu iria ter uma melhora de 90%. Correto?

Então resolvi olhar também o SQL por CPU para ver como estava.

SQL ordered by CPU DB/Inst: NBS/nbs Snaps: 789-792
-> Total DB CPU (s): 122,334
-> Captured SQL accounts for 100.7% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU

CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
42558.68 25 1702.35 34.8 42728.58 222,218,710 2327177011
Module: java.exe
SELECT a.cod_cliente,
a.nome,
Decode(Nvl(a.cod_c
liente, 1),
1,
'Cliente Novo',
Nvl(classe.classe, 'Cliente Novo')) classe,
Nvl(cpf, cgc) cpf_cnpj,
Nvl(a.bloqueado, 'N') bloq,

24554.72 630 38.98 20.1 87154.00 3,139,925,546 2430481418
Module: NFVENDAS.exe
select vendas.cod_empresa, empresas.nome, trunc(ve
ndas.emissao) emissao, Case When nfe_movimento.t
ipo_nf = '1' Then 'Entrada' Else 'S
aida' End Tipo, null as numero_controle, ve


Os dois primeiros SQL ocuparam respectivamente 34.8% e 20.1%.

Parâmetros

O único que chamou minha atenção foi o filesystemio_options que estava como asynch. Os outros estavam da forma que já esperava. Tem algum que merece destaque na sua opnião?

O que fiz:

Sobre as waits: Aumentei o db_cache_size para 18G.
Sobre CPU: Passei para a sofware a house analisar os dois SQLs.
Sobre parâmetros: Alterei para "setall".

Como foi minha análise? Segui pelo caminho certo?

Outra coisa, pode me ajudar a fazer o cálculo de ROI, por gentileza? Eu me confundi com isso. Queria saber quanto vou ganhar em "minutos e/ou porcentagem com a mudança no tamanho do Cache.

Ps: Eu fiz as mudanças e tenho o relatório do resultado das ações, mas coloco em outra interação para facilitar a visualização. Também surgiram outras dúvidas do resultado.

Abraços, Portilho!
Anexos
report_1.zip
(46.75 KiB) Baixado 64 vezes

Mensagem Sex Mar 01, 2019 5:13 pm
portilho Site Admin

Mensagens: 482
Oi.
Achei ótima sua análise. Fiz a minha antes de olhar a sua, só vendo o relatório, e batemos em quase tudo, com alguns complementos.
- Versão: 12.1.0.1.0 não é utilizável.
- DB Time: realmente temos um problema. Já vimos piores, mas já vimos melhores.
- Shared Pool: realmente temos um problema. Mas não há muito Parse em Time Model, pode ser um problema do passado. Você fez bem em subir o Cache.
- As 2 principais Waits Events são bem relevantes até mesmo em "Background Wait Events", o que é raro.
- Além dos SQLs por CPU, por conta das 2 Waits, você deve ver os "SQL ordered by Gets": os 02 principais SQLs consomem 49.5% e 43.2%, respectivamente, deste recurso.
- O parâmetro optimizer_index_caching está em 40 (o padrão é 0) e o optimizer_index_cost_adj em 60 (o padrão é 100). São valores muito agressivos, que induzem o CBO a utilizar mais métodos de acesso por índices, o que não necessariamente é bom. Daí devem vir planos ruins, o que nos leva aos nossos dois maiores Wait Events.
- Realmente não vejo motivo para "filesystemio_options" estar em "asynch". Eu colocaria sim em "setall";
- O "Buffer Pool Advisory" está dando um ganho magnífico, mas veja que este ganho é para reduzir as leituras físicas. E "db file sequential read" não sabemos se é físico ou não.
- Não podemos calcular o ROI pelo Advisor do Buffer Cache (pois não parecem ser leituras físicas), mas podemos fazer pela CPU:
DB Time = 3979 minutos.
CPU = 2038 minutos (51,22% do DB Time%).
Os 3 principais SQLs são responsáveis por 63,5% (34.8% + 20.1% + 8.6%) do uso de CPU.
Se os desenvolvedores conseguirem otimiza-los, digamos, em 50%, ganharemos 31,75% de 50% do DB Time. Ou seja, iremos reduzir 647 minutos dos 2038 anteriormente gastos em CPU.


Voltar para Treinamento Oracle Performance Diagnostics & Tuning

cron