Blog

le 20/05/2019
rodclaudot
Données / Sécurité, Oracle, Oracle 12c

Oracle Database 12.2 : Monitoring des index

Dans les versions précédentes de base de données Oracle, on pouvait utiliser la clause « ALTER INDEX (…) MONITORING USAGE » pour analyser l’utilisation des index utilisés.

La version 12.2 d’Oracle a ajouté deux nouvelles vues qui surveillent automatiquement l’utilisation des index :

La vue V$INDEX_USAGE_INFO assure le suivi de l’utilisation de l’index depuis le dernier vidage.
La vue DBA_INDEX_USAGE affiche des statistiques cumulatives pour chaque index.

Avec ces deux nouvelles vues, Oracle suit automatiquement l’utilisation des index.

La vue DBA_INDEX_USAGE contient plusieurs colonnes permettant de connaître le nombre d’accès reçus par les index, le nombre de lignes renvoyées; l’heure la plus récente d’utilisation de l’index est également enregistrée.

Dans l’exemple suivant, je vais créer une table avec trois colonnes, avec un index dans chaque colonne.

Ensuite, je vais lancer quelques requêtes sur la table afin d’utiliser ces index, et nous pourrons contrôler que la version 12.2 d’ Oracle suit leur utilisation.

-- Création table SCTTST.TABLE1

create table SCTTST.TABLE1 (id number, lib1 varchar2(30), lib2 varchar2(30));

create index SCTTST.TABLE1_IDX1 on SCTTST.TABLE1(id);
create index SCTTST.TABLE1_IDX2 on SCTTST.TABLE1(lib1);
create index SCTTST.TABLE1_IDX3 on SCTTST.TABLE1(lib2);


insert into SCTTST.TABLE1 values (1,'le','la');
insert into SCTTST.TABLE1 values (2,'la','li');
insert into SCTTST.TABLE1 values (3,'me','ma');
insert into SCTTST.TABLE1 values (4,'ma','ta');
insert into SCTTST.TABLE1 values (5,'ti','tu');
insert into SCTTST.TABLE1 values (6,'ve','va');
insert into SCTTST.TABLE1 values (7,'vi','ri');
insert into SCTTST.TABLE1 values (8,'ra','re');
insert into SCTTST.TABLE1 values (9,'xa','xo');
insert into SCTTST.TABLE1 values (10,'xi','xu');
commit;

-- calcul de statistiques sur les index 

exec dbms_stats.gather_index_stats('SCTTST','TABLE1_IDX1');                            
exec dbms_stats.gather_index_stats('SCTTST','TABLE1_IDX2');                            
exec dbms_stats.gather_index_stats('SCTTST','TABLE1_IDX3');

Je vais exécuter des requêtes d’interrogation, une trace est activée pour vérifier que la requête utilise bien l’index.

Nous verrons qu’à chaque requête correspond un accès et que le nombre de lignes renvoyées est également surveillé.

select lib1  from  SCTTST.TABLE1 where id = 8;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2559070595
---------------------------------------------------------------------------------------------------
| Id | Operation                         | Name        | Rows | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                   |             |    1 |     6 |       2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1      |    1 |     6 |       2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN                  | TABLE1_IDX1 |    1 |       |       1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN                  | TABLE1_IDX1 |    1 |       |       1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
2 - access("ID"=8)


select id  from  SCTTST.TABLE1 where lib1 like  'l%';

ID
----------
2
1

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 734406154
---------------------------------------------------------------------------------------------------
| Id | Operation                          | Name        | Rows | Bytes | Cost (%CPU)|     Time |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                    |             | 1    |    6 |        2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE1      | 1    |    6 |        2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN                   | TABLE1_IDX2 | 1    |      |        1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------
2 - access("LIB1" LIKE 'l%')
    filter("LIB1" LIKE 'l%')

select lib1  from  SCTTST.TABLE1 where lib2 like 'x%';
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 42112554
--------------------------------------------------------------------------------
-------------------
| Id | Operation                          | Name              | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
-------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0- | SELECT STATEMENT                    |                  | 1   |      6 |2 (0)| 00:00:01 |
| 0- | SELECT STATEMENT                    |                  | 1   |      6 |2 (0)| 00:00:01 |
| 0- | SELECT STATEMENT                    |                  | 1   |      6 |2 (0)| 00:00:01 |
| 1  | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE1           | 1   |      6 |2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN                    | TABLE1_IDX3      | 1   |        |1 (0)| 00:00:01 |

Si on interroge maintenant la vue DBA_INDEX_USAGE, on constate, pour chaque requête exécutée, le nombre de lignes retournées et le nombre d’accès à l’index.

set linesize 500
col owner format A20
col name format A20
select owner,name, total_access_count, total_exec_count, total_rows_returned, last_used from DBA_INDEX_USAGE WHERE owner = 'SCTTST' ORDER BY name;

OWNER                NAME                 TOTAL_ACCESS_COUNT TOTAL_EXEC_COUNT TOTAL_ROWS_RETURNED LAST_USE
-------------------- -------------------- ------------------ ---------------- ------------------- --------
SCTTST               TABLE1_IDX1                           1                1                   1 09/05/19
SCTTST               TABLE1_IDX2                           1                1                   2 09/05/19
SCTTST               TABLE1_IDX3                           1                1                   2 09/05/19

La requête suivante indique le nombre d’accès reçus par l’index :

set  linesize 200
col owner format A20
col name format A20
select name, bucket_2_10_rows_returned, bucket_11_100_rows_returned, bucket_101_1000_rows_returned from DBA_INDEX_USAGE where owner='SCTTST';

NAME BUCKET_2_10_ROWS_RETURNED BUCKET_11_100_ROWS_RETURNED BUCKET_101_1000_ROWS_RETURNED
-------------------- ------------------------- --------------------------- -----------------------------
TABLE1_IDX1                                 0                           0                             0
TABLE1_IDX3                                 2                           0                             0
TABLE1_IDX2                                 2                           0                             0

La colonne « BUCKET_2_10_ROWS_RETURNED » indique que l’index a été utilisé en 2 et 10 fois.

Oracle introduit de nouvelles vues qui fournissent des informations très utiles aux administrateurs de bases de données afin que ceux-ci puissent suivre plus facilement l’utilisation des index. Oracle exécute cette opération automatiquement, sans surcharge, avec peu d’impact sur la performance.

—

Montez en compétences sur les bases de données Oracle ? Découvrez nos prochaines formations sur Paris et en région

rodclaudot

Voir tous ses articles

Blog

Oracle Database 12.2 : Monitoring des index

Articles récents