Historique des requêtes SQL

Version imprimable

07/02/2020, 10h44
NelsonD33

Historique des requêtes SQL
Bonjour,

Dans le cadre d'un stage, je dois réaliser du monitoring sur plusieurs bases de données. Une de leurs demandes étant d'avoir un historique intégral des requêtes qui sont exécutées (avec le temps d'exécution, les ressources consommées, ...) sur une base de données.
Les seules choses que j'ai pu trouver dans mes recherches qui se rapprochent le plus de leur besoin est :

Vue dynamique v$sql

Les données des requêtes :
- sont de type SELECT, INSERT, UPDATE, DELETE
- sont agrégées par SQL_ID : L'exécution d'une même requête
- sont monitorées sur le nombre d'EXECUTIONS et l'ELAPSED_TIME passés pour l'agrégation des exécutions. Il est possible de remonter la moyenne de temps d'exécution d'une requête
- sont tronquées sur 100 caractères dans SQL_TEXT
Les informations complémentaires sont :
- Le nom du schéma PARSING_USER_NAME et PARSING_USER_ID (lien vers DBA_USERS)
- Le nom du MODULE qui permet de différencier ce qui est utilisé depuis l'application de ce qui est exécuté depuis SQLDeveloper
- La dernière exécution de la requête LAST_LOAD_TIME
Conclusion
Cette vue dynamique ne me permet pas d'avoir un réel historique puisque :
- elle ne possède que des requête de type: SELECT, INSERT, UPDATE, DELETE
- elle ne garde pas en mémoire chaque instance d'une requête : elle n'indique que la dernière fois qu'une même requête à été chargée
- si une requête est utilisée plusieurs fois dans un délai très court sans qu'une autre requête soit faite en parallèle alors la vue v$sql ne la garde pas en mémoire (du moins ne change pas la date du dernier chargement).
Vue dynamique v$active_session_history

Les données des requêtes :
- sont de tout type (SELECT, INSERT, UPDATE, ...) et définies dans SQL_OPNAME
- sont agrégées par SAMPLE_ID
- sont identifiables par SQL_ID
- sont issues de v$session (si session active)
Les informations complémentaires sont :
- Le nom de l'utilisateur peut être récupérable avec USER_ID (lien avec dba_user)
- La date et l'heure à laquelle la session était active: SAMPLE_TIME
- Le nom du MODULE et de la MACHINE permettent de spécifier l'utilisateur
Conclusion
Cette vue dynamique ne me permet pas d'avoir un réel historique puisque :
- elle ne récupère les données que sur les sessions actives à un instant T. Conséquence: Toutes les 'petites' requêtes ne sont pas enregistrées
- pour chaque temps de rafraîchissement (1s), une ligne est écrite pour chaque session active (même si elle l'était déjà à la seconde précédente). Conséquence: On ne peut pas savoir si c'est la même instance d'une requête ou une nouvelle.
Les autres pistes (non concluantes) que j'ai pu chercher sont :

Rapport AWR: Uniquement des statistiques, pas d'historique réel + non automatisable pour du monitoring
CDC (Change Data Capture) : Soit sur la vue v$sql (impossible sur une vue au final). Soit sur toutes les tables (Beaucoup trop de tables pour être viable)
Aspirer les données sur le réseau / application: Pas d'information sur le temps d’exécution et autres...

Je commence a être à court d'idées. Etant plutôt novice, j'ai surement du passer à coté d'une spécificité ou d'un autre outil. J'espère que vous saurez m'éclairer.

Merci d'avance !
07/02/2020, 14h35
vanagreg

Bonjour,

Je vois que tu as déjà fait pas mal de recherches, c'est bien!
C'est très ambitieux comme projet car c'est un peu réinventer la poudre de logiciels qui font déjà ce travail.

Concernant la requête dans V$SQL, il faut utiliser la colonne SQL_FULLTEXT plutôt pour avoirt la requête complète (attention il y a un bug en 11.2.0.4 pour cette colonne).

Pour avoir toutes les données dont tu as besoin, tu vas devoir en effet interroger des vues du dictionnaire de données comme V$SQL, V$SESSION ou des vues de ASH (Active Session History).
ASH n'est utilisable que si l'option Diagnostics and Tuning Pack a été souscrite.

La difficulté ici est de pouvoir capturer toutes les requêtes en temps réel. Je suppose qu'il s'agira de requêtes "utilisateur" car des milliers de requêtes système sont exécutées en tâche de fond par la BDD.
Tu devras probablement activer l'AUDIT pour en exploiter le contenu. Pour avoir le détail des requêtes il te faudra l'activer en mode Extended.
Tu as la possibilité de mettre en place des triggers aussi, mais ça risque d'être fastidieux.

Il faut également garder à l'esprit que cette fonctionnalité peut avoir un impact non négligeable sur les performances et sur l'espace consommé.
07/02/2020, 19h33
Ikebukuro

C'est marrant car je bosse en ce moment même, à titre personnel, sur exactement la même chose.

Il n'y a, et j'avais posé ici la question, AUCUNE vue V$*** ou DBA*** pour répondre à ta question : https://www.developpez.net/forums/d2...cution-select/
Les vues Oracle intéressantes comme V$SQL, V$SQLSTATS, V$SQLAREA : ce sont des vues aggrégées donc pas intéressantes.

Une solution aurait été de créer un trigger BEFORE ou AFTER INSERT sur V$SQL et copier le nouvel enregistrement dans une table mais Oracle interdit la création d'un trigger sur un objet du schéma SYS.

Une solution serait peut-être de faire un job, qui tourne toutes les 1 seconde, 5 secondes ou 10 secondes selon tes besoins et qui copie le contenu de V$SQL dans une table ayant la même structure (créée avec un CREATE TABLE zz_test AS SELECT * FROM V$SQL). Après la première copie, tu ne prends plus que les enregistrements créés depuis le précédent INSERT : il faut identifier la colonne DATE ou TIMESTAMP pour identifier ces enregistrements.

Pas trop compliqué à mettre en oeuvre, surtout si tu as une bonne base de tests avec plusieurs sessions actives.

On aurait pu faire un AUDIT qui, a priori, va enregistrer tous les ordres SQL mais tu n'auras aucune info sur les ressources consommées car ce n'est pas le but de l'audit de stocker ces infos.
On le voit dans la doc Oracle de la vue DBA_AUDIT_TRAIL : https://docs.oracle.com/en/database/...D-938CC32D74CC
07/02/2020, 22h33
pachot

Citation:

Envoyé par NelsonD33

Dans le cadre d'un stage, je dois réaliser du monitoring sur plusieurs bases de données. Une de leurs demandes étant d'avoir un historique intégral des requêtes qui sont exécutées (avec le temps d'exécution, les ressources consommées, ...)

Première chose il y a quelque chose d'incohérent dans la demande. Monitoring veut dire qu'on veut collecter des métriques sans pénaliser les performances. Ca ne sera donc pas un historique intégral car le fait de mesurer et d'enregistrer sera souvent plus long que la requête elle-même. Pour du monitoring on ne collecte que les requêtes les plus consommatrices, à partir des vues que tu as mentionné, ou avec SQL Monitoring (nécessite Tuning Pack)

Pour une capture complète, alors il s'agit de tracer. Ca pénalise les performance, donc à n'activer que pour analyser un problème. Il s'agit de SQL Trace qui génère une trace des PARSE/EXEC/FETCH avec quelques métriques (elapsed, CPU, logical reads, physical reads) qui peut être formaté avec tkprof (profiling à partir des traces)

Je note que ton analyse est déjà pas mal. Quelques remarques:

Citation:

elle ne possède que des requête de type: SELECT, INSERT, UPDATE, DELETE

V$SQL a plus que ça, par exemple blocks pl/sql ou DDL. Mais V$SQL n'a pas un but de monitoring ou de tracing, mais de cache pour ne pas recompiler à chaque fois. On y trouve donc souvent les requêtes récentes et/ou exécutées souvent, ce qui est souvent suffisant pour du monitoring.

Citation:

On ne peut pas savoir si c'est la même instance d'une requête ou une nouvelle

Si, le SQL_EXEC_ID est incrémenté si c'en est une autre

Citation:

CDC (Change Data Capture)

Là il n'y aura pas les SELECT car le but des de capturer les modifications seulement.

Citation:

Aspirer les données sur le réseau

C'est plutôt SQL_TRACE dont tu as besoin mais pour la capture réseau il y a ceci qui est assez geek et cool:
https://blog.ora-600.pl/2020/01/22/w...ready-smartdb/ ceci
Intéressant so on veut compter le temps des aller-retour réseau. Mais à noter que ceci ne capture que les requêtes user. Si celles-ci exécutent des requête récursives elles ne seront pas vues (dans sql trace ils sont identifiées avec depth > 0)

N'hésites pas à poser des questions. Vu les recherches que tu as déjà fait, je suis sûr que les questions seront intéressantes :)
10/02/2020, 14h28
NelsonD33
Tout d'abord, merci à vous pour vos réponses.

J'ai bien compris, grâce à vous, que le besoin de mon entreprise n'était pas cohérent. Je vais donc le limiter aux requêtes les plus consommatrices/longues. Je garde l'historique complet uniquement dans un cas d'analyse de problème.
Je vais aussi regarder l'impact que cela a sur la base de données, ce qui va m'orienter sur mon choix d'outil (puisque le monitoring ne doit pas impacter les performances de la BDD).

Hypothèse de solution actuel :

Créer un exporter qui va permettre d'interroger la vue v$ash toutes les 10 secondes pour y stocker toutes les activités de sessions récentes. J'obtiendrai alors un pseudo-historique avec les requêtes les plus importantes sans pour autant impacter drastiquement la BDD (un simple SELECT sur une dizaine de lignes)
- Outil de collecte: Fluentd
- Stockage des activités: Elastic Stack
- Visualisation des données: Grafana (obligatoire pour l'homogénéité des solutions)
Je pense pouvoir arriver à quelque chose de concret et très proche du besoin initial en respectant la notion de performance liée au monitoring.

SI vous avez des avis / commentaires, je suis tout ouïe.
10/02/2020, 14h56
Ikebukuro

Citation:

Envoyé par NelsonD33

Hypothèse de solution actuel :

Créer un exporter qui va permettre d'interroger la vue v$ash toutes les 10 secondes pour y stocker toutes les activités de sessions récentes. J'obtiendrai alors un pseudo-historique avec les requêtes les plus importantes sans pour autant impacter drastiquement la BDD (un simple SELECT sur une dizaine de lignes)

SI vous avez des avis / commentaires, je suis tout ouïe.

ATTENTION : la vue V$active_session_history (dite V$ASH) nécessite le Diagnostic Pack.
Si vous avez celui-là, je pense que vous avez aussi le Tuning Pack car l'un sans l'autre... dans ce cas, tu peux remplacer v$active_session_history par v$sql_monitor qui fait déjà une sélection sur les requêtes les plus consommatrices.

Si tu n'as aucun pack, utilise V$sql ou v$sqlstats.
11/02/2020, 12h17
Pomalaix

L'exercice est bien sûr très intéressant, mais :

D'un point de vue visualisation, j'ai un peu du mal à imaginer comment vous pourriez lisiblement, avec Grafana, représenter les requêtes principales, qui se révéleront immanquablement fluctuantes et nombreuses.
(J'ai eu l'occasion de faire de la métrologie de bases Oracle, avec visualisation par Grafana. Je collectais tout un tas d'indicateurs, mais rien sur les requêtes. Pour moi ça aurait été bien trop insuffisant pour avoir la moindre utilité.
Pour les requêtes, j'utilisais un outil dédié complémentaire).

Un outil comme Ligthy (payant mais très raisonnable pour une entreprise) répond pleinement à votre objectif.
Il a tout ce qu'il faut pour collecter, historiser et visualiser les données de performances avec un grand luxe de détails comme les plans d'exécution, les curseurs fils, les événements d'attente, etc, c'est à dire les informations utiles pour diagnostiquer et identifier la cause d'un problème de performances.
Il y a également la possibilité de déclencher des notifications quand un certain seuil est atteint.
https://orachrome.com/
12/02/2020, 16h05
NelsonD33

Merci encore pour vos réponses.

Citation:

Envoyé par Ikebukuro

ATTENTION : la vue V$active_session_history (dite V$ASH) nécessite le Diagnostic Pack.
Si vous avez celui-là, je pense que vous avez aussi le Tuning Pack car l'un sans l'autre... dans ce cas, tu peux remplacer v$active_session_history par v$sql_monitor qui fait déjà une sélection sur les requêtes les plus consommatrices.

L'entreprise a bien le Diagnostic Pack, je vais regarder la vue sql_monitor, elle va surement compléter mon besoin.

Citation:

Envoyé par Pomalaix

Un outil comme Ligthy (payant mais très raisonnable pour une entreprise) répond pleinement à votre objectif.

Malheureusement, ma mission appartient à un projet beaucoup plus large regroupant d'autres outils à monitorer. Ils souhaitent donc garder le même outil (Grafana) pour l'intégralité du projet, par soucis d'homogénéité (au détriment d'une réponse au besoin approximative).