.
\nIf 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.
\n
\nHere is how I did my tests:<\/p>\n
Of course, I’ve scripted to run several tests varying the number of sessions, work unit, etc. while I was doing something more productive.<\/p>\n
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:<\/p>\n
SQL> desc DBA_HIST_DATAFILE;\n Name Null? Type\n ----------------------------------------- -------- ----------------------------\n DBID NOT NULL NUMBER\n FILE# NOT NULL NUMBER\n CREATION_CHANGE# NOT NULL NUMBER\n FILENAME NOT NULL VARCHAR2(513)\n TS# NOT NULL NUMBER\n TSNAME VARCHAR2(30)\n BLOCK_SIZE NUMBER\n CON_DBID NUMBER\n CON_ID NUMBER<\/code><\/pre>\nThere’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’.<\/p>\n
Then, I grepped for ‘WRH$_HISTORY’ in ORACLE_HOME\/rdbms\/admin and came upon this:<\/p>\n
dbmsawr.sql: -- This routine updates WRH$_DATAFILE rows for the datafile name and\ndbmsawr.sql: -- WRH$_DATAFILE with the current information in database.<\/code><\/pre>\nThere is an update_datafile_info procedure here in the dbms_workload_repository and the comment says something like:<\/p>\n
This change will be captured at max after some\n -- (generally 50) snapshots. So the AWR and AWR report may be wrong with\n -- respect to data file name or tablespace name for that duration.<\/code><\/pre>\nI love to work with Oracle. All information is there if you know where to look at.<\/p>\n
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.<\/p>\n
Here is the example:<\/p>\n
SQL> exec dbms_workload_repository.create_snapshot;\nPL\/SQL procedure successfully completed.\n \nSQL> select file#,filename from DBA_HIST_DATAFILE where file#=6;\n \n FILE# FILENAME\n---------- --------------------------------------\n 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n \nSQL> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 order by snap_id fetch first 10 rows only;\n \n SNAP_ID FILE# FILENAME\n---------- ---------- --------------------------------------\n 1244 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1245 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1246 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1247 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1248 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1249 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n6 rows selected.<\/code><\/pre>\nMy file is user01 and this is what is stored in AWR.<\/p>\n
I rename it to users02 (thanks to 12c<\/em> online move)<\/p>\nSQL> alter database move datafile '\/u01\/DEMO\/oradata\/DEMO14\/users01.dbf' to '\/u01\/DEMO\/oradata\/DEMO14\/users02.dbf';\nDatabase altered.<\/code><\/pre>\nbut AWR is not aware of the change even after a snapshot:<\/p>\n
SQL> exec dbms_workload_repository.create_snapshot;\nPL\/SQL procedure successfully completed.\n \nSQL> select file#,filename from DBA_HIST_DATAFILE where file#=6;\n \n FILE# FILENAME\n---------- --------------------------------------\n 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n \nSQL> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 order by snap_id fetch first 10 rows only;\n \n SNAP_ID FILE# FILENAME\n---------- ---------- --------------------------------------\n 1244 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1245 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1246 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1247 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1248 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1249 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n 1250 6 \/u01\/DEMO\/oradata\/DEMO14\/users01.dbf\n<\/code><\/pre>\nYou have to wait for those 50 snapshots or run the update:<\/p>\n
SQL> exec dbms_workload_repository.update_datafile_info;\nPL\/SQL procedure successfully completed.\n\nSQL> select file#,filename from DBA_HIST_DATAFILE where file#=6;\n \n FILE# FILENAME\n---------- --------------------------------------\n 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n \nSQL> select snap_id,file#,filename from DBA_HIST_FILESTATXS where file#=6 order by snap_id fetch first 10 rows only;\n \n SNAP_ID FILE# FILENAME\n---------- ---------- --------------------------------------\n 1244 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n 1245 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n 1246 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n 1247 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n 1248 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n 1249 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n 1250 6 \/u01\/DEMO\/oradata\/DEMO14\/users02.dbf\n<\/code><\/pre>\nBut as you see no history about previous names.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[422,96],"type_dbi":[],"class_list":["post-8765","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-awr","tag-oracle"],"acf":[],"yoast_head":"\n
Filenames in AWR reports - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Filenames in AWR reports\" \/>\n<meta property=\"og:description\" content=\"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.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-08-29T17:33:31+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Filenames in AWR reports\",\"datePublished\":\"2016-08-29T17:33:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/\"},\"wordCount\":432,\"commentCount\":0,\"keywords\":[\"AWR\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/\",\"name\":\"Filenames in AWR reports - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-08-29T17:33:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Filenames in AWR reports\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Filenames in AWR reports - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/","og_locale":"en_US","og_type":"article","og_title":"Filenames in AWR reports","og_description":"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.","og_url":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/","og_site_name":"dbi Blog","article_published_time":"2016-08-29T17:33:31+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Filenames in AWR reports","datePublished":"2016-08-29T17:33:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/"},"wordCount":432,"commentCount":0,"keywords":["AWR","Oracle"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/","url":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/","name":"Filenames in AWR reports - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-08-29T17:33:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/filenames-in-awr-reports\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Filenames in AWR reports"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8765","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8765"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8765\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8765"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8765"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8765"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8765"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}