Introduction
Cet article présentait les concepts de base d'une table IOT : http://dbaoraclesql.canalblog.com/archives/2019/01/29/37058939.html.
Nous allons maintenant voir s'il est possible de prouver, comme le dit la doc Oracle, que lorsque les logicals rowid de l'index secondaire ne sont plus en phase avec les vrais rowids de la table IOT, alors Oracle fait une lecture directe via le rowid qui échoue avant de parcourir la table IOT. 


 
Points d'attention
Aucun.



Base de tests
Une base Oracle 12.



Exemples
============================================================================================
Environnement de tests
============================================================================================

Création de la table de test et d'un index secondaire sur la colonne FIRST_NAME.
     SQL> create table ZZ_IOT (id number primary key, first_name varchar2(50 char), last_name varchar2(50 char), created date) organization index;

     SQL> INSERT INTO zz_iot SELECT rownum, 'First_name' || to_char(rownum), 'Last_name' || to_char(rownum), sysdate FROM dual CONNECT BY LEVEL <=100000;

     SQL> commit;


     SQL> create index idx_zz_iot_first_name on zz_iot(first_name);
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);

Pour notre problématique, la colonne importante est PCT_DIRECT_ACCESS de USER_INDEXES. Elle n'a de sens que pour un index secondaire. Oracle dit que c'est : "For a secondary index on an index-organized table, the percentage of rows with VALID guess".
En français : c'est le pourcentage d'accès direct à la table IOT via le logical rowid d'un index secondaire.
En clair? Quand on veut des données qui sont absentes de l'index secondaire en utilisant dans la clause Where une colonne de l'index secondaire, Oracle va alors parcourir l'index secondaire, récupérer le logical rowid ET essayer d'accéder directement au bloc de la table IOT avec ce rowid. Si ce bloc est le bon (celui contenant les données recherchées), on s'arrête là sinon, à cause de réorganisations des blocs de la table IOT, Oracle doit calculer la PK à partir du logical rowid et parcourir l''index primaire (la table IOT) pour retrouver le bon bloc.

Actuellement cette colonne vaut 100 pour l'index secondaire, donc si on utilise celui-ci pour une recherche, Oracle parcourt cet index secondaire et avec le rowid il lit directement le bloc de la table.
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME               INDEX_TYPE     PCT_DIRECT_ACCESS
     ------------------------------ ---------------------------
     IDX_ZZ_IOT_FIRST_NAME    NORMAL              100
     SYS_IOT_TOP_80295        IOT - TOP             0

Maintenant, on force le ré-arrangement des blocs de données en supprimant 99 000 rows et, par le shrink, on invalide les rowid de l'index secondaire car les quelques données restantes changent de bloc.
     SQL> delete from zz_iot where mod(id, 100) <> 0;
     99000 rows deleted.
 
     SQL> commit;

     SQL> ALTER TABLE zz_iot SHRINK SPACE;


     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);


On a bien maintenant 0 pour le champ PCT_DIRECT_ACCESS.   
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                       INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ ---------------------------
     IDX_ZZ_IOT_FIRST_NAME        NORMAL                     0
     SYS_IOT_TOP_80295            IOT - TOP                 0


============================================================================================
Identifier l'accès direct : plan d'exécution
============================================================================================
   
Que ce soit avec un PCT_DIRECT_ACCESS à 0 ou 100, on a le même plan d'exécution. L'accès direct avec logical rowid, qu'il soit OK ou KO, n'apparait pas, il faut donc chercher ailleurs, avec Autotrace ou une trace 10046.
     --------------------------------------------------------------
     | Id  | Operation      | Name          
     --------------------------------------------------------------
     |   0 | SELECT STATEMENT  |    
     |*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_80295      
     |*  2 |   INDEX RANGE SCAN| IDX_ZZ_IOT_FIRST_NAME
     --------------------------------------------------------------

============================================================================================
Identifier l'accès direct : Autotrace
============================================================================================
   
On vide le cache mémoire et on active l'autotrace.
     SQL> alter system flush buffer_cache;

     SQL> set autotrace on

PCT_DIRECT_ACCESS à 0
Première exécution.
     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';   

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      5  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
    
Si on ré-exécute l'ordre sans vider le cache, on n'a plus de physical read, seulement 6 consistent gets.
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      0  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
     
PCT_DIRECT_ACCESS à 100
Pour passer d'un PCT_DIRECT_ACCESS de 0 à 100, on remet en phase les rowid de l'index secondaire avec ceux de l'index primaire, c'est à dire de la table. Pour cela on a le choix : REBUILD de l'index ou bien utiliser la commande UPDATE BLOCK REFERENCES.
     SQL> alter index IDX_ZZ_IOT_FIRST_NAME UPDATE BLOCK REFERENCES;
    
Euh, échec...
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME               INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ --------------------------- ----
     IDX_ZZ_IOT_FIRST_NAME           NORMAL                      0
     SYS_IOT_TOP_80295           IOT - TOP                   0
     
Oups, j'avais oublié de recalculer les stats.
C'est bon, on a à nouveau 100.
     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);
        
     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME               INDEX_TYPE           PCT_DIRECT_ACCESS
     ------------------------------ --------------------------- -----------------
     IDX_ZZ_IOT_FIRST_NAME           NORMAL                     100
     SYS_IOT_TOP_80295           IOT - TOP                   0
     
On vide le cache mémoire.
     SQL> alter system flush buffer_cache;
    
     SQL> set autotrace on

     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';
     ID FIRST_NAME            LAST_NAME            CREATED
     -------------------- ----------------------------------
     200 First_name200        Last_name200         09-FEB-19
    
     Statistics
     ----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      5  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Si on réexécute plusieurs fois, on a ça.
     Statistics
     ----------------------------------------------------------
      0  recursive calls
      0  db block gets
      6  consistent gets
      0  physical reads
      0  redo size
    783  bytes sent via SQL*Net to client
    607  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

Aïe, échec à nouveau... Qu'on ait 100 ou 0 dans PCT_DIRECT_ACCESS, on a les mêmes nombre de consistent gets et physical reads.
Bon, avec Autotrace, le résultat n'est pas probant. Voyons voir avec une trace 10046 si on observe quelque chose de bizarre...


============================================================================================
Identifier l'accès direct : trace 10046
============================================================================================
   
Trace 10046 avec PCT_DIRECT_ACCESS à 100
     SQL> ALTER SESSION SET timed_statistics = TRUE;

     SQL> ALTER SESSION SET max_dump_file_size = unlimited;
     SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_IOT.trc';               
     SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';

     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';
     ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- ----------------
     200 First_name200                      Last_name200                     09-FEB-19

On la désactive et on identifie où le fichier a été généré.
     SQL> ALTER SESSION SET events '10046 trace name context off';

     SQL> SELECT value FROM   v$diag_info WHERE  name = 'Default Trace File';
     VALUE
     ---------------------------------------------------------------------------------
     /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.trc

Tkprof rend le fichier trace plus lisible.
     SQL> ! tkprof /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.trc /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.txt
     TKPROF: Release 12.2.0.1.0 - Development on Sat Feb 9 11:35:24 2019
     Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Contenu de la trace 10046 avec PCT_DIRECT_ACCESS à 100
     SQL> ! view /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.txt
     TKPROF: Release 12.2.0.1.0 - Development on Sat Feb 9 11:35:24 2019

     Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

     Trace file: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT.trc
     Sort options: default

     ********************************************************************************
     count    = number of times OCI procedure was executed
     cpu      = cpu time in seconds executing
     elapsed  = elapsed time in seconds executing
     disk     = number of physical reads of buffers from disk
     query    = number of buffers gotten for consistent read
     current  = number of buffers gotten in current mode (usually for update)
     rows     = number of rows processed by the fetch or execute call
     ********************************************************************************

     SQL ID: b0un6gq6pbk1x Plan Hash: 4235454309

     select *
     from
      zz_iot where FIRST_NAME = 'First_name200'

     call     count       cpu    elapsed       disk      query    current        rows
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     Parse        1      0.00       0.00          0          0          0           0
     Execute      1      0.00       0.00          0          0          0           0
     Fetch        2      0.00       0.00          0          5          0           1
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     total        4      0.00       0.00          0          5          0           1

     Misses in library cache during parse: 1
     Optimizer mode: ALL_ROWS
     Parsing user id: 128
     Number of plan statistics captured: 1

     Rows (1st) Rows (avg) Rows (max)  Row Source Operation
     ---------- ---------- ----------  ---------------------------------------------------
              1          1          1  INDEX UNIQUE SCAN SYS_IOT_TOP_80295 (cr=5 pr=0 pw=0 time=28 us starts=1 cost=3 size=43 card=1)(object id 80296)
              1          1          1   INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME (cr=4 pr=0 pw=0 time=25 us starts=1 cost=3 size=0 card=1)(object id 80300)

Trace 10046 avec PCT_DIRECT_ACCESS à 0
Maintenant, on génère une trace avec un PCT_DIRECT_ACCESS à 0 et on va comparer les deux.
Le truc est qu'on va faire un MOVE en transférant la table d'un tablespace dans un autre pour invalider les rowid de l'index secondaire. En effet, dans le rowid il y a l'id du fichier et là, en changeant de tbs, on change forcément de fichier.
     SQL> create tablespace tbs_iot datafile '/u01/app/oracle/oradata/orcl12c/orcl/iot.dbf' size 10m EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

     SQL> alter user HR quota unlimited on TBS_IOT;


     SQL> alter table zz_iot move tablespace tbs_iot;

     SQL> exec dbms_stats.gather_table_stats('', 'ZZ_IOT', CASCADE=>TRUE);


     SQL> select INDEX_NAME, INDEX_TYPE, PCT_DIRECT_ACCESS from USER_INDEXES where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                    INDEX_TYPE             PCT_DIRECT_ACCESS
     -----------------------------------------------------------------
     IDX_ZZ_IOT_FIRST_NAME        NORMAL                      0
     SYS_IOT_TOP_80295            IOT - TOP                 0

Génération de la trace.
     SQL> ALTER SESSION SET timed_statistics = TRUE;
     SQL> ALTER SESSION SET max_dump_file_size = unlimited;
     SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'zztest_IOT_1.trc';               
     SQL> ALTER SESSION SET events '10046 trace name context forever, level 4';

     SQL> select * from zz_iot where FIRST_NAME = 'First_name200';
     ID FIRST_NAME                          LAST_NAME                      CREATED
     ---------- -------------------------------------------------- -----------------
     200 First_name200                      Last_name200                     09-FEB-19

     SQL> ALTER SESSION SET events '10046 trace name context off';

     SQL> ! tkprof /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_0.trc /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_0.txt

Contenu de la trace.
     select *
     from
      zz_iot where FIRST_NAME = 'First_name200'

     call     count       cpu    elapsed       disk      query    current        rows
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     Parse        1      0.00       0.00          0          2          0           0
     Execute      1      0.00       0.00          0          0          0           0
     Fetch        2      0.00       0.00          4          6          0           1
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     total        4      0.00       0.00          4          8          0           1

     Misses in library cache during parse: 1
     Optimizer mode: ALL_ROWS
     Parsing user id: 128
     Number of plan statistics captured: 1

     Rows (1st) Rows (avg) Rows (max)  Row Source Operation
     ---------- ---------- ----------  ---------------------------------------------------
         1          1          1  INDEX UNIQUE SCAN SYS_IOT_TOP_80295 (cr=6 pr=4 pw=0 time=63 us starts=1 cost=4 size=43 card=1)(object id 80296)
         1          1          1   INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME (cr=4 pr=0 pw=0 time=15 us starts=1 cost=3 size=0 card=1)(object id 80300)

Si on ré-exécute l'ordre : le nombre de cr (consistent gets) ne change pas et le nombre de pr (physical reads) tombe à 0.
     SQL> ! tkprof /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_1.trc /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_1.txt
     SQL> ! view /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_ora_5277_zztest_IOT_1.txt
     SQL ID: b0un6gq6pbk1x Plan Hash: 4235454309
     select *
     from
      zz_iot where FIRST_NAME = 'First_name200'

     call     count       cpu    elapsed       disk      query    current        rows
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     Parse        1      0.00       0.00          0          0          0           0
     Execute      1      0.00       0.00          0          0          0           0
     Fetch        2      0.00       0.00          0          6          0           1
     ------- ------  -------- ---------- ---------- ---------- ----------  ----------
     total        4      0.00       0.00          0          6          0           1

     Misses in library cache during parse: 1
     Optimizer mode: ALL_ROWS
     Parsing user id: 128
     Number of plan statistics captured: 1

     Rows (1st) Rows (avg) Rows (max)  Row Source Operation
     ---------- ---------- ----------  ---------------------------------------------------
         1          1          1  INDEX UNIQUE SCAN SYS_IOT_TOP_80295 (cr=6 pr=0 pw=0 time=37 us starts=1 cost=4 size=43 card=1)(object id 80296)
         1          1          1   INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME (cr=4 pr=0 pw=0 time=23 us starts=1 cost=3 size=0 card=1)(object id 80300)


Maintenant, si on compare les traces, que voit-on?
Quand le champ PCT_DIRECT_ACCESS vaut 100, on a pour l'opération INDEX UNIQUE SCAN SYS_IOT_TOP_80295 le chiffre de cr=5 (Consistent Reads), quand il vaut 0, on a cr=6. A noter que pour Autotrace, le libellé est "Consistent Gets" : les deux stats sont équivalentes? Autre chose, avec la trace 10046 on a le détail des blocs lus opération par opération alors que pour l'autotrace, c'est pour l'ordre SQL en entier.

Est-ce suffisant pour dire qu'on a la preuve qu'Oracle fait une lecture directe via le logical rowid et, comme il échoue, il parcourt l'index primaire?
C'est compliqué à dire car la doc sur les traces 10046 est peu abondante et interpréter les résultats est difficile.

Ce que je comprends est que pour un PCT_DIRECT_ACCESS à 100 : 
- Opération exécutée en second : INDEX UNIQUE SCAN SYS_IOT_TOP_80295 cr=5 : accès direct au bloc de la table car PCT_DIRECT_ACCESS à 100; je m'attendais à 1 cr, pas à 5. Pourquoi cr = 5? Ca correspond à 5 lecture de blocs mémoire pour cette table? Mais pourquoi pas 1 seulement? Ou bien c'est 5 en tout : les 4 cr de l'opération sur l'index secondaire + la lecture du bloc de la table justement via un accès direct réussi?

- Opération exécutée en premier : INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME cr=4 : 4 consistent gets pour parcourir l'index secondaire et récupérer le logical rowid.


Pour un PCT_DIRECT_ACCESS à 0 :
- Opération exécutée en second : INDEX UNIQUE SCAN SYS_IOT_TOP_80295 cr=6 : accès direct KO car PCT_DIRECT_ACCESS à 0 et donc Oracle doit parcourir la table IOT comme on parcourt un index; On a bien 1 cr de plus que lorsque PCT_DIRECT_ACCESS vaut 100, ce qui serait bien la preuve que l'accès direct a échoué et qu'Oracle a ensuite dû parcourir la table IOT.

- Opération exécutée en premier : INDEX RANGE SCAN IDX_ZZ_IOT_FIRST_NAME cr=4 : 4 consistent gets pour parcourir l'index secondaire et récupérer le logical rowid


Pour info, voici le niveau des index utilisés; dans USER_INDEXES, la colonne Blevel signifie : "depth of the index from its root block to its leaf blocks".
     SQL> select index_name, BLEVEL, NUM_ROWS from user_indexes where table_name = 'ZZ_IOT' order by 1;
     INDEX_NAME                       BLEVEL    NUM_ROWS
     ------------------------- ---------- ----------
     IDX_ZZ_IOT_FIRST_NAME            2        1000
     SYS_IOT_TOP_80295                1        1000


============================================================================================
Conclusion
============================================================================================

Bon, ce fut compliqué et passionnant, mais on a bien montré que, lorsque les logical rowids d'un index secondaire sur une table IOT ne sont plus en phase avec l'index primaire, Oracle échoue dans son accès direct à la table IOT, ce qui se voit dans la trace 10046 avec la stat cr (Consistent Read).