|
Ederson Elias -- Thanks for the question regarding "Otimização base dados", version
Submitted on 9-Apr-2007 15:57 Eastern US time
Last updated 10-Apr-2007 20:34
You Asked
Boa tarde (Tio) Márcio,
Parabéns pelos excelentes artigos no BLOG.
Estreando sua página no ASK, gostaria que comentasse sobre otimização, um assunto que tira o sono quando a coisa está ruim.
Migrei (copiei) recentemente um banco de um servidor antigo para um novo com export. Após coletado as novas estatísticas, sem modificar nenhum parâmetro de otimização (como era no servidor antigo), um certo schema (entre 12) não se comportou como deveria, mesmo com as estatísticas coletadas e o processador 10 vezes superior ao servidor antigo, tivemos um decréscimo de performance que causou transtornos por um dia neste sistema.
Alterei SGA, aumentei cache, coloquei checkpoint a cada 5 minutos, aumente redolog, criei indices novos ==> TUDO EM VÃO.
Não queria mexer em parâmetros de otimização, porque os outros 11 schemas estavam rodando redondinho, sem nenhum problema.
Mas devido ao desespero, fui obrigado a testar o STAR_TRANSFORMATION e para a minha surpresa, o problema de lentidão no schema específico desapareceu. Nem precisa dizer que a velocidade nos outros schemas (que já estava boa) melhorou mais ainda.
Apesar de já trabalhar com o Oracle já há algum tempo (desde 96), me considero um estudante e venho pedir uma luz neste assunto, pois estou com a pulga atrás do database.
Obrigado.
Ederson Elias.
www.alfa.br
and we said...
Primeiramente, como prometido, voce está concorrendo ao livro do Thomas Kyte, Oracle Programação avançada (tradução do One-On-one)
Agora vamos a dúvida.
A principal peça em sua dúvida não veio, versão do Oracle! 8i, 9i, 10g?...
Se voce tem, nesse esquema, um modelo baseado em DW, sim, pode haver vantagem em setar o star_transformation_enabled para true, principalmente se houver bitmap index criados. Porém, é preciso entender que setando esse parâmetro apenas na tentativa e erro não resolve. O que ele faz? Permite que uma query normal seja considerada e transformada em star query (fato x dimensão) e com alguma sorte, ele usa bitmap (mesmo inventado).
Acho que o correto seria identificar a causa da lentidão justamente nesse esquema. Veja as diferenças dos servidores, parametrização, etc. Não deixe de olhar nos planos de execução das queries mais caras (rode o statspack e veja as top 5).
Otimização base dados
April 09, 2007
Reviewer: Ederson Elias from Goiânia
Obrigado pela resposta, Márcio.
Na pressa faltou informar que o antigo server era um RISC HP (HP L1000 1x360Mhz) 64bits com HP-UX 10 e 2Gb de RAM, rodando Oracle 9.2.0.3 64 bits.
O novo server é HP DL380 64bits (2 Xeon 5160 DualCore 3.0Ghz) com 8Gb RAM, rodando RHES4 64bits com Oracle 9.2.0.4 também 64bits.
O esquema problemático não é um modelo baseado em DW, é OLTP puro e não há indices bitmap. Havia uma consulta (somente uma) que impactava no sistema, pois era muito usada e a tabela é pequena, mas de nenhum modo consegui otimizar a consulta, exceto quando coloquei o star_transformation no select (como deu certo, no desespero habilitei o enable dele no banco).
Hoje, lendo em seu blog sobre forçar RBO em esquema CBO, pode ter dado uma luz, e lembrei que não tentei deletar as estatísticas da tabela que estava travando a consulta.
Vou rever estas configurações.
Obrigado
Followup:
Bem de início eu faria um plano (urgente) para aplicar o patchset 7 (9.2.0.8) - o mais recente. Ajustaria a cpu, já que está saindo de 1 cpu para 2). Exemplo:
ops$marcio:LX10G> show parameter cpu
NAME TYPE VALUE
------------------------- ----------- ------------
cpu_count integer 2
parallel_threads_per_cpu integer 2
Estuda o plano de execução na máquina velha e se quiser, poste aqui para analise.
April 10, 2007
Reviewer: Ederson Elias Oliveira from Goiânia
Bom dia Márcio,
Agradeço o empenho.
Verifiquei os planos de execução, e listo mais informações do ambiente:
Servidor novo:
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ------- -------
cpu_count integer 4
parallel_threads_per_cpu integer 2
SQL> show sga
Total System Global Area 1077905992 bytes
Fixed Size 744008 bytes
Variable Size 805306368 bytes
Database Buffers 268435456 bytes
Redo Buffers 3420160 bytes
**************************
Servidor antigo:
SQL> show parameter cpu
NAME TYPE VALUE
------------------------------------ ----------- -------
cpu_count integer 1
parallel_threads_per_cpu integer 2
SQL> show sga
Total System Global Area 404188680 bytes
Fixed Size 736776 bytes
Variable Size 335544320 bytes
Database Buffers 67108864 bytes
Redo Buffers 798720 bytes
------------------------------------------------------------------
O select problemático envolve:
SQL> select count(*) from IV_AGENDA;
COUNT(*)
----------
267014
SQL> select count(*) from GE_PESSOA;
COUNT(*)
----------
26203
SQL> select count(*) from GE_USUARIO;
COUNT(*)
----------
268
Verificando Indices (no servidor novo, que é cópia da base antiga)
------------------------------------------------------------------
SQL> @ve_ind
Enter value for tabela: IV_AGENDA
NOMEINDEX UNICIDADE POSICAO NOMECOLUNA
------------------------------ --------- ---------- ------------------
IV_AGENDAIE1 NONUNIQUE 1 SEQPESSOA
IV_AGENDAIE12 NONUNIQUE 1 SEQUSUARIO
NONUNIQUE 2 HISTORICOORIGEM
IV_AGENDAIE2 NONUNIQUE 1 DTAAGENDA
NONUNIQUE 2 REALIZADA
IV_AGENDAIE7 NONUNIQUE 1 DTAULTRESULTADO
NONUNIQUE 2 ULTRESULTADO
IV_AGENDAIE8 NONUNIQUE 1 SEQUSUARIO
NONUNIQUE 2 REALIZADA
IV_AGENDAIE9 NONUNIQUE 1 PROCESSO
IV_AGENDAIF102 NONUNIQUE 1 ACAO
IV_AGENDAIF434 NONUNIQUE 1 SEQUSUARIO
I_DA_ONTATO_FK NONUNIQUE 1 SEQCONTATO
SYS_C0022367 UNIQUE 1 SEQAGENDA
14 rows selected.
Elapsed: 00:00:00.72
SQL> /
Enter value for tabela: GE_USUARIO
NOMEINDEX UNICIDADE POSICAO NOMECOLUNA
------------------------------ --------- ---------- ------------------
GE_USUARIOXAK1 UNIQUE 1 CODUSUARIO
SYS_C0022310 UNIQUE 1 SEQUSUARIO
Elapsed: 00:00:00.63
SQL> /
Enter value for tabela: GE_PESSOA
NOMEINDEX UNICIDADE POSICAO NOMECOLUNA
------------------------------ --------- ---------- ---------------
GE_PESSOAIE10 NONUNIQUE 1 GRUPO
GE_PESSOAIF398 NONUNIQUE 1 SEQCIDADE
GE_PESSOAIF406 NONUNIQUE 1 SEQCIDADE
NONUNIQUE 2 SEQBAIRRO
I_OA_AIRRO_FK NONUNIQUE 1 SEQBAIRRO
I_OA_OGRADOUR_FK NONUNIQUE 1 SEQLOGRADOURO
XIE1IV_CLIENTE NONUNIQUE 1 NOMERAZAO
XIE2IV_CLIENTE NONUNIQUE 1 FONENRO1
XIE3IV_CLIENTE NONUNIQUE 1 NROCGCCPF
XIE7IV_CLIENTE NONUNIQUE 1 FONENRO2
XIE8IV_CLIENTE NONUNIQUE 1 FONENRO3
XPKIV_CLIENTE UNIQUE 1 SEQPESSOA
12 rows selected.
Elapsed: 00:00:00.25
------------------------------------------------------------------
Ei-lo:
------------------------------------------------------------------
Select AG.SEQAGENDA,
AG.DTALEITURA,
AG.DTAAGENDA,
AG.PRIORIDADE,
AG.CLASSE,
AG.ASSUNTO,
AG.SEQPESSOA,
AG.SEQUSUARIO,
AG.USUGEROUACAO,
PES.NOMERAZAO,
PES.FANTASIA,
US.CODUSUARIO,
AG.DEPARTAMENTO,
AG.REVISARHISTORICO,
AG.ACAO,
AG.TAREFACOMPROMISSO,
AG.PROCESSO,
AG.PROCESSOPAI,
AG.REALIZADA,
AG.DETALHE,
AG.VENDEDOR
from IV_AGENDA AG, GE_PESSOA PES, GE_USUARIO US
where PES.SEQPESSOA = AG.SEQPESSOA
AND US.SEQUSUARIO = AG.SEQUSUARIO
and (AG.TIPOACESSO = 'P' OR AG.SEQUSUARIO = 47)
and AG.DTAAGENDA <= sysdate - 2
and AG.REALIZADA = 'N'
and AG.SEQUSUARIO = 47
Union
Select AG.SEQAGENDA,
AG.DTALEITURA,
AG.DTAAGENDA,
AG.PRIORIDADE,
AG.CLASSE,
AG.ASSUNTO,
AG.SEQPESSOA,
AG.SEQUSUARIO,
AG.USUGEROUACAO,
'',
'',
US.CODUSUARIO,
AG.DEPARTAMENTO,
AG.REVISARHISTORICO,
AG.ACAO,
AG.TAREFACOMPROMISSO,
AG.PROCESSO,
AG.PROCESSOPAI,
AG.REALIZADA,
AG.DETALHE,
AG.VENDEDOR
from IV_AGENDA AG, GE_USUARIO US
where US.SEQUSUARIO = AG.SEQUSUARIO
AND AG.SEQPESSOA IS NULL
and (AG.TIPOACESSO = 'P' OR AG.SEQUSUARIO = 47)
and AG.DTAAGENDA <= sysdate - 2
and AG.REALIZADA = 'N'
and AG.SEQUSUARIO = 47
order by 3 asc, 4 asc;
Servidor antigo (STAR_TRANSFORMATION_ENABLE=FALSE):
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=422 Card=12 Bytes=2497)
1 0 SORT (UNIQUE) (Cost=421 Card=12 Bytes=2497)
2 1 UNION-ALL
3 2 NESTED LOOPS (Cost=214 Card=11 Bytes=2321)
4 3 NESTED LOOPS (Cost=203 Card=11 Bytes=1936)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'GE_USUARIO' (Cost=1 Card=1 Bytes=11)
6 5 INDEX (UNIQUE SCAN) OF 'SYS_C0012897' (UNIQUE)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'IV_AGENDA' (Cost=202 Card=1 Bytes=165)
8 7 INDEX (RANGE SCAN) OF 'IV_AGENDAIE8' (NON-UNIQUE) (Cost=3 Card=646)
9 3 TABLE ACCESS (BY INDEX ROWID) OF 'GE_PESSOA' (Cost=1 Card=19401 Bytes=679035)
10 9 INDEX (UNIQUE SCAN) OF 'XPKIV_CLIENTE' (UNIQUE)
11 2 NESTED LOOPS (Cost=203 Card=1 Bytes=176)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'GE_USUARIO' (Cost=1 Card=1 Bytes=11)
13 12 INDEX (UNIQUE SCAN) OF 'SYS_C0012897' (UNIQUE)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'IV_AGENDA' (Cost=2 Card=1 Bytes=165)
15 14 INDEX (RANGE SCAN) OF 'IV_AGENDAIE8' (NON-UNIQUE) (Cost=3 Card=646)
Servidor Novo (STAR_TRANSFORMATION_ENABLE=TRUE)
(antes da mudança, a query demorava de 4 a 6 minutos)
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=2 Bytes=692)
1 0 SORT (UNIQUE) (Cost=15 Card=2 Bytes=692)
2 1 UNION-ALL
3 2 NESTED LOOPS (Cost=5 Card=1 Bytes=375)
4 3 NESTED LOOPS (Cost=4 Card=1 Bytes=317)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'GE_USUARIO' (Cost=2 Card=1 Bytes=13)
6 5 INDEX (UNIQUE SCAN) OF 'SYS_C0022310' (UNIQUE) (Cost=1 Card=266)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'IV_AGENDA' (Cost=2 Card=1 Bytes=304)
8 7 INDEX (RANGE SCAN) OF 'IV_AGENDAIE2' (NON-UNIQUE) (Cost=1 Card=6865)
9 3 TABLE ACCESS (BY INDEX ROWID) OF 'GE_PESSOA' (Cost=1 Card=1 Bytes=58)
10 9 INDEX (UNIQUE SCAN) OF 'XPKIV_CLIENTE' (UNIQUE)
11 2 NESTED LOOPS (Cost=4 Card=1 Bytes=317)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'GE_USUARIO' (Cost=2 Card=1 Bytes=13)
13 12 INDEX (UNIQUE SCAN) OF 'SYS_C0022310' (UNIQUE) (Cost=1 Card=266)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'IV_AGENDA' (Cost=2 Card=1 Bytes=304)
15 14 INDEX (RANGE SCAN) OF 'IV_AGENDAIE2' (NON-UNIQUE) (Cost=1 Card=6865)
Ao que parece, os planos diferiram apenas no custo (cards e Bytes), usando os índices existentes. Não pude simular a execução do select sem o parâmetro de otimização STAR, porque o sistema está em produção e é necessário reiniciar o banco (alter session set star_transformation_enable=FALSE não influiu).
De todo modo, agora que as coisas estão mais claras, e o sistema funcionando, dá para aprofundar nos estudos com a cabeça mais fria.
Muito obrigado.
Ederson Elias
www.alfa.br
Followup:
Preciso saber algumas coisinhas.
- Como voce está coletando as estatísticas destas tabelas?
- como está os parametros (optimizer_index_caching e optimizer_index_cost_adj)
Outras sugestões.
volte o star_transformation_enabled=false;
troque o UNION para UNION ALL para evitar o SORT (UNIQUE), a menos que voce queira um distinct no result set.
coloque um hint /*+ first_rows */ e veja qual o resultado. Depois, podemos ajustar os optimizer_index_* caso não renda!
|
|
|
|
|
|