Vérifier le paramétrage CBO spécifique à une instance

Normalement on sait comment se comporte l’optimiseur Oracle, mais quand on a des surprises, c’est parfois tout simplement lié au paramétrage CBO, donc avant de sauter aux conclusions un petit check-up n’est pas du luxe.

Deux requêtes pour ça, les premières, basées sur v$sys_optimizer_env, pour vérifier le paramétrage, la dernière, basée sur v$system_fix_control pour voir si on a modifié le patchset :

select NAME
     , VALUE
     , DEFAULT_VALUE 
from v$sys_optimizer_env 
where VALUE &lt;&gt; DEFAULT_VALUE ;

Résultat (pour exemple de pourquoi ce con m’a mis optimizer_index_caching à 100 ? )

NAME                                     VALUE                     DEFAULT_VALUE
---------------------------------------- ------------------------- -------------------------
_pga_max_size                            4194200 KB                2048000 KB
optimizer_index_cost_adj                 10                        100
optimizer_index_caching                  100                       0

Si on a la possibilité d’effectuer la requête en tant que SYS, on peut aller un peu plus loin avec la ‘fixed table’ x$qkscesys :

select pname_qkscesyrow NAME
     , PVALUE_QKSCESYROW VALUE
     , DEFPVALUE_QKSCESYROW default_value
from x$qkscesys 
where PVALUE_QKSCESYROW != DEFPVALUE_QKSCESYROW
union 
select name
     , value
     , default_value 
from v$sys_optimizer_env
where VALUE &lt;&gt; DEFAULT_VALUE ;

Il est à noter que l’on peut aussi travailler au niveau des sessions avec x$qksceses et v$ses_optimizer_env, attention cependant, ces vues renseignent les SID (Session ID) mais pas les valeurs par défaut.

select VALUE
     , DESCRIPTION
     , OPTIMIZER_FEATURE_ENABLE
from V$SYSTEM_FIX_CONTROL
where IS_DEFAULT=0 ;

C’était pourtant pas si compliqué.