Tuesday, August 11, 2020
    Home Oracle Tuning Improve Oracle Database Performance using Data File Striping

    Improve Oracle Database Performance using Data File Striping

    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.

    - Advertisement -

    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.

    - Advertisement -
    - Advertisment -

    Most Popular

    ASM Disk creation issue

    The article explains how to troubleshoot Oracle ASM disk creation. Solution of Oracle ASM disk creation with ASMLIB

    Oracle Tuning Basics Part-2

    This is the 2nd part of Oracle database tuning basics including depth of every aspects.

    Oracle Tuning Basics Part-1

    The article explains Oracle database performance tuning basics details.

    When you finding new job

    The article guides you 10 things to remember when you are searching a new job to improve yourself including skills and better growth for every DBA.

    Recent Comments