This was first published on https://blog.dbi-services.com/filenames-in-awr-reports (2016-08-29)
Republishing here for new followers. The content is related to the the versions available at the publication date
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:
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.