Otimização banco oracle 10 ou 11g

Tuning de Banco, Tuning de SQL, Ferramentas de tuning
Responder
skiche
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Sex, 27 Abr 2012 9:45 am

Bom dia Galera

Tudo bem?

Então trabalho com delphi e Banco de dados oracle e onde eu trabalho lidou com algumas bases bem grande e algumas vezes essas ficam um pouco lentas em alguns processos.

Existem alguns processos que utilizou para melhorar o processo.E são os listados a baixo:

-- Cria todos os deletes de statistics para que seja executado

Selecionar tudo

SELECT ' ANALYZE TABLE ', TNAME, ' DELETE STATISTICS; ' FROM TAB WHERE TABTYPE = 'TABLE'
ORDER BY 1,2,3
-- Cria os compute para as tabelas

Selecionar tudo

SELECT ' ANALYZE TABLE ', TNAME, ' COMPUTE STATISTICS; '
FROM TAB
WHERE TABTYPE = 'TABLE'
ORDER BY 1,2,3
-- Cria os compute para os index

Selecionar tudo

SELECT 'ANALYZE INDEX ', INDEX_NAME , ' COMPUTE STATISTICS;'
FROM USER_INDEXES
ORDER BY TABLE_NAME, INDEX_NAME;

-- Faz o Compute estatisticas do schema inteiro.

Selecionar tudo

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('USER',CASCADE=>TRUE);

Gostaria de saber se alguém possue algum processo a mais.

Fora isso, algumas vezes me deparo com tabelas sem index ou com alguma ligação sem index. Gostaria de saber se alguém sabe alguma sql que me mostraria possíveis indexs que deveria ser criados, pois ajudaria na performace também.

Provavel que algum DBA saiba disso. Eu deixo nas mãos deles hehe.

Atenciosamente

Skiche
Avatar do usuário
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 293
Registrado em: Qua, 13 Dez 2006 5:02 pm
Localização: São Paulo
Contato:
________________________________
Douglas - Madmax.

Olá Skiche

Segue o que e necessario para verificar tabelas sem índices e sem PK muito útil para mim .

Selecionar tudo

--> Tabelas SEM indices 
select   OWNER,
  TABLE_NAME
from 
(
select   OWNER, 
  TABLE_NAME 
from   dba_tables
minus
select   TABLE_OWNER, 
  TABLE_NAME 
from   dba_indexes
)
orasnap_noindex
where  OWNER = ('SCHEMA')
order   by OWNER,TABLE_NAME


--> TABELAS SEM PK
select  OWNER,
	TABLE_NAME
from    dba_tables dt
where   not exists (
        select  'TRUE'
        from    dba_constraints dc
        where   dc.TABLE_NAME = dt.TABLE_NAME
        and     dc.CONSTRAINT_TYPE='P')
and 	OWNER not in ('SYS','SYSTEM')
order	by OWNER, TABLE_NAME
Espero ter ajudado.

Abs.
skiche
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Sex, 27 Abr 2012 9:45 am

Opa Madmax sempre ajudando.
Somente uma duvida, a primeira sql verifica os index dos relacionamentos ou somente a tabela que não possui nenhum index?
Agradecidoo
abraços.
Avatar do usuário
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 293
Registrado em: Qua, 13 Dez 2006 5:02 pm
Localização: São Paulo
Contato:
________________________________
Douglas - Madmax.

Fala Skiche,

O 1º verifica tabelas sem index.

Abs..
Avatar do usuário
fbifabio
Moderador
Moderador
Mensagens: 199
Registrado em: Ter, 22 Fev 2011 1:51 pm
Localização: São Paulo - SP
Contato:
Fábio Prado
www.fabioprado.net

Skiche,

Existem muitas variantes para definir se um índice deve ou não ser criado em uma determinada coluna de uma tabela. Muita gente cria índices do tipo incorreto e muitas vezes esses índices nem são utilizados pelo otimizador do Oracle. Criar índices em PK , por exemplo, é uma boa prática e pode melhorar performance de algumas consultas e até mesmo DELETE CASCADE, portanto use o script que foi passado pelo madmax sem nenhum receio! Para demais situações sugiro que estude melhor quais os tipos de índices que exsitem no Oracle e quando realmente você deve criá-los. Eu ensino isso e muito mais no meu treinamento de SQL Tuning: http://www.fabioprado.net/p/sql-tuning- ... racle.html


[]s

Fábio Prado
www.fabioprado.net
ThiagolRamos
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Ter, 03 Nov 2015 9:53 am

Boa noite pessoal,

vou aproveitar o tópico para não criar outro com uma dúvida simples.

Ao atualizar as estatísticas do Oracle 11g, usado por exemplo:

Selecionar tudo

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('OWNER','COMPUTE');
Há algum risco? Quero dizer, risco de afetar alguma coisa nos registros, configurações e etc?

Sou novo no Oracle, então sim, a dúvida pode ser um pouco besta. =\

Mas fora a lentidão que pode causar caso haja muitos usuários conectados, há algum outro risco?

Desde já, obrigado
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Thiago,

Acho que não seria recomendado o uso do DBMS_UTILITY.ANALYZE_SCHEMA, pois me parece uma rotina antiga das versões anteriores de ORACLE (ex: 8i).

Eu recomendaria ao invés disso a DBMS_STATS. Não necessariamente você precisa analisar 100% dos registros.

Selecionar tudo

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS
               (OWNNAME         => USER,    
                ESTIMATE_PERCENT=> DBMS_STATS.AUTO_SAMPLE_SIZE);
END;
/
Creio que a ANALYZE_SCHEMA só existe por motivos de compatibilidade e pode gerar algum impacto negativo em sua performance futura. Eu já tive problemas em usar ANALYZE TABLE e tive que remover as estatísticas antes de recriar as mesmas novamente via DBMS_STAT.

At

Sergio
ThiagolRamos
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Ter, 03 Nov 2015 9:53 am

Entendi, neste caso posso utilizar

Selecionar tudo

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('OWNER', estimate_percent=> 20);
Pois preciso gerar de um schema inteiro, mas nesse caso só irá analisar 20%, correto?
Fora a possível lentidão para os usuários então, não há nenhum outro risco?

O comando anterior eu utilizei em uma base de testes, funcionou normal, ainda bem. :D
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Thiago,

Acho que o risco está em usar os dois métodos. Recomendo que você use um ou outro. Se por acaso quiser trocar de método, remova a estatística pelo método antigo e aplique o novo método.

Acho que seria mais recomendável usar o

Selecionar tudo

 DBMS_STATS.AUTO_SAMPLE_SIZE
ao invés de uma porcentagem fixa.

Uma outra coisa: ao invés de atualizar todas as tabelas do schema, você poderia por exemplo gerar a estatísticas por grupos de tabelas (ex: criticas, não-críticas), executando em dias diferentes e com periodicidade diferentes. Por exemplo, as não críticas seriam analisadas uma vez a cada 15 dias, as críticas a cada semana, etc.

Você pode até descartar desta análise as tabelas de cadastro com número fixo de registros.

Abraços,

Coutinho
ThiagolRamos
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Ter, 03 Nov 2015 9:53 am

Boa tarde,

Eu não utilizei os 2. No caso o:

Selecionar tudo

EXEC DBMS_UTILITY.ANALYZE_SCHEMA('OWNER','COMPUTE');
Eu fiz em uma base de testes, só para verificar se funcionava, mas como você disse, e pesquisei também que a Oracle recomenda o uso deste que você sugeriu, a ideia era utilizar ele na base real.

Agora, fiquei com outra dúvida, não sou muito experiente no Oracle como disse, mas não tenho preguiça de ler e aprender, então pretendo pesquisar mais, só preciso de certa orientação no material, e estou pesquisando isso.

A dúvida é com relação ao "agendamento" por dias que você citou, é possível agendar isso no próprio Oracle, certo? Precisar ser através de scripts ou algo do tipo?

Ou no Enterprise Manager eu consigo fazer?

Mais uma vez, desculpe a quantidade de perguntas :D
Avatar do usuário
stcoutinho
Moderador
Moderador
Mensagens: 850
Registrado em: Qua, 11 Mai 2011 5:15 pm
Localização: são Paulo - SP

Thiago,

Você consegue fazer:

A) Um job que se autoexecuta periodicamente (pesquise DBMS_JOB);
http://www.experts-exchange.com/questio ... 2-0-1.html

B) Pelo Enterprise Manager (aqui você consegue criar janelas de manutenção). Para o OEM, dê uma olhada neste links:
https://docs.oracle.com/database/121/TG ... m#TGSQL397

Abraços,

Sergio
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 10 visitantes