Menu de navegação

[mpvargasParticipante]

Trabalho com Oracle 10g num Servidor Quad Core 64bits 14Gb RAM com S.O. Linux RedHat… Nos últimos dias, estamos com muito processo no sistema em virtude dos processos da contabilidade e alguns usuários reclamam de lentidão do sistema em alguns momentos.
Observei no EM que estão acontecendo muitas leituras físicas, mas no servidor não acusa uso de SWAP. Existem momentos que os Processos Executáveis no Host, na média de carga, excedem o Máximo de CPU.
Onde mostra o I/O de Disco na Instancia, nesses momentos de pico, chegam a bater 1000000 de Leituras Fisicas (KB) por segundo e no Throughput da Instancia as Leituras Fisicas (KB) chegam a 150000 por segundo.
Meu banco está configurado com SGA_Target 6,2 Gb e PGA 1 Gb, e tenho 5 grupos de redo log, com 1 arquivo cada, de 150Mb.
O que posso fazer para melhorar os processos ou a configuração está OK e o que eu tenho é uma limitação de hardware?
Obrigado pela ajuda.

[juliano_sfParticipante]

Cara, puxa um relatório do AWR e olha quais são as consultas que mais fazem I/O. Tenta puxar o plano de execução delas e ve as tabelas que estão fazendo FULL TABLE SCAN. Nessas tabelas vc cria os índices, pois assim ele pode utilizar o índice ao invés de ir ler a tabela no disco. Lembre-se que o Oracle não leva pra memória os blocos que ele puxa via full table scan, exatamente para não degradar a memória.
Geralmente são poucas as consultas que fazem a maior parte do I/O do sistema…

Falow!

Juliano

[vieriParticipante]

1)Verifique o plano de execução das
principais queryes,
2)se está fazendo mto I/O verifique como as tablespaces
estão espalhadas no disco,
3)veja quais tabelas + acessadas e se algo a fazer por elas,
4)verifique principais eventos de expera

No EM consegue isso facilmente.

Não adianta ficar abstraindo …
aaaa horário tal teve 10000billhões de leituras físicas, isto não é informação para encontrar problema… oque vc fez com essa informação??

Agora : Está fazendo 100000 reads na tablespace XPTO!! e 80% desses reads são
nos indices da tabela TB_XPTO, agora sim vc tem algo que possa gerar uma atuação : rebuild dos índices em uma nova tbps em outro filesystem.

[]s

[vieriParticipante]

EXATO !!

Poste a[i o resultado do AWR e de quebra do ADDM também,
para o pessoal lhe ajudar

😉

[mpvargasParticipante]

Vieri,
Posso executar o AWR e o ADDM em qualquer hora?
Não causa lentidão no sistema?

1)Verifique o plano de execução das principais queryes,
2)se está fazendo mto I/O verifique como as tablespaces estão espalhadas no disco,
3)veja quais tabelas + acessadas e se algo a fazer por elas,
4)verifique principais eventos de expera

Como vejo essas opções no EM?

[vieriParticipante]

SIM !

Pode executar a vontade, ele irá obter as informações
em tabelas a parte do produto tunnig pack…
que são carregas com jobs do EM , não acessa direto o catalogo,
somento no momento da coleta.

Essas infos que eu questiono no item 1,2,3 4 vc encontra no awr e addm.

gere os 2 em momento de alta utilizaão dos sistemas
e poste pra nós !

[]s

[Rodrigo AlmeidaParticipante]

Marcelo,

Todos os passos citados, é o caminho.

O relatório de AWR, ADDM e ASH podem encontar os seus problemas, no report do AWR, pegue os TOP10, senão me engano ele até fornece que está queimando por physical e logical reads… ele ordena!!!

E o que pode ser feito.

1) Analisar as instruções SQL.
2) Dos planos de execução analisados, veja como está a saúde dos índices e se a tabela está com algum FULL SCAN perdido!!!
3) Tente alocar buffer no nível de tabela para KEEP e RECYCLE
4) veja como está a configuração do seu buffer_cache
5) DB_MULTI_BLOCK_READ_COUNT tb deve estar bem configurado!
6) DBMS_STATS nas tabelas que estão no TOP 10, com opção CASCADE=TRUE!

Acho que isso dá para brincar!!

Particularmente, nunca gostei do SGA_TARGET!!! Tudo que é automático semrpe gera uns erros loucos ou não fazem de acordo com o seu ambiente! Mais isso é apenas uma opinião!

Abraços,
Rodrigo Almeida

[mpvargasParticipante]

ADDM
Horário de Captura do Snapshot Final 05/02/2009 16:03:20

Report Summary
Cache Sizes
Begin End
Buffer Cache: 5,936M 5,920M Std Block Size: 8K
Shared Pool Size: 288M 304M Log Buffer: 14,396K

Load Profile
Per Second Per Transaction
Redo size: 122,126.20 12,519.01
Logical reads: 109,112.55 11,185.00
Block changes: 709.93 72.77
Physical reads: 101,840.32 10,439.53
Physical writes: 9.28 0.95
User calls: 6,528.28 669.21
Parses: 767.82 78.71
Hard parses: 117.33 12.03
Sorts: 219.96 22.55
Logons: 10.90 1.12
Executes: 1,074.21 110.12
Transactions: 9.76

% Blocks changed per Read: 0.65 Recursive Call %: 27.94
Rollback per transaction %: 0.21 Rows per Sort: 3.90

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.76 In-memory Sort %: 100.00
Library Hit %: 89.96 Soft Parse %: 84.72
Execute to Parse %: 28.52 Latch Hit %: 99.55
Parse CPU to Parse Elapsd %: 77.87 % Non-Parse CPU: 76.40

Shared Pool Statistics
Begin End
Memory Usage %: 91.91 91.39
% SQL with executions>1: 58.51 50.25
% Memory for SQL w/exec>1: 74.64 52.85

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
db file sequential read 86,225 426 5 31.5 User I/O
CPU time 339 25.1
log file sync 1,792 93 52 6.9 Commit
db file scattered read 11,341 88 8 6.5 User I/O
log file parallel write 1,931 75 39 5.5 System I/O

[mpvargasParticipante]

Time Model Statistics
Total time in database user-calls (DB Time): 1352,4s
Statistics including the word “background” measure background process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
sql execute elapsed time 1,180.09 87.26
DB CPU 339.33 25.09
parse time elapsed 102.94 7.61
hard parse elapsed time 43.94 3.25
hard parse (sharing criteria) elapsed time 2.81 0.21
PL/SQL execution elapsed time 1.71 0.13
PL/SQL compilation elapsed time 1.46 0.11
connection management call elapsed time 1.07 0.08
hard parse (bind mismatch) elapsed time 0.19 0.01
sequence load elapsed time 0.08 0.01
repeated bind elapsed time 0.03 0.00
failed parse elapsed time 0.00 0.00
DB time 1,352.38
background elapsed time 173.76
background cpu time 4.46

[mpvargasParticipante]

Postei o ADDM.
Mas como faço para gerar o AWR e ASH?

[mpvargasParticipante]

BUFFER_CACHE = 5,7Gb
DB_MULTI_BLOCK_READ_COUNT = 16

[mpvargasParticipante]

Caros Amigos,
Gostaria de ajuda com relação ao resultado do relatório ADDM.
Obrigado.

[vieriParticipante]

este relatório está completo?

me parece que sua shared_pool está sub-dimensionada

poste o ash

é super simples!! em->performance->principal atividade->awr

[]s

[mpvargasParticipante]

Desculpe Vieri, mas não achei a opção “performance”no EM.
Quanto a SHARED_POOL, estou usando o SGA_TARGET, não sei ao certo, mas acho que nesse caso a SHARED_POOL é configurada automaticamente…

[mpvargasParticipante]

DETAILED ADDM REPORT

FINDING 1: 17% impact (957 seconds)

Waits on event “log file sync” while performing COMMIT and ROLLBACK operations
were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 17% benefit (957 seconds)
ACTION: Investigate application logic for possible reduction in the
number of COMMIT operations by increasing the size of transactions.
RATIONALE: The application was performing 1792 transactions per minute with an average redo size of 4854 bytes per transaction.
RECOMMENDATION 2: Host Configuration, 17% benefit (957 seconds)
ACTION: Investigate the possibility of improving the performance of I/O to the online redo log files.
RATIONALE: The average size of writes to the online redo log files was 4K and the average time per write was 14 milliseconds.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class “Commit” was consuming significant database time. (17% impact [957 seconds])

FINDING 2: 13% impact (736 seconds)

Wait class “User I/O” was consuming significant database time.

NO RECOMMENDATIONS AVAILABLE

ADDITIONAL INFORMATION:
Waits for I/O to temporary tablespaces were not consuming significant database time.
The throughput of the I/O subsystem was not significantly lower than
expected.
The SGA was adequately sized.

FINDING 3: 8,5% impact (480 seconds)

Time spent on the CPU by the instance was responsible for a substantial part of database time.

RECOMMENDATION 1: Application Analysis, 8,5% benefit (480 seconds)
ACTION: Parsing SQL statements were consuming significant CPU. Please refer to other findings in this task about parsing for further details.

ADDITIONAL INFORMATION:
The instance spent significant time on CPU. However, there were no
predominant SQL statements responsible for the CPU load.

FINDING 4: 7,5% impact (428 seconds)

Individual database segments responsible for significant user I/O wait were found.

RECOMMENDATION 1: Segment Tuning, 7,5% benefit (428 seconds)
ACTION: Run “Segment Advisor” on TABLE “MSIGA.CT2010” with object id 58714.
RELEVANT OBJECT: database object with id 58714
ACTION: Investigate application logic involving I/O on TABLE
“MSIGA.CT2010” with object id 58714.
RELEVANT OBJECT: database object with id 58714
RATIONALE: The I/O usage statistics for the object are: 0 full object
scans, 446689 physical reads, 818 physical writes and 0 direct reads.
RATIONALE: The SQL statement with SQL_ID “8tfdfwskvh3b1” spent
significant time waiting for User I/O on the hot object.
RELEVANT OBJECT: SQL statement with SQL_ID 8tfdfwskvh3b1

  RATIONALE: The SQL statement with SQL_ID "2jyqz480z40k3" spent 
     significant time waiting for User I/O on the hot object.
     RELEVANT OBJECT: SQL statement with SQL_ID 2jyqz480z40k3

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Wait class “User I/O” was consuming significant database time. (13% impact [736 seconds])
INFO: Waits for I/O to temporary tablespaces were not consuming
significant database time.
The throughput of the I/O subsystem was not significantly lower
than expected.
The SGA was adequately sized.

FINDING 5: 7,1% impact (403 seconds)

SQL statements were not shared due to the usage of literals. This resulted in additional hard parses which were consuming significant database time.

RECOMMENDATION 1: Application Analysis, 7,1% benefit (403 seconds)
ACTION: Investigate application logic for possible use of bind variables instead of literals.
ACTION: Alternatively, you may set the parameter “cursor_sharing” to “force”.
RATIONALE: At least 85 SQL statements with PLAN_HASH_VALUE 2502276886
were found to be using literals. An example is SQL statement with
SQL_ID “16xj2j1r64r4x” .
RELEVANT OBJECT: SQL statement with SQL_ID 16xj2j1r64r4x and
PLAN_HASH 2502276886
SELECT /+ */ R_E_C_N_O_ FROM SE5010 WHERE E5_FILIAL = ’04’ AND
E5_RECPAG = ‘R’ AND E5_DATA = ‘20080604’ AND E5_NUMCHEQ = ‘ ‘ AND E5_DOCUMEN = ‘ ‘ AND E5_PREFIXO = ‘GRA’ AND
E5_NUMERO > ‘DBUWG6’ AND D_E_L_E_T_ != ‘‘ AND (E5_DATA >= ‘20080601’ AND E5_DATA ‘C’ AND INSTR(( ‘PA/RA/BA/VL/V2/DC/D2/JR/J2/MT/M2/CM/C2/AP/EP/PE/RF/IF/CP/TL/ES/TR/DB/OD/LJ/E2/TE/PE ‘ ),( E5_TIPODOC ))>0 AND E5_FILIAL = ’04’) ORDER BY E5_FILIAL,E5_RECPAG,E5_DATA,E5_NUMCHEQ,E5_DOCUMEN,E5_PREFIXO,E5_NUMER
O,E5_PARCELA,E5_TIPO,E5_CLIFOR,E5_LOJA,E5_SEQ,R_E_C_N_O_
RATIONALE: At least 85 SQL statements with PLAN_HASH_VALUE 2502276886 were found to be using literals. An example is SQL statement with
SQL_ID “gdscx9v0cxtqj” .
RELEVANT OBJECT: SQL statement with SQL_ID gdscx9v0cxtqj and
PLAN_HASH 2502276886
SELECT /+ */ R_E_C_N_O_ FROM SE5010 WHERE E5_FILIAL = ’04’ AND E5_RECPAG = ‘R’ AND E5_DATA = ‘20080604’ AND E5_NUMCHEQ = ‘ ‘ AND E5_DOCUMEN = ‘ ‘ AND E5_PREFIXO = ‘GRA’ AND E5_NUMERO > ‘DBUWLU’ AND D_E_L_E_T_ != ‘‘ AND (E5_DATA >= ‘20080601’ AND E5_DATA ‘C’ AND INSTR(( ‘PA/RA/BA/VL/V2/DC/D2/JR/J2/MT/M2/CM/C2/AP/EP/PE/RF/IF/CP/TL/ES/TR/DB/OD/LJ/E2/TE/PE ‘ ),( E5_TIPODOC ))>0 AND E5_FILIAL = ’04’) ORDER BY E5_FILIAL,E5_RECPAG,E5_DATA,E5_NUMCHEQ,E5_DOCUMEN,E5_PREFIXO,E5_NUMER
O,E5_PARCELA,E5_TIPO,E5_CLIFOR,E5_LOJA,E5_SEQ,R_E_C_N_O_
RATIONALE: At least 6 SQL statements with PLAN_HASH_VALUE 1045956401 were found to be using literals. Look in V$SQL for examples of such SQL statements.
RATIONALE: At least 5 SQL statements with PLAN_HASH_VALUE 588552390 were found to be using literals. Look in V$SQL for examples of such SQL statements.

SYMPTOMS THAT LED TO THE FINDING:
SYMPTOM: Hard parsing of SQL statements was consuming significant
database time. (7,6% impact [432 seconds])

FINDING 6: 2,6% impact (147 seconds)

Soft parsing of SQL statements was consuming significant database time.

RECOMMENDATION 1: Application Analysis, 2,6% benefit (147 seconds)
ACTION: Investigate application logic to keep open the frequently used cursors. Note that cursors are closed by both cursor close calls and
session disconnects.

RECOMMENDATION 2: DB Configuration, 2,6% benefit (147 seconds)
ACTION: Consider increasing the maximum number of open cursors a session can have by increasing the value of parameter “open_cursors”.
ACTION: Consider increasing the session cursor cache size by increasing the value of parameter “session_cached_cursors”.
RATIONALE: The value of parameter “open_cursors” was “300” during the analysis period.
RATIONALE: The value of parameter “session_cached_cursors” was “20” during the analysis period.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      ADDITIONAL INFORMATION
      ----------------------

Wait class “Application” was not consuming significant database time.
Wait class “Concurrency” was not consuming significant database time.
Wait class “Configuration” was not consuming significant database time.
Wait class “Network” was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database time.

The analysis of I/O performance is based on the default assumption that the average read time for one database block is 10000 micro-seconds.

An explanation of the terminology used in this report is available when you run the report with the ‘ALL’ level of detail.

[Autor]

Posts