15.3 How do I…Stripe a tablespace across multiple devices?
Problem
Some heavily used tablespaces are causing contention on my database server’s disk devices. It doesn’t make sense to move the objects out of the tablespace and I want to stripe the tablespace across devices to reduce contention. Performance for simultaneous access of the tablespace will also be greatly improved if the tablespace spans multiple devices. How do I stripe a tablespace?
Technique
You can stripe a new tablespace at the database level when you initially create it by specifying multiple datafiles existing on more than one physical disk in the CREATE TABLESPACE statement. You can stripe an existing tablespace by using the ALTER TABLESPACE statement to create new, additional datafiles on other drives.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Use the START command to load and execute the CHP15_4.SQL script. The script and its output appears in Listing 15.2. The file contains a CREATE TABLESPACE statement distributing a single tablespace over multiple devices. The format of this statement assumes that you have three disk drives with drive designations C:, D:, and E:. If you do not have these drives, or if your operating system environment features different physical path specifications, then change the statement in script CHP15_4.SQL to reference valid disk drives and physical files on your system.
Listing 15.2 Running script CHP15_4.SQL in SQL*Plus to create a striped tablespace
SQL> START CHP15_4
1 CREATE TABLESPACE WAITE_TBS
2 DATAFILE ‘C:\WAITE1.ORA’ SIZE 1M,
3 ‘D:\WAITE2.ORA’ SIZE 1M,
4 ‘E:\WAITE3.ORA’ SIZE 1M
Tablespace created.
Line 1 identifies the statement with the CREATE TABLESPACE keyword and specifies the name of the tablespace as WAITE_TBS. Lines 2 through 4 specify the three datafiles making up the tablespace. The filename of the datafiles and the size are specified on each line.
2. Use the start command to load and execute CHP15_5.SQL into the SQL buffer. This script, which appears in Listing 15.3, adds an additional datafile to an existing tablespace. The goal is to add this new datafile to a disk other than the one housing the tablespace’s original data files. In this case, the script adds the additional data file to the tablespace created in Step 1. This statement assumes that an F: drive exists on your system. If this is not a valid drive designation or physical filename for your system, change the drive and filename to comply with your operating system’s conventions.
Listing 15.3 Running script CHP15_5.SQL in SQL*Plus to add a file on a different device to an existing tablespace
SQL> START CHP15_5
1 ALTER TABLESPACE WAITE_TBS
2 ADD DATAFILE
3 ‘F:\WAITE4.ORA’ SIZE 1M
Line 1 identifies the statement as an ALTER TABLESPACE statement and specifies that the WAITE_TBS tablespace will be modified. Line 2 presents the keywords ADD DATAFILE to specify that a new datafile will be created. Line 3 specifies the datafile to be created and its size. The filename specification is operating system specific.
When a new datafile is created, the tablespace begins using it as space is needed. Adding a new datafile to a nearly empty tablespace does not ensure that the load will be balanced across the files, because no objects can reside in the new datafile until the first datafile no longer accommodates them.
How It Works
Step 1 creates a new tablespace using the CREATE TABLESPACE statement and distributes the tablespace over three datafiles, each located on a separate disk drive. Step 2 adds a datafile to the tablespace created in Step 1 using the ALTER TABLESPACE statement with an ADD DATAFILE clause. Striping a tablespace does not ensure that all the datafiles will be used equally; the Oracle8 server uses datafile blocks as needed.
Comments
The performance gains, which will be most dramatic on large database servers, only materialize if the datafiles created exist on separate physical devices. When you perform this How-To on your own computer, you may not have the disk drives specified in the statements. You can modify the statements to work on your computer. The method presented in this How-To is a less reliable performance enhancement than creating new tablespaces on separate drives and placing objects in them manually. Under this methodology users and DBAs can take advantage of their specific knowledge of database usage patterns to control precisely which objects exist on which physical drives.
Another way to stripe tablespaces is to use operating system striping if your database server supports this capability. UNIX and NT database servers, for example, support disk striping at the operating system level. Indeed, if the SYSTEM tablespace is suffering from contention then this option may be the only viable choice. Operating system striping works best if the stripe size is a multiple of the batch size for full table scans. The initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT determines the record batch size for full table scans.
Table and index partitioning is worth mentioning again here. These capabilities in conjunction with a DBA’s intimate knowledge of data distributions afford Oracle8 users finer control over I/O balancing than operating system or database level striping.