Improve Oracle Database Performance using Data File Striping

January 10, 2019 | By

full table scanWhile disk level striping really advantages many Oracle databases, Oracle databases necessitate striping at an additional granular level. Except we can streak in sizes of less than the cylinder size for the hard disk, the disk will have the hunt for disputation as the read-write head shifts among the cylinders.  In a perfect world, we desire to have only a single hot spot on each hard-disk. Carving numerous hot tablespaces diagonally a small number of disks would generate circumstances where the roving hot spots would move among the disks, creating an I/O bottleneck at any time two or more hot spots dwell on the same spindle. The purpose is to have the active Oracle blocks residing on the same cylinder or at worst, directly contiguous cylinders to minimize seek delay on the disk device.

Also, beware that improper file striping may cause collisions when the hot spots converge on a single disk. Striping an application tablespace at the filesystem or Oracle level may create a situation where hot spots migrate across spindles, causing I/O bottlenecks whenever hotspots appear on the same spindle.

If disk cost is not a consideration, each hot application or ERP tablespace should reside entirely on a single spindle. Unfortunately, commercial disks are getting larger every year, and it is very difficult to find small disk devices that contain less than 3 gigabytes of storage. Another problem with all forms of striping is the lack of availability of small disk spindles. Just 10 years ago, the storage disk was considered huge at 1 gigabyte of storage. Today, it is hard to find disks smaller than 8 gigabytes. The larger disks mean that there are fewer disk spindles and fewer opportunities for striping. Since it is often not possible to isolate ERP tablespaces on separate disks without wasting a huge amount of disk space, the Oracle Administrator must balance active with inactive ERP tablespace across his or her disks.

Although most Oracle DBA associate the striping technique with very large tables, a small, but very active, ERP table will experience relief from bugger busy waits by striping. This technique can be very useful for small ERP reference tables that are simultaneously updated by many transactions. The tables are characterized by a relatively small number of rows with a small row length. Because of the large amount of updating, these tables will experience buffer busy waits, even though the data blocks are already in the database buffer cache.

Therefore, table striping is the best option to avoid physical disk I/O contention as well as it is improving performance bottleneck of your ERP application database.

Tags: , , , ,

Category: Oracle performance tuning

Comments are closed.