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 <> 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 <> 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é.

Commenter le spfile ou les profiles SQL

Mais pourquoi est-ce que ce paramètre est positionné à cette valeur ? Qui ne s’est pas posé la question devant un optimizer_index_cost_adj qui n’est pas à 100 ou un NLS_SORT qui n’est pas à Binary. Le vilain qui a fait le coup n’a pas commenté. Alors que pour le faire il suffit de passer les bonnes commandes

Pour commenter une valeur que l’on passe au niveau du SPFILE (Commentaire de 255 caractères maximum):

alter system set PARAMETRE=VALEUR 
    comment='Gagne 42 minutes, le 25 mai 2001, D. N. Adams'  
    scope=spfile 
    sid='*' ;

Pour commenter un SQL_PROFILE existant (Commentaire de 500 caractères maximum):

begin
 sys.dbms_sqltune.ALTER_SQL_PROFILE( 'nomDuProfile'
                                   , 'COMMENT'
                                   , 'Souci sur V$OGONS, 
contourne 0RA-1952, 11 mars 2013, Z. Beeblebrox' ) ;
end;
/

C’était pourtant pas si compliqué

Identifier sa session

En PL/SQL

exec sys.dbms_session.set_identifier          ('identifiant de session')
exec sys.dbms_application_info.set_client_info('info client')
exec sys.dbms_application_info.set_module     ('module', 'action')

Eventuellement, si l’on veut ne changer que l’action

exec sys.dbms_application_info.set_action('action')

En PHP

<?php 
 oci_set_client_identifier($db , 'identifiant de session'); 
 oci_set_client_info      ($db , 'info client'); 
 oci_set_module_name      ($db , 'module'); 
 oci_set_action           ($db , 'action'); 
?>

Retrouver ces informations

select sys_context('USERENV', 'CLIENT_IDENTIFIER') as "client identifier"
     , sys_context('USERENV', 'CLIENT_INFO') as "client info"
     , sys_context('USERENV', 'MODULE') as module
     , sys_context('USERENV', 'ACTION') as action
from sys.dual ;

Avec un peu de mise en forme

SQL> col "client identifier" for a25
SQL> col "client info" for a17
SQL> col module for a17
SQL> col action for a17

SQL> /

client identifier         client info       MODULE            ACTION
------------------------- ----------------- ----------------- -----------------
identifiant de session    info client       module            action

C’était pourtant pas si compliqué

PS: On peut aussi le faire en java ou en .Net si tant est qu’on utilise encore des trucs aussi dépassés ou encore en C via la bibliothèque OCI1 si l’on est résolument moderne. Tout cela est détaillé dans l’excellent Troubleshooting Oracle Performance de Christian Antognini paru chez APress au chapitre 2 pages 45 à 48. Et de manière plus cryptique et éparpillée dans la documentation de l’éditeur.


1. OCI pour Oracle Call Interface, livré par Oracle avec tous les “clients”