Optimizer_index_cost_adj

Vou tentar explicar um pouco deste parâmetro que no bolo do CBO, por vezes, gera grande confusão.

Este parâmetro, têm por finalidade indicar ao Optimizer o nível de ponderação para a utilização de índices ou full tables scan. Basicamente isto indica quanto mais rápido é uma operação de single block read (tipicamente índices) em relação a um multi block read (FTS) e vice-versa, dependendo do valor indicado. Para isto, o outro parâmetro db_file_multiblock_read_count também é importante nesta configuração.

Sendo a tendência dos valores:

1………………………………………………………………………..100

<- Índices                                                                                        FTS ->

Em resumo o db_file_multiblock_read_count indica quantos bloco serão lidos numa única operação de multi block I/O scan, portanto, quanto mais alto, maior a tendência para FTS.

Exemplo:

Criei uma tabela de testes, com a seguinte dispersão de dados:

SQL> select m,count(*)*100/1500026 “%”,count(*) from teste group by m order by 3 desc;

M          %                      COUNT(*)
–       ———-               ———-
A     16.7259767     250894
B      9.99682672     149955
I       9.99262679     149892
G      9.9846936        149773
E      9.97656041     149651
C      6.68155085     100225
D      6.67988422     100200
F      6.67588428     100140
L      6.66701777    100007
H     6.65988456      99900
M    3.33094226      49965
N     3.31647585      49748
Z      3.3113426         49671
P      .000266662     4
W     .000066666    1

• Com o optimizer_index_cost_adj=100, vê-se um FTS para uma query, cujo output será de 16% de todas as linhas da tabela.

• Com o optimizer_index_cost_adj=16, vê-se a utilização do índice, cujo output será de 16% de todas as linhas da tabela.

Abaixo uma query que ajuda a ter um norte para a configuração deste parâmetro:

col c1 heading ‘Media Waits para|Full Scan Read I/O’ format 9999.999
col c2 heading ‘Media Waits para|Index Read I/O’ format 9999.999
col c3 heading ‘Porcentagem de| I/O Waits|para Full Scans’ format 9.99
col c4 heading ‘Porcentagem de| I/O Waits|para Index Scans’ format 9.99
col c5 heading ‘Valor|inicial|para|optimizer|index|cost|adj’ format 999

select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from v$system_event a, v$system_event b
where a.event = ‘db file scattered read’ and b.event = ‘db file sequential read’;

Claro, que em alguns casos um FTS será melhor que usar índice, por isso, muito moderação para a configuração destes parâmetros.

Saudações Vascaínas….