Veritas Storage Foundation 5.1 SP1: Storage and Availability Management for Oracle (5900-1504, April 2011)

■ Index segments: contain blocks for table indexes. It is important to database
performance that I/O to these blocks are read/written at highest rate and hence
should always be in the primary storage tier. Index segments move from one
location to other in a tablespace/datafile and are not good candidates for
relocating to secondary tier.
■ Rollback segment: contain UNDO data which is critical for database recovery
and should always be in on primary storage.
■ Temp segment: the temporary workspace for intermediate stages of sort
operations required by Oracle. Performance depends on I/O time of the temp
segments and hence these segments must always be in primary storage.
Database extents to file location
Logical objects like tables are stored as database extents. These database extents
are contiguous file system blocks within a given file. Dividing a file into an equal
number of objects does not cleanly match database extents. This is the reason
why the statistics feature of Veritas File System may not work for an Oracle
database. A file object may span more than one database extent. Oracle catalog
entries help to convert a database extent to (file, offset, length) set. If we get (file,
offset, length) set, we could use our relocation API to relocate that extent to a
secondary tier.
Oracle Catalog tables dba_extents and dba_segments can be used to compute (file,
offset, length) set for a given extent.
For a given dba_segment, we could join dba_segments and dba_extents to list the
(extent-id, file_id, starting_bloc/offset, length) set. This will indicate the exact
location in the file of a database extent.
Statistics in Oracle
For versions starting with Oracle 10g, Oracle collects various statistics and stores
them inside a database repository called the Automatic Workload Repository
(AWR). The statistics collected include CPU, memory, file IO, and disk statistics.
AWR is useful to find out usage trends, bottlenecks and improve performance.
By default, the statistics snapshot is taken every 60 minutes and stored in AWR.
AWR keeps the snapshot for seven days by default. Both statistics interval and
retention period can be changed via init.ora. Oracle recommends a retention
period of 30 days for statistics. Oracle provides tools to collect extra statistics on
demand. There are various scripts and tools available to query the AWR and
generate trend reports. AWR can be used to get file usage trends that will identify
least-used files that can then be relocated to secondary storage.
Advantages of using file statistics from AWR:
241Understanding storage tiering with SmartTier
How SmartTier for Oracle works