By Franck Pachot

.
If you have read my latest blog posts, you know I’ve measured IOPS with SLOB to estimate ACFS overhead on a fast storage. This blog is about something I learned after wasting one hour on the result.

Here is how I did my tests:

  1. Create a SLOB database in ACFS
  2. Run SLOB PIO tests and tag the AWR report as ‘ACFS’
  3. Move datafile to +DATA
  4. Run SLOB PIO tests and tag the AWR report as ‘ASM’

Of course, I’ve scripted to run several tests varying the number of sessions, work unit, etc. while I was doing something more productive.

While done, I got a set of AWR report and the first task was to check that they were consistent. But they were not. The datafile in ‘File IO Stats’ section did not match the tag I’ve put in the file name. First I suspected a bug in my script with bad tagging or failed datafile move. I had to read the alert.log to get that my tagging was good but filename in AWR reports was wrong. I finally looked at AWR views to understand why the filename was wrong and understood the problem:

SQL> desc DBA_HIST_DATAFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DBID                                      NOT NULL NUMBER
 FILE#                                     NOT NULL NUMBER
 CREATION_CHANGE#                          NOT NULL NUMBER
 FILENAME                                  NOT NULL VARCHAR2(513)
 TS#                                       NOT NULL NUMBER
 TSNAME                                             VARCHAR2(30)
 BLOCK_SIZE                                         NUMBER
 CON_DBID                                           NUMBER
 CON_ID                                             NUMBER

There’s no SNAP_ID. AWR do not store the history of file names. We can suppose that it stores only the latest filename, but then my reports would be good as they were generated immediately after the snapshot. Or that the first name stays, but I had some reports with ‘+DATA’.

Then, I grepped for ‘WRH$_HISTORY’ in ORACLE_HOME/rdbms/admin and came upon this:

dbmsawr.sql:  --  This routine updates WRH$_DATAFILE rows for the datafile name and
dbmsawr.sql:  --  WRH$_DATAFILE with the current information in database.

There is an update_datafile_info procedure here in the dbms_workload_repository and the comment says something like:

This change will be captured at max after some
  --  (generally 50) snapshots. So the AWR and AWR report may be wrong with
  --  respect to data file name or tablespace name for that duration.

I love to work with Oracle. All information is there if you know where to look at.

So if you want to rely on filename in an AWR report after a move, you should run this procedure before taking the report. And you should run this report before the next datafile move.

Here is the example:

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
 
SQL> select file#,filename from DBA_HIST_DATAFILE where file#=6;
 
     FILE# FILENAME
---------- --------------------------------------
         6 /u01/DEMO/oradata/DEMO14/users01.dbf
 
SQL> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 order by snap_id fetch first 10 rows only;
 
   SNAP_ID      FILE# FILENAME
---------- ---------- --------------------------------------
      1244          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1245          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1246          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1247          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1248          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1249          6 /u01/DEMO/oradata/DEMO14/users01.dbf
6 rows selected.

My file is user01 and this is what is stored in AWR.

I rename it to users02 (thanks to 12c online move)

SQL> alter database move datafile '/u01/DEMO/oradata/DEMO14/users01.dbf' to '/u01/DEMO/oradata/DEMO14/users02.dbf';
Database altered.

but AWR is not aware of the change even after a snapshot:

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
 
SQL> select file#,filename from DBA_HIST_DATAFILE where file#=6;
 
     FILE# FILENAME
---------- --------------------------------------
         6 /u01/DEMO/oradata/DEMO14/users01.dbf
 
SQL> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 order by snap_id fetch first 10 rows only;
 
   SNAP_ID      FILE# FILENAME
---------- ---------- --------------------------------------
      1244          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1245          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1246          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1247          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1248          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1249          6 /u01/DEMO/oradata/DEMO14/users01.dbf
      1250          6 /u01/DEMO/oradata/DEMO14/users01.dbf

You have to wait for those 50 snapshots or run the update:

SQL> exec dbms_workload_repository.update_datafile_info;
PL/SQL procedure successfully completed.

SQL> select file#,filename from DBA_HIST_DATAFILE where file#=6;
 
     FILE# FILENAME
---------- --------------------------------------
         6 /u01/DEMO/oradata/DEMO14/users02.dbf
 
SQL> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 order by snap_id fetch first 10 rows only;
 
   SNAP_ID      FILE# FILENAME
---------- ---------- --------------------------------------
      1244          6 /u01/DEMO/oradata/DEMO14/users02.dbf
      1245          6 /u01/DEMO/oradata/DEMO14/users02.dbf
      1246          6 /u01/DEMO/oradata/DEMO14/users02.dbf
      1247          6 /u01/DEMO/oradata/DEMO14/users02.dbf
      1248          6 /u01/DEMO/oradata/DEMO14/users02.dbf
      1249          6 /u01/DEMO/oradata/DEMO14/users02.dbf
      1250          6 /u01/DEMO/oradata/DEMO14/users02.dbf

But as you see no history about previous names.

Note that if you look at the table behind the view, there’s a SNAP_ID but it’s not part of the primary key. It is used by the purge procedures.