Chiappa, obrigado pelo retorno e pelas dicas.
O sistema que roda a procedure é de terceiros, entramos em contato com o
fabricante e o mesmo argumentava que o problema estava no banco, que não tinha
memória para processar. Hoje a base temos 160GB de informação, é uma empresa de
pão, então temos um processamento em tabelas com milhões de linhas, e o
relatório do fornecedor com uma tabela de 8 mil linhas demora mais de 4 horas.
Alterei a procedure e consegui um tempo bom, o usuário fará o teste para ver se
os valores estão corretos. O problema que eu via no select da procedure, era um
LEFT OUTER JOIN, eu tirava essa linha e o resultado trazia, além dessa mudança
eu filtrei os dados para o processamento.
A Tablespace Temporária ela é realmente de 4GB, peguei o ambiente pronto, e
hoje se eu fosse precisar montar um ambiente novo não saberia o quanto colocar
na Temporária, estarei consultando os manuais para um detalhamento. Sobre o
espaço, tinha visto no OEM, ele exibi em uma das telas essa porcentagem
utilizada, mas olhando bem tenho apenas 70MB utlizado.
[]'s.
Márcio
----- Original Message -----
From: jlchiappa
To: oracle_br@yahoogrupos.com.br
Sent: Monday, October 26, 2009 12:19 PM
Subject: [oracle_br] Re: direct path read/write temp
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]
>
[As partes desta mensagem que não continham texto foram removidas]
