Colega, umas dicas de tunning de SQL : 1. o CUSTO nada mais é do que uma estimativa de tempo, em cima de quanto tempo o otimizador acha que vai levar pra recuperar os blocos todos necessários - ORA, eu disse estimativa porque NA PRÁTICA NÃO TEM COMO o Otimizador saber o throughput ** REAL ** do seu sub-sistema de I/O, se vai ou não ter ** várias ** sessões concorrendo pelos mesmos blocos , essas coisas podem MUITO FACILMENTE deixar um plano com um custo baixo demorado ao extremo, ok ?????? Sendo assim, se o Custo pode ser 'tapeado' por concorrência e situações do tipo, não é legal confiar só nele pro tuning, então como é que vc identifica ineficiências no seus SQLs mais seguramente ? Fácil, ** esqueça ** o CUSTO num primeiro momento e vá olhar o traço que está SEMPRE presente num SQL ruim, que I/O excessivo.... Aqui cabe uma observação CRÍTICA, que é : no bd Oracle, I/O é definido como a leitura de um bloco, e **** QUALQUER TIPO DE I/O **** é ruim - mesmo quando a informação vêm dum cache, a leitura foi feita em RAM mas isso é SIM um I/O (um LIO, Logical I/O, um I/O lógico, mas é I/O), e como todo I/O ele CONSOME CPU, faz o banco trabalhar mais, NÂO É instantâneo... Tem gente que acha que um I/O em RAM é desprezível mas NÂO É VERDADE, cada LIO consome CPU, há waits sim por eles, o bloco lido TEM que ser colocado no cache, a fila de blocos menos e mais usados tem que ser gerida.... Complemento essa informação com o fato que : a. é IMPOSSÌVEL vc ter toda a informação em cache b. mesmo quando a informação não está no cache e uma leitura física (PIO, Physical I/O) é requerida, o bloco (de dados) lido do disco *** necessariamente *** tem que ir pro cache, ou seja, há um LIO após o PIO Então, o teu objetivo passa a ser BAIXAR OS LIOs !!! Assim, ** despreze ** indicadores tipo cache hit ratio (que medem quanto de PIO está havendo), pois como eu disse acima AINDA que a esmagadora maioria dos I/Os sejam LIOs, se eles estiverem acontecendo em excesso waits vão ser inevitáveis, eles TEM SIM custo em termos de performance...
Como vc analisa os LIOs de um SQL ? De várias maneiras, mas as melhores são as tools nativas do Oracle, como E como vc baixa os LIOs ??? De várias maneiras : a) RE-ESCREVENDO o SQL de uma maneira que leia menos informação (por exemplo, não pedidno pra varrer a mesma tabela várias vezes), usando os RECURSOS do banco de dados Oracle que permitem economia de leituras (como funções analíticas, WITH clause, etc) e/ou b) RECRIANDO/adicionando novos elementos na estrutura física (ou mesmo no modelo) do seu banco que permitam obter os dados sem ter que varrer tabelas - por exemplo, novos índices (talvez até mesmo índices parciais, criados via FUNCTION, que só indexem a parte que vc quer da informação), views materializadas, desnormalizar parte do modelo, uma tabela global temporary table com apenas a parte da informação que interessa pro JOIN, coisas do tipo .... http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6749454952894 fala bastante sobre o assunto, ok ??? 2. evidentemente, antes de sair otimizando o SQL vc tem que garantir que o CBO está gerando o MELHOR PLANO POSSÌVEL, então tenha absoluta certeza que as estatísticas estão frescas e completas (tanto pra tabelas quanto pra índices), veja que os parâmetros de controle do CBO estão adequados pro seu caso (nãosó os optimizer, mas os NN_size se vc os usa), veja que as constraints (principalmente as de PK/UK) estão sim no lugar (elas dão insights preciosos pro CBO muitas vezes).... 3. CARDINALIDADE, essa é uma questão-CHAVE quando se discute tuning em CBO : a função do CBO é estimar quantas linhas um SQL retorna, que aí sabendo o total de linhas (coluna NUM_ROWS, as estats coletam isso) ele pode fazer uma regrinha de três , tipo assim, e chegar na resposta : PORÉM, podem haver casos de distribuição IRREGULAR, aonde pra um valor X em especial retorna muito mais ou muito menos linhas do que a estimativa aritmética, aí isso tem que ser ajustado : http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.ppt.pdf fala um pouqinho sobre isso 4. o ponto final é o MAIS importante, o Conhecimento do seu ambiente : se vc postar a sua SQL aqui, com certeza pode ser que nós NÂO CONSIGAMOS dizer nada, pois NÂO SABEMOS quais índices existem pras tabelas, QUAIS das tabelas no JOIN (que deve ser JOIN essa query...) são obrigatórias de estarem lá pelo negócio, NÃO SABEMOS se (digamos) é aceitável/possível informar valores menores nos eventuais intervalos de pesquisa, entre outros.... Assim, se mesmo vc não tem esse Conhecimento, adquira-o consultando o dicionário de dados, conversando com os Analistas, usuários, programadores, fornecedor do programa, aonde puder, pois ele VAI SER SIM necessário... []s Chiappa OBS : - o latch que vc cita ocorre quando várias sessões estão fazendo LIOs nos mesmos e mesmos blocos, isso PODE SER uma indicação de custo sendo 'tapeado' por concorrência, E direct path read implica que alguma operação temporária em disco (criação de hash table, ordenação, seja o que for) está rolando : creio que vc TEM QUE identificar se o plano está bom, se o CBO está OK, se a cardinalidade tá dentro, tentar re-escrever , se nada resultar vc pode tentar otimizar a operação temporária alocando mais área de sort, de hash ou o que for q ela usa e - quanto à pergunta de temp, veja lá : vc *** ESTÀ ** medindo o consumo da temp de forma correta ????????? Pois no bd Oracle quando alguém deixa de usar a área temporária em disco, pra melhor performance o banco ***** NÂO ****** diminui a área, simplesmente libera-a, e a próxima sessão grav POR CIMA do que a outra fez, ok ? Assim, se vc tiver na DBA_FREE_SPACE, ou em disco consultando pelo SO, 3.9 Gb isso NÂO QUER DIZER que todo esse valor está em uso efetivo, PODE SER QUE as sessões anteriores fizeram a área crscer até 3.9 mas nesse momento não é esse o consumo real, vc TEM QUE consultar as views de V$SORT_nnn pra ver desse total quanto que REALMENTE está sendo usado e quanto que está em disco mas tá liberado, é reusável... SE EFETIVAMENTE o consumo REAL estiver perto do limite aí SIM, não tem o que pensar, aumentar essa temp em muito : aliás, 4 Gb é ** minúsculo **, isso é menos que o PEN DRIVE que tá espetado no meu micro, Realmente vc tá usando mesmo só isso ??? --- Em oracle_br@yahoogrupos.com.br, Márcio Ricardo Alves da Silva <marcio_...@...> escreveu > > GeleiraBoas. > > Estou com uma procedure num sistema para geração de relatório, que está > demorando cerca de 5 horas, o custo inicial dela era: > > Cost=25 Cardinality=1 Bytes=235 CPU cost=34540690 > e deixei até agora com > Cost=25 Cardinality=1 Bytes=235 CPU cost=34540690 > > quando roda o select da procedure ele chega em uma tabela e fica com DIRECT > PATH READ TEMP, e LATCH BUFFER CHAINS. > > Minha Tablespace Temporária é de 4GB e está sendo usado 3,9GB, é aconselhável > eu criar outra tablespace para? > > Release 10.2.0.1.0 > > Grato, > Márcio. > > [As partes desta mensagem que não continham texto foram removidas] >
