Un monitoring des index plus évolué que celui d'Oracle - More advanced index monitoring than Oracle one
Introduction
Si vous avez déjà utilisé l'outil de monitoring des index d'Oracle, vous avez dû être effarés devant la pauvreté de celui-ci. Oracle n'indique en effet que si Oui ou Non un index est utilisé durant la période de monitoring mais pas le nombre de fois qu'on l'utilise. Dans le cas où vous voulez supprimer des index d'une table qui en a trop parce qu'ils ralentissement les INSERT et DELETE, la première chose à faire est de voir si ces index sont utilisés ou non mais aussi combien de fois ils le sont avant de prendre une décision.
Points d'attention
NA.
Base de tests
N'importe quelle base Oracle.
Exemples
============================================================================================
Le monitoring des index existant.
============================================================================================
La vue de monitoring des index est V$OBJECT_USAGE et la commande pour déclencher ce monitoring est la suivante :
SQL> ALTER INDEX <schema>.<index_name> monitoring usage;
DESC V$OBJECT_USAGE
Name Null? Type
----------------------------------------- -------- ----------------------------
INDEX_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
Attention : dans V$OBJECT_USAGE le user n'apparait pas! D'après mes tests, si SYS met en place le monitoring sur un index du schéma HR et fait un SELECT utilisant l'index monitoré (on le voit avec AUTOTRACE), la table V$OBJECT_USAGE n'est pas mise à jour. C'est uniquement si le user HR fait des SELECT utilisant ses index que le monitoring est déclenché. Cela mériterait une analyse plus fine mais ce n'est pas l'objet de ce post.
Faisons un test de monitoring.
SQL> alter index hr.PK_ZZ01 MONITORING USAGE;
SQL> select * from hr.zz01 where id < 20;
SQL> select * from V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
-------------------------------------------------------------------------------------------------------------------------------------------------------
PK_ZZ01 ZZ01 YES YES 05/21/2017 10:34:51
C'est la colonne USED, valant NO par défaut, qui basculera à YES lors de la première utilisation de l'index. Attention, je me répète mais cette table ne dit pas combien de fois l'index sera utilisé mais seulement s'il a été utilisé ou non.
Et c'est donc tout l'objet de ce post : améliorer le monitoring des index en utilisant les infos à notre disposition dans le dictionnaire de données Oracle. Car, vous avez compris, que les infos affichés par Oracle ne sont pas suffisamment pertinentes pour nos besoins! NO signifie : l'index monitoré n'a pas été utilisé durant la période d'observation mais en aucun cas que cet index ne sera jamais utilisé, notamment lors d'un traitement trimestriel, semestriel ou annuel. A l'inverse, YES veut dire que l'index monitoré a été utilisé durant la période d'observation MAIS combien de fois? S'il est utilisé une fois par mois ou 100 000 fois par jour cela n'a rien à voir!
Dans tous les cas, si on veut supprimer des index, il faut le faire de concert avec les développeurs mais pour cela il faut venir avec des informations plus détaillées que celles par défaut d'Oracle.
============================================================================================
La vue V$SQL_PLAN : là que se trouvent les infos sur l'utilisation des index.
============================================================================================
Le plus gros problème fut de trouver dans le dictionnaire de données une vue comportant l'information comme quoi tel index a bien été utilisé. Au bout d'un moment, je suis tombé sur la vue V$SQL_PLAN, vue qui contient les plans d'exécution générés par Oracle. Là où c'est intéressant, c'est que celle-ci a une colonne OPERATION correspondant au type d'opération du plan d'exécution et ce champ vaut "INDEX" lorsqu'un index est utilisé! C'est donc cette vue que nous allons exploiter :-)
Mon idée de base était de créer un trigger sur les INSERT dans la vue V$SQL_PLAN et de copier les infos créées vers une table miroir, non réinitialisée lors des arrêts/relances de la base.
SQL> create table zzsql_plan as select * from v$sql_plan where 1 = 10; -- 1=10 pour ne pas copier les données
Table créée.
Malheureusement, il est impossible de créer un trigger sur une vue appartenant à SYS. Cela se comprend car un trigger sur vue remplace l'ordre SQL au lieu de s'exécuter en plus, comme c'est le cas pour une table. N'oubliez pas : pas de mot clé AFTER ou BEFORE pour un trigger sur vue mais INSTEAD OF, ce qui signifie "A LA PLACE DE" --> si j'avais pu créer ce trigger sur l'évènement INSERT, la vue V$SQL_PLAN n'aurait plus jamais été renseignée par Oracle car les infos auraient été insérées dans ma table. A noter que le message d'erreur apparait sous SQL*Plus mais pas sous SQL Developer.
SQL> CREATE OR REPLACE TRIGGER TRIGGER1 INSTEAD OF INSERT ON V_$SQL_PLAN BEGIN dbms_output.put_line ('TEST'); END;
2 /
CREATE OR REPLACE TRIGGER TRIGGER1 INSTEAD OF INSERT ON V_$SQL_PLAN BEGIN dbms_output.put_line ('TEST'); END;
*
ERREUR à la ligne 1 :
ORA-04089: impossible créer déclencheurs sur objets appartenant à SYS
============================================================================================
Le trigger pour renseigner la table miroir de V$SQL_PLAN
============================================================================================
OK, on va alors créer ce trigger sur l'évènement système AFTER LOGON. Il aurait été plus sur de créer une procédure stockée puis de l'appeler dans des triggers AFTER LOGON, BEFORE LOGOFF et BEFORE SHUTDOWN pour ne perdre aucune des informations insérées dans V$SQL_PLAN mais ce post est là pour donner des idées, des pistes, et non pas pour récupérer 100% de toutes les infos de V$SQL_PLAN. On peut très bien décider de supprimer un index en ayant le nombre de fois grosso modo où il est utilisé plutôt que le nombre de fois exact.
Autre idée, plus performante : créer une procédure stockée et l'appeler via un job toutes les 5 minutes (ou 10 ou 30 selon l'activité de votre base et la purge de cette vue), de la sorte on a pas besoin de créer un trigger.
Voici donc le code du trigger. Vous aurez remarqué l'astuce : on ne stocke que la ligne où le champ OPERATION vaut "INDEX" car c'est cette ligne là du plan d'exécution qui dit si un index est utilisé et lequel il s'agit. Nous excluons les plans d'exécution générés par Oracle et nous ne copions dans la table miroir que les enregistrements dont la date est supèrieure à la dernière date de la table miroir.
create or replace TRIGGER TRIG_AFTER_LOGON
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO HR.ZZSQL_PLAN
SELECT *
FROM SYS.V$SQL_PLAN
WHERE SYS.V$SQL_PLAN.OBJECT_OWNER = 'HR' AND
SYS.V$SQL_PLAN.OPERATION = 'INDEX' AND
TO_CHAR(TIMESTAMP,'DD/MM/YYYY HH24:MI:SS') >
(SELECT NVL(MAX (TO_CHAR(TIMESTAMP,'DD/MM/YYYY HH24:MI:SS')), SYSDATE)
FROM HR.ZZSQL_PLAN) ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Erreur ' || TO_CHAR(SQLCODE) || ' - ' || SQLERRM);
END;
============================================================================================
Problèmes éventuels
============================================================================================
Le SELECT sur V$SQL_PLAN est très bien MAIS il peut ramener des SELECT effectués la nuit par SYS lors de la génération des stats sur les index. On peut alors faire un autre ordre SQL, en filtrant sur l'utilisateur ayant parsé l'ordre SQL. Le champ intéressant est PARSING_USER_ID qui vaut 0 pour SYS. Attention, ce champ se trouve dans peu de tables, notamment DBA_HIST_SQLSTAT.
Le SELECT suivant ramène le nombre de SELECT lancés par un user non SYS et qui utilise un index. Attention, les tables DBA_HIST... sont les snapshots de AWR
SQL> select SQL.PARSING_USER_ID, H.object_owner, H.OBJECT_NAME, count(*)
FROM DBA_HIST_SQL_PLAN H, DBA_HIST_SQLSTAT SQL
WHERE H.SQL_ID = SQL.SQL_ID and
H.OPERATION = 'INDEX'
SQL.PARSING_USER_ID <> 0
group by SQL.PARSING_USER_ID, H.object_owner, H.OBJECT_NAME
order by SQL.PARSING_USER_ID, H.object_owner, H.OBJECT_NAME;
Une autre façon de s'en sortir, serait aussi de reprendre l'ordre SQL plus haut et de sélectionner, via la colonne TIMESTAMP, que les ordres lancés pendant les heures de bureau, donc entre 09h00 et 18h00.
SQL> SELECT *
FROM SYS.V$SQL_PLAN
WHERE SYS.V$SQL_PLAN.OBJECT_OWNER = 'HR' AND
SYS.V$SQL_PLAN.OPERATION = 'INDEX' AND
TO_CHAR(TIMESTAMP,'HH24') BETWEEN '09' AND '18' AND
TO_CHAR(TIMESTAMP,'DD/MM/YYYY HH24:MI:SS') >
(SELECT NVL(MAX (TO_CHAR(TIMESTAMP,'DD/MM/YYYY HH24:MI:SS')), SYSDATE)
FROM HR.ZZSQL_PLAN) ;
Autre problème potentiel : Oracle monitore, depuis la 11g je crois, les ordres SQL les plus consommateurs de ressources. Pour cela il utilise deux vues, V$SQL_MONITOR et V$SQL_PLAN_MONITOR. Si un ordre se retrouve dans V$SQL_PLAN_MONITOR, je ne pense pas qu'il soit aussi dans V$SQL_PLAN car, dans un SGBD, on ne doublonne pas les données. Il faudrait donc, sur le SELECT ci-dessus, faire un UNION de V$SQL_PLAN avec V$SQL_PLAN_MONITOR.
============================================================================================
Les tests du trigger pour alimenter V$SQL_PLAN
============================================================================================
ATTENTION : comme expliqué ci-dessus, une meilleure solution est de déclencher toutes les X minutes un job plutôt que de passer par un trigger.
Les tables de test appartiennent au schéma HR; nous utiliserons comme PK un champ remplit avec la pseudo colonne rownum pour être sûr d'avoir des valeurs uniques :
SQL> CREATE TABLE ZZ01 AS SELECT OBJECT_NAME, OBJECT_TYPE, rownum AS ID FROM DBA_OBJECTS;
SQL> CREATE TABLE ZZ03 AS SELECT OWNER, table_name, tablespace_name, rownum AS ROWNUM_TAB FROM DBA_TABLES;
SQL> desc zz01
Nom NULL ? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(23)
ID NOT NULL NUMBER
SQL> desc zz03
Nom NULL ? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
TABLESPACE_NAME VARCHAR2(30)
ROWNUM_TAB NUMBER
Les index sont ceux créés par Oracle sur les PK de chacune des tables, correspondant aux champs ID et ROWNUM_TAB.
SQL> select INDEX_NAME, TABLE_NAME from user_indexes where index_name like '%ZZ%';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
PK_ZZ01 ZZ01
PK_ZZ03 ZZ03
Il faut maintenant exécuter des SELECT qui utiliseront les index; donc on évitera de faire des SELECT sans clause WHERE :-)
Dans certains cas (selon la sélectivité de la requête), le CBO décidera oui ou non d'utiliser un index.
SQL> select * from zz01 where id = 10;
SQL> select * from zz03 where rownum_tab > 10 and rownum_tab < 50;
SQL> select * from zz03;
etc etc
Et attention, pour alimenter la table pérenne ZZSQL_PLAN, il faut qu'un user se connecte pour déclencher le trigger AFTER LOGON.
A ce moment là, il n'y a rien dans la table d'historisation alors qu'il y a des infos dans V$SQL_PLAN : sept SELECT ont utilisé un index de HR.
SQL> select count(*) from HR.ZZSQL_PLAN ;
COUNT(*)
----------
0
SQL> SELECT count(*)
FROM SYS.V$SQL_PLAN
WHERE SYS.V$SQL_PLAN.OBJECT_OWNER = 'HR'
AND SYS.V$SQL_PLAN.OPERATION = 'INDEX' ;
COUNT(*)
----------
7
Un utilisateur se connecte : le trigger est déclenché et la table HR.ZZSQL_PLAN est renseignée avec les infos sur les index que nous recherchons.
SQL> select count(*) from HR.ZZSQL_PLAN ;
COUNT(*)
----------
7
Les infos détaillées maintenant.
SQL> SELECT TO_CHAR(S.TIMESTAMP, 'DD/MM/YYYY HH:MI:SS') AS "DATE ACCESS", I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", S.OPTIONS, S.object_type AS "TYPE INDEX"
FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
WHERE S.OBJECT_NAME = I.INDEX_NAME
ORDER BY OBJECT_NAME, "INDEX", "DATE ACCESS";
DATE ACCESS TABLE INDEX OPTIONS TYPE INDEX
--------------------------------------------------------------------------------------------
20/05/2017 02:39:13 ZZ01 PK_ZZ01 UNIQUE SCAN INDEX (UNIQUE)
20/05/2017 12:58:14 ZZ01 PK_ZZ01 UNIQUE SCAN INDEX (UNIQUE)
20/05/2017 12:59:35 ZZ01 PK_ZZ01 RANGE SCAN INDEX (UNIQUE)
20/05/2017 02:40:41 ZZ03 PK_ZZ03 RANGE SCAN INDEX (UNIQUE)
20/05/2017 02:43:42 ZZ03 PK_ZZ03 FAST FULL SCAN INDEX (UNIQUE)
20/05/2017 02:43:43 ZZ03 PK_ZZ03 FULL SCAN INDEX (UNIQUE)
20/05/2017 02:46:28 ZZ03 PK_ZZ03 UNIQUE SCAN INDEX (UNIQUE)
J'arrête et redémarre ma base : les infos dans SYS.V$SQL_PLAN pour les plans d'exécution concernant le user HR sont perdues; normal puisque V$SQL_PLAN est une vue dynamique de performance (un synonyme en réalité), réinitialisée lors des arrêts/relances. Ces infos sont définitivement perdues, d'où l'intérêt de les avoir sauvegardées dans une table miroir.
SQL> startup
Base de données pluggable ouverte.
SQL> SELECT count(*) FROM SYS.V$SQL_PLAN WHERE SYS.V$SQL_PLAN.OBJECT_OWNER = 'HR' AND SYS.V$SQL_PLAN.OPERATION = 'INDEX';
COUNT(*)
----------
0
Et, bingo : les données sont toujours dans ma table miroir!
SQL> SELECT TO_CHAR(S.TIMESTAMP, 'DD/MM/YYYY HH:MI:SS') AS "DATE ACCESS", I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", S.OPTIONS, S.object_type AS "TYPE INDEX"
FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
WHERE S.OBJECT_NAME = I.INDEX_NAME AND
I.TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'HR')
ORDER BY OBJECT_NAME, "INDEX", "DATE ACCESS";
DATE ACCESS TABLE INDEX OPTIONS TYPE INDEX
-----------------------------------------------------------------------------------
20/05/2017 02:39:13 ZZ01 PK_ZZ01 UNIQUE SCAN INDEX (UNIQUE)
20/05/2017 12:58:14 ZZ01 PK_ZZ01 UNIQUE SCAN INDEX (UNIQUE)
20/05/2017 12:59:35 ZZ01 PK_ZZ01 RANGE SCAN INDEX (UNIQUE)
20/05/2017 02:40:41 ZZ03 PK_ZZ03 RANGE SCAN INDEX (UNIQUE)
20/05/2017 02:43:42 ZZ03 PK_ZZ03 FAST FULL SCAN INDEX (UNIQUE)
20/05/2017 02:43:43 ZZ03 PK_ZZ03 FULL SCAN INDEX (UNIQUE)
20/05/2017 02:46:28 ZZ03 PK_ZZ03 UNIQUE SCAN INDEX (UNIQUE)
============================================================================================
Les nouvelles stats de monitoring des index
============================================================================================
A partir de là, nous pouvons faire nos stats sur l'utilisation des index pour les deux tables de test ZZ01 et ZZ03.
Le résultat est OK mais il manque les stats sur les index non utilisés.
SQL> SELECT I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", COUNT(*) AS "NOMBRE D'ACCES"
FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
WHERE S.OBJECT_NAME = I.INDEX_NAME AND
I.TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'HR')
GROUP BY I.TABLE_NAME, S.object_name
ORDER BY "TABLE", "INDEX";
TABLE INDEX NOMBRE D'ACCES
----------------------------------------------------------------------------------
ZZ01 PK_ZZ01 10
ZZ03 PK_ZZ03 4
Nous ajoutons deux colonnes à nos tables de test, col_ind01 et col_ind03 de type NUMBER ainsi qu'une contrainte unique sur ces colonnes.
SQL> alter table HR.zz01 add constraint UNIQ01 UNIQUE (col_ind01);
SQL> alter table HR.zz03 add constraint UNIQ03 UNIQUE (col_ind03);
Oracle a bien créé deux index sur ces colonnes.
SQL> select INDEX_NAME, TABLE_NAME from dba_indexes where owner = 'HR' order by 2, 1;
INDEX_NAME TABLE_NAME
------------------------------ --------------------
PK_ZZ01 ZZ01
UNIQ01 ZZ01
PK_ZZ03 ZZ03
UNIQ03 ZZ03
Et maintenant, même les index non utilisés apparaissent! Avec ces données détaillées, on peut discuter avec les responsables d'applications et les développeurs de la suppression d'index.
COL TABLE format A10
COL INDEX format A10
SQL> SELECT I.TABLE_NAME AS "TABLE", S.object_name AS "INDEX", COUNT(*) AS "NOMBRE D'ACCES"
FROM HR.ZZSQL_PLAN S, DBA_INDEXES I
WHERE S.OBJECT_NAME = I.INDEX_NAME AND
I.TABLE_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'HR')
GROUP BY I.TABLE_NAME, S.object_name
UNION
SELECT D.TABLE_NAME, D.INDEX_NAME, 0
FROM DBA_INDEXES D
WHERE D.OWNER = 'HR' AND
D.INDEX_NAME NOT IN (SELECT S.OBJECT_NAME FROM HR.ZZSQL_PLAN S)
ORDER BY "TABLE", "NOMBRE D'ACCES" DESC, "INDEX";
TABLE INDEX NOMBRE D'ACCES
---------- ---------- -------------------------------------------------
ZZ01 PK_ZZ01 10
ZZ01 UNIQ01 0
ZZ03 PK_ZZ03 4
ZZ03 UNIQ03 0
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)