This post might interest you if your organisation has Oracle databases running on Veritas filesystems (VxFS).

What Does Veritas File Fragmentation Mean?

The extents allocated to a datafile are not contiguous in the filesystem.  If the extents are too small and scattered then large or sequential I/Os (eg, full tablescans) might be inefficient.

Since learning about how to avoid UFS Fragmentation for Oracle datafiles, I was able to recognise a similar problem with a database on Veritas volume/filesystem.  (Solaris 10).  Many of the notes I made about UFS Fragmentation apply to Veritas fragmentation, so check that page for more information.

A Real World Example

The customer’s DBA had complained that the performance of a training database was significantly less than a test database; both being (RMAN) duplicates.  There were very few differences between the two, and they used the same storage (XIV), but the difference had been narrowed down to I/O performance.

Here is an example, using a full scan of a big table, showing the difference between ORATRN and ORATST:

alter session set db_file_multiblock_read_count=128;
select /*+ full(t) parallel(t) nocache */ count(1) from A_BIG_TABLE t;

Output from vxstat -i 1

 OPERATIONS          BLOCKS           AVG TIME(ms)
 TYP NAME              READ     WRITE      READ     WRITE   READ  WRITE
Fri Oct 18 11:09:36 2013
 vol oratrn           2224         5    429840        80  17.46   2.20
Fri Oct 18 11:09:37 2013
 vol oratrn           1890         4    355216        36  20.78   1.32
Fri Oct 18 11:09:38 2013
 vol oratrn           2008         4    313488        34  19.12   1.11
Fri Oct 18 11:09:39 2013
 vol oratrn           2325         7    328352        84  15.96   1.51
Fri Oct 18 11:09:40 2013
 vol oratrn           1960         7    310064        84  19.62   1.33
Fri Oct 18 11:09:41 2013
 vol oratrn           1895         7    320480        88  19.63   2.62

Fri Oct 18 10:48:45 2013
 vol oratst           1157         2   2107904         8  26.73   3.07
Fri Oct 18 10:48:46 2013
 vol oratst           1178         2   2154944         4  27.32   9.16
Fri Oct 18 10:48:47 2013
 vol oratst           1167         3   2172352        48  26.82   1.80
Fri Oct 18 10:48:48 2013
 vol oratst           1279         4   2346320         8  25.14   7.13
Fri Oct 18 10:48:49 2013
 vol oratst           1223         2   2219488        32  25.97   9.91

The throughput is much higher in the ORATST filesystem, and the average I/O size is larger.

Truss of the database session showed pread system calls of 1MB (128x8KB DB blocks) for both databases, and the Veritas settings were the same.  (The XIV distributes space in 1MB chunks, and 1MB is a common maximum for I/Os, so this should be an efficient value).

I suspected the files were more fragmented in the ORATRN volume than in the ORATST volume because the I/Os were broken up into smaller pieces, despite the environment being almost identical.  I wasn’t sure though, because Veritas is an extent based filesystem, so I wonder if the database files could become fragmented in the same way as files in UFS can.
The customer’s DBA confirmed that the ORATRN database was created with several RMAN channels, while the ORATST database was created with only one.

How can we see the file fragmentation?  I found a standard Veritas command called fsmap.

$: find . | /opt/VRTSvxfs/sbin/fsmap -aH -
                    Volume  Extent Type     File Offset     Extent Size     File
                    oratrn         Data         0 Bytes       264.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data       264.00 KB       128.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data       392.00 KB       128.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data       520.00 KB       128.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data       648.00 KB       384.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data         1.01 MB       128.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data         1.13 MB       304.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data         1.43 MB        80.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data         1.51 MB       128.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data         1.63 MB       384.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratrn         Data         2.01 MB       128.00 KB     ./TWO_GB_DATAFILE.DBF
 ... etc

$: find . | /opt/VRTSvxfs/sbin/fsmap -aH -
                    Volume  Extent Type     File Offset     Extent Size     File
                    oratst         Data         0 Bytes         8.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         8.00 KB        32.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data        40.00 KB        64.00 KB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       104.00 KB         5.31 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         5.41 MB        24.81 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data        30.23 MB        25.88 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data        56.10 MB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data        72.10 MB        19.25 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data        91.35 MB        27.69 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       119.04 MB        23.38 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       142.41 MB        32.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       174.41 MB        17.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       191.41 MB        34.75 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       226.16 MB        42.19 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       268.35 MB        17.94 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       286.29 MB        38.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       324.29 MB        32.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       356.29 MB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       372.29 MB        32.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       404.29 MB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       420.29 MB        18.31 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       438.60 MB        16.25 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       454.85 MB        21.75 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       476.60 MB        17.38 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       493.98 MB        23.75 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       517.73 MB        23.50 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       541.23 MB        20.12 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       561.35 MB        16.25 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       577.60 MB        16.69 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       594.29 MB        16.12 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       610.41 MB        18.12 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       628.54 MB        22.88 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       651.41 MB        34.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       685.41 MB        43.06 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       728.48 MB        45.12 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       773.60 MB        23.88 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       797.48 MB        24.25 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       821.73 MB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       837.73 MB        18.12 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       855.85 MB        22.12 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       877.98 MB        64.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data       941.98 MB        96.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.01 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.03 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.04 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.06 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.08 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.09 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.11 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.12 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.14 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.15 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.17 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.19 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.20 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.22 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.23 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.25 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.26 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.28 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.29 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.31 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.33 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.34 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.36 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.37 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.39 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.40 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.42 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.44 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.45 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.47 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.48 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.50 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.51 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.53 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.54 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.56 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.58 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.59 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.61 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.62 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.64 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.65 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.67 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.69 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.70 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.72 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.73 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.75 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.76 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.78 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.79 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.81 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.83 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.84 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.86 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.87 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.89 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.90 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.92 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.94 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.95 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.97 GB        16.00 MB     ./TWO_GB_DATAFILE.DBF
                    oratst         Data         1.98 GB        10.02 MB     ./TWO_GB_DATAFILE.DBF

After copying the ORATRN files to a new filesystem using rsync, the extent sizes were up to 256MB each, eg:

$: find . | /opt/VRTSvxfs/sbin/fsmap -aH -
                   oranew          Data         0 Bytes        24.00 KB     ./TWO_GB_DATAFILE.DBF
                   oranew          Data        24.00 KB       246.62 MB     ./TWO_GB_DATAFILE.DBF
                   oranew          Data       246.65 MB       256.00 MB     ./TWO_GB_DATAFILE.DBF
                   oranew          Data       502.65 MB       256.00 MB     ./TWO_GB_DATAFILE.DBF
... etc

Also see Symantec’s note on this issue.

Update

During another project, I had the opportunity to examine how RMAN caused this fragmentation.

The database version was 9.2, so RMAN wrote from a 256KB output/disk buffer using four asynchronous threads.  The average VxFS extent size of the restored datafiles was 256KB.

Later versions of Oracle use larger and configurable buffers, so the problem will not be as severe.

We are going to try to use the VxFS tunables “initial_extent_size” and “max_seqio_extent_size” to reduce the fragmentation during the next restore test.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s