I/O-Based Tuning

In a distributed database environment, it is important to understand that the overall distributed system is only going to perform as well as the weakest link. Therefore, most distributed database tuning treats each remote node as an independent database, individually tuning each one and thereby improving overall distributed system requests.

Input/Output is the single most important factor in database tuning. Business systems, by their very nature, are relatively light on processing and heavy on their demands from the disks that comprise the database. Several tricks are available to reduce I/O time from disk, including cache memory, buffer expansion, file placement, and file striping.

Disk Striping

Striping involves taking a very large or very busy data table and distributing it across many disks. When a performance problem occurs on a regular basis, it is most often the result of disks waiting on I/O. By distributing the file across many physical devices, the overall system response time will improve. Disk striping is generally done for tables that are larger than the size of a disk device, but striping can be equally effective for small, heavily accessed tables (see Figure 3.6).

Figure 3.6  Striping a table across many disks.

Note that the data file appears to the database management system as a single logical file, which avoids any I/O problems from within the database when doing table striping. As rows are requested from the table, the SQL I/O module will request physical data blocks from the disk—one at a time—unaware that the logically continuous table is actually comprised of many physical data files.

In an Oracle database, disk striping is done in a similar fashion. Consider the following Oracle syntax:

CREATE TABLESPACE TS1
  DATA FILE "/usr/disk1/bigfile1.dbf"  SIZE30M
  DATA FILE "/usr/disk2/bigfile2.dbf"  SIZE30M;

CREATE TABLE BIG_TABLE (
  big_field1    CHAR(8)
  big_field2    VARCHAR(2000))
TABLESPACE TS1
STORAGE (INITIAL 25M   NEXT 25M   MINEXTENTS 2   PCTINCREASE 1);

Here we see that a tablespace is created with two data files—bigfile1 and bigfile2—each with a size of 30 megabytes. When we are ready to create a table within the tablespace, we size the extents of the table such that the database is forced to allocate the table’s initial extents into each data file. As the table is created in the empty tablespace, the MINEXTENTS parameter tells the database to allocate two extents, and the INITIAL parameter tells the database that each extent is to be 25 megabytes each. The database then goes to bigfile1 on disk1 and allocates an extent of 25 megabytes. It then tries to allocate another extent of 25 megabytes on bigfile1, but only 5 megabytes of free space are available. The database must move to bigfile2 to allocate the final extent of 25 megabytes, as shown in Figure 3.7.

Figure 3.7  Allocating an Oracle table with striped extents.

After the table has been initially created, the value for the NEXT extent should be changed to a smaller value than the INITIAL extent:

ALTER TABLE BIG_TABLE
STORAGE (NEXT 1M);

Some database administrators recommend striping all tables across each and every physical disk. If a system has 10 tables and the CPU is configured for 2 disks, then each of the 10 tables would be striped into each disk device.

It is unfortunate that the relational databases require the DBA to “trick” the database allocation software into striping the files rather than allowing direct control over the file placement process. This lack of control can be a real problem when tables are “compressed.”

Several methods will ensure that the tables are striped across the disks. Many databases with a sophisticated data dictionary allow queries that reveal the striping of the files. Oracle relies upon the following script:

striping.sql - displays striped file names
SELECT DISTINCT file_name,
FROM    dba_data_files a, dba_extents b
WHERE
        a.file_id = b.file_id
AND
        segment_name = :striped_table_name;

(WHERE :striped_table_name = 'BIG_TABLE')

Other databases offer utilities that report on the physical file utilization for a specific table or database record type.

Tuning Data Fragmentation

Fragmentation occurs when the preallocated space for a data area or table has been exceeded. In relational databases, tables are allocated into tablespaces, and each table is given several storage parameters. The storage parameters include INITIAL, NEXT, PCTINCREASE, and MAXEXTENTS.

As tables grow, they automatically allocate “extents,” or extra storage at a size determined by the NEXT parameter. If a table reaches its maximum number of extents, all processing against that table will stop, causing major performance interruptions. In Oracle, the maximum number of extents can range from 121 extents for a 2 K blocksize to 505 for an 8 K block size, and increases according to the value of the db_block_size init.ora parameter. See your Oracle installation and configuration guide for the exact values for max extents on your operating system. If you are using Oracle 7.3 and above, you may want to consider using MAXEXTENTS UNLIMITED.

Even before the data tables fill, performance against the table may degrade as the amount of extents increases. An operating system such as Unix is forced to chase the “inode” chains to scan the entire table, and the increased I/O translates into performance delays (see Figure 3.8).

Figure 3.8  Free space allocation in an Oracle tablespace.

Several SQL queries can be used to detect table fragmentation. These queries are unique to the database system tables, but most databases allow for table fragmentation to be measured. These reports are generally incorporated into a periodic report, and those files that are fragmenting are then scheduled for export-import to unfragment the tables.

As the initial extents of a table fill, the database manager allocates additional spaces on the disk to allow the table to expand. These fragments eventually create a performance problem, and the tables need to be compressed to remove these extents.

Table compression is achieved in the following manner:

1.  Determine the level of fragmentation and determine the new table sizes.

2.  Offload all table data using an export utility.

3.  Drop the old tables.

4.  Reallocate the new tables at their new sizes.

5.  Import the data to repopulate the tables.

Disk Issues With Other System Resources

Most databases have other high-impact resources such as the recovery logs and transaction logs. Most systems have several recovery logs, and it is a good idea to locate these logs on different disk devices as well as devices that do not have any other high-impact data tables.

Summary

Now that we have discussed how Oracle functions at the system level, we are ready to take a closer look at how individual SQL can be tuned to get the most from the available database resources. As we have already learned, myriad factors can degrade performance. Fortunately, many techniques are available for diagnosing and correcting performance problems, from tuning Oracle locks and/or the client application, to performance and tuning for distributed servers.