Tuesday, October 20, 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

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Differences Raw Device, ASM Device and CFS

    The article explains basic differences of ASM device, raw device and Oracle cluster file system cfs with fundamentals

    What is a Database Administrator?

    The blog post explains what is the work of database administrator called DBA in database domain and what is the importance.

    Top Reasons to Implement Remote Database Monitoring

    The blog post explains top three reasons and ideas to get remote monitoring services from outer support company.

    Recent Comments