Les tables IOT : partie 2, index secondaire décalé avec l'index primaire - Secondary index shifted with primary index
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)
Comparaison des traces
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).
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)