In an environment where datafiles are simply file system files or are created directly on a raw device, it is relatively straight forward to see the association between a tablespace and the underlying device. Oracle Database provides views, such as
V$DATAFILE, that provide a mapping of files onto devices. These mappings, along with device statistics can be used to evaluate I/O performance.
However, with the introduction of host based Logical Volume Managers (LVM), and sophisticated storage subsystems that provide RAID (Redundant Array of Inexpensive Disks) features, it is not easy to determine file to device mapping. This poses a problem because it becomes difficult to determine your "hottest" files when they are hidden behind a "black box". This section presents the Oracle Database approach to resolving this problem.
The following topics are contained in this section:
Note:This section presents an overview of the Oracle Database file mapping interface and explains how to use the
DBMS_STORAGE_MAPpackage and dynamic performance views to expose the mapping of files onto physical devices. You can more easily access this functionality through the Oracle Enterprise Manager (EM). It provides an easy to use graphical interface for mapping files to physical devices.
To acquire an understanding of I/O performance, one must have detailed knowledge of the storage hierarchy in which files reside. Oracle Database provides a mechanism to show a complete mapping of a file to intermediate layers of logical volumes to actual physical devices. This is accomplished though a set of dynamic performance views (
V$ views). Using these views, you can locate the exact disk on which any block of a file resides.
To build these views, storage vendors must provide mapping libraries that are responsible for mapping their particular I/O stack elements. The database communicates with these libraries through an external non-Oracle Database process that is spawned by a background process called FMON. FMON is responsible for managing the mapping information. Oracle provides a PL/SQL package,
DBMS_STORAGE_MAP, that you use to invoke mapping operations that populate the mapping views.
Note:The file mapping interface is not available on Windows platforms.
This section describes the components of the Oracle Database file mapping interface and how the interface works. It contains the following topics:
The following figure shows the components of the file mapping mechanism.
The following sections briefly describes these components and how they work together to populate the mapping views:
FMON is a background process started by the database whenever the
FILE_MAPPING initialization parameter is set to
TRUE. FMON is responsible for:
Building mapping information, which is stored in the SGA. This information is composed of the following structures:
File system extents
These structures are explained in "Mapping Structures".
Refreshing mapping information when a change occurs because of:
Changes to datafiles (size)
Addition or deletion of datafiles
Changes to the storage configuration (not frequent)
Saving mapping information in the data dictionary to maintain a view of the information that is persistent across startup and shutdown operations
Restoring mapping information into the SGA at instance startup. This avoids the need for a potentially expensive complete rebuild of the mapping information on every instance startup.
You help control this mapping using procedures that are invoked with the
FMON spawns an external non-Oracle Database process called
FMPUTL, that communicates directly with the vendor supplied mapping libraries. This process obtains the mapping information through all levels of the I/O stack, assuming that mapping libraries exist for all levels. On some platforms the external process requires that the
SETUID bit is set to
ON because root privileges are needed to map through all levels of the I/O mapping stack.
The external process is responsible for discovering the mapping libraries and dynamically loading them into its address space.
Oracle Database uses mapping libraries to discover mapping information for the elements that are owned by a particular mapping library. Through these mapping libraries information about individual I/O stack elements is communicated. This information is used to populate dynamic performance views that can be queried by users.
Mapping libraries need to exist for all levels of the stack for the mapping to be complete, and different libraries may own their own parts of the I/O mapping stack. For example, a VERITAS VxVM library would own the stack elements related to the VERITAS Volume Manager, and an EMC library would own all EMC storage specific layers of the I/O mapping stack.
Mapping libraries are vendor supplied. However, Oracle currently supplies a mapping library for EMC storage. The mapping libraries available to a database server are identified in a special file named filemap.ora.
The mapping structures and the Oracle Database representation of these structures are described in this section. You will need to understand this information in order to interpret the information in the mapping views.
The following are the primary structures that compose the mapping information:
A file mapping structure provides a set of attributes for a file, including file size, number of file system extents that the file is composed of, and the file type.
File system extents
A file system extent mapping structure describes a contiguous chunk of blocks residing on one element. This includes the device offset, the extent size, the file offset, the type (data or parity), and the name of the element where the extent resides.
Note:File system extents are not the same as Oracle Database extents. File system extents are physical contiguous blocks of data written to a device as managed by the file system. Oracle Database extents are logical structures managed by the database, such as tablespace extents.
An element mapping structure is the abstract mapping structure that describes a storage component within the I/O stack. Elements may be mirrors, stripes, partitions, RAID5, concatenated elements, and disks. These structures are the mapping building blocks.
A subelement mapping structure describes the link between an element and the next elements in the I/O mapping stack. This structure contains the subelement number, size, the element name where the subelement exists, and the element offset.
All of these mapping structures are illustrated in the following example.
Consider an Oracle Database which is composed of two data files X and Y. Both files X and Y reside on a file system mounted on volume A. File X is composed of two extents while file Y is composed of only one extent.
The two extents of File X and the one extent of File Y both map to Element A. Element A is striped to Elements B and C. Element A maps to Elements B and C by way of Subelements B0 and C1, respectively.
Element B is a partition of Element D (a physical disk), and is mapped to Element D by way of subelement D0.
Element C is mirrored over Elements E and F (both physical disks), and is mirrored to those physical disks by way of Subelements E0 and F1, respectively.
All of the mapping structures are illustrated in Figure 13-2.
Note that the mapping structures represented are sufficient to describe the entire mapping information for the Oracle Database instance and consequently to map every logical block within the file into a (element name, element offset) tuple (or more in case of mirroring) at each level within the I/O stack.
The configuration ID captures the version information associated with elements or files. The vendor library provides the configuration ID and updates it whenever a change occurs. Without a configuration ID, there is no way for the database to tell whether the mapping has changed.
There are two kinds of configuration IDs:
These configuration IDs are persistent across instance shutdown
The configuration IDs are not persistent across instance shutdown. The database is only capable of refreshing the mapping information while the instance is up.
This section discusses how to use the Oracle Database file mapping interface. It contains the following topics:
The following steps enable the file mapping feature:
Ensure that a valid filemap.ora file exists in the /opt/ORCLfmap/prot1_32/etc directory for 32-bit platforms, or in the /opt/ORCLfmap/prot1_64/etc directory for 64-bit platforms.
Caution:While the format and content of the filemap.ora file is discussed here, it is for informational reasons only. The filemap.ora file is created by the database when your system is installed. Until such time that vendors supply their own libraries, there will be only one entry in the filemap.ora file, and that is the Oracle-supplied EMC library. This file should be modified manually by uncommenting this entry only if an EMC Symmetrix array is available.
The filemap.ora file is the configuration file that describes all of the available mapping libraries. FMON requires that a filemap.ora file exists and that it points to a valid path to mapping libraries. Otherwise, it will not start successfully.
The following row needs to be included in filemap.ora for each library:
vendor_name should be
Oracle for the EMC Symmetric library
mapping_library_path is the full path of the mapping library
Note that the ordering of the libraries in this file is extremely important. The libraries are queried based on their order in the configuration file.
The file mapping service can be even started even if no mapping libraries are available. The filemap.ora file still needs to be present even though it is empty. In this case, the mapping service is constrained in the sense that new mapping information cannot be discovered. Only restore and drop operations are allowed in such a configuration.
The instance does not have to be shut down to set this parameter. You can set it using the following
ALTER SYSTEM statement:
ALTER SYSTEM SET FILE_MAPPING=TRUE;
In a cold startup scenario, the Oracle Database is just started and no mapping operation has been invoked yet. You execute the
DBMS_STORAGE_MAP.MAP_ALL procedure to build the mapping information for the entire I/O subsystem associated with the database.
In a warm start scenario where the mapping information is already built, you have the option to invoke the
DBMS_STORAGE_MAP.MAP_SAVE procedure to save the mapping information in the data dictionary. (Note that this procedure is invoked in
DBMS_STORAGE_MAP.MAP_ALL() by default.) This forces all of the mapping information in the SGA to be flushed to disk.
Once you restart the database, use
DBMS_STORAGE_MAP.RESTORE() to restore the mapping information into the SGA. If needed,
DBMS_STORAGE_MAP.MAP_ALL() can be called to refresh the mapping information.
DBMS_STORAGE_MAP package enables you to control the mapping operations. The various procedures available to you are described in the following table.
||Build the mapping information for the database object identified by object name, owner, and type|
||Build mapping information for the specified element|
||Build mapping information for the specified filename|
||Build entire mapping information for all types of database files (excluding archive logs)|
||Drop the mapping information for a specified element|
||Drop the file mapping information for the specified filename|
||Drop all mapping information in the SGA for this instance|
||Save into the data dictionary the required information needed to regenerate the entire mapping|
||Load the entire mapping information from the data dictionary into the shared memory of the instance|
||Lock the mapping information in the SGA for this instance|
||Unlock the mapping information in the SGA for this instance|
Oracle Database PL/SQL Packages and Types Reference for a description of the
"File Mapping Examples" for an example of using the
||Contains a list of all mapping libraries that have been dynamically loaded by the external process|
||Contains a list of all file mapping structures in the shared memory of the instance|
||Contains a list of all file system extent mapping structures in the shared memory of the instance|
||Contains a list of all element mapping structures in the SGA of the instance|
||Contains supplementary information for all element mapping|
||Contains a list of all subelement mapping structures in the shared memory of the instance|
||Contains supplementary information for all element mapping structures.|
||The hierarchical arrangement of storage containers for the file displayed as a series of rows. Each row represents a level in the hierarchy.|
See Also:Oracle Database Reference for a complete description of the dynamic performance views
However, the information generated by the
DBMS_STORAGE_MAP.MAP_OBJECT procedure is captured in a global temporary table named
MAP_OBJECT. This table displays the hierarchical arrangement of storage containers for objects. Each row in the table represents a level in the hierarchy. A description of the
MAP_OBJECT table follows.
||Name of the object|
||Owner of the object|
||File index (corresponds to
||Element depth within the I/O stack|
||Index corresponding to element|
||Contiguous set of logical blocks of the file, in HKB (half KB) units, that is resident contiguously on the element|
||Number of HKB between contiguous units (CU) in the file that are contiguous on this element. Used in RAID5 and striped files.|
||Number of contiguous units that are adjacent to each other on this element that are separated by
||Element offset in HKB units|
||Offset in HKB units from the start of the file to the first byte of the contiguous units|
||Position of the parity. Only for RAID5. This field is needed to distinguish the parity from the data part.|
||Parity period. Only for RAID5.|
The following examples illustrates some of the powerful capabilities of the Oracle Database file mapping feature. This includes:
The ability to map all the database files that span a particular device
The ability to map a particular file into its corresponding devices
The ability to map a particular database object, including its block distribution at all levels within the I/O stack
Consider an Oracle Database instance which is composed of two datafiles:
These files are created on a Solaris UFS file system mounted on a VERITAS VxVM host based striped volume,
/dev/vx/dsk/ipfdg/ipf-vol1, that consists of the following host devices as externalized from an EMC Symmetrix array:
Note that the following examples require the execution of a
The following query returns all Oracle Database files associated with the
/dev/vx/rdmp/c2t1d1s2 host device:
SELECT UNIQUE me.ELEM_NAME, mf.FILE_NAME FROM V$MAP_FILE_IO_STACK fs, V$MAP_FILE mf, V$MAP_ELEMENT me WHERE mf.FILE_MAP_IDX = fs.FILE_MAP_IDX AND me.ELEM_IDX = fs.ELEM_IDX AND me.ELEM_NAME = '/dev/vx/rdmp/c2t1d1s2';
The query results are:
ELEM_NAME FILE_NAME ------------------------ -------------------------------- /dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db2.f
The following query displays a topological graph of the
WITH fv AS (SELECT FILE_MAP_IDX, FILE_NAME FROM V$MAP_FILE WHERE FILE_NAME = '/oracle/dbs/t_db1.f') SELECT fv.FILE_NAME, LPAD(' ', 4 * (LEVEL - 1)) || el.ELEM_NAME ELEM_NAME FROM V$MAP_SUBELEMENT sb, V$MAP_ELEMENT el, fv, (SELECT UNIQUE ELEM_IDX FROM V$MAP_FILE_IO_STACK io, fv WHERE io.FILE_MAP_IDX = fv.FILE_MAP_IDX) fs WHERE el.ELEM_IDX = sb.CHILD_IDX AND fs.ELEM_IDX = el.ELEM_IDX START WITH sb.PARENT_IDX IN (SELECT DISTINCT ELEM_IDX FROM V$MAP_FILE_EXTENT fe, fv WHERE fv.FILE_MAP_IDX = fe.FILE_MAP_IDX) CONNECT BY PRIOR sb.CHILD_IDX = sb.PARENT_IDX;
The resulting topological graph is:
FILE_NAME ELEM_NAME ----------------------- ------------------------------------------------- /oracle/dbs/t_db1.f _sym_plex_/dev/vx/rdsk/ipfdg/ipf-vol1_-1_-1 /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_0_0 /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d0s2 /oracle/dbs/t_db1.f _sym_symdev_000183600407_00C /oracle/dbs/t_db1.f _sym_hyper_000183600407_00C_0 /oracle/dbs/t_db1.f _sym_hyper_000183600407_00C_1 /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_1_0 /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db1.f _sym_symdev_000183600407_00D /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_0 /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_1
This example displays the block distribution at all levels within the I/O stack for the
MAP_OBJECT() operation must first be executed as follows:
The query is as follows:
SELECT io.OBJECT_NAME o_name, io.OBJECT_OWNER o_owner, io.OBJECT_TYPE o_type, mf.FILE_NAME, me.ELEM_NAME, io.DEPTH, (SUM(io.CU_SIZE * (io.NUM_CU - DECODE(io.PARITY_PERIOD, 0, 0, TRUNC(io.NUM_CU / io.PARITY_PERIOD)))) / 2) o_size FROM MAP_OBJECT io, V$MAP_ELEMENT me, V$MAP_FILE mf WHERE io.OBJECT_NAME = 'BONUS' AND io.OBJECT_OWNER = 'SCOTT' AND io.OBJECT_TYPE = 'TABLE' AND me.ELEM_IDX = io.ELEM_IDX AND mf.FILE_MAP_IDX = io.FILE_MAP_IDX GROUP BY io.ELEM_IDX, io.FILE_MAP_IDX, me.ELEM_NAME, mf.FILE_NAME, io.DEPTH, io.OBJECT_NAME, io.OBJECT_OWNER, io.OBJECT_TYPE ORDER BY io.DEPTH;
The following is the result of the query. Note that the
o_size column is expressed in KB.
O_NAME O_OWNER O_TYPE FILE_NAME ELEM_NAME DEPTH O_SIZE ------ ------- ------ ------------------- ----------------------------- ------ ------ BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/dsk/ipfdg/ipf-vol1 0 20 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_plex_/dev/vx/rdsk/ipf 1 20 pdg/if-vol1_-1_-1 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ 2 12 ipfdg/ipf-vol1_0_1_0 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipf 2 8 dg/ipf-vol1_0_2_0 BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2 3 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d2s2 3 8 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00D 4 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00E 4 8 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_0 5 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_1 5 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_0 6 8 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_1 6 8