Índice do fórum Treinamentos Avançados Treinamento Oracle Performance Diagnostics & Tuning OPTIMIZER_INDEX_CACHING & OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_INDEX_CACHING & OPTIMIZER_INDEX_COST_ADJ

Dúvidas, dicas e atualizações sobre o Treinamento Oracle Performance Diagnostics & Tuning.


Mensagens: 0
Pessoal,
Estou com uma dúvida:

No OPTIMIZER_INDEX_CACHING onde o default é zero ele diz ao CBO o quanto (em %) os blocos de um IDX estão na buffer cache (bc) evitando que ele faça I/O.
Se eu colocar esse valor para 5% CBO irá na bc e usará 5% dos blocos do índice SE estiverem lá e fará 95% de I/O para ler o restante dos blocos, correto?
Supondo que seja esse o caso, se eu setar esse valor para 100% o CBO irá sempre procurar pelos blocos na bc e usa-los antes de fazer um I/O em busca do IDX, mas esse não é um comportamento normal dele? Verificar primeiro se ele está em memória para depois procurar em disco?

Em relação ao OPTIMIZER_INDEX_COST_ADJ se setarmos o OPTIMIZER_INDEX_CACHING para 50% e colocarmos OPTIMIZER_INDEX_COST_ADJ para 1000 (o valor default é 100) forçando que ele escolha o IDX ao invés do FTS, num mundo perfeito, estaríamos tenho um bom ganho na performance afinal ele iria ler 50% do indíce direto na memória e ler o restante no disco, mas sem ler a tabela toda no disco, apenas os blocos do índice.

Minha analise está correta?

portilho Site Admin

Mensagens: 482
Estes dois parâmetros influenciam o CBO a respeito das decisões em relação ao plano que será compilado / utilizado.
A alteração destes parâmetros não faz com que o CBO tente acessar estes blocos no DB_CACHE_SIZE ou no disco. O Oracle (na verdade o Server Process do User, e não o CBO) sempre utilizará blocos do DB_CACHE_SIZE, se eles estiverem lá.

O OPTIMIZER_INDEX_CACHING influencia o plano do CBO a respeito da utilização de "Nested Loops Joins" (ao invés de Hash Joins ou Sort Merge Joins) e "IN-list Iterators" sobre Índices (ao invés de Full Table Scan). Ao aumentar este valor (o padrão é 0, e vai de 0 até 100), estamos avisando ao CBO que a respeito da probabilidade (em %) de que os blocos de índices utilizados por estes dois tipos de operação estejam no DB_CACHE_SIZE. É apenas uma suposição do DBA, para influenciar artificialmente a escolha do CBO.

O OPTIMIZER_INDEX_COST_ADJ influencia o plano do CBO a respeito da utilização de índices, no geral. Ao reduzir (ao contrário do OPTIMIZER_INDEX_CACHING) este valor (o padrão é 100, e vai de 0 a 1000), o CBO tenderá mais a utilizar índices, utilizando uma lógica similar ao OPTIMIZER_INDEX_CACHING: o DBA está supondo que é mais provável que os blocos estejam no DB_CACHE_SIZE.

Então, para influenciar o CBO pelo uso de índices no maior grau possível, o OPTIMIZER_INDEX_COST_ADJ deve estar em 0, e o OPTIMIZER_INDEX_CACHING em 100.


Mensagens: 0
Interessante!

O que me causa dúvida é, se eu setar os valores do OPTIMIZER_INDEX_COST_ADJ para 0 e ele não encontrar os blocos lá ele fará um FTS, certo? Isso não gera nenhum impacto no momento do parse, pois entendo que ele irá procurar na buffer cache primeiro como um comportamento default e depois no disco.

portilho Site Admin

Mensagens: 482
Estes parâmetros influenciam a decisão do CBO a respeito do Access Path que será utilizado (FTS ou Range Scan, por exemplo), ANTES de iniciar o SELECT.
Se depois de iniciar o SELECT os dados estiverem ou não no CACHE, nada mudará. O plano continuará como foi compilado.


Mensagens: 0
Entendi. Agora ficou bem claro, obrigado!

portilho Site Admin

Mensagens: 482
:-D


Voltar para Treinamento Oracle Performance Diagnostics & Tuning

cron