Oracle Certified Master

J’ai un peu délaissé ce blog. D’une part parce que je blogue plutôt en anglais sur le blog de dbi services (les derniers blogs: comment mesurer si on se rapproche des limites nofile et nproc).

Mais aussi parce que ces derniers mois je préparais l’examen OCM 11g dont je viens d’avoir les résultats: je suis maintenant Oracle Certified Master. Une préparation longue et difficile afin d’être au point sur tous les sujets. Un examen delicat vu la quantité de tâches à réaliser en peu de temps, qui ne laisse pas de place à des hésitations, et peu de temps pour rechercher dans la doc. Et heureusement une attente pas trop longue dans mon cas car j’ai eu les résultats 2 semaines après.

Ce challenge m’a été proposé par dbi services lorsque j’ai rejoint cette société. Une composante indispensable de cette certification vient des moyens mis en oeuvre pour le préparer (2 formations obligatoires, quelques dizaines de jours de préparation pour être prêt à tous les scénarios). L’investissement de mon employeur là dessus m’a permis de faire cette longue préparation sans stress (d’ailleurs, j’ai arrêté de fumer au moment où je suis arrivé chez dbi services, et n’ai même pas eu envie d’une clope ni avant ni après l’exam OCM).

Au fait, dbi services recrute toujours sur la Suisse: http://www.dbi-services.com/index.php/sur-nous/jobs-a-career/senior-consultant-oracle-hf

Même si ce blog est un peu au ralentit, je suis toujours sur le forum et les autres news sont sur twitter.

Comment fournir un plan d’exécution pertinent

La version détaillée est en anglais sur mon blog à dbi services.
Ici un résumé, laissez un commentaire pour plus de détail en français est nécessaire.

Vous avez une requête qui prend trop de temps et vous voulez de l’aide pour analyser le plan d’exécution ? Alors, vous devez fournir le plan avec toute les infos pertinentes, et correctement formatées. Pas de plan sans la partie prédicat. Pas d’estimation de cardinalité sans les valeurs réelles. Pas de copie d’écran: soit un plan venant de dbms_xplan.display_cursor, soit, si vous avez Tuning Pack, un plan SQL Monitor.

Sans Tuning Pack

Paramétrage de la session:

set pagesize 10000 linesize 300 trimspool on serveroutput off
alter session set statistics_level=all;

Puis l’exécution de la requête, en se mettant dans le bon schema et en assignant des valeurs aux bind variables:

-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Et enfin, la sortie du plan en ficher texte:

spool plan.txt
select * from table(dbms_xplan.display_cursor(format=>'allstats last +outline +peeked_binds'));
spool off

Avec Tuning Pack

Paramétrage de la session:

set pagesize 0 linesize 10000 trimspool on serveroutput off long 1000000000 longc 1000000000 echo off feedback off

Puis l’exécution de la requête, avec le hint MONITOR:

-- set the schema
alter session set current_schema=SCOTT;
-- define variables
variable minimum_salary number
-- bind values
exec :minimum_salary := 3000
-- execute the query
SELECT /*+ monitor */ * FROM DEPT JOIN EMP USING(DEPTNO) WHERE SAL>:minimum_salary;

Et enfin, la sortie du plan en ficher html:

spool plan.htm
select dbms_sqltune.report_sql_monitor(report_level=>'all',type=>'active') from dual;
spool off

L’event 10704 pour tracer les verrous

En passant

La version détaillée est en anglais sur mon blog à dbi services.
Ici un résumé, laissez un commentaire ici pour plus de détail en français.

On peut voir les verrous posés avec V$LOCK mais certaines verrous ne durent pas longtemps.
Si on veut connaitre exactement les verrous posés par une commande SQL (pour vérifier si une opération est réellement online par exemple) on peut utiliser l’event 10704.

On l’active avec:

alter session set events='10704 trace name context forever, level 3';
Session altered.

et le désactive avec

alter session set events='10704 trace name context off';
Session altered.

Sur le dump généré, je récupère les lignes qui m’intéressent avec un grep -E sur:

ksqgtl [*]{3}|ksqrcl: [A-Z]|ksqcnv: [A-Z]

Il faut aussi avoir les object_id en hexadécimal:

select object_id , to_char(object_id,'0XXXXXXX') , object_name,object_type from all_objects

Exemples:

On a l’acquisition d’un verrou (ici en mode 4 qui est Share):

ksqgtl *** TM-00017EA8-00000000-00000000-00000000 mode=4

La conversion du verrou vers un autre mode:

ksqcnv: TM-00017EA8-00000000-00000000-00000000 mode=3

La libération du verrou:

ksqrcl: TM-00017EA8-00000000-00000000-00000000

C’est avec cet évent que j’étudie le comportement des verrous de version en version, par exemple ici

Générer une clé avec une sequence – sans contention

La version détaillée est en anglais sur mon blog à dbi services.
Ici un résumé, laissez un commentaire ici pour plus de détail en français.

Lorsqu’on génère une clé primaire à partir d’une sequence, le problème est que toutes les insertions concurrentes vont toucher au même bloc d’index. Puisque les valeurs venant de la sequence s’incrémentent elles vont toutes à la fin de l’index.

Il est souvent conseillé d’utiliser un index REVERSE pour disperser ces valeurs. Mais ce n’est pas bon non plus car elles seront trop dispersées. Les inserts vont toucher à tous les blocs de l’index et dès qu’il y aura un peu de volume ça ne logera plus en buffer cache.

L’autre conseil est alors de partitioner l’index par HASH. Effectivement, on distribue les inserts sur quelques blocs. Mais en RAC on va voir passer ces blocs d’un noeud à l’autre, et on aura à nouveau des contentions.

Ce qu’il faut, c’est distribuer les inserts non pas en fonction des valeurs générées, mais en fonction de la session. Le but est qu’une session touche au même bloc d’index pour ses inserts, mais qu’une autre session touche à un autre bloc, surtout si elle est sur un autre noeud du RAC. La solution est donc de préfixer le numéro qui vient de la sequence avec un ora_hash() ou un mod() du session_id. Et mettre devant le numéro d’instance si on est en RAC.
La clé sera un peu plus longue, mais c’est toujours mieux qu’un reverse index qui va faire grossir tout l’index. Les numéros ne seront plus consécutifs, mais de toute façon ça n’a jamais été le cas. Une sequence sert à fournir un nombre unique pour une clé générée, pas è numéroter dans l’ordre.

En 12c il y a une option de la sequence qui permet de faire le préfixage automatiquement en donnant simplement le nombre de partition. Mais ce n’est pas documenté… donc à ne pas utiliser. J’espère le voir sur une prochaine version.

La demo est ici.

N’oubliez-pas hanganalyze et systemstate

La version détaillée est en anglais sur mon blog à dbi services.
Ici un résumé, laissez un commentaire ici pour plus de détail en français.

Il arrive parfois que l’instance soit bloquée et qu’il faille faire un shutdown abort puis redémarrer.
Mais si on ne fait rien, on ne pourra pas analyser la root cause du problème.

Pour avoir les infos sur la situation de blocage (hanganalyze) et les infos complètes du système à ce moment là (systemstate), afin de pouvoir analyser et/ou ouvrir un SR, il faut penser à générer les dumps correspondants:

sqlplus / as sysdba
 oradebug setmypid
 oradebug unlimit
 oradebug hanganalyze 3
 oradebug dump ashdumpseconds 30
 oradebug systemstate 266
 oradebug tracefile_name

Si la situation est telle qu’on n’arrive même pas à se connecter sysdba, il est possible d’essayer la connection -prelim et de s’attacher à une session existante (le process DIAG par exemple):

sqlplus -prelim / as sysdba
 oradebug setorapname diag
 oradebug unlimit
 oradebug hanganalyze 3
 oradebug dump ashdumpseconds 30
 oradebug systemstate 266
 oradebug tracefile_name

Si par contre on peut se connecter et faire des requêtes, la vue V$WAIT_CHAINS donne les infos sur les sessions bloquées.
Un exemple où une session a posé plusieurs verrous puis attend sur un dbms_lock.sleep (‘PL/SQL lock timer’), bloquant des session qui attendent de poser leur verrou (enq: TM – contention)

CHAIN_ID CHAIN CHAIN_SIGNATURE INSTANCE OSID PID SID BLOCK
1 FALSE ‘PL/SQL lock timer’ <='enq: TM
– contention' <='enq: TM – contention'
1 7929 42 23 TRUE
1 FALSE ‘PL/SQL lock timer’ <='enq: TM
– contention' <='enq: TM – contention'
1 7927 41 254 TRUE
1 FALSE ‘PL/SQL lock timer’ <='enq: TM
– contention' <='enq: TM – contention'
1 7925 39 256 FALSE
2 FALSE ‘PL/SQL lock timer’ <='enq: TM
– contention' <='enq: TM – contention'
1 7933 46 25 TRUE
3 FALSE ‘PL/SQL lock timer’ <='enq: TM
– contention' <='enq: TM – contention'
1 7931 45 260 TRUE
4 FALSE ‘PL/SQL lock timer’ <='enq: TM
– contention' <='enq: TM – contention'
1 7935 47 262 TRUE

On voit toute la chaîne des session bloquées, avec la racine. Et même lorsque qu’il ne s’agit pas d’une session utilisateur active.

Quels archived logs sont supprimables dans FRA ?

English version on dbi services blog

Avez-vous déjà vérifié si la FRA gère bien votre DELETION POLICY ? C’est important de ne pas supprimer de archived logs nécessaire même si on a besoin de place. Et c’est important aussi de ne pas se retrouver avec une FRA pleine. La vue v$recovery_area_usage montre le pourcentage de l’espace qui est récupérable, mais aucune vue ne donne la liste, ou au moins les dates des archivelogs correspondant.

J’ai eu besoin d’aller dans le détail pour analyser pourquoi les archived logs de la standby n’étaient pas supprimés. C’est un bug en 11.2 qui fait que même avec CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY une standby en MOUNT ne rafraîchit pas le flag des archived logs récupérables.
Tout le détail est ici.

Voici la requête (issue de la définition interne de la vue v$recovery_area_usage ) qui permet de voir lesquels sont récupérables en fonction du nombre de backups et du fait qu’ils ont été appliqués sur les standby:

column deleted format a7 
column reclaimable format a11 
set linesize 120 
select applied,deleted,backup_count 
 ,decode(rectype,11,'YES','NO') reclaimable,count(*) 
 ,to_char(min(completion_time),'dd-mon hh24:mi') first_time 
 ,to_char(max(completion_time),'dd-mon hh24:mi') last_time 
 ,min(sequence#) first_seq,max(sequence#) last_seq 
from v$archived_log left outer join sys.x$kccagf using(recid) 
where is_recovery_dest_file='YES' 
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#) 
/

Un exemple du résultat sur la Primaire, en CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK:

APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ 
--------- ------- ------------ ----------- -------- ------------ ------------ --------- -------- 
NO        YES                1 NO               277 15-jan 17:56 19-jan 09:49      5936     6212 
NO        NO                 1 YES              339 19-jan 10:09 22-jan 21:07      6213     6516 
NO        NO                 0 NO                33 22-jan 21:27 23-jan 07:57      6517     6549

Les redo logs archivés avant le 19-jan 09:49 ont été supprimés. Ceux qui ont été backupés sont toujours là mais supprimables. Les autres doivent rester jusqu’à leur backup.

Et un exemple sur la Standby en CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY:

APPLIED   DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME   LAST_TIME    FIRST_SEQ LAST_SEQ 
--------- ------- ------------ ----------- -------- ------------ ------------ --------- -------- 
YES       YES                0 NO               746 07-jan 13:27 17-jan 11:17      5320     6065 
YES       NO                 0 YES              477 17-jan 11:37 23-jan 05:37      6066     6542 
YES       NO                 0 NO                 8 23-jan 05:57 23-jan 08:14      6543     6550 
IN-MEMORY NO                 0 NO                 1 23-jan 08:15 23-jan 08:15      6551     6551

Normalement, seul le redo en cours d’application (je suis en SYNC) ne serait pas récupérable. A cause d’un bug le flag ‘récupérable’ n’a pas été mis à jour depuis le 23-jan 05:57, date où le job de backup refait un CONFIGURE ARCHIVELOG DELETION POLICY qui rafraîchit ce flag.

2014

En passant

En souhaitant une bonne année 2014 à tous, voici les nouveautés 2014 pour moi:
Je suis maintenant certifié 12c (longue attente pour les résultats de l’exam beta…) et je travaille maintenant pour dbi services, toujours en Suisse Romande.

OCP 12c dbi services

Je continuerais à être présent sur developpez.com, sur mon blog et sur le forum, mais je blogguerais aussi sur le blog dbi services.