[Oracle] Ferramenta de tuning de SQL – desafio

Buenas.

Tenho um colega na empresa, o Thiagão (editado, Thiagão permitiu o link pro perfil dele no linkedin), que está concluindo a facul esse ano. Palmas pra ele! Clap! Clap! Clap!

Ele desenvolveu, como projeto pro TCC, uma ferramenta pra tuning em SQL’s Oracle.

Conversei com ele e perguntei o que ele achava de disponibilizar aqui a ferramenta, os dumps, e um pequeno desafio que ele lançou… ele curtiu a idéia, então irei postar para quem quiser ‘brincar’.

Lembrando… isso é um projeto de TCC, não tem qualquer tipo de suporte, e dependerá da boa vontade do Thiago, que ainda está envolvido com o TCC, portanto com tempo escasso, de responder algo relacionado a utilização.

Baseado nisso, segue o mail que o Thiago me enviou:

Como alguns já estão sabendo estou desenvolvendo uma ferramenta de “Tuning de SQL em Oracle” para meu TCC. Neste primeiro momento até a apresentação do trabalho (ainda sem data, mas deve ser até 20/12) não pretendo evoluir a ferramenta apenas resolver bugs, pois preciso cumprir o que foi prometido na minha proposta inicial. Após a data de apresentação pretendo dar continuidade ao desenvolvimento com o objetivo de facilitar atividades do nosso dia-a-dia e ganhar em produtividade.

Quem tiver interesse em ter uma cópia da ferramenta para utilizar no dia-a-dia aqui da Ilegra e se possível me enviar criticas, sugestões e bugs para evolução da ferramenta, só me responder este e-mail que vou montar uma compilação para enviar.

Alguns itens que já estão mapeados:

–> Sugestão:

-> Possibilidade de fazer um explain plan sem ter que executar a consulta e consultas as outras informações

-> Extrair informações para criação de outlines

–> Bugs:

-> dependendo da resolução da tela a tabela de índices na “tab dicionário” de dados pode “diminuir” e ficar visível somente a primeira linha, porém ainda é possível navegar nas linhas seguintes mesmo não visualizando os dados (em analise, desenvolvo utilizando 1366 X 768)

-> Separadores de milhar e decimal para colunas do tipo inteiro

-> Campos inteiros com alinhamento a direita

Como a ferramenta ainda tem alguns problemas e ainda não fiz a apresentação final, para não gerar “spam” na caixa de alguns que não tem interesse e tuning enviei este e-mail somente para algumas pessoas que sei que já atenderam alguma demanda relacionada com tuning de consultas em Oracle ou que poderiam repassar para pessoa interessadas, portanto caso conheçam alguém que tenha interesse fiquem a vontade para repassar este e-mail.

Obs: Hoje a ferramenta infelizmente só roda em plataforma Windows

Pessoal,

Conforme prometido, segue anexo a versão inicial….

Para executar a ferramenta:
– Extrair o ZIP no disco local (pode ser em “arquivos de programas” ou qualquer outro diretório)
– Sugestão é criar um atalho na área de trabalho para “TuningSQL.jar” executar diretamente de lá
– Para executar a aplicação basta clicar no arquivo “TuningSQL.jar”

Obs: Nenhuma alteração é feita em produção, somente são geradas informações e dicas para o DBA tomar as decisões necessárias.

Requisitos:
– Todos já devem ter mas caso precisem baixar JRE:
http://www.oracle.com/technetwork/java/javase/downloads/index.html
– Usuário com permissão de “DBA” para logar na base de destino

Obs: Não é necessário ter o client da Oracle instalado para executar a ferramenta

Sobre a ferramenta:
– Existe um arquivo de configuração chamado “config.ini” que fica na mesma pasta da aplicação onde é persistindo as seguintes informações:
– Parâmetros utilizados para conectar (senha não persistida) e local do tnsnames.ora
– Parâmetros de contextualização “tab parâmetros”
– Somente na primeira vez que for utilizar é necessário configurar o local onde vai estar o arquivo “tnsnames.ora”, depois disso a informação será persistido no arquivo “config.ini”
– Tab parâmetros:
– Tamanho do fetch -> Informa o número de linhas retornadas em cada fetch (Mesmo que “set arraysize do sqlplus”)
– Intervalo AWR(dias) -> Informar “0” para não buscar informações do AWR ou informar o número de dias que será considerado para retornar informações do AWR quando informado o SQL_ID na “tab consulta”
– Sugestão é informar “0” sempre que possível principalmente para base de dados com históricos muito grandes de AWR, só informe valor quando realmente necessário buscar informações históricas
– Intervalo Últimos DDLs(dias) -> Informar “0” para não buscar informações de DDLs ou informar o número de dias que será considerado para retornar informações sobre DDLs que afetaram os usuários envolvidos na consulta
– Current_Schema -> Em qual contexto o SQL deve executar ( mesmo que “alter session set current_schema=xxxx;” )
– Gerar Dicas -> Informa se deve gerar Dicas em relação ao SQL Executado
– Sugestão é ativar esse item somente em ambiente de desenvolvimento, pois esta precisa executar o procedimento “DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO” que é o mesmo que é executado pelo Oracle antes de iniciar a coletar estatísticas e este faz o flush dos contadores de alterações de tabelas para o dicionário de dados (DBA_TAB_MODIFICATIONS…) e pode gerar um overhead desnecessário.
– Retornar dados -> Informa se deve retornar dados para “tab Dados” ou se apenas deve fazer fetch sem apresentar os dados na grid (tipo “set autotrace trace” do SQL Plus)
– Gerar histórico -> Determina se informações de histórico de execuções anteriores deve ser mantidos até o fechamento da ferramenta
– “Tab Consulta”
– Possível carregar um SQL_ID ( Busca no cache ativo depois no AWR) ou informar um texto diretamente
– Suporte para informar consultas com binds
– Quando for executar a consulta deve abrir uma janela para informar o nome da bind, tipo dado e valor
– Para binds do tipo date é necessário colocar um to_date(:var, ‘dd/mm/yyyy hh:mi:ss’) no texto do SQL e informar seu valor como varchar2
– Como não existe nada automatizado para buscar os binds da consulta na API da Oracle esta busca é feita via expressões regulares, portanto é possível que alguns binds sejam mapeados de modo indevido, porém possível remover da lista pressionado DELETE na linha ou incluir um registro pressionado TAB na última coluna.
– Caso “Gerar histórico” esteja ativado então é possível dar 2 cliques na grid para carregar todas as informações sobre uma execução anterior, ideal para comparação entre execuções do mesmo SQL.
– “Tab Dados”
– Tem um “tool tip” que demonstra quantidade de dados retornados e quantidade dados processados (ver parâmetro “Retornar dados”)
– “Tab Ultimos DDLs”
– Demostra informações de DDLs que afetaram os usuário envolvidos na consulta (ver parâmetros “Intervalo Últimos DDLs(dias)”)
– “Tab Dicionário de dados”
– Informações detalhadas de estáticas de tabelas, colunas e índices
– Informações detalhadas de índices
– Os índices que foram utilizados ficam marcados com uma cor acinzentada para facilitar a identificação
– “Tab Plano”
– Diversas visões do plano de execução
– Básico/Trace/ Memória
– Detalhes de predicados, filtros e projeções
– Informa quantidade de SQL com mesma assinatura
– Informa quantidade de SQL com mesmo plano
– Disponibiliza informações totais e médias do SQL no cache do Oracle
– “Tab AWR”
– Só é exibe informações quando o SQL_ID é informado
– Demostra os diferentes plano que existem para o SQL_ID em detalhes em modo arvore
– Informa quantidade de planos encontrados
– Informa quantidade de ocorrências encontradas no AWR
– Binds históricos
– Demostra informações totais e médias de histórico de execução da consulta conforme intervalo solicitado (ver “Intervalo AWR(dias)”)
– “Tab dicas”
– Gera dicas com base em regras pré-definidas
– Estatisticas
– Gargalo do plano
– Ordem de join
– Adicionar colunas em índices
– Verificar DDLs

Pessoal,

Já tenho o número mínimo de participantes escritos para mim documentar os resultados na monografia, mas quem quiser participar:

Tenho um dump de um schema Oracle 11g (5 MBs) onde tenho 2 desafios de tuning em cima de 2 SQLs diferentes. São desafios simples, onde a solução final (se tiver) deve aparecer em até no máximo 30 minutos. Quem tiver afim de exercitar seus conhecimento de tuning e aprender um pouco sobre a ferramenta e sobre tuning, só me responder esse e-mail que envio as informações, regras e o dump. O objetivo é documentar os tempos e a efetividade do tuning utilizando minha ferramenta e outras ferramentas.

Os nomes dos participantes e as sugestões enviadas não precisam constar na monografia portanto fiquem a vontade para participar, ah e no final eu envio a resposta e a explicação se vocês não chegarem no resultado final.

Outro item importante:

Para quem pelo menos já conseguiu abrir a ferramenta (hehehehe, sei que o dia a dia é corrido), precisaria que vocês me respondessem um questionário de 5 perguntas (bem simples é 1 minuto, sim ou não). Para responder só clicar no link abaixo:

http://www.surveymonkey.com/s/DCX5BLZ

Obs: Não precisa se identificar para responder……

Desafio:

Algumas questões:

– Anexo tem um dump com o ambiente para validar ambas consultas. O dump foi gerado com o “exp” na versão “11.2.0.3.0 – 64bit Production”

–   exp system/oracle owner=HR file=hr.dmp log=hr.log compress=n buffer=10000

– Anexo também a última versão da ferramenta

– Fiquem as vontade para realizar criação de índices a alterar o SQL, a ideia é que cada um gere a sua solução.

– No final eu envio para vocês as minha repostas com os porquês

– Quando for analisar utilizando outra ferramenta, é valido utilizar seus scripts para analise, é valido qualquer ferramenta, porém não use a minha ferramenta em nenhum momento

– Utilizado a minha ferramenta considerem a guia “dicas”, pois ela deve te direcionar para o resultado final

– Quem precisar de dicas sobre a ferramenta ou sobre tuning antes de iniciar só me falar que eu posso ajudar

– Por favor enviem as repostas somente para mim, para não influenciar no resultado

Repostas que preciso que me enviem:

– Solução gerada para cada SQL

–   Índices (se tiver)

–   Estatisticas (se tiver)

–   Novo SQL (se tiver)

–   Hints (se tiver)

–   Outras alterações

– Tempo gasto pode ser em minutos ou horas (não precisa levar muito longe, até 30 minutos é suficiente, se não deu, não deu……heheheheh)

Depois de enviado vou aplicar as soluções de cada um para coletar as informações de resultado de custo de IO e CPU que serão as métricas para determinar a efetividade do tuning. Outra métrica que vou documentar o tempo gasto para analisar as consultas.

Isso foi o que o Thiagão me mandou de mail’s pessoal. Se chegaram até aqui, hehehe, hospedei todos os arquivos que ele me enviou no meu dropbox. Seguem os links:

TbT (Tuning by Thiago)
Diagrama ER
Dump
Log do export

Pessoal, o Thiago ficaria muito agradecido se vocês deixassem feedbacks aqui no blog sobre a utilização da ferramenta, beleza?

Ps.: Consegui um widget para ver quantas vezes a ferramenta está sendo baixada. Segue abaixo:

Abraço a todos.

att
Vitor Jr