Introduction
Est-ce que vous vous êtes déjà demandé ce que faisait réellement Oracle lors d'un ordre SQL? Quels sont les tests et contrôles internes qu'il effectue? Nous avons à notre disposition un très bon outil, la trace 10046, qui permet justement de voir ce travail effectué sous le capot.

Dans cet article je vais examiner un ordre très basique, un CREATE TABLE d'une table avec une colonne, sans contrainte d'intégrité ni index, et en plus sur l'ancienne architecture Oracle, celle non PDB, pour avoir une trace plus simple.



Points d'attention
Vérifiez que votre trace ne remplisse pas votre disque dur.



Base de tests
Une base Oracle 12c avec l'architecture non CDB.



Exemples

============================================================================================
Le CREATE TABLE

============================================================================================
Ma base Oracle est une base 12c, avec l'ancienne architecture dite non CDB.
          SQL> select banner from v$version where rownum = 1;
          BANNER
          --------------------------------------------------------------------------------
          Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
          
Je crée un user de test pour ne pas tracer le CREATE TABLE avec le user SYS et ainsi éviter les requêtes internes qui parasiteraient le fichier trace.
          SQL> CREATE USER ZZTEST identified by nospam3138686970;
          SQL> GRANT DBA TO ZZTEST;
          SQL> connect ZZTEST
          Enter password:
          Connected.
          
J'active la trace 10046 avec le niveau 4 pour avoir les valeurs des bind variables.
          SQL> ALTER SESSION SET timed_statistics = TRUE;
          SQL> ALTER SESSION SET max_dump_file_size = unlimited;
          SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_create_table01.trc';
          SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';

L'ordre SQL à tracer.
          SQL> CREATE TABLE ZZ01 (ID NUMBER);

Ne pas oublier de désactiver la trace une fois l'ordre SQL exécuté.
          SQL> ALTER SESSION SET events '10046 trace name context off';
         
Interroger v$diag_info pour trouver le fichier de trace : vous noterez que je l'ai appelé
zztest_create_table01.trc mais que Oracle a préfixé ce nom avec TGARC2_ora_17012, le nom de ma base.
          SQL> SELECT value FROM   v$diag_info WHERE  name = 'Default Trace File';
          VALUE
          --------------------------------------------------------------------------------
          /pprd/GARC/db/TGARC2/admin/diag/rdbms/tgarc2/TGARC2/trace/TGARC2_ora_17012_zztest_create_table01.trc
          
L'utilitaire TKPROF permet de rendre le fichier de trace brut plus lisible; attention, il y a un prix à payer, on perd les valeurs des bind variables dans le fichier généré.
          Unix$ tkprof TGARC2_ora_17012_zztest_create_table01.trc TGARC2_ora_17012_zztest_create_table01.txt
          TKPROF: Release 12.1.0.2.0 - Development on Mon Feb 5 13:40:47 2018
          Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


============================================================================================
Liste des ordres SQL dans la trace 10046

============================================================================================
         
Que contient ce fichier trace au format TKPROF? Je recopie ici tous les ordres SQL de la trace avec quelques commentaires de mon cru pour y voir plus clair mais comme les opérations internes ont lieu directement sur les tables du dictionnaire de données d'Oracle et que celles-ci ne sont pas documentées, il est parfois impossible de comprendre ce que fait le SGBD, tout au plus peut-on essayer de deviner. Pour rappel, le nom de ces tables se termine par $.

Je laisse les SQL_ID au cas où vous voudriez comparer votre base et la mienne.
      
1) La création de l'objet est enregistrée dans la table d'audit.
          SQL ID: 4vs91dcv7u1p6 Plan Hash: 0
          insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid, userhost,terminal,action#,returncode, obj$creator,obj$name,auth$privileges, auth$grantee, new$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$text,spare1,spare2,  priv$used,clientid, sessioncpu,proxy$sid,user$guid, instance#,process#,xid,scn,auditid, sqlbind,sqltext,obj$edition,dbid)
          values (:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP),:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)
          
            
2) Le CREATE TABLE proprement dit. Attention, cet ordre en lui même ne fait rien, les INSERTs dans les tables du dictionnaire de données, qui correspondent vraiment au CREATE TABLE, se font après.s
          SQL ID: a9xqu7xuna3xz Plan Hash: 0
          CREATE TABLE ZZ01 (ID NUM
          
           
3) ?? Ce SELECT ne retourne aucun enregistrement dans ma base. S'agit-il d'une gestion de triggers système?
          SQL ID: 5hrvvu1r771m5 Plan Hash: 415205717
          SELECT VALUE$
          FROM SYS.PROPS$
          WHERE NAME = 'OGG_TRIGGER_OPTIMIZATION'
          
           
4) Vérification si l'objet à créer existe déjà?
          SQL ID: 1p5grz1gs7fjq Plan Hash: 813480514
          select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9
          from obj$
          where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
          
Dans le fichier de trace brut, cet ordre est exécuté trois fois avec les bind variables suivantes.
          Exec 01 : est-ce que la table existe dans le schéma du user courant?
          Bind#0 value=96         --> id du user zztest
          Bind#1 value="ZZ01"        --> nom de l'objet à créer
          Bind#2 value=1          --> namespace des tables
          
          Exec 02 : est-ce que la table existe dans le schéma du user d'id 1? Ce user n'existe pas dans ma base; reliquat d'anciennes versions d'Oracle?
          Bind#0 value=1
          Bind#1 value="ZZ01"
          Bind#2 value=1
            
          Exec 03 : est-ce que la table de nom _NEXT_OBJECT existe dans le schéma du user d'id 0, à savoir SYS? Attention, c'est visiblement une table interne d'Oracle puisqu'elle commence avec un underscore.
          Bind#0 value=0
          Bind#1 value="_NEXT_OBJECT"
          Bind#2 value=1
          
          
5) Update d'un objet de SYS?
          SQL ID: 8zc85a8249x81 Plan Hash: 2683643009
          update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9, dataobj#=:10,flags=:11,oid$=:12,spare1=:13,spare2=:14,spare3=:15,signature=:16,spare7=:17,spare8=:18,spare9=:19
          where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
           
          
6) L'objet existe-t-il déjà?
Oracle a calculé un nombre, 94977 (valeur de ma bind variable), et regarde en base si celui-ci correspond à un objet. A cette étape, cet objet n'existe toujours pas car aucun INSERT dans le dictionnaire de données n'a eu lieu. Après ces INSERTs, le nombre 94977 correspond bien à la table ZZ01 créée par le user ZZTEST. Donc ce SELECT présent sert à quoi? A vérifier si ce nombre est disponible? Ou bien à récupérer des infos sur la table déjà créée, malgré ce que j'ai dis dans la partie 4, sinon pourquoi ne pas faire un select count(*)?
          SQL ID: 87gaftwrm2h68 Plan Hash: 1072382624
          select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname
          from obj$ o
          where o.obj#=:1
          
          
7) Le SELECT précédent a-t-il généré une erreur?
          SQL ID: 77mtwmnd4c4pk Plan Hash: 1589386835
          select obj#
          from objerror$
          

8) Requête sur la table du nouvel audit Oracle 12, l'Unified Audit Policies.
          SQL ID: 04kug40zbu4dm Plan Hash: 4006480256
          select policy#, action#
          from aud_object_opt$ where object# = :1 and type = 2
          
          
9) SELECT sur les valeurs par défaut des index à créer, au cas où la table aurait une PK ou une contrainte Unique?
          SQL ID: 616m6uhpa2usu Plan Hash: 2542797530
          select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
            i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
            i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
            nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
            i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
            nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
            null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1),
            nvl(i.unusablebefore#,0),nvl(i.unusablebeginning#,0), ist.cachedblk,
            ist.cachehit,ist.logicalread
          from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
            min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
            valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c
            where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
          
          
10) SELECT sur les colonnes? Attention, la table s'appelle icol$, pas col$; est-ce que ça ne concernerait pas les colonnes indexées?
          SQL ID: 5n1fs4m2n2y0r Plan Hash: 452367486
          select pos#,intcol#,col#,spare1,bo#,spare2,spare3
          from icol$ where obj#=:1
           
          
11) SELECT sur les histogrammes. A quoi ça sert? Aucune idée, désolé.
Néanmoins, petit cadeau et grooooose surprise, un hint RULE!!!!! pour utiliser le mode RBO au lieu du CBO; je ne pensais pas que cela existait encore, surtout en 12c  :-)
          SQL ID: 96g93hntrzjtr Plan Hash: 2239883476
          select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln
          from hist_head$ where obj#=:1 and intcol#=:2
          
          
12) SELECT sur les histogrammes encore...
          SQL ID: 7u49y06aqxg1s Plan Hash: 3312420081
          select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count
          from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
          
         
13) SELECT sur la table des colonnes des tables de la base.
          SQL ID: 14d7z6mh1sxuz Plan Hash: 3765558045
          select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
            nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
            scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
            rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
            nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),
            nvl(unusablebeginning#,0)
          from col$
          where obj#=:1 order by intcol#
          
          Exécution 1 : pour la table SYN$ (OBJ# = 94)
          Bind#0 value=94
         
          
14) SELECT sur les segments. Oracle regarde dans le tablespace SYSTEM, file 1024 et block 1112; est-ce là que se trouvent les paramètres par défaut quand on veut créer une table sans préciser les paramètres de stockage?
          SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3755742892
          select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
            NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
            NVL(scanhint,0),NVL(bitmapranges,0)
          from seg$
          where ts#=:1 and file#=:2 and block#=:3
         
          Bind#0 value=0
          Bind#1 value=1024
          Bind#2 value=1112
         
           
15) SELECT sur les synonymes. A quoi cela sert-il?
          SQL ID: ac805sb6btmg3 Plan Hash: 663321076
          select obj#, owner, node
          from syn$
          where name=:1 and owner=:2
          
          Bind#0 value="ZZ01"
          Bind#1 value="ZZTEST"
          
          
16) SELECT sur les users. Est-ce pour récupérer notamment le tablespace de données par défaut du user et ainsi savoir dans quel tablespace créer la table?
          SQL ID: 2tkw12w5k68vd Plan Hash: 1457651150
          select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,
            decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,
            ext_username,spare2
          from user$
          where name=:1
          
         
17) Premier INSERT
Création de la table dans la table obj$. Les valeurs par défaut de plusieurs paramètres ont été récupérés lors des SELECTs précédents.
          SQL ID: 0kcbwucxmazcp Plan Hash: 0
          insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,
            remoteowner,linkname,subname,dataobj#,flags,oid$,spare1,spare2,spare3,
            signature, spare7, spare8, spare9)
          values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22)
          
Je ne mets pas toutes les bind variables, il y en a trop.
          Bind#0 value=96
          Bind#1 value="ZZ01"
          Bind#2 value=1
          Bind#3 value=94977  
          ...
          
            
18) SELECT sur les privilèges du user propriétaire de la table?
          SQL ID: cyyqdf5szgffv Plan Hash: 1218126112
          select distinct(-privilege#),mod(nvl(option$,0), 2), bitand(nvl(option$,0), 4) /4, bitand(nvl(option$,0), 8) /8, bitand(nvl(option$,0), 16) /16
          from sysauth$
          where grantee#=:1 and privilege#<0
          
         
19) ??
          SQL ID: cn6hhn36a4rrs Plan Hash: 3845132125
          select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3,refact
          from cdef$
          where robj#=:1
          
         
20) ??
          SQL ID: gx4mv66pvj3xz Plan Hash: 2570921597
          select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
            rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3
          from cdef$
          where obj#=:1
          
         
21) ??
          SQL ID: 53saa2zkr6wc3 Plan Hash: 3038981986
          select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
          from ccol$
          where con#=:1
        
           
22) SELECT sur les droits associés aux objets? Aucune ligne renvoyée par ma base.
          SQL ID: ct2g3h4c98fp4 Plan Hash: 2968095032
          select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)),
            min(bitand(nvl(option$,0), 4) /4), max(bitand(nvl(option$,0), 8) /8),
            max(bitand(nvl(option$,0), 16) /16)
          from objauth$
          where obj#=:1 and col# is not null
          group by privilege#, col#, grantee#
          order by col#, grantee#
          
          
23) SELECT sur les droits associés aux objets? Aucune ligne renvoyée par ma base.
          SQL ID: 8uh6xphq54kh0 Plan Hash: 2968095032
          select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2)),
            min(bitand(nvl(option$,0), 4) /4), max(bitand(nvl(option$,0), 8) /8),
            max(bitand(nvl(option$,0), 16) /16)
          from objauth$
          where obj#=:1
          group by grantee#,privilege#,nvl(col#,0)
          order by grantee#
         
          
24) Deuxième INSERT
Création du segment initial car la base n'est pas en mode DEFFERED SEGMENT. Les différents paramètres ont été récupérés avec les SELECTs précédents dans le dictionnaire de données.
          SQL ID: g7mt7ptq286u7 Plan Hash: 0
          insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts, extsize,extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapranges)
          values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0,NULL, :17),:18,:19)
          
          Je ne mets pas toutes les bind variables, il y en a trop.  
          Bind#0 value=1024
          Bind#1 value=82168
          Bind#2 value=3
          Bind#3 value=0
          ...
          
          
25) SELECT sur la table des options d'audit par défaut.
          SQL ID: 7fwum1yuknrsh Plan Hash: 2035254952
          select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
            nvl(t.clucols,0),t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,
            t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,
            t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),
            t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),
            nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread
          from tab$ t, tab_stats$ ts
          where t.obj#= :1 and t.obj# = ts.obj# (+)
          
          Bind#0 value=444 ----- le champ NAME de sys.obj$ vaut _default_auditing_options_ pour obj# = 444
           
          
26) SELECT sur les audits.
          SQL ID: gd28w82ct6rva Plan Hash: 2970138452
          select audit$
          from tab$
          where obj# = :1
         
          
27) Troisième INSERT
Création de la table proprement dite, après avoir créé l'objet et le segment. Plusieurs paramètres ont été récupérés lors des SELECTs précédents.
          SQL ID: asnhcg241fr2y Plan Hash: 0
          insert into tab$(obj#,ts#,file#,block#,bobj#,tab#,intcols,kernelcols,clucols, audit$,flags,pctfree$,pctused$,initrans,maxtrans,rowcnt,blkcnt,empcnt, avgspc,chncnt,avgrln,analyzetime,samplesize,cols,property,degree,instances, dataobj#,avgspc_flb,flbcnt,trigflag,spare1,spare6)
          values (:1,:2,:3,:4,decode(:5,0,null,:5),decode(:6,0,null,:6),:7,:8,decode(:9,0,null, :9),:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25, decode(:26,1,null,:26),decode(:27,1,null,:27),:28,:29,:30,:31,:32,:33)
          
          
28) Quatrième INSERT
Création des colonnes de la table. La table n'a qu'un champ, ID, donc cet INSERT n'est fait qu'une fois.  
          SQL ID: 3ru3r9twxsazy Plan Hash: 0
          insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale, null$,offset,fixedstorage,segcollength,deflength,default$,col#,property, charsetid,charsetform,spare1,spare2,spare3,evaledition#,unusablebefore#, unusablebeginning#)
          values (:1,:2,:3,:4,:5,:6,decode(:5,182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0, null,:7)),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8, 180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20,decode(:21,1,null,:21),decode(:22,0,null,:22),decode(:23,0,null,:23))
          
           
29) Mise à jour des infos du segment de la table une fois la création finie.  
          SQL ID: 0kkhhb2w93cx0 Plan Hash: 2170058777
          update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13), groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19
          where ts#=:1 and file#=:2 and block#=:3
          
         
30) Désactivation de la trace
          SQL ID: 959zc6wq090jm Plan Hash: 0
          ALTER SESSION SET events '10046 trace name context off'
           
          
============================================================================================
Conclusion

============================================================================================
         
Que pouvons nous conclure du contenu de cette trace? A vrai dire pas grand chose puisque Oracle ne donne aucune information sur le rôle des tables internes du dictionnaire de données, même via la table DBA_TAB_COMMENTS. Néanmoins nous voyons que pour une base 12c non CDB, Oracle exécute pour un CREATE TABLE basique (sans contrainte d'intégrité ni d'index) trente ordres SQL dans les tables du dictionnaire de données : vingt et un SELECTs, cinq INSERTs e
t quatre UPDATEs! Rien que pour comprendre cela, ça valait la peine de générer cette trace.

Pour les INSERT, les tables concernées sont :

  • sys.aud$ : table de l'audit pour tracer la session qui crée un objet
  • obj$ : table des objets de la base car on crée un nouvel objet
  • seg$: table des segments car la base n'étant pas en mode DEFERRED SEGMENT, le CREATE TABLE crée un segment
  • tab$ : table des table de la base puisque l'ordre DML est justement un CREATE TABLE
  • col$ : table des colonnes de la base puisque la table doit contenir au moins une colonne

Pour les UPDATE, les tables concernées sont :

  • obj$ : 2 UPDATEs
  • seg$ : 2 UPDATEs


Dans un prochain article je travaillerai sur une base 12c architecture CDB et, croyez-moi, c'est bien plus complexe que sur une 12c non CDB.