Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration Oracle Discussion :

Historique des requêtes SQL


Sujet :

Administration Oracle

  1. #1
    Nouveau Candidat au Club
    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 !

  2. #2
    Membre éclairé
    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é.

  3. #3
    Membre expérimenté
    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/d2042568/bases-donnees/oracle/administration/vue-detaillee-v-sql-stats-execution-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/oracle/oracle-database/19/refrn/DBA_AUDIT_TRAIL.html#GUID-A9993FAC-12D3-4725-A37D-938CC32D74CC
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  4. #4
    Expert éminent
    Citation Envoyé par NelsonD33 Voir le message

    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:


    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.

    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

    CDC (Change Data Capture)
    Là il n'y aura pas les SELECT car le but des de capturer les modifications seulement.

    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
    Franck Pachot - dbi services - Consulting et Formation en Suisse et remote - fpa@dbi-services.com
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  5. #5
    Nouveau Candidat au Club
    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.

  6. #6
    Membre expérimenté
    Citation Envoyé par NelsonD33 Voir le message

    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.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  7. #7
    Rédacteur

    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/
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

    Ma dernière formation Oracle 19c publiée sur Linkedin : https://fr.linkedin.com/learning/oracle-19c-l-administration

  8. #8
    Nouveau Candidat au Club
    Merci encore pour vos réponses.

    Citation Envoyé par Ikebukuro Voir le message
    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).

###raw>template_hook.ano_emploi###