Canalblog
Editer l'article Suivre ce blog Administration + Créer mon blog
Publicité
Blog d'un DBA sur le SGBD Oracle et SQL
6 février 2020

Existe-t-il des vues détail pour chaque ordre SQL? - Are there detailed views for each SQL order?


Introduction

Est-ce qu'il existe sous Oracle une ou des vues qui ont une ligne par exécution d'un SELECT? En clair a-t-on l'historique précis des exécutions des ordres SQL?

A quoi cela sert-il? A voir les dégradations de temps de réponse sur une période précise, à faire des moyennes sur plusieurs indicateurs etc etc plutôt que d'avoir des infos agrégées.

Le problème est que ces infos sont stockées dans les vues dynamiques de performances V$*** (des synonymes en vrai) et que leur temps de rétention est assez court (surtout sur des bases très actives). Pour les vrais vues d'historisation, celles du référentiel AWR en DBA_HIST***, seuls les ordres les plus consommateurs en ressource sont retenus, ce qui fait que certaines exécutions d'un SELECT ne seront pas gardées.

Voyons donc si ces fameuses vues V$*** stockent des infos détaillées et non pas agrégées.
 



Points d'attention

N/A.
 


 
Base de tests
Une base Oracle 18.


 
Exemples
============================================================================================
L'environnement de recette

============================================================================================
On crée une table de test très basique.
     SQL> CREATE TABLE zztest AS SELECT * FROM dba_objects WHERE rownum < 20001;
     Table created.

     SQL> ALTER TABLE zztest ADD id number;
     Table altered.

     SQL> UPDATE zztest SET id = rownum;
     20000 rows updated.

     SQL> commit;
     Commit complete.

     SQL> ALTER TABLE zztest ADD CONSTRAINT pk_zztest PRIMARY KEY (id);
     Table altered.

Génération des stats.
     SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'HR' , tabname => 'ZZTEST', cascade => true);
     PL/SQL procedure successfully completed.


============================================================================================
Quelles sont les vues sur les ordres SQL exécutés?
============================================================================================
Pour mes tests de perfs de SELECTs, je voudrais utiliser les colonnes suivantes de V$SQL : LAST_ACTIVE_TIME, EXECUTIONS, ROWS_PROCESSED, CHILD_NUMBER, CPU_TIME, ELAPSED_TIME et BUFFER_GETS.


Lançons un ordre basique et cherchons son SQL_ID; pour faciliter cela, je mets un commentaire dans mon SELECT.

     SQL> select * /* TEST INDICATEUR */ from zztest;
     ...
     20000 rows selected.

     SQL> select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%';
     SQL_ID SQL_TEXT
     ----------------------------------------------------------------------------
     fajyg3snp5hza select sql_id from v$sql where sql_text like '%INDICATEUR%'
     5c7a8nbrmjzcz select sql_id, sql_text from v$sql where sql_text like '%INDICATEUR%'
     g29m41tavz3rn select * /* TEST INDICATEUR */ from zztest

V$SQL : vue agrégées
Le SQL_ID intéressant est g29m41tavz3rn.

     SQL> select to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CHILD_NUMBER, CPU_TIME, ELAPSED_TIME,      BUFFER_GETS from V$SQL where SQL_ID = 'g29m41tavz3rn';
     TO_CHAR(LAST_ACTIVE EXECUTIONS ROWS_PROCESSED CHILD_NUMBER CPU_TIME ELAPSED_TIME BUFFER_GETS
     ------------------- ---------- -------------- ------------ ---------- ------------ -----------
     28/01/2020 11:22:02    1       20000          0            18416     98713         2077

Si on réexécute le même ordre : aïe, V$SQL est une vue agrégée et pas une vue détail… le champs EXECUTIONS est passé de 1 à 2. Ce qui implique qu'il y a un cumul des données relatives à cet ordre. Et en plus, si le nombre de ROWS_PROCESSED est bien multiplié par deux, le nombre BUFFER_GETS lui ne l'est pas ==> impossible d'utiliser cette vue en l'état.
     SQL> select * /* TEST INDICATEUR */ from zztest;
     SQL> select to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CHILD_NUMBER, CPU_TIME, ELAPSED_TIME, BUFFER_GETS from V$SQL where SQL_ID = 'g29m41tavz3rn';
     TO_CHAR(LAST_ACTIVE EXECUTIONS ROWS_PROCESSED CHILD_NUMBER CPU_TIME ELAPSED_TIME BUFFER_GETS
     ------------------- ---------- -------------- ------------ ---------- ------------ -----------
     28/01/2020 11:23:23    2       40000          0            23781      114871       3749

V$SQLSTATS : vue agrégée
Idem pour V$SQLSTATS.

     SQL> select to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS from V$SQLSTATS where SQL_ID = 'g29m41tavz3rn';
     TO_CHAR(LAST_ACTIVE EXECUTIONS ROWS_PROCESSED CPU_TIME ELAPSED_TIME BUFFER_GETS
     ------------------- ---------- -------------- ---------- ------------ -----------
     28/01/2020 11:23:23    2       40000          23781      114871       3749

V$SQLAREA : vue agrégée
Idem pour V$SQLAREA.

     SQL> select to_char(LAST_ACTIVE_TIME, 'DD/MM/YYYY HH24:MI:SS'), EXECUTIONS, ROWS_PROCESSED, CPU_TIME, ELAPSED_TIME, BUFFER_GETS from V$SQLAREA where SQL_ID = 'g29m41tavz3rn';
     TO_CHAR(LAST_ACTIVE EXECUTIONS ROWS_PROCESSED CPU_TIME ELAPSED_TIME BUFFER_GETS
     ------------------- ---------- -------------- ---------- ------------ -----------
     28/01/2020 11:23:23    2       40000          23781      114871       3749

V$SQL_PLAN_STATISTICS : rien
Dans V$SQL_PLAN_STATISTICS : rien!

     SQL> select count(*) from V$SQL_PLAN_STATISTICS where SQL_ID = 'g29m41tavz3rn';
     COUNT(*)
     ----------
     0

V$SQL_PLAN_STATISTICS_ALL : pas intéressant
Dans V$SQL_PLAN_STATISTICS_ALL, deux lignes :-)

     SQL> select count(*) from V$SQL_PLAN_STATISTICS_ALL where SQL_ID = 'g29m41tavz3rn';
     COUNT(*)
     ----------
     2

Mais le résultat est décevant... : on a les infos des différentes opérations du plan d'exécution et non pas des infos sur chaque exécution de la requête.
     SQL> SELECT TIMESTAMP, OPERATION, BYTES, LAST_OUTPUT_ROWS, OUTPUT_ROWS, LAST_CR_BUFFER_GETS, LAST_DISK_READS, LAST_ELAPSED_TIME, TOTAL_EXECUTIONS FROM V$SQL_PLAN_STATISTICS_ALL where SQL_ID = 'g29m41tavz3rn';
     TIMESTAMP OPERATION BYTES LAST_OUTPUT_ROWS OUTPUT_ROWS LAST_CR_BUFFER_GETS LAST_DISK_READS LAST_ELAPSED_TIME TOTAL_EXECUTIONS
     ----------------- ---------- ---------------- ----------- ------------------- --------------- ----------------- -------------
     28-JAN-20 SELECT STATEMENT
     28-JAN-20 TABLE ACCESS 2500000

V$SQL_MONITOR : rien mais ce serait bien une vue détail
Et si on monitorait la requête? Attention, les commentaires sont pris en compte dans le calcul du SQL_ID par hashage. Les hints étant un commentaire, il faut récupèrer le nouveau SQL_ID qui 
est 87nkvqjfs59n9.
     SQL> select * /*+ MONITOR */ from zztest;

     SQL> select sql_id, sql_text from v$sql where sql_text like '%MONITOR%';
     SQL_ID             SQL_TEXT
     ------------- --------------------------------------------------
     87nkvqjfs59n9      select * /*+ MONITOR */ from zztest
     9vjpg0sg0q5m8      SELECT SQL_EXEC_START, ELAPSED_TIME, CPU_TIME, FET
     CHES, BUFFER_GETS, DISK_READS, PHYSICAL_READ_REQUE
     STS, PHYSICAL_READ_BYTES from V$SQL_MONITOR where
     SQL_ID = 'g29m41tavz3rn'
     5908h4p4qm00g      select sql_id, sql_text from v$sql where sql_text
     like '%MONITOR%'

Rien dans V$SQL_MONITOR : bizarre, je me serais trompé sur l'écriture du hint? Mais de toute façon dans V$SQL_MONITOR le champ ROWS_PROCESSED n'existe pas ni même un champ NUMROWS ou NUM_ROWS… donc laissons tomber cette vue.
     SQL> SELECT SQL_EXEC_START, ELAPSED_TIME, CPU_TIME, FETCHES, BUFFER_GETS, DISK_READS, PHYSICAL_READ_REQUESTS, PHYSICAL_READ_BYTES from V$SQL_MONITOR where SQL_ID = '87nkvqjfs59n9';
     no rows selected

[EDIT 05/08/2020]
Hé bien, en lisant le livre "Oracle 11g New Features", j'apprends que cette vue est une vue détaillée ainsi que V$SQL_PLAN_MONITOR : si un ordre SQL est exécuté deux fois, il y aura deux lignes dans V$SQL_MONITOR
.


============================================================================================
Pas de vue détail des ordres SQL : quelle solution quand même avec V$SQL?

============================================================================================
OK, résultat des courses : impossible de trouver une vue avec des infos détaillées pour chaque exécution d'un ordre SQL. 

La seule solution à ma connaissance est la suivante :
1) créer une table ZZ_SQL avec la même structure que V$SQL
2) avec DBMS_SHARED_POOL.PURGE, vider V$SQL pour le SQL_ID de notre SELECT (voir cet article : "Comment vider des données en Shared Pool ?")
3) exécuter le SELECT
4) récupérer dans V$SQL les infos pour le SQL_ID (il n'y en a que un enregistrement suite à la purge) et les insérer dans ZZ_SQL
5) exécuter les étapes 2 à 4 autant de fois que besoin


============================================================================================
La solution Autotrace

============================================================================================

Une autre solution est d'utiliser la commande Autotrace, qui ne donne que des infos pour la dernière exécution.
SQL> set autotrace on

Autotrace sous SQL*Plus
Hé hé, que vois-je comme infos? "rows selected", "consistents gets" (nb de blocs lus en mémoire), "physical reads" (nb de blocs lus sur disque dur) :-)

Exactement ce que je voulais pour faire mes tests de tuning.  
     SQL> select * from zztest;
     20000 rows selected.

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 3582063246

     ----------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     ----------------------------------------------------------------------------
     | 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
     | 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
     ----------------------------------------------------------------------------

     Statistics
     ----------------------------------------------------------
     120 recursive calls
     4 db block gets
     1908 consistent gets
     0 physical reads
     0 redo size
     2958855 bytes sent via SQL*Net to client
     15270 bytes received via SQL*Net from client
     1335 SQL*Net roundtrips to/from client
     8 sorts (memory)
     0 sorts (disk)
     20000 rows processed

Si je relance? Hé bien je n'ai pas de cumul puisque la sortie d'Autotrace est dédiée à une exécution et une seule :-) On remarquera que cette fois j'ai toujours 20 000 lignes MAIS 1668 consistents gets au lieu de 1908 : on a bien le résultat pour cette exécution du SELECT.
     SQL> select * from zztest;
     20000 rows selected.

     Execution Plan
     ----------------------------------------------------------
     Plan hash value: 3582063246

     ----------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     ----------------------------------------------------------------------------
     | 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
     | 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
     ----------------------------------------------------------------------------

     Statistics
     ----------------------------------------------------------
     0 recursive calls
     4 db block gets
     1668 consistent gets
     0 physical reads
     0 redo size
     2958855 bytes sent via SQL*Net to client
     15270 bytes received via SQL*Net from client
     1335 SQL*Net roundtrips to/from client
     0 sorts (memory)
     0 sorts (disk)
     20000 rows processed

C'est donc Autotrace qu'il faut utiliser si je veux avoir des stats détaillées sur chaque exécution d'un SELECT! Le problème est que je ne peux pas faire de SELECT sur la sortie d'Autotrace... je peux faire un tableau excel en y copiant les indicateurs ou alors faire un INSERT dans une table créée de toute pièce. A vous de voir :-)

Autotrace sous SQLcl
La sortie Autotrace sous SQLcl est plus riche mais aussi beaucoup beaucoup plus complexe que sous SQL*Plus. Les champs qui m'intéressent sont "consistent gets", "logical read bytes from cache", "physical read bytes", "physical reads".

     SQL> select * from zztest;
     20,000 rows selected.

     Explain Plan
     -----------------------------------------------------------

     PLAN_TABLE_OUTPUT
     ------------------------------------------------------------
     Plan hash value: 3582063246

     ----------------------------------------------------------------------------
     | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
     ----------------------------------------------------------------------------
     | 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
     | 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
     ----------------------------------------------------------------------------

     Statistics
     -----------------------------------------------------------
     1 CCursor + sql area evicted
     4 CPU used by this session
     5 CPU used when call started
     24 DB time
     1523 Requests to/from client
     1523 SQL*Net roundtrips to/from client
     2073 buffer is not pinned count
     9 buffer is pinned count
     18681 bytes received via SQL*Net from client
     3064549 bytes sent via SQL*Net to client
     226 calls to get snapshot scn: kcmgss
     184 calls to kcmgcs
     114688 cell physical IO interconnect bytes
     48 cluster key scan block gets
     44 cluster key scans
     2433 consistent gets
     236 consistent gets examination
     235 consistent gets examination (fastpath)
     2433 consistent gets from cache
     2197 consistent gets pin
     2184 consistent gets pin (fastpath)
     1 cursor authentications
     1 cursor reload failures
     4 db block gets
     4 db block gets from cache
     4 db block gets from cache (fastpath)
     10 enqueue releases
     10 enqueue requests
     224 execute count
     33291 file io wait time
     14 free buffer requested
     67 index fetch by key
     71 index scans kdiixs1
     19963904 logical read bytes from cache
     2010 no work - consistent read gets
     1545 non-idle wait count
     4 non-idle wait time
     228 opened cursors cumulative
     1 opened cursors current
     10 parse count (hard)
     38 parse count (total)
     4 parse time cpu
     8 parse time elapsed
     14 physical read IO requests
     114688 physical read bytes
     14 physical read total IO requests
     114688 physical read total bytes
     14 physical reads
     14 physical reads cache
     119 process last non-idle time
     635 recursive calls
     3 recursive cpu usage
     15 rows fetched via callback
     2 session cursor cache count
     219 session cursor cache hits
     2437 session logical reads
     2 shared hash latch upgrades - no wait
     55 sorts (memory)
     1972 sorts (rows)
     10 sql area evicted
     66 table fetch by rowid
     1839 table scan blocks gotten
     8485 table scan disk non-IMC rows gotten
     28485 table scan rows gotten
     87 table scans (short tables)
     3 user I/O wait time
     1526 user calls


============================================================================================
Conclusion

============================================================================================
Nous venons de voir que sous Oracle il n'existe pas de vue ou table stockant les infos pour CHAQUE exécution d'un ordre SQL. D'un côté c'est vraiment gênant pour tuner, d'un autre, je ne suis pas surpris car stocker ces informations sous forme détaillée consommerait énormément d'espace disque et pourrait ralentir la base.

Seules solutions : soit on duplique manuellement le contenu de V$SQL soit on utilise Autotrace. Mais, à chaque fois, il faudra créer une table de tuning et y insérer les données que Oracle efface régulièrement (cas des V$***) ou même ne stocke pas (cas du Autotrace).

[EDIT 05/08/2020]
Dans le livre "Oracle 11g New Features", la vue $SQL_MONITOR et V$SQL_PLAN_MONITOR sont des vues détaillées : si un ordre SQL est exécuté deux fois, il y aura deux lignes dans ces deux vues.


Publicité
Publicité
Commentaires
Blog d'un DBA sur le SGBD Oracle et SQL
Publicité
Archives
Blog d'un DBA sur le SGBD Oracle et SQL
  • Blog d'un administrateur de bases de données Oracle sur le SGBD Oracle et sur les langages SQL et PL/SQL. Mon objectif est de vous faire découvrir des subtilités de ce logiciel, des astuces, voir même des surprises :-)
  • Accueil du blog
  • Créer un blog avec CanalBlog
Visiteurs
Depuis la création 339 403
Publicité