Oracle 9iR1 a 12cR1 performance e tuning ptbr 129p

Prévia do material em texto

Oracle Performance Diagnostics & Tuning
9iR1 a 12cR1
1
Ricardo Portilho Proni
ricardo@nervinformatica.com.br
Esta obra está licenciada sob a licença
Creative Commons Atribuição-SemDerivados 3.0 Brasil.
Para ver uma cópia desta licença, visite 
http://creativecommons.org/licenses/by-nd/3.0/br/.
● Performance de Sistemas Computacionais só pode ser medida em TEMPO.
● Performance Tuning deve ser reativa.
● Performance Tuning deve ter ROI.
● Apenas os maiores gargalos devem ser solucionados.
● O processo deve ser Diagnostics, e depois Tuning.
● Alto consumo de CPU não é um problema.
● O usuário não executa um SQL por prazer.
● O desenvolvedor não deveria saber como fazer um bom SQL (COBOL?).
● Ferramentas Gráficas / Enterprise Manager / Wizards / Automação são bons auxiliares.
● Bancos com bom desempenho devem ser observados.
● Conheça outros RDBMSs: TI não é lugar para paixões.
● Não acredite em nada (separar tabelas e índices?). Teste.
● Se houvesse um parâmetro que sempre deixasse o Oracle mais rápido, sem nenhum efeito 
colateral, ele já viria habilitado.
● Desenvolva um método de convencimento gerencial.
● Por algo chamar-se Storage, não quer dizer que ele não tenha problemas.
● KISS (Keep It Simple, Stupid): a probabilidade de falha cresce linearmente com o aumento de 
complexidade.
● Saiba dizer “Não”.
● Saiba dizer “Não sei”.
2
Minha abordagem
Performance Diagnostics & Tuning
3
4
Mistificação
Métodos Antigos
5
● Experiência
● Intuição
● Imprecisão
● Tempo
● Sorte
● Recursos
6
Requisitos
7
TOP Tuning
• Verificar maior consumidor de CPU;
• Verificar o SQL agressor;
• Alterar o SQL e esperar que o desempenho melhore;
• Adicionar Índices e esperar que o desempenho melhore;
• Se não melhorar, matar a sessão.
• Se o desempenho não melhorar, voltar ao início.
● Verificar Sistema Operacional (free / taskmgr / Performance Monitor);
● Verificar Sistema Operacional (vmstat / taskmgr / Performance Monitor);
● Verificar Sistema Operacional (iostat / taskmgr / Performance Monitor);
● Verificar SGA;
● Verificar PGA;
● Verificar coleta de estatísticas;
● Verificar parâmetros do Oracle;
● Verificar fragmentação de tabelas;
● Verificar LOCKs;
● Verificar SQLs que consomem mais recursos;
● …
● Construir uma teoria baseada nos dados observados;
● Alterar algo e esperar que o desempenho melhore;
● Se o cliente não gostar da teoria, apenas cite e altere alguns parâmetros 
relacionados;
● Se o desempenho não melhorar, voltar ao início.
8
Checklist Tuning
• Verificar Buffer Cache Hit Ratio;
• Verificar Data Dictionary Hit Ratio;
• Verificar SQL Cache Hit Ratio;
• Verificar Library Cache Hit Ratio;
• …
• Construir uma teoria baseada nos dados observados;
• Alterar algo (geralmente aumentar) e esperar que o desempenho melhore;
• Se o desempenho não melhorar, voltar ao início.
9
Ratios Tuning
● KIWI = Kill It With Iron;
● Adicionar Memória RAM;
● Adicionar CPUs;
● Melhorar o I/O;
● Migrar para um Servidor maior;
● Migrar para RAC;
● Adicionar Nós no RAC;
● …
● Pagar a conta, e esperar que o desempenho melhore.
● Se o desempenho não melhorar, voltar ao início.
10
KIWI Tuning
● Migrar Banco para outro servidor;
● Executar Upgrade de Banco de Dados;
● Executar Upgrade da Aplicação;
● Executar Upgrade do Middleware;
● Juntar Aplicação e Banco de Dados;
● Separar Aplicação e Banco de Dados;
● Voltar Backups;
● …
● Se o desempenho não melhorar, tentar outra coisa, até melhorar.
11
Manager Tuning
O que está errado?
12
13
Paradigma
14
Lendas do Oracle
● Todo teu SELECT deverá utilizar um índice, para que ele seja rápido.
● Terás uma área de SWAP com o dobro de tua RAM.
● Não utilizarás mais que 50% de tua RAM para a SGA.
● Utilizarás HINTs, pois tu és mais sábio que o Oracle.
● Não coletarás estatísticas do dicionário de dados.
● Deverás separar teus dados e índices.
● Deverás separar teus dados em diversas TABLESPACEs.
● Teus DATAFILEs deverão ter no máximo 2GB / 10GB / xGB.
● Não habilitarás AUTOEXTEND ON.
● Deverás executar COMMIT a cada N linhas.
● Utilizarás RAID 5, pois é mais rápido para leituras.
● Colocar os Redo Logs em SSD (ODA?).
● Não permitirás mais que um SWITCH a cada 20 minutos.
● Mas não terás grandes REDO LOGs.
● Executarás REBUILD de índices regularmente.
● Executarás MOVE de tabelas regularmente.
● Se grande a tabela tornar-se, a particionarás.
● Se quiseres mais velocidade, usarás RAC.
● Quanto mais CPUs, mais rápido teu banco de dados será.
● Se teus RATIOS estiverem altos, felizes estarão teus usuários.
● Sempre que possível, aumentarás seu DB_CACHE_SIZE e SHARED_POOL.
● Desabilitarás o AWR, pois ele causa lentidão.
● Não utilizarás memória automática. Tu és mais sábio que o Oracle.
● Se usar, deixarás a SGA_TARGET um pouco menor que a SGA_MAX_SIZE. 
● AUTOMATIC SQL TUNING é um dos cavaleiros do apocalipse.
15
Lendas do Oracle
16
Arquitetura Oracle
17
UNDO
Qual a transação mais longa que o sistema deve suportar?
UNDO_RETENTION
CREATE UNDO TABLESPACE … RETENTION GUARANTEE
AUTOEXTEND ON
18
SWAP
[root@nerv01 ~]# free
 total used free shared buffers cached
Mem: 3913052 957340 2955712 0 108616 318084
-/+ buffers/cache: 530640 3382412
Swap: 8388600 0 8388600
[root@nerv01 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 286G 12G 260G 5% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
RAM Total do Servidor
 - (quantidade máxima de conexões simultâneas + 40) x 2MB
 - Memória do Sistema Operacional
 = (OLTP) 80% SGA e 20% PGA OU
 = (OLAP) 80% PGA e 20% SGA
19
RAM x SGA
20
Checkpoint
Eventos relacionados
● control file single write
● control file parallel write
● control file sequential read
● db file single write
21
Tempo
Tempo Computacional
22
R = S + W
OU
Response Time = Service Time + Wait Time
23
Instrumentação: Mainframe
24
Instrumentação: Solaris
Oracle Wait Interface
25
26
Oracle Wait Interface
• Benchmark 7.0.12: Juan Loainza
• YAPP Paper: Anjo Kolk
27
Nascimento da OWI
● Versão 7.0.12: 104 Wait Events
● Versão 8: 140 Wait Events
● Versão 8i: 220 Wait Events
● Versão 9i: 400 Waits Events
● Versão 10gR1: >800 Wait Events
● Versão 11gR2: >1100 Wait Events 
28
Evolução da OWI
29
Enterprise Manager
• Administrative
• Application
• Cluster
• Commit
• Concurrency
• Configuration
• Idle
• Network
• Other
• Queueing
• Scheduler
• System I/O
• User I/O
30
Wait Classes
Conceitos Básicos
31
● db_block_size
● db_file_multiblock_read_count
● memory_max_target
● memory_target
● sga_max_size
● sga_target
● pga_aggregate_target
● db_cache_size (db_2k_cache_size, db_4k_cache_size, db_8k_cache_size...)
● buffer_pool_keep, buffer_pool_recycle
● shared_pool_size, shared_pool_reserved_size
● large_pool_size
● java_pool_size
● streams_pool_size
● result_cache_max_result, result_cache_max_size, result_cache_mode
● log_buffer
● fast_start_mttr_target
● log_checkpoint_interval, log_checkpoint_timeout
32
Parâmetros elementares
Hands ON !
Parâmetros elementares
33
● Verifique os parâmetros elementares em seu banco de dados.
● Altere o parâmetro memory_max_target para 0;
● Altere o parâmetro memory_target para 0;
● Altere o parâmetro sga_max_size para metade da RAM da máquina;
● Altere o parâmetro sga_target para 0;
● Altere o parâmetro db_cache_size para metade do sga_max_size.
● Altere o parâmetro shared_pool_size para metade do db_cache_size.
● Altere o parâmetro pga_aggregate_target para 100M;
34
Lab 1.1: Parâmetros elementares● SQL Statement
● Session
● Instance
35
Granularidades de Análise
36
Cenários de Análise
● Há lentidão agora.
● Tivemos lentidão ontem.
● Dynamic Performance Views
● Extended SQL Trace (Event 10046)
● Statspack / AWR
37
Ferramentas de Análise
Limitações da OWI
38
● Não é um monitoramento End-to-End
● Sem dados de consumo de CPU
● Sem dados de consumo de Memória
● Bugs
● Imprecisões
39
Limitações: OWI
● Sem histórico
40
Limitações: Views
● Muitos dados
● Altíssima granularidade
● Desempenho
● Correlação de informações
● Sessões PARALLEL
● Sessões SHARED SERVER
● Waits só disponíveis em >=9iR1
● Suporte oficial só em >10gR1
41
Limitações: Extended SQL Trace
● Baixa granularidade
● Apenas histórico
42
Limitações: Statspack / AWR
Hands ON !
Dynamic Performance Views
43
→ V$SYSTEM_EVENT
→ V$SESSION_EVENT
→ V$SESSION_WAIT
● Verifique as Dynamic Performance Views da OWI em seu banco de dados.
● Quais suas colunas mais importantes?
● Que Waits você tem em seu banco de dados?
● Habitue-se a seu conteúdo.
44
Lab 2.1: Views
Wait Events mais comuns
45
● buffer busy
● free buffer
● read by oher session
● control file single write / control file parallel write / control file sequential read
● db file single write / db file parallel read / db file parallel write
● db file scatteread read / db file sequential read
● direct path read / direct path write
● enqueue
● free buffer
● latch free / latch: library cache / latch: cache buffers chains
● library cache pin / library cache lock
● log buffer space
● log file parallel write / log file single write / log file sequential read
● log file switch (archiving needed)
● log file switch (checkpoint incomplete) / log file switch completion
● log file sync
● SQL*Net message from client / SQL*Net message to client
● SQL*Net more data from client / SQL*Net more data to client
● SQL*Net break/reset from client / SQL*Net break/reset to client
46
Wait Events mais comuns
Hands ON !
Simulando Wait Events
Gravações
47
Habilite o usuário SCOTT.
SQL> ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY TIGER;
SQL> GRANT SELECT ANY DICTIONARY TO SCOTT;
Abra uma sessão com o SCOTT com SET TIMING ON.
SQL> CONN SCOTT/TIGER
SQL> SET TIMING ON
Em outra sessão, com o SYS, verifique (várias vezes seguidas) o conteúdo 
da V$SESSION_WAIT durante a execução dos comandos do SCOTT a 
seguir.
Com o usuário SCOTT, crie uma grande tabela, com pelo menos 2GB.
SQL> CREATE TABLE T AS SELECT * FROM ALL_OBJECTS; 
SQL> INSERT INTO T SELECT * FROM T; – – Execute 8 vezes.
SQL> COMMIT;
48
Lab 3.1: Gravações
Feche e abra a sessão com o SCOTT com SET TIMING ON
SQL> CONN SCOTT/TIGER
SQL> SET TIMING ON
Em outra sessão, com o SYS, verifique o conteúdo da V$SESSION_EVENT 
relacionado a sessão do SCOTT.
SQL> SELECT SID FROM V$SESSION WHERE USERNAME = 'SCOTT';
SQL> SELECT EVENT, TOTAL_WAITS, TOTAL_TIMEOUTS, AVERAGE_WAIT FROM 
V$SESSION_EVENT WHERE SID = 17 ORDER BY 4;
Com o usuário SCOTT, duplique a grande tabela.
SQL> CREATE TABLE T2 AS SELECT * FROM T;
Na sessão do SYS, após a duplicação da tabela, verifique novamente o 
conteúdo da V$SESSION_EVENT relacionado a sessão do SCOTT
Remova a tabela T2, feche e abra a sessão com o SCOTT, e repita a 
operação. Durante a repetição da operação, verifique as mudanças do 
conteúdo da V$SESSION_EVENT relacionado a sessão do SCOTT.
49
Lab 3.2: Gravações
Responda as seguintes perguntas:
 - Onde foi gasto mais tempo nesta sessão?
 - A que se referem os maiores Wait Events?
 - Qual dos maiores Wait Events podem ser reduzidos?
 - A eliminação de um Wait Event que pode ser reduzido, causará uma melhoria de quanto tempo?
 - Como reduzir este Wait Event?
Corrija a causa deste Wait Event.
Remova a tabela T2, feche e abra a sessão com o SCOTT, e repita a operação.
50
Lab 3.3: Gravações
• Verifiquem as Dynamic Performance Views de seus servidores.
• Tragam suas dúvidas para a aula.
51
Lição de casa
Extended SQL Trace
52
● 0 = Standard Trace
● 4 = Bind Variables
● 8 = Wait States
● 12 = Bind Variables + Wait States
53
Níveis Extended SQL Trace
● Em toda a instância
● Em sua sessão
● Em outra sessão
54
Ativar Extended SQL Trace
55
Detalhes Extended SQL Trace
*** 2010-03-22 11:43:12.276
WAIT #9: nam='db file scattered read' ela= 183330 file#=4 block#=9124 blocks=26 obj#=74574 
WAIT #9: nam='db file scattered read' ela= 2528 file#=4 block#=9150 blocks=26 obj#=74574 
WAIT #9: nam='db file scattered read' ela= 170358 file#=4 block#=9176 blocks=26 obj#=74574 
WAIT #9: nam='db file scattered read' ela= 96261 file#=4 block#=9202 blocks=26 obj#=74574 
WAIT #9: nam='db file scattered read' ela= 1669 file#=4 block#=9228 blocks=26 obj#=74574 
WAIT #9: nam='db file scattered read' ela= 26055 file#=4 block#=9254 blocks=26 obj#=74574 
WAIT #9: nam='db file scattered read' ela= 4760 file#=4 block#=9280 blocks=26 obj#=74574 
WAIT #9: nam='db file scattered read' ela= 108783 file#=4 block#=9306 blocks=26 obj#=74574 
tim=1269268992840594
=====================
56
tkprof
● Não é um monitoramento End-to-End
● Sem dados de consumo de CPU
● Sem dados de consumo de Memória
● Bugs
● Imprecisões
● Muitos dados
● Altíssima granularidade
● Desempenho
● Correlação de informações
● Sessões PARALLEL
● Sessões SHARED SERVER
● Waits só disponíveis em >=9iR1
● Suporte oficial só em >10gR1
57
Limitações: Extended Trace
Hands ON !
Extended SQL Trace
Full Table Scan
58
Feche e abra a sessão com o SCOTT com SET TIMING ON
SQL> EXIT
$ sqlplus SCOTT/TIGER
SQL> SET TIMING ON
Com o usuário SYS, habilite o Extended Trace para a sessão do SCOTT:
SQL> SELECT S.USERNAME, P.SPID OS_PROCESS_ID, P.PID ORACLE_PROCESS_ID 
FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.USERNAME 
= 'SCOTT';
SQL> oradebug setospid 8708;
SQL> oradebug tracefile_name;
SQL> oradebug unlimit;
SQL> oradebug event 10046 trace name context forever, level 12;
Em outro terminal, verifique o conteúdo do Trace.
$ tail -f /u01/app/oracle/diag/rdbms/test11gr2/TEST11GR2/trace/TEST11GR2_ora_8708.trc
59
Lab 4.1: Extended SQL Trace
Com o usuário SCOTT, apague o conteúdo da grande tabela, altere o valor 
do parâmetro db_file_multiblock_read_count (apenas na sessão) e reinsira 
os dados.
SQL> TRUNCATE TABLE T2;
SQL> ALTER SESSION SET db_file_multiblock_read_count = 8;
SQL> INSERT INTO T2 SELECT * FROM T;
SQL> COMMIT;
Continue verificando o conteúdo do Trace durante a execução da operação.
Ao término da execução, verifique os valores de V$SESSION_EVENT da 
sessão do SCOTT. Guarde este resultado.
Execute o tkprof nos Trace gerado.
$ tkprof /u01/app/oracle/diag/rdbms/test11gr2/TEST11GR2/trace/TEST11GR2_ora_8708.trc
Analise o relatório gerado pelo tkprof.
Repita a operação, mas com db_file_multiblock_read_count de 50 e 1000.
60
Lab 4.2: Extended SQL Trace
Wait Events - Detalhes
61
62
Ensinar a Pescar
63
Referência
64
Performance Tuning Guide
65
MOS
Explicação: O bloco solicitado está em uso, pois outra sessão está carregando o 
bloco para o DB_CACHE_SIZE, ou outra sessão está utilizando o bloco no 
DB_CACHE_SIZE em um modo incompatível.
Causa: DB_CACHE_SIZE insuficiente, ou SQL ineficiente.
Correção: Aumente o DB_CACHE_SIZE ou altere o SQL.
P1: Número do DATAFILE.
P2: Número do bloco.
P3: ID – a solicitação vem de diferentes locais da sessão.
66
buffer busy
67
buffer busy
Explicação: O RDBMS aguarda blocos de DB_CACHE_SIZE livres.
Causa: DB_CACHE_SIZE insuficiente.
Correção: Aumente o DB_CACHE_SIZE.
P1: Número do DATAFILE.
P2: Número do bloco.
68
free buffer
Explicação: O bloco solicitadoestá em uso, pois outra sessão está carregando o 
bloco para o DB_CACHE_SIZE, ou outra sessão está utilizando o bloco no 
DB_CACHE_SIZE em um modo incompatível.
Causa: DB_CACHE_SIZE insuficiente, ou SQL ineficiente.
Correção: Aumente o DB_CACHE_SIZE ou altere o SQL.
P1: Número do DATAFILE.
P2: Número do bloco.
P3: Razão (<10g).
P3: Wait Class (>=10g).
69
read by other session
Explicação: Espera de I/O para gravar em CONTROLFILEs.
Causa: Excesso de gravação nos CONTROLFILEs ou I/O ineficiente.
Correção: Minimize as gravações nos CONTROLFILEs ou melhore o mecanismo 
de I/O.
P1: Quntidade de CONTROLFILEs.
P2: Quantidade de blocos.
P3: Quantidade de solicitações de I/O.
70
control file parallel write
Explicação: Espera de I/O para gravar em CONTROLFILEs.
Causa: Excesso de gravação nos CONTROLFILEs ou I/O ineficiente.
Correção: Minimize as gravações nos CONTROLFILEs ou melhore o mecanismo 
de I/O.
P1: Número do CONTROLFILE.
P2: Número do bloco.
P3: Quantidade de blocos.
71
control file single write
Explicação: Espera de I/O para ler os CONTROLFILEs.
Causa: Excesso de leitura nos CONTROLFILEs ou I/O ineficiente.
Correção: Minimize as leituras nos CONTROLFILEs ou melhore o mecanismo de 
I/O.
P1: Número do CONTROLFILE.
P2: Número do bloco.
P3: Quantidade de blocos.
72
control file sequential read
Explicação: Gravações de dados nos DATAFILEs esperam pelo I/O.
Causa: Excesso de gravações ou lentidão de I/O.
Correção: Minimize as gravações ou melhore o mecanismo de I/O.
P1: Quantidade de requisições.
P2: Interrupt.
P3: Timeout.
73
db file parallel write
Explicação: Uma gravação no HEADER do DATAFILE espera pelo I/O.
Causa: Excesso de gravações no HEADER dos DATAFILEs ou lentidão de I/O.
Correção: Minimize as gravações no HEADER dos DATAFILEs ou melhore o 
mecanismo de I/O.
P1: Quantidade de requisições.
P2: Interrupt.
P3: Timeout.
74
db file single write
Explicação: Durante RECOVER ou durante PREFETCHING, leituras de 
DATAFILEs esperam pelo I/O.
Causa: RECOVER muito longo, PREFETCHING excessivo, ou lentidão de I/O.
Correção: Acelere o RECOVER, minimize o PREFETCHING, ou melhore o 
mecanismo de I/O.
P1: Quantidade de DATAFILEs.
P2: Quantidade de blocos.
P3: Quantidade de requisições.
75
db file parallel read
76
User I/O
● CURSOR_SHARING
● DB_FILE_MULTIBLOCK_READ_COUNT
● OPTIMIZER_INDEX_CACHING
● OPTIMIZER_INDEX_COST_ADJ
● OPTIMIZER_MODE
● PGA_AGGREGATE_TARGET
● STAR_TRANSFORMATION_ENABLED
77
Influenciando o Otimizador
Explicação: Durante FTS, leituras de DATAFILEs esperam pelo I/O.
Causa: DB_CACHE_SIZE insuficiente, FTS desnecessário ou lentidão de I/O
Correção: Aumente o DB_CACHE_SIZE, elimine o FTS, ou melhore o 
mecanismo de I/O.
P1: Número do DATAFILE.
P2: Bloco inicial.
P3: Quantidade de blocos.
78
db file scattered read
Explicação: Durante a leitura de um bloco, leituras de DATAFILEs esperam pelo 
mecanismo de I/O.
Causa: DB_CACHE_SIZE insuficiente, leitura desnecessária ou lentidão de I/O
Correção: Aumente o DB_CACHE_SIZE, elimine a leitura desnecessária, ou 
melhore o mecanismo de I/O.
P1: Número do DATAFILE.
P2: Bloco inicial.
P3: Quantidade de blocos.
79
db file sequential read
Explicação: Leitura / gravação entre DATAFILEs / TEMPFILEs e PGA.
Causa: PGA insuficiente, ou lentidão de I/O.
Correção: Aumente a PGA, ou melhore o mecanismo de I/O.
P1: Número do arquivo (DATAFILE ou TEMPFILE).
P2: Bloco inicial.
P3: Quantidade de blocos.
80
direct path read / direct path write
Explicação: Mecanismo de fila ordenada do RDBMS.
Causa: Diversas, dependendo do tipo de fila.
Correção: Diversas, dependendo do tipo de fila.
P1: Tipo ou modo da enqueue.
P2: ID1 (como na V$LOCK).
P3: ID2 (como na V$LOCK).
Problemas mais comuns:
● TX, Transaction
● TM, DML Enqueue
● HW, High-Water Lock
● SQ, Sequence Number Enqueue
● CF, Controlfile Transaction
81
enqueue
Explicação: Mecanismo de fila desordenada do RDBMS.
Causa: Diversas, dependendo do tipo de fila.
Correção: Diversas, dependendo do tipo de fila.
P1: Endereço da Latch (como na V$LATCH).
P2: Número da Latch (como na V$LATCH).
P3: Quantidade de tentativas.
Problemas mais comuns:
● shared pool
● library cache
● cache buffers lru chain
● cache buffers chains
● row cache objects
82
latch free
Explicação: Uso incompatível do objeto entre duas sessões.
Causa: Uso do objeto de forma incompatível entre duas sessões.
Correção: Finalizar o uso do objeto por uma das sessões.
P1: Endereço do objeto.
P2: Endereço do load lock.
P3: Mode + Namespace.
SQL> SELECT /*+ ORDERED */ W1.SID WAITING_SESSION, H1.SID HOLDING_SESSION, 
W.KGLLKTYPE LOCK_OR_PIN, W.KGLLKHDL ADDRESS, 
DECODE(H.KGLLKMOD,0,’None’,1,’Null’,2,’Share’,3,’Exclusive’,'Unknown’) MODE_HELD, 
DECODE(W.KGLLKREQ,0,’None’,1,’Null’,2,’Share’,3,’Exclusive’,'Unknown’) MODE_REQUESTED 
FROM DBA_KGLLOCK W, DBA_KGLLOCK H, V$SESSION W1, V$SESSION H1 WHERE 
(((H.KGLLKMOD != 0) AND (H.KGLLKMOD != 1) AND ((H.KGLLKREQ = 0) OR (H.KGLLKREQ = 1))) 
AND (((W.KGLLKMOD = 0) OR (W.KGLLKMOD= 1)) AND ((W.KGLLKREQ != 0) AND (W.KGLLKREQ !
= 1)))) AND W.KGLLKTYPE = H.KGLLKTYPE AND W.KGLLKHDL = H.KGLLKHDL AND W.KGLLKUSE 
= W1.SADDR AND H.KGLLKUSE = H1.SADDR;
 
SQL> SELECT TO_NAME FROM V$OBJECT_DEPENDENCY WHERE TO_ADDRESS = 
‘0700000010F62750';
83
library cache pin / library cache lock
Explicação: Mais espaço no LOG_BUFFER é necessário para gravações.
Causa: LOG_BUFFER insuficiente, REDO LOGs insuficientes, ou I/O lento.
Correção: Aumente o LOG_BUFFER, aumente a quantidade / tamanho de 
REDO LOGs, ou melhore o mecanismo de I/O.
P1: Quantidade de REDO LOGs.
P2: Quantidade de blocos do sistema operacional.
P3: Quantidade de requisições de I/O.
84
log buffer space
Explicação: Durante gravação de REDO LOGs, o LGWR espera pelo I/O.
Causa: Excesso de membros nos grupos de REDO LOGs ou lentidão de I/O.
Correção: Reduza a quantidade de membros nos grupos de REDO LOGs ou 
melhore o mecanismo de I/O.
P1: Quantidade de REDO LOGs.
P2: Quantidade de blocos de sistema operacional.
P3: Quantidade de requisições de I/O.
85
log file parallel write
Explicação: Durante gravação no HEADER de um REDO LOGs, o LGWR 
espera pelo I/O.
Causa: Excesso de gravações no HEADER do REDO LOG ou lentidão de I/O.
Correção: Reduza a quantidade de gravações no HEADER do REDO LOG ou 
melhore o mecanismo de I/O.
P1: Número do REDO LOG.
P2: Número do bloco.
P3: Quantidade de blocos.
86
log file single write
Explicação: Durante leitura de REDO LOGs, o LGWR espera pelo I/O.
Causa: Lentidão de I/O.
Correção: Melhore o mecanismo de I/O.
P1: Número do REDO LOG.
P2: Número do bloco.
P3: Quantidade de blocos.
87
log file sequential read
Explicação: Todos os grupos de REDO LOGs foram utilizados e ainda são 
necessários para um eventual RECOVER, pois o ARCn ainda não criou os 
ARCHIVED REDO LOGs e o DBWR ainda não gravou seu conteúdo nos 
DATAFILEs.
Causa: REDO LOGs sub-dimensionados, configuração inadequada de destino 
de ARCHIVED REDO LOGs ou I/O ineficiente.
Correção: Aumentar os REDO LOGs em quantidade e/ou tamanho, corrigir a 
configuração de destino do ARCn, ou melhorar o mecanismo de I/O.
P1: Não utilizado.
P2: Não utilizado.
P3: Não utilizado.
Variações:
● log file switch (archiving needed)
● log file switch (checkpoint incomplete)
● log file switch (clearing log file)
● log file switch (private strand flush incomplete)
● log file switch completion
88
log file switch
Explicação: Um CHECKPOINT foi executado, e precisa ser registrado no REDO 
LOG, e o LGRW está aguardando pelo mecanismo de I/O.
Causa: COMMIT em quantidade excessiva, ou I/O ineficiente.
Correção: Reduzir a quantidade de COMMITs ou otimizar o mecanismo de I/O.
P1: Número do Log Buffer.P2: Não utilizado.
P3: Não utilizado.
89
log file sync
Explicação: Espera durante comunicação via rede com o protocolo SQL*Net.
Causa: Sessão inativa, latência de rede ou limitação do cliente.
Correção: Eliminar a sessão inativa, minimizar a latência na rede ou minimizar a limitação 
do cliente.
P1: Driver de rede.
P2: Quantidade de bytes.
P3: Não utilizado.
Variações
● SQL*Net message from client
● SQL*Net message to client
● SQL*Net more data from client
● SQL*Net more data to client
● SQL*Net break/reset to client
● SQL*Net message from dblink
● SQL*Net message to dblink
● SQL*Net more data from dblink
● SQL*Net more data to dblink
● SQL*Net break/reset to dblink
90
SQL*Net message to / from client
Hands ON !
Simulando Wait Events
LGWR x DBWR
91
Feche e abra a sessão com o SCOTT com SET TIMING ON
SQL> CONN SCOTT/TIGER
SQL> SET TIMING ON
Com o usuário SCOTT, apague o conteúdo da grande tabela, e reinsira os 
dados.
SQL> TRUNCATE TABLE T2;
SQL> INSERT INTO T2 SELECT * FROM T;
SQL> COMMIT;
Ao término da execução, verifique os valores de V$SESSION_EVENT da sessão 
do SCOTT. Guarde o resultado.
Altere o valor do parâmetro log_buffer para 512k, repita a operação, e compare.
Altere o valor do parâmetro log_buffer para 10m, repita a operação, e compare.
Altere o valor do parâmetro log_buffer para 100m, repita a operação, e compare.
Mantenha log_buffer com a configuração mais otimizada.
Altere o parâmetro FAST_START_MTTR_TARGET para 1, repita a operação, e 
compare.
92
Lab 5.1: LGWR x DBWR
Parallel SQL
93
Permite Query, DML e DDL.
Um objeto pode ter Parallel permanente, independente do SQL:
SQL> ALTER TABLE SCOTT.T PARALLEL DEGREE 4;
O Parallel SQL pode ser utilizado diretamente no SQL:
SQL> SELECT /*+ PARALLEL(T2 4) */ COUNT(*) FROM T2;
94
Parallel SQL
Parâmetros:
PARALLEL_MIN_SERVERS = Número entre 0 e PARALLEL_MAX_SERVERS.
PARALLEL_MAX_SERVERS = De 0 a 3600.
PARALLEL_MIN_PERCENT = De 0 a 100.
PARALLEL_DEGREE_POLICY = MANUAL, LIMITED ou AUTO.
PARALLEL_MIN_TIME_THRESHOLD = AUTO | Segundos. 
PARALLEL_ADAPTIVE_MULTI_USER = true ou false.
PARALLEL_DEGREE_LIMIT = CPU, IO ou Número.
PARALLEL_SERVERS_TARGET = Número entre 0 e PARALLEL_MAX_SERVERS.
PARALLEL_THREADS_PER_CPU = Qualquer número.
PARALLEL_EXECUTION_MESSAGE_SIZE = De 2148 a 32768
PARALLEL_FORCE_LOCAL = true ou false.
PARALLEL_INSTANCE_GRouP = Oracle RAC service_name ou group_name.
PARALLEL_AUTOMATIC_TUNING: Deprecated.
PARALLEL_IO_CAP_ENABLED = Deprecated.
95
Parallel SQL
Hands ON !
Simulando Wait Events
Design de Aplicação
96
Reinicie a Instance.
Verifique o conteúdo da V$SYSTEM_EVENT. Guarde esta consulta.
Abra a sessão com o SCOTT com SET TIMING ON.
Em seguida, faça um SELECT da tabela toda.
$ sqlplus SCOTT/TIGER
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM T;
Ao término da execução, verifique os valores de V$SESSION_EVENT da 
sessão do SCOTT. Guarde o resultado.
Repita a operação com PARALLEL, e compare.
SQL> SELECT /*+ PARALLEL(T 4) */ COUNT(*) FROM T;
SQL> SELECT /*+ PARALLEL(T 20) */ COUNT(*) FROM T;
97
Lab 6.1: Design de Aplicação
98
Paralelismo
SQL> SELECT SID, SERIAL#, QCSID, QCSERIAL# FROM V$PX_SESSION;
 SID SERIAL# QCSID QCSERIAL#
---------- ---------- ---------- ----------
 202 5249 12 387
 20 3587 12 387
 75 4043 12 387
 141 233 12 387
 204 751 12 387
 16 229 12 387
 73 3279 12 387
 137 403 12 387
 203 1137 12 387
 18 103 12 387
 79 5 12 387
 134 3431 12 387
 206 5 12 387
 19 5 12 387
 76 31 12 387
 140 5 12 387
 12 387 12
99
Lab 6.2: Design de Aplicação
Crie esta tabela com o usuário SCOTT:
SQL> CREATE TABLE T3 (NUMERO NUMBER);
Observe o conteúdo dos seguintes scripts Perl, os execute, e compare:
$ perl /home/oracle/CommitBAD_BindsBAD.pl
$ perl /home/oracle/CommitBAD_BindsGOOD.pl
$ perl /home/oracle/CommitGOOD_BindsBAD.pl
$ perl /home/oracle/CommitGOOD_BindsGOOD.pl
100
Lab 6.3: Design de Aplicação
Crie um índice BITMAP na tabela T3 com o usuário SCOTT:
SQL> CREATE BITMAP INDEX IDX_BITMAP_T3 ON T3(NUMERO);
Execute um INSERT nesta tabela, sem executar COMMIT ou fechar a 
sessão.:
SQL> INSERT INTO T3 VALUES (1);
Abra outra sessão com o SCOTT, e faça outro INSERT na tabela T3:
SQL> INSERT INTO T3 VALUES (1);
Com o usuário SYS, verifique a V$SESSION_WAIT.
Repita o exercício, mas utilizando um índice BTREE:
SQL> DROP INDEX IDX_BITMAP_T3;
SQL> CREATE INDEX IDX_T3 ON T3(NUMERO);
101
BITMAP x BTREE
SQL> SELECT COUNT(*) FROM T; - - Sem índice.
COUNT(1)
———-
10936000
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T; - - Sem índice.
COUNT(DISTINCT(OWNER))
———————-
28
Decorrido: 00:00:26.75
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T; - - Com índice BTREE.
COUNT(DISTINCT(OWNER))
———————-
28
Decorrido: 00:00:05.29
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T; - - Com índice BITMAP.
COUNT(DISTINCT(OWNER))
———————-
28
Decorrido: 00:00:01.84
102
BITMAP x BTREE
SQL> SELECT COUNT(*) FROM T;
COUNT(1)
———-
10936000
SQL> SELECT COUNT(DISTINCT(OWNER)) FROM T;
COUNT(DISTINCT(OWNER))
———————-
28
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T; -- Com índice BTREE
COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998
Decorrido: 00:00:08.95
SQL> SELECT COUNT(DISTINCT(OBJECT_NAME)) FROM T; – Com índice BITMAP
COUNT(DISTINCT(OBJECT_NAME))
—————————-
40998
Decorrido: 00:00:03.15
103
Lab 6.4: Design de Aplicação
Abra uma sessão com o usuário SCOTT com SET TIMING ON:
Crie um índice BTREE na coluna OWNER da tabela T:
SQL> CREATE INDEX IDX_T ON T(OWNER);
Execute este SQL:
SQL> SELECT COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';
Com o usuário SYS, verifique o conteúdo da V$SESSION_EVENT da sessão 
do SCOTT. Guarde o resultado.
Feche e abra a sessão com o SCOTT com SET TIMING ON
Altere a sessão para utilizar o Rule Based Optimizer:
SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;
Execute este SQL:
SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE 
OBJECT_NAME = 'T';
Estatísticas
104
Optimizer Statistics
Table statistics
Number of rows
Number of blocks
Average row length
Column statistics
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)
Extended statistics
Index statistics
Number of leaf blocks
Levels
Clustering factor
System Statistics
I/O performance and utilization
CPU performance and utilization
105
Estatísticas
DBMS_AUTO_TASK_ADMIN.DISABLE
DBMS_STATS.GATHER_DATABASE_STATS
DBMS_STATS.GATHER_DICTIONARY_STATS
DBMS_STATS.GATHER_SCHEMA_STATS
DBMS_STATS.GATHER_TABLE_STATS
DBMS_STATS.GATHER_INDEX_STATS
DBMS_STATS.DELETE_TABLE_STATS
DBMS_STATS.LOCK_TABLE_STATS
DBMS_STATS.EXPORT_*_STATS
DBMS_STATS.IMPORT_*_STATS
OPTIMIZER_DYNAMIC_SAMPLING 
DBMS_STATS.GATHER_SYSTEM_STATS
106
Estatísticas
Hands ON !
DBMS_SQLTUNE
107
108
Lab 7.1: DBMS_SQLTUNE
Escolha um dos SQLs mais lentos na V$SQL e o analise com DBMS_SQLTUNE.
DECLARE RET_VAL VARCHAR2(4000);
BEGIN
RET_VAL := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => '12345678910', SCOPE => 
DBMS_SQLTUNE.SCOPE_COMPREHENSIVE, TIME_LIMIT => 60, TASK_NAME => 'Portilho Tuning Task', 
DESCRIPTION => 'Portilho Tuning Task');
END;
/
BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK('Portilho Tuning Task'); END;
/
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('Portilho Tuning Task') RECOMMENTATION FROM 
DUAL;
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('PortilhoTuning Task') RECOMMENTATION FROM 
DUAL;
BEGIN DBMS_SQLTUNE.DROP_TUNING_TASK('Portilho Tuning Task'); END;
/
Fragmentação
109
● Blocos logicamente contíguos espalhados fisicamente.
● Espaço livre na TABLESPACE / DATAFILEs.
● Espaço livre da TABELA.
● Espaço livre no ÍNDICE.
● Row Chaining.
● Migrated Rows.
● EXTENTs.
110
Fragmentação
111
Fragmentação: SHRINK
112
Fragmentação: COALESCE
113
Fragmentação: Row Chaining
114
Fragmentação: Row Migration
Hands ON !
DBMS_ADVANCED_REWRITE
115
116
Lab 8.1: DBMS_ADVANCED_REWRITE
Abra uma sessão com o usuário SCOTT com SET TIMING ON:
Altere a sessão para utilizar o Rule Based Optimizer:
SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;
Execute este SQL e anote seu tempo de execução:
SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE 
OBJECT_NAME = 'T';
Execute este SQL e anote seu tempo de execução:
SQL> SELECT COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';
Com o usuário SYS, dê as permissões necessárias para que o usuário 
SCOTT utilize o DBMS_ADVANCED_REWRITE:
$ sqlplus / AS SYSDBA
SQL> GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO SCOTT;
SQL> GRANT CREATE MATERIALIZED VIEW TO SCOTT;
117
Lab 8.2: DBMS_ADVANCED_REWRITE
Na sessão do usuário SCOTT, execute o DBMS_ADVANCE_REWRITE:
BEGIN
 SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
 NAME => 'PORTILHO_REWRITE',
 SOURCE_STMT => 'SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE 
OBJECT_NAME = ''T''',
 DESTINATION_STMT => 'SELECT COUNT(OBJECT_NAME) FROM T T_ALIAS WHERE 
OBJECT_NAME = ''T''',
 VALIDATE => FALSE,
 REWRITE_MODE => 'TEXT_MATCH');
END;
/
Execute novamente este SELECT e verifique seu tempo de execução:
SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';
Remova o REWRITE, execute novamente o SELECT e verifique seu tempo de 
execução:
SQL> EXEC SYS.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE (NAME => 
'PORTILHO_REWRITE');
SQL> SELECT /*+ INDEX(T_ALIAS,IDX_T) */ COUNT(*) FROM T T_ALIAS WHERE OBJECT_NAME = 'T';
Resource Plan
118
Separação de Recursos por:
● ORACLE_USER
● SERVICE_NAME
● CLIENT_OS_USER
● CLIENT_PROGRAM
● CLIENT_MACHINE
● MODULE_NAME
● MODULE_NAME_ACTION
● SERVICE_MODULE
● SERVICE_MODULE_ACTION
Controle dos Recursos:
● CPU
● Sessões Ativas
● Paralelismo
● I/O (>= 11gR1)
119
Resource Plan
120
Resource Plan
Mudanças de planos:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PEAKTIME';
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'OFF-PEAK';
Monitoração:
● DBA_RSRC_CONSUMER_GROUP_PRIVS 
● DBA_RSRC_PLANS 
● V$SESSION 
● V$RSRC_PLAN
● V$RSRC_CONSUMER_GROUP
● V$RSRC_SESSION_INFO
● V$RSRC_PLAN_HISTORY
● V$RSRC_CONS_GROUP_HISTORY
● V$RSRCMGRMETRIC
● V$RSRCMGRMETRIC_HISTORY
121
Resource Plan
LAB 10 – Resource Plan
Hands On !
122
Analise o código do arquivo ResourcePlan.sql.
Altere o arquivo para suprir as necessidades de três tipos de uso do banco 
de dados:
● Usuário SOE: OLTP, deve ter muita prioridade durante o dia, e pouca durante a noite.
● Usuario HR: BI, deve ter pouca prioridade durante o dia e muita durante a noite.
● Usuário SCOTT: AD-HOC, só pode utilizar CPU que nenhum dos usuários acima estiver 
utilizando.
● Outros: OTHERS_GROUP, só podem utilizar CPU que nenhum dos usuários acima 
estiver utilizando.
123
Lab 9.1 – Resource Plan
Statspack / AWR
124
● Statspack
$ sqlplus / AS SYSDBA
SQL> @?/rdbms/admin/spcreate.sql
$ sqlplus PERFSTAT/PERFSTAT
SQL> @?/rdbms/admin/spauto.sql
SQL> EXECUTE STATSPACK.SNAP;
SQL> @?/rdbms/admin/spreport.sql
• AWR / ASH Report
SQL> EXEC dbms_workload_repository.create_snapshot;
SQL> @?/rdbms/admin/awrrpt.sql
125
Statspack / AWR
Hands ON !
Statspack / AWR
126
Tire um SNAPSHOT avulso.
$ sqlplus / AS SYSDBA
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
Execute a carga no sistema.
$ unzip -q swingbench25909.zip
$ cd swingbench/bin
./charbench -uc 10 -cs //localhost/ORCL
Tire outro SNAPSHOT avulso.
$ sqlplus / AS SYSDBA
SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
Tire um relatório AWR comparando os dois SNAPSHOTs.
SQL> @?/rdbms/admin/awrrpt.sql
Analise o relatório AWR.
127
Lab 10.1: AWR
Revisão
128
● O Banco de Dados está lento agora:
● Encontrar indícios do gargalo na V$SYSTEM_EVENT.
● Encontrar indícios do gargalo na V$SESSION_WAIT.
● Encontrar o(s) SID(s) ofensor na V$SESSION_WAIT.
● Encontrar o maior Wait Event deste(s) SID(s) na V$SESSION_EVENT.
● Corrigir o maior Wait Event possível.
● Se o tempo esta satisfatório, finalizar o processo.
● O Banco de Dados estava lento ontem:
● Encontrar indícios do gargalo na V$SYSTEM_EVENT.
● Encontrar o maior Wait Event via Statspack / AWR.
● Corrigir o maior Wait Event possível.
● Se o tempo esta satisfatório, finalizar o processo.
● Este SQL está lento:
● Executar o comando SQL com Extended SQL Trace.
● Encontrar indícios do gargalo durante a execução do SQL Trace.
● Encontrar o maior Wait Event via tkprof.
● Corrigir o maior Wait Event possível.
● Se o tempo esta satisfatório, finalizar o processo.
129
Método de Tuning
	Slide 1
	Slide 2
	Slide 3
	Slide 4
	Slide 5
	Slide 6
	Slide 7
	Slide 8
	Slide 9
	Slide 10
	Slide 11
	Slide 12
	Slide 13
	Slide 14
	Slide 15
	Slide 16
	Slide 17
	Slide 18
	Slide 19
	Slide 20
	Slide 21
	Slide 22
	Slide 23
	Slide 24
	Slide 25
	Slide 26
	Slide 27
	Slide 28
	Slide 29
	Slide 30
	Slide 31
	Slide 32
	Slide 33
	Slide 34
	Slide 35
	Slide 36
	Slide 37
	Slide 38
	Slide 39
	Slide 40
	Slide 41
	Slide 42
	Slide 43
	Slide 44
	Slide 45
	Slide 46
	Slide 47
	Slide 48
	Slide 49
	Slide 50
	Slide 51
	Slide 52
	Slide 53
	Slide 54
	Slide 55
	Slide 56
	Slide 57
	Slide 58
	Slide 59
	Slide 60
	Slide 61
	Slide 62
	Slide 63
	Slide 64
	Slide 65
	Slide 66
	Slide 67
	Slide 68
	Slide 69
	Slide 70
	Slide 71
	Slide 72
	Slide 73
	Slide 74
	Slide 75
	Slide 76
	Slide 77
	Slide 78
	Slide 79
	Slide 80
	Slide 81
	Slide 82
	Slide 83
	Slide 84
	Slide 85
	Slide 86
	Slide 87
	Slide 88
	Slide 89
	Slide 90
	Slide 91
	Slide 92
	Slide 93
	Slide 94
	Slide 95
	Slide 96
	Slide 97
	Slide 98
	Slide 99
	Slide 100
	Slide 101
	Slide 102
	Slide 103
	Slide 104
	Slide 105
	Slide 106
	Slide 107
	Slide 108
	Slide 109
	Slide 110
	Slide 111
	Slide 112
	Slide 113
	Slide 114
	Slide 115
	Slide 116
	Slide 117
	Slide 118
	Slide 119
	Slide 120
	Slide 121
	Slide 122
	Slide 123
	Slide 124
	Slide 125
	Slide 126
	Slide 127
	Slide 128
	Slide 129