---/data/sfaedb/data02.dbf 2225795 1453298 2291527 2087071 5371446 521421 /data/sfaedb/data03.dbf 2225697 1368342 2291338 2135174 9070509 552163 /data/sfaedb/data04.dbf 2156475 1327998 2221101 2054575 9036635 584703 /data/sfaedb/data05.dbf 2127539 1343213 2187749 2060534 8854222 626665 /data/sfaedb/data06.dbf 2076911 1306907 2135606 1972178 8600763 659874 /data/sfaedb/data07.dbf 2025810 1265414 2084207 1910216 8434117 667847 /data/sfaedb/data08.dbf 1937825 1171931 1996027 1779837 7991536 673212 /data/sfaedb/data09.dbf 1875733 1154502 1934094 1786911 7777172 692082 /data/sfaedb/data10.dbf 1819224 1114452 1877443 1721618 7521514 690706 /data/sfaedb/data01.dbf 1750829 1086044 1805138 1691258 7267005 357762
Generating summary reports of historical activity by using Cached ODM Advisor in SFHA environment
The Cached ODM Advisor can generate reports that summarize historical read activity on the datafiles. These reports use data from the AWR snapshots periodically taken by the database. You can use several options to fine-tune the report.
Reports generated using either of the following options have the following columns:
■ Tablespace name
■ File name
■ Average physical reads per snapshot
■ Average physical blocks read per snapshot
■ Read time (in seconds)
The reports can be restricted to a specific tablespace by using the-toption. Datafiles which have Cached ODM enabled are not displayed in the report. If you would like to include those datafiles in the report as well, use the -aoption. By default, the top 10 datafiles are displayed in the report. This default can be changed by using the-noption. The-poption generates a programmatically parseable report by disabling of the table formatting performed by default.
Using the Cached ODM Advisor blacklist to ignore files in SFHA environment
If tuning and measurement indicates that enabling Cached ODM on certain datafiles does not improve performance, you can add the files to a blacklist. Files added to the blacklist are not included in any of the reports generated by using the-o display option ofdbed_codm_adm.
103 Improving database performance with Veritas Cached Oracle Disk Manager Administering Cached ODM settings with Cached ODM Advisor in SFHA environment
The blacklist file is stored in the file system atlost+found/odmadm-blacklist. You can also provide an alternate blacklist location using the-boption.
You can use the-o add-to-blacklistand-o remove-from-blacklistoptions to manage the default blacklist. These options can be used with either a single file name or with a list of files using the-f filelistoption. The-o print-blacklist option displays the list of blacklisted files. The blacklist is consulted only when generating a report. It has no effect on enabling or disabling Cached ODM on datafiles.
Average metric values during a time interval by using Cached ODM Advisor in SFHA environment
You can use the-d start_date end_dateand-m start_hour end_houroptions to restrict the report to activities that were recorded during the specified time interval.
Thestart_dateandend_dateparameters are inclusive. Therefore, activities occuring on these dates are also included in the generated report. Thestart_hour andend_hourparameters specify the time interval bounding the AWR snapshots on each day. This report is useful when you start using the Cached ODM Advisor utility for the first time and need a list of datafiles that see the most read activity during workload hours.
The following is an example of generating a report of read activity on the DATA tablespace between 9 am and 5 pm, from 2011/07/09 to 2011/07/11.
$ dbed_codm_adm -S sfaedb -H /u01/app/11gr2 -o display \ -d 2011/07/09 2011/07/11 -m 9 17 -t DATA
Tablespace Filename Average Average Read
Physical Physical Time (s) Reads per Blocks
Snapshot Read per Snapshot
DATA /data/sfaedb/data03.dbf 13848 13848 497.16
DATA /data/sfaedb/data04.dbf 13103 13104 472.68
DATA /data/sfaedb/data05.dbf 12656 12656 451.79
DATA /data/sfaedb/data06.dbf 12154 12154 430.81
DATA /data/sfaedb/data07.dbf 11937 11937 423.87
DATA /data/sfaedb/data08.dbf 11853 11853 417.74
DATA /data/sfaedb/data09.dbf 11720 11720 415.45
DATA /data/sfaedb/data10.dbf 11298 11299 400.59
DATA /data/sfaedb/data01.dbf 10610 10611 390.25
Average per-block read time: 33.539 ms
104 Improving database performance with Veritas Cached Oracle Disk Manager Administering Cached ODM settings with Cached ODM Advisor in SFHA environment
Average metric values during an interval specified as a range of AWR snapshots by using Cached ODM Advisor in SFHA environment You can use the-u snapshot_specificationoption of thedbed codm_adm command to restrict the report to activities that were recorded in a specific range of AWR snapshots. Thesnapshot_specificationcan be a single integer, in which case the report considers that many snapshots starting from the latest, in a reverse order. This allows you to generate a report on the recent read activity. Alternatively, thesnapshot_specificationcan be used to specify a range of AWR snapshot ids, in the formatstarting_snapshot_id-ending_snapshot_id. This makes the report consider the snapshots in the specified range of snapshot ids.
The following is an example of generating a report of read activity during the interval specified by the last 10 AWR snapshots.
$ dbed_codm_adm -S sfaedb -H /u01/app/11gr2 -o display -u 10 -t DATA
Tablespace Filename Average Average Read
Physical Physical Time (s) Reads per Blocks
Snapshot Read per Snapshot
DATA /data/sfaedb/data06.dbf 31188 31188 1547.12
DATA /data/sfaedb/data05.dbf 31131 31131 1542.57
DATA /data/sfaedb/data03.dbf 30757 30757 1500.78
DATA /data/sfaedb/data04.dbf 30687 30687 1525.45
DATA /data/sfaedb/data07.dbf 30569 30569 1522.47
DATA /data/sfaedb/data08.dbf 28781 28781 1422.77
DATA /data/sfaedb/data09.dbf 27789 27789 1372.41
DATA /data/sfaedb/data10.dbf 26828 26828 1317.54
DATA /data/sfaedb/data01.dbf 24978 24978 1223.66
Average per-block read time: 46.449 ms
The following is an example of generating a report of read activity during the interval specified by AWR snapshot id 80 to 90.
$ dbed_codm_adm -S sfaedb -H /u01/app/11gr2 -o display -u 80-90 -t DATA
Tablespace Filename Average Average Read
Physical Physical Time (s) Reads per Blocks
Snapshot Read per Snapshot
DATA /data/sfaedb/data03.dbf 38896 38896 1544.60
DATA /data/sfaedb/data04.dbf 37649 37649 1586.97
DATA /data/sfaedb/data05.dbf 37139 37139 1532.11
DATA /data/sfaedb/data06.dbf 35875 35875 1462.15
105 Improving database performance with Veritas Cached Oracle Disk Manager Administering Cached ODM settings with Cached ODM Advisor in SFHA environment
DATA /data/sfaedb/data07.dbf 34451 34451 1404.10
DATA /data/sfaedb/data08.dbf 32219 32219 1284.36
DATA /data/sfaedb/data10.dbf 31451 31451 1247.67
DATA /data/sfaedb/data01.dbf 31377 31377 1254.35
DATA /data/sfaedb/data09.dbf 31320 31320 1243.18
Average per-block read time: 37.691 ms