Oracle Disk Management in UNIX Administration Oracle UNIX/Linux Tips by Burleson Consulting

Disk Management in UNIX

This chapter deals with the single most important component of Oracle tuning: the time required to fetch data blocks from disk. Because I/O bottlenecks are the greatest source of response time problems in any Oracle database, the Oracle DBA must constantly be alert for I/O-related slowdowns. Every time that Oracle has to visit the disk to retrieve a database block, time is wasted and Oracle must wait for the I/O to complete.

The purpose of this chapter is to show how to minimize the amount of I/O in your Oracle database. In a data warehouse we can?t minimize our I/O but we always want to make sure that I/O is as efficient as possible.

This chapter will be divided into several sections.

* Basic UNIX Disk management commands?This section is a brief review of basic UNIX command to display files and mount points.

* Monitoring UNIX mount points?This section will describe a Korn shell script to monitor the UNIX file systems.

* Configuring Oracle tablespaces and data files This section will describe the best methods for allocating tablespaces and data files in UNIX.

* Mapping Oracle data file to UNIX?This section describes how to ?map? your Oracle data files to UNIX mount points.

* Oracle tuning factors that affect disk I/O?This section will discuss the Oracle configurations and parameters that affect the amount of disk I/O.

* Oracle internals and disk I/O?This section will show the transient nature of disk hot spots and show how hot spots are related to table parameters such as pctused.

* Mapping Oracle disk architectures?This section will demonstrate the benefits of segregating large tables into separate tablespaces and mapping the tablespaces to specific datafiles. We will also examine the various file organization techniques and show their relative merits.

* STATSPACK reports for Oracle data files?This section will show how STATSPACK reports can be used to identify ?hot? datafiles in your environment and provide the DBA with an alert mechanism for hot files.

* Extending STATSPACK for disk I/O data?This section will show how to extend STATSPACK to capture data from the UNIX iostat utility. We will also show how the data from stats$filestatxs and stats$iostat can be compared.

* Viewing I/O signatures with STATSPACK?This important section shows how to develop I/O signatures for a database and how to compare the I/O of datafiles to the entire database.

This chapter deals exclusively with I/O at the Oracle level, and how the Oracle DBA can identify patterns within the I/O subsystem and load balance their database for optimal performance.

Note: You are encouraged to run these prepared STATSPACK scripts against your database. It is only after your get insight into the I/O patterns within your database that you will be able to properly load balance your I/O subsystem.

This chapter emphasizes that there are many techniques that can be performed within Oracle to reduce the amount of I/O, and stresses that the primary motive for all Oracle tuning is to reduce I/O on the disks.

Basic UNIX disk management commands

Within UNIX there is a mapping hierarchy of files.  At the lowest level of the hierarchy we see the discrete data files, such as the files that comprise the Oracle database.  These file are located within a UNIX mount point.  A mount point is an area of logical storage that has no direct relationship to the physical disks.

Each UNIX mount point can be made up of one or many logical volumes (sometimes known as volume groups). The UNIX Systems Administrator (SA) allocates chunks of physical partitions to the logical volumes. At the lowest level, chunk of a disk are separated into physical partitions.

In sum, the hierarchy can be defined as follows:

* A disk consists of physical partitions

* A logical volume consists of physical partitions

* A mount point consists of a filesystem built on the logical volume

Viewing physical volumes

There are several differences when displaying physical volumes, each depending on your dialect of UNIX.  The physical volume display generally correspond directly to disk spindles, but sometimes the internals of the disk devices are hidden by mapping software at the disk level.  For example, EMC disk arrays often have hidden mappings that cannot be seen with standard UNIX commands.

In HP/UX you can use the pvdisplay command is to display physical volumes, while in AIX the lspv command us invoked.

Viewing UNIX volume groups

Now we need to inspect the mapping between a physical partition and a UNIX volume group. The following commands can be used to pair volume groups with UNIX mount points in Solaris and AIX.

root> lsvg ?o

vgpvg116
vgpvg153
vgpvg624

For details on a specific volume group, you can use the lsvg ?l command, passing the volume group name as a parameter.

root> lsvg -l appvg01

appvg01:
LV NAME             TYPE       LPs   PPs   PVs  LV STATE      MOUNT POINT
loglv00             jfslog     1     1     1    open/syncd    N/A
lv01                jfs        123   123   1    open/syncd    /u01
lv17                jfs        62    62    1    open/syncd    /legato

Now let?s use the xargs UNIX command to directly display the volume groups.  The xargs UNIX command is used to pass the complete output of the first command (lsvg ?o in this case), one at a time to the lsvg ?l command. This is equivalent to running lsvg ?o, gathering the logical volume name and issuing the lsvg ?l command for each logical volume.

root> lsvg -o|xargs lsvg ?l

appvg16:
LV NAME             TYPE       LPs   PPs   PVs  LV STATE      MOUNT POINT
loglv15             jfslog     1     1     1    open/syncd    N/A
lv16                jfs        489   489   1    open/syncd    /u16
appvg15:
LV NAME             TYPE       LPs   PPs   PVs  LV STATE      MOUNT POINT
loglv14             jfslog     1     1     1    open/syncd    N/A
lv15                jfs        489   489   1    open/syncd    /u15

Now that we understand UNIX volume groups, let?s take a closer look at displaying UNIX mount points.

Display UNIX Mount Points

A UNIX ?mount point? is the UNIX location of disk storage.  There are two main commands to display base file systems the bdf and the df commands.

As the Oracle DBA, you should know the mapping between your physical disks, logical volumes and mount points.  If you do not know this mapping, the detection of a hot disk will not be helpful since you will be unable to trace the hot disk to an Oracle data file.

The best way to start a file placement strategy is to create a transparent disk architecture whereby the DBA can correlate the datafile name to a specific Oracle table or index. Let?s explore how we create this type of architecture.

Let?s start with HP/UX. The bdf command is used with HP/UX to display a filesystem:

root> bdf

Filesystem          kbytes     used   avail %used Mounted on
/dev/vg00/lvol3      143360   140317     2868   98% /
/dev/vg00/lvol1       83733    28295    47064   38% /stand
/dev/vg00/lvol8     1597440   642544   895482   42% /var
/dev/vg00/lvol7      999424   503351   465097   52% /usr
/dev/vg05/u08       6291456  5445579   794737   87% /u08
/dev/vg05/u07       2621440  1048595   174643   42% /u07
/dev/vg05/u06      12582912  6520474  5888678   53% /u06
/dev/vg05/u05       8388608  6450888  1877206   77% /u05
/dev/vg04/u04      71675904 45483648 25987920   64% /u04
/dev/vg03/u03      35545088 29105880  6339604   82% /u03
/dev/vg02/u02      35545088 33483744  2045828   94% /u02
/dev/vg01/u01      17772544  8259264  9364928   47% /u01
/dev/vg00/lvol4       65536     8053    53992   13% /tmp
/dev/vg00/lvol6      630784   581669    46143   93% /opt
/dev/vg00/lvol5       61440    44001    16355   73% /home

On AIX and Solaris, the df command is the primary command to display mount points.

root> df ?k

Filesystem    1024-blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4            32768     11636   65%     2017    13% /
/dev/hd2           802816     15920   99%    26308    14% /usr
/dev/hd9var         49152     28316   43%      567     5% /var
/dev/hd3            32768     14420   56%      285     4% /tmp
/dev/hd1           131072     20484   85%     5611    18% /home
/dev/lv01         2015232    843328   59%     5750     2% /u01
/dev/lv02         2015232    247172   88%      916     1% /u02
/dev/lv03         4521984    944420   80%      199     1% /u03
/dev/lv04         4505600   1646880   64%       53     1% /u04

In HP/UX version 11, the df ?k command can be used but the output different in HP/UX than AIX and Solaris:

root> df -k

/home           (/dev/vg00/lvol5       ) :    60356 total allocated Kb
                                               16355 free allocated Kb
                                               44001 used allocated Kb
                                                  72 % allocation used
/opt            (/dev/vg00/lvol6       ) :   627811 total allocated Kb
                                               46140 free allocated Kb
                                              581671 used allocated Kb
                                                  92 % allocation used
/tmp            (/dev/vg00/lvol4       ) :    62052 total allocated Kb
                                               53872 free allocated Kb
                                                8180 used allocated Kb
                                                  13 % allocation used
/u01            (/dev/vg01/u01         ) : 17624192 total allocated Kb
                                             9364928 free allocated Kb
                                             8259264 used allocated Kb
                                                  46 % allocation used
/u02            (/dev/vg02/u02         ) : 35529552 total allocated Kb
                                             2047288 free allocated Kb
                                            33482264 used allocated Kb
                                                  94 % allocation used
/u03            (/dev/vg03/u03         ) : 35445484 total allocated Kb
                                             6339604 free allocated Kb
                                            29105880 used allocated Kb
                                                  82 % allocation used
/u04            (/dev/vg04/u04         ) : 71471568 total allocated Kb
                                            25987920 free allocated Kb
                                            45483648 used allocated Kb
                                                  63 % allocation used

Show mount points for a disk in AIX

Here you can use the list physical volume command called lspv to display disk mount points in IBM AIX.  Here we restrict the lspv command to display the mount points that map to disk hdisk16.

root> lspv -l hdisk16

hdisk16:
LV NAME               LPs   PPs   DISTRIBUTION          MOUNT POINT
Loglv53               1     1     00..01..00..00..00    N/A
Lv75                  175   175   00..207..108..60..00  /u01

Next, let?s see how automated scripts can be used to monitor UNIX file systems.

Setting your UNIX Oracle environment for dialect differences

When you write UNIX scripts that remotely access servers with different dialects of UNIX, you mist be able to issue the appropriate command based on the current UNIX dialect.  For example, if you are on HP/UX you need to use the bdf command and if you are on Solaris you need to use the df ?k command.

Below is a script that will set a UNIX variable to your OS dialect.  This script sets the $dialect_df UNIX environment variable to the proper syntax depending upon the dialect if UNIX. If you work in an environment where you change frequently between UNIX dialects, you can execute the $dialect_df command to execute the appropriate command.

#*************************************************************
# Set-up the dialect changes for HP/UX and AIX (df -k) vs (bdf)
#*************************************************************
os=`uname -a|awk '{ print $1 }'`
if [ $os = "OSF1" ]
then
    alias df="df -k"
fi
if [ $os = "AIX" ]
then
    alias df="df -k"
fi
if [ $os = "IRIX64" ]
then
    alias df="df -k"
fi
if [ $os = "HP-UX" ]
then
    alias df="bdf"
fi

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.