External Performance   «Prev  Next»
Lesson 4Disk load balancing
Objective Identify data files with high I/O.

Identify high I/O data files when Disk load balancing

Within the OS, it is relatively easy to identify disks with a disproportional amount of activity. However, identifying the Oracle data files on these disks and which data file is causing the problem can be very challenging. Typically, there is a hierarchy between physical disks and Oracle data files.

hierarchy
Hierarchy

How is the bstat-estat utility used in Oracle?

bstat/estat utility in Oracle is used to gather statistics on various components of the database such as tables, indexes, and data files. The utility collects and displays information about the performance and status of the database, allowing administrators to monitor the database's performance and identify potential issues. The
  1. bstat (begin statistics) and
  2. estat (end statistics) commands
are used to start and stop the collection of statistics. The output generated by the utility can be used for performance tuning and troubleshooting purposes.

Load balancing

To remedy high I/O due to over-activity, use load balancing. The first step in disk load balancing is to find a disk that has high I/O activity. Having identified the disk, you must find the Oracle data files causing the high I/O. To do this, use the bstat-estat utility.

How do I remedy high I/O for disks with over-activity in Oracle?

There are several ways to remedy high I/O for disks with over-activity in an Oracle database:
  1. Add more disk resources: Adding additional disk resources can help reduce I/O contention and improve overall disk performance. This could include adding disk space, disk arrays, or solid-state drives (SSDs).
  2. Tune the database: Ensure that the database is properly tuned to minimize disk I/O. This includes optimizing the buffer cache size, table and index design, and optimizing SQL statements.
  3. Use storage optimization techniques: Implementing storage optimization techniques such as data compression, data deduplication, and tiering can help reduce disk I/O and improve performance.
  4. Partition large tables and indexes: Partitioning large tables and indexes can reduce disk I/O by limiting the number of blocks that need to be read or written to disk.
  5. Use automatic storage management (ASM): Using ASM can help manage disk resources and improve disk performance by rebalancing disk I/O across multiple disks.
  6. Implement a backup strategy: Implementing a backup strategy can help prevent disk over-activity by reducing the amount of disk I/O required for backup operations.
  7. Monitor disk performance: Regularly monitor disk performance and track I/O trends to identify bottlenecks and proactively address issues.
It's important to note that the best solution for high I/O for disks with over-activity in Oracle will depend on the specific requirements of your database and the underlying infrastructure. It may be necessary to employ multiple solutions to fully resolve the issue.
  1. Now type @utlbstat in order to get a sample from the V$ views. These will then be compared with the sample that utlestat obtains to get the difference between the elapsed times.
  2. Type the following command into the SQL *Plus
  3. @utlbstat
  4. Type @utlbstat and hit Enter.
  1. Now connect.
  2. SQL*Plus prompt
  3. connect internal
  4. Type connect internal and hit Enter.
-->

Identifying highly active data files

  1. Enter the SQL *Plus
  2. Now type @utlbstat in order to get a sample from the V$ views. These will then be compared with the sample that utlestat obtains to get the difference between the elapsed times.
  3. Now connect.
  4. Note the Drop table stats$begin_stats line and the commit line. This is a standard part of utlbstat. It removed the old holding tables from the last time the utility was executed. Now run the utlestat.sql utility the same way.
  5. For this simulation, you are just seeing a portion of the output. These are the file I/O statistics. These statistics show information for all Oracle data files. A datafile is overactive if the blks_read or blks_write for the tablespaces is significantly more than the readings for the other tablespaces. This is the end of the simulation.

Tracing data files to disks

How do we translate this Oracle data file information back to the physical disks? Since the Oracle data file’s name will contain the name of the UNIX filesystem that it is attached to, you can work backwards to see which Oracle data files reside on each disk.
Once you identify the hot disk and the Oracle data files on that disk, use this report to see which Oracle data files are getting the majority of the activity. The remedy is to move these files onto a less active disk. You may still have a problem, however. When you move data files onto another disk that disk may become hot, too. There are cases where a highly active data file must be divided and spread across many physical disks. This is known as data striping. The next lesson illustrates how to do this.