Historique du nb de lignes d'une table : table AWR SYS.WRI$_OPTSTAT_TAB_HISTORY - History of the row number of a table
Introduction
Par défaut, il n'est pas possible sous Oracle de voir l'historique du nombre de lignes d'une table. Cela peut-être assez gênant si on veut expliquer pourquoi un SELECT prends dix fois plus de temps que le mois précédent; surtout si les développeurs et chefs de projet jurent, la main sur le coeur, que non, la taille de la table n'a pas changé et que donc le problème est forcément sur la base :-)
Pour leur prouver que cette table a grossi, fortement même, il faut utiliser des moyens détournés, par exemple requêter une des tables du référentiel AWR, non externalisée sous forme de vue : SYS.WRI$_OPTSTAT_TAB_HISTORY.
Points d'attention
N/A.
Base de tests
N'importe quelle base Oracle avec AWR
Exemples
============================================================================================
Le référentiel AWR
============================================================================================
Les vraies tables du référentiel AWR, une des composantes du dictionnaire de données, sont, pour une 12.2, au nombre de 364. Beaucoup ne sont malheureusement pas externalisées par Oracle dans des vues DBA_HIST...
Nombre d'objets dans le référentiel AWR : 1016.
SQL> select count(*) from dba_objects where object_name like 'WR%$%';
COUNT(*)
----------
1016
Si on regarde par type d'objets maintenant : 364 tables.
SQL> select object_type, count(*)
from dba_objects
where object_name like 'WR%$%'
group by object_type
order by 1;
OBJECT_TYPE COUNT(*)
----------------------- ----------
INDEX 321
INDEX PARTITION 79
SEQUENCE 23
SYNONYM 28
TABLE 364
TABLE PARTITION 91
TABLE SUBPARTITION 32
TYPE 41
TYPE BODY 38
9 rows selected.
On peut même les classer en cinq grandes familles. Attention, les tables WRP* et WRR* semblent récentes car je n'ai pas trouvé d'infos dessus avec Google.
- The wrm$ tables store metadata information for the Workload Repository
- The wrh$ tables store historical data or snapshots
- The wri$ tables: These tables store data related to advisory functions
SQL> select substr(object_name, 1, 3), count(*) from dba_objects where object_name like 'WR%$%' and object_type = 'TABLE' group by substr(object_name, 1, 3) order by 1;
SUBSTR(OBJEC COUNT(*)
------------ ----------
WRH 189
WRI 105
WRM 16
WRP 4
WRR 50
Un exemple du nom de ces tables.
SQL> select distinct object_name from dba_objects where object_name like 'WRH$%' and object_type = 'TABLE' and rownum < 11 order by 1;
OBJECT_NAME
--------------------------------------------------------------------------------
WRH$_COMP_IOSTAT
WRH$_DATAFILE
WRH$_FILESTATXS
WRH$_FILESTATXS_BL
WRH$_IOSTAT_FILETYPE
WRH$_IOSTAT_FILETYPE_NAME
WRH$_IOSTAT_FUNCTION
WRH$_IOSTAT_FUNCTION_NAME
WRH$_TEMPFILE
WRH$_TEMPSTATXS
10 rows selected.
Oracle ne dispose que de 151 vues pour interroger le référentiel AWR, ce qui signifie que beaucoup d'informations ne sont pas accessibles facilement car les vraies tables du dictionnaire ne sont pas documentées par Oracle.
SQL> select count(*) from dba_objects where object_name like 'DBA_HIST%' and object_type and object_type = 'VIEW';
COUNT(*)
----------
151
Exemples de ces vues.
SQL> select object_name from dba_objects where object_name like 'DBA_HIST%' and object_type = 'VIEW' and rownum < 11;
OBJECT_NAME
--------------------------------------------------------------------------------
DBA_HIST_SNAP_ERROR
DBA_HIST_REPORTS_TIMEBANDS
DBA_HIST_BASELINE_TEMPLATE
DBA_HIST_REPORTS
DBA_HIST_REPORTS_DETAILS
DBA_HIST_REPORTS_CONTROL
DBA_HIST_DATABASE_INSTANCE
DBA_HIST_SNAPSHOT
DBA_HIST_COLORED_SQL
DBA_HIST_BASELINE_METADATA
10 rows selected.
La configuration d'AWR est la suivante, classique : 31 jours de rétention, un snapshot toutes les heures.
SQL> select * from DBA_HIST_WR_CONTROL;
DBID SNAP_INTERVAL RETENTION TOPNSQL CON_ID
---------- -------------------- ------------------------------ ----------
768045447 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT 0
============================================================================================
La table SYS.WRI$_OPTSTAT_TAB_HISTORY
============================================================================================
La vue qui nous intéresse est SYS.WRI$_OPTSTAT_TAB_HISTORY, accessible par tout user ayant accès au dictionnaire de données.
SQL> desc SYS.WRI$_OPTSTAT_TAB_HISTORY
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
SAVTIME TIMESTAMP(6) WITH TIME ZONE
FLAGS NUMBER
ROWCNT NUMBER
BLKCNT NUMBER
AVGRLN NUMBER
SAMPLESIZE NUMBER
ANALYZETIME DATE
CACHEDBLK NUMBER
CACHEHIT NUMBER
LOGICALREAD NUMBER
SPARE1 NUMBER
IM_IMCU_COUNT NUMBER
IM_BLOCK_COUNT NUMBER
SCANRATE NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 TIMESTAMP(6) WITH TIME ZONE
Pour une 12.2, voici sa définition prise dans $ORACLE_HOME/rdbms/admin/doptim.bsq, ce qui nous permet d'avoir le rôle des colonnes.
Une colonne sympa est rowcnt : number of rows :-)
Rem Table to store optimizer statistics history
Rem for table and table partition objects
create table wri$_optstat_tab_history
( obj# number not null, /* object number */
savtime timestamp with time zone, /* timestamp when stats saved */
flags number,
rowcnt number, /* number of rows */
blkcnt number, /* number of blocks */
avgrln number, /* average row length */
samplesize number, /* number of rows sampled by Analyze */
analyzetime date, /* timestamp when last analyzed */
cachedblk number, /* blocks in buffer cache */
cachehit number, /* cache hit ratio */
logicalread number, /* number of logical reads */
spare1 number, /* number of chained rows */
im_imcu_count number, /* number of IMCUs for inmemory object */
im_block_count number, /* number of IM blocks for inmemory object */
scanrate number, /* scan rate in MB/S */
spare2 number,
spare3 number,
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 timestamp with time zone
) tablespace sysaux
pctfree 1
enable row movement
============================================================================================
Comment la table SYS.WRI$_OPTSTAT_TAB_HISTORY est-elle renseignée?
============================================================================================
Nous allons voir que la mise à jour de cette table n'est pas simple, et qu'elle est liée au calcul des stats plus que à la génération des snapshots AWR.
Table de tests
On crée une table de tests et on insère 1000 lignes.
SQL> create table zztest(id number primary key, date01 date);
SQL> insert into zztest select level, systimestamp from dual connect by level < 1001;
1000 rows created.
SQL> commit;
Par défaut, la table n'a aucune info sur ma table de test!
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
Générer les stats pour remplir la table
On génère les stats : bingo!
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
PL/SQL procedure successfully completed.
SQL> select num_rows from dba_tab_statistics where table_name = 'ZZTEST';
NUM_ROWS
----------
1000
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
Cette table semble être mise à jour dès que les stats sont générées MAIS, après plusieurs tests, non, c'est plus complexe que ça; ce serait trop simple...
Je recrée 1000 lignes et zut, la vue a encore l'ancien nombre de lignes. Tiens, pourtant on a bien une nouvelle ligne, correspondant à l'exécution de dbms_stats.gather_table_stats mais la valeur n'est pas 2000... et cependant on a généré les stats comme précédemment...
SQL> insert into zztest select level+2000, systimestamp from dual connect by level < 1001;
1000 rows created.
SQL> commit;
SQL> select count(*) from zztest;
COUNT(*)
----------
2000
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
PL/SQL procedure successfully completed.
SQL> select num_rows from dba_tab_statistics where table_name = 'ZZTEST';
NUM_ROWS
----------
2000
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
20-APR-19 08.15.35.768188 AM -04:00 1000
Est-ce qu'il faut que le snapshot AWR soit généré? On va créer manuellement un snapshot AWR.
SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
Et non, KO...
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
20-APR-19 08.15.35.768188 AM -04:00 1000
Allez, on continue :-)
SQL> insert into zztest select level+3000, systimestamp from dual connect by level < 2001;
2000 rows created.
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
Tiens tiens, j'ai l'impression qu'Oracle stocke le nombre de lignes concernées par une opération MAIS pas le total : 2 INSERT de 1000 et 1 de 2000 rows. En réalité, comme on le verra plus loin, la table est mise à jour de façon décalée; la dernière ligne est le nb de lignes dans la table après les deux INSERTs de 1000 lignes.
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
20-APR-19 08.15.35.768188 AM -04:00 1000
20-APR-19 12.33.19.012796 PM -04:00 2000
Et si on DELETE? Et si on UPDATE?
SQL> delete from zztest where rownum < 501;
500 rows deleted.
SQL> commit;
Alors là, j'y comprends plus rien... Je supprime 500 enregistrements et Oracle me dit qu'il y a 4000 lignes dans la table au lieu de 3500... J'ai l'impression qu'il y a un décalage entre la modification des données et la mise à jour de sys.WRI$_OPTSTAT_TAB_HISTORY ou bien qu'il faille générer deux fois les stats pour avoir les bonnes données.
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
20-APR-19 08.15.35.768188 AM -04:00 1000
20-APR-19 12.33.19.012796 PM -04:00 2000
20-APR-19 12.37.07.359142 PM -04:00 4000
SQL> select count(*) from zztest;
COUNT(*)
----------
3500
Bon, juste un pb de temps, maintenant c'est OK mais j'ai du mal à comprendre la logique de mise à jour de cette table... On a bien dans ROWCNT le nombre de lignes dans la table et pas le nombre de lignes concernées par l'opération : par exemple on ne voit pas une ligne de 500 DELETEs. Cette table est donc bien un historique des lignes par table, et elle est mise à jour quand il y a une opération DML suivie d'un calcul des stats APRES que quelques minutes se soient écoulées.
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
20-APR-19 08.15.35.768188 AM -04:00 1000
20-APR-19 12.33.19.012796 PM -04:00 2000
20-APR-19 12.37.07.359142 PM -04:00 4000
20-APR-19 12.40.00.087303 PM -04:00 3500
6 rows selected.
Maintenant j'UPDATE.
SQL> update zztest set id = (select max(id) from zztest) + rownum where rownum < 11;
10 rows updated.
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
Bon ben cela ne change rien, normal puisque le nombre de lignes dans la table reste le même :-)
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
---------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
20-APR-19 08.15.35.768188 AM -04:00 1000
20-APR-19 12.33.19.012796 PM -04:00 2000
20-APR-19 12.37.07.359142 PM -04:00 4000
20-APR-19 12.40.00.087303 PM -04:00 3500
20-APR-19 12.48.52.092688 PM -04:00 3500
7 rows selected.
Un dernier test, pour montrer que cette table est mise à jours lors du calcul des stats MAIS, attention, on a vu que parfois la colonne ROWCNT était mise à jour en décalé...
SQL> exec dbms_stats.gather_table_stats('HR', 'ZZTEST');
SQL> select SAVTIME, ROWCNT
from sys.WRI$_OPTSTAT_TAB_HISTORY WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2;
SAVTIME ROWCNT
--------------------------------------------------------------------------- ----------
20-APR-19 07.50.59.258718 AM -04:00
20-APR-19 08.09.20.514543 AM -04:00 1000
20-APR-19 08.15.35.768188 AM -04:00 1000
20-APR-19 12.33.19.012796 PM -04:00 2000
20-APR-19 12.37.07.359142 PM -04:00 4000
20-APR-19 12.40.00.087303 PM -04:00 3500
20-APR-19 12.48.52.092688 PM -04:00 3500
22-APR-19 06.27.56.352836 AM -04:00 3500
8 rows selected.
Malheureusement les autres données de la table ne sont pas très utiles pour identifier les différentes opérations : INSERT, DELETE...
SQL> select
flags,
spare2,
spare3,
spare4,
spare5,
spare6
from SYS.wri$_optstat_tab_history WRI, DBA_OBJECTS OBJ
WHERE WRI.OBJ# = OBJ.OBJECT_ID AND
OBJ.OWNER = 'HR' AND
OBJECT_NAME = 'ZZTEST'
ORDER BY 1, 2
FLAGS SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ----------
0
10
10
10
10
10
10
10
8 rows selected.
Voilà, article fini; cette table est bien pratique MAIS difficile à apprivoiser :-)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)