Suite à un échange à propos du fonctionnement du CBO et plus particulièrement sur les choix de parcours d’index en full scan ou en fast full scan sur le forum developpez.net qui était de la teneur suivante :
Intervenant 1 – Est-il vrai que si on effectue le requête select ename from emp, oracle n’utilisera pas l’index même si celui-ci existe ?
Intervenant 2 – Vrai sauf si la colonne ename ne peut pas être nulle et ce par contrainte, auquel cas un index full scan sera choisi
Intervenant 3 – Ne vouliez vous pas dire index fast full scan ?
Intervenant 2 – Non :
connect scott/tiger alter table emp modify ename not null; select ename from emp ; select * from table( dbms_xplan.display_cursor() ); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 1405736511 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 14 | 84 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | EMP_T_IX1 | 14 | 84 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------Intervenant 3 – Oui mais sur une table plus grosse ça fait du fast full scan (démo à l’appui) donc si.
Intervenant 2 – Peut être mais la question c’est sur la table emp et pas sur une autre donc relire l’énnoncé et donc re-non
Intervenant 4 – C’est possible aussi sur la table empconnect scott/tiger alter table emp modify ename not null; select ename from emp ; select * from table( dbms_xplan.display_cursor() ); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 3610277708 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | INDEX FAST FULL SCAN| IDX_EMP_ENAME | 14 | 84 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------------------
La problématique qui se situe derrière cet échange est pourquoi dans un cas un index full scan est choisi et pas dans l’autre et quelle est au juste la différence entre un index full scan et un index fast full scan ?
La différence profonde entre un index full scan et un index fast full scan réside principalement dans la manière d’aller lire les blocs de données sur disque, le premier effectuera des accès monoblocs (ou db file sequential read) et arborescents alors que le second effectuera des accès multiblocs (ou db file scattered read) et séquentiels sur les seules feuilles de l’index, accès censés être plus rapides dès lors qu’il y a plus d’un bloc à lire.
Le premier plan (celui de l’intervenant 2) utilise un index monobloc tandis que le second plan (celui de l’intervenant 4) utilise un index de deux blocs feuilles.
Comment le choix s’est-il opéré ?
Si on reproduit le premier test :
drop index IDX_EMP_ENAME ; create index IDX_EMP_ENAME on emp(ename) ; alter session set tracefile_identifier=Monobloc_idx ; alter system flush shared_pool ; alter session set events='10053 trace name context forever' ; select ename from emp ; alter session set events='10053 trace name context off' ;
La lecture de la trace effectuée et plus particulièrement des données liées aux statistiques sur les objets et au mode d’accès aux données présentent les informations suivantes :
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP Alias: EMP
#Rows: 14 #Blks: 5 AvgRowLen: 38.00 ChainCnt: 0.00
Index Stats::
Index: IDX_EMP_ENAME Col#: 2
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: PK_EMP Col#: 1
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Access path analysis for EMP
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMP[EMP]
Table: EMP Alias: EMP
Card: Original: 14.000000 Rounded: 14 Computed: 14.00 Non Adjusted: 14.00
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 37987
Resp_io: 3.00 Resp_cpu: 37987
Access Path: index (index (FFS))
Index: IDX_EMP_ENAME
resc_io: 2.00 resc_cpu: 8801
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 8801
Resp_io: 2.00 Resp_cpu: 8801
Access Path: index (FullScan)
Index: IDX_EMP_ENAME
resc_io: 1.00 resc_cpu: 9921
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_EMP_ENAME
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 14.00 Bytes: 0
On constate en bleu que les statistiques de l’index montrent que celui-ci est bien constitué d’un unique bloc feuille (#LB pour Number of Leaf-Blocks), l’information se retrouve dans la colonne blocks de la vue user_indexes.
Si on compare les coût d’accès non parallèles (Cost_io)1 en rouge ou vert on s’apperçoit qu’il choisi l’accès le moins coûteux, certes, mais surtout qu’il compte un accès à 2 blocs en cas d’index fast full scan ce qui peut paraitre un peu fort de café alors que de son propre aveux, Oracle ne comptabilise qu’un seul bloc pour l’index. La seule explication raisonnable que je peux voire ici, c’est que pour une entrée/sortie multibloc il lira forcément un minimum de deux blocs ce qui est plus que logique.
Si on reproduit le second test :
drop index IDX_EMP_ENAME ; create index IDX_EMP_ENAME on emp(ename) pctfree 96 ; alter session set tracefile_identifier=Multibloc_idx ; alter system flush shared_pool ; alter session set events='10053 trace name context forever' ; select ename from emp ; alter session set events='10053 trace name context off' ;
La lecture de la trace effectuée et plus particulièrement des données liées aux statistiques sur les objets et au mode d’accès aux données présentent les informations suivantes :
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP Alias: EMP
#Rows: 14 #Blks: 5 AvgRowLen: 38.00 ChainCnt: 0.00
Index Stats::
Index: IDX_EMP_ENAME Col#: 2
LVLS: 1 #LB: 2 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: PK_EMP Col#: 1
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Ici le nombre de blocs feuille est de 2 et le nombre total de blocs d’index de 3 (une racine et deux feuilles).
Access path analysis for EMP
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMP[EMP]
Table: EMP Alias: EMP
Card: Original: 14.000000 Rounded: 14 Computed: 14.00 Non Adjusted: 14.00
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 37987
Resp_io: 3.00 Resp_cpu: 37987
Access Path: index (index (FFS))
Index: IDX_EMP_ENAME
resc_io: 2.00 resc_cpu: 15923
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 15923
Resp_io: 2.00 Resp_cpu: 15923
Access Path: index (FullScan)
Index: IDX_EMP_ENAME
resc_io: 3.00 resc_cpu: 24164
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 3.00 Resp: 3.00 Degree: 1
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: IDX_EMP_ENAME
resc_io: 3.00 resc_cpu: 24164
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 3.00 Resp: 3.00 Degree: 0
Access Path: index (FullScan)
Index: IDX_EMP_ENAME
resc_io: 3.00 resc_cpu: 24164
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 3.00 Resp: 3.00 Degree: 0
Bitmap nodes:
Used IDX_EMP_ENAME
Cost = 3.751463, sel = 1.000000
****** finished trying bitmap/domain indexes ******
******** End index join costing ********
Best:: AccessPath: IndexFFS
Index: IDX_EMP_ENAME
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 14.00 Bytes: 0
C’est bien le parcours des feuilles d’index en mode multibloc qui est choisi.
- les coûts d’accès parallèles sont donnés par Resp_io