Opa, blz ? Então, imho é *** rigorosamente Impossível *** vc "pegar problemas
de performance com query" - o que é possível é vc identificar SQLs 'caros',
SQls custosos, que podem ou não ser foco de má performance, mas NÃO HÁ COMO vc
identificar de maneira automática (seja via query ou qual for a maneira) se
esses SQLs estão apresentando performance abaixo do possível ou se o gasto (de
I/O, de CPU ou o que for) é normal para a tarefa... Pra ficar mais claro, tome
o exemplo de um SQL que leia, sei lá, 10 mil blocos pra retornar meia dúzia de
linhas : normalmente num caso desses neguim já ia sair gritando "tem problema!
tem problema" mas suponha que é um SELECT com algum tipo de Agrupamento e que
retorne as vendas todas do ano que estão na tabela - não adianta querer indexar
(pois NECESSARIAMENTE a tabela toda tem que ser lida, e índice é eficiente
quando se necessita ler MENOS que a totalidade dos dados) , não adianta
particionar por mês/dia (já que queremos/precisamos TODOS os dados) aí não tem
jeito, se a tabela tem 10 mil blocos o RDBMS *** TEM *** que ler todos eles
pra atender à tarefa tal como proposta... Neste exemplo, por mais que esse SQL
seja apontado na listagem não há RIGOROSAMENTE NADA que se possa fazer no SQL
em si , para este caso a melhoria de performance passaria por estruturas
físicas diferentes (views materializada que já faça o agrupamento num período
fora de pico, talves) e/ou alteração da solicitação (digamos, alterar o
relatório para MENSAL ao invés de ANUAL, com os relatórios dos meses anteriores
(ou seus dados) sendo armazenados talvez fora do banco, coisas assim....
Assim sendo : nunca esquecendo essa ENORME RESSALVA acima exposta, ie, que o
que vc vai obter são SUSPEITOS, que PODEM ou NÃO ser causa de problemas de
performance, a resposta é : vc encontra as estatísticas de SQLs na V$SQL e na
V$SQLAREA, vc vai fazer simples consultas nelas ordenando pelos |TOP-N
(tipicamente se indica uns 25) resultados...
Um exemplinho rastaquera,indicando os top-25 SQLs mais demorados (em termos de
tempo total) :
SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
elapsed_time,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 26;
Com essa mesma lógica, vc pode encontrar os TOP-N SQLs que mais I/Os fizeram,
os TOP-N SQLs que mais gastaram CPU, os TOP-N SQLs mais usados/executados no
database... Bico...Dá um look na doc 9i para ver EXATAMENTE quais colunas vc
tem na V$SQL e na V$SQLAREA e é isso...
Apenas aviso que :
a. as V$ são TOTALMENTE APAGADAS após um reboot do database : se vc pára o
banco para backups frios ou coisa assim, esteja preparado para isso, SALVANDO
de alguma forma os conteúdos . SE fosse banco 10g vc já tem isso automático (na
figura do AWR/ASH) mas no banco 9i não tem, no 9i ou vc pode usa o STATSPACK
para fazer as consultas (quie aí ele já tem um local pra salva dos dados
extraídos das V$) ou então vc cria uma rotina sua :
http://datavirtualizer.com/ash-masters/33-2/ é um exemplo mas talvez no seu
caso vc queira escrever uma rotina mais simples que só salve as V$SQLxxx, ao
invés dos WAITs
b. as V$SQLxxx refletem o CACHE de SQLs, ie, elas mostram APENAS e TÃO SOMENTE
os SQLs que estão atualmente no cache : para que vc não perca aqueles SQLs
infrequentes mas importantes pro negócio e/ou pra análise de performance, vc
TEM que repetir as consultas nelas várias e várias vezes , com intervalos ente
cada consulta, por dias e dias (ou mesmo semanas) a fio...
[]s
Chiappa
