Page 801
You have seen guidelines and clustering to help alleviate contention for hot tablespaces, but how do you tell which tablespaces are hot? You learned in Chapter 3 that if you have good quantitative estimates from your early modeling specifications, namely good transaction analysis figures, you have a head start and should use those figures to guide your initial physical layout.
If, for example, 3 tables out of 20 incur 150 transactions per second (tps) on average, and your remaining tables incur less than 30 tps, you can safely say that those first 3 tables are your hot tables, relative to the others, and therefore should be separated from the rest, along with their indexes. After you're past deployment, however, and into production usage, you need monitoring techniques that give you actual, low-level I/O figures, such as physical reads and writes per second, rather than high-level estimates, such as tps.
You have your usual two Oracle standbys to help do the monitoring: the V$ dynamic performance views and the report.txt output from properly "bookended" utlbstat.sql/utlestat.sql runs. These utilities are explained in Chapters 30 and 31. You can also use OEM/PP to examine FILE I/O. You need to examine V$DATAFILE and V$FILESTAT views:
SQL> SELECT NAME, PHYSRDS, PHYSWRTS 2> FROM V$DATAFILE DF, V$FILESTAT FS 3> WHERE DF.FILE# = FS.FILE#;
Also examine PHYS_READS and PHYS_WRITES from the I/O section of report.txt. The sum of the physical reads and the physical writes is the total I/O for that file or tablespace. Consider the sum of all the files for all the tablespaces by each disk. If using the V$ views method, select the information twice, bookending your statistics collection: first, after your application has reached peak capacity, and last, just before your application would be expected to decline. This will mimic the utlbstat.sql/utlestat.sql approach. Using either method, sum your beginning and ending total I/Os by disk. Subtract the beginning I/Os from the ending. Subtract your beginning time from the ending time of your statistics-gathering runs. Convert your time figure to seconds. Divide your deltas, the elapsed I/Os, by the elapsed time between the beginning and the ending.
Here's an example: Disk #3 contains Files #6 and #7 and shows beginning figures of 1,200 physical reads, 400 physical writes at 11:00 a.m. Its ending figures are 31,000 physical reads, 17,000 physical writes at 11:20 p.m. This is equal to
((31000_1200) + (17000_400) ) / (11:20_11:00) =
( 29880 +16600 ) / (20 minutes) =
(46400) / (1200 seconds) =
38.67 I/Os per second
This shows less than 40 I/Os per second for this disk, which is desirable because this is widely considered a saturation point for most modern disks. However, if all the other disks, for example, are much less than 40 I/Os per second, you would still want to offload this disk to help balance the I/O. You don't want simply to reduce each disk in isolation, but also to balance the I/O as much as possiblein effect, spread it out across all disks.
Page 802
NOTE |
Guideline: If any given disk approaches or exceeds 40 I/Os per second, you will likely need to offload some of its burden to other disks. In the best case, this will mean moving tables, or, worse, possibly moving tablespaces and their corresponding datafiles. After that, you would need to consider interleaving, clustering, striping, or, if necessary, buying more disks. Your goal is to even out all the disk I/Os so that they are each at 40 I/Os per second or less, given your application's peak load requirements, and to equalize all the disk I/Os per second as much as possible, so that all disks are evenly burdened.n |
Oracle blocks are organized by extents, and extents comprise tablespaces. They are the physical foundation of the storage of tablespaces. Hence, the more efficiently we can access the data in them and manage their growth, the better our performance will be.
From Chapter 29 you learned that dynamic allocation incurs too much overhead and hurts I/O performance. Static preallocation is preferred in almost all cases. You can statically preallocate a segment (table or index) or a tablespace. Generally, you choose one way or the other. If you preallocate the tablespace, you should already have a good idea of the sizes of your tables and how they will map to the extents. If you set tablespace defaults and then preallocate your tables, you have a little more flexibility in mixing tables of different extent requirements in the same tablespace. However, remember that you don't want to mix tables of too many different extent sizes (not more than three, as a general guideline), because this effectively engineers fragmentation, which is undesirable.
Rather than recover all the storage parameters given in Part VI, let's briefly look at the two different approaches for the same tablespace storing the same two tables. Suppose you have two tables requiring a maximum of 100MB each. Let's create a tablespace of 256MB to enable some additional growth, with a single datafile. The first approach, preallocating the tablespace, would look like:
SQL> CREATE TABLESPACE TS1 2> DATAFILE `/data1/file1.dat' SIZE 256M 3> STORAGE (INITIAL 100M NEXT 100M 4> MINEXTENTS 2); SQL> CREATE TABLE T1 (a number(9), ..., z number(9)) 2> TABLESPACE TS1; SQL> CREATE TABLE T2 (a number(9), ..., z number(9)) 2> TABLESPACE TS1;
The second approach, preallocating the tables, would look like:
SQL> CREATE TABLESPACE TS1 2> DATAFILE `/data1/file1.dat' SIZE 256M;
Page 803
SQL> CREATE TABLE T1 (a number(9), ..., z number(9)) 2> TABLESPACE TS1 3> STORAGE (INITIAL 100M NEXT 10M 4> MINEXTENTS 1); SQL> CREATE TABLE T2 (a number(9), ..., z number(9)) 2> TABLESPACE TS1 3> STORAGE (INITIAL 100M NEXT 10M 4> MINEXTENTS 1);
Preallocating the tables individually gives us a finer-grained control not only over the growth, but also over the performance associated with this tablespace. Why allocate 100MB if one of the tables happens to extend when we need only allocate 10MB? In general, the finer the unit of storage preallocated, the better the performance, for many of the reasons discussed in Chapter 29.
You've encountered striping before in the discussions regarding physical design and RAID in Chapter 3. This section shows you how to do manual striping, also known as Oracle striping.
Oracle striping is essentially a form of preallocation of extents so that each extent takes up (nearly) all its corresponding datafile, which is conveniently located on a separate disk. Of course, the drawback is that you should have a very good idea of what your maximum growth might be for the long term, or at least know what your peak size is for the medium term. However, the benefits are generally worth the effort, helping to parallel many of your I/O operations to your high-activity tablespaces. Suppose you have one very high activity table that you want to stripe. It has a peak size of less than 600MB, and you have three available disks with which to stripe it. The syntax would look like the following:
SQL> CREATE TABLESPACE TS1 2> DATAFILE `/data1/file1.dat' SIZE 200M, 3> DATAFILE `/data2/file2.dat' SIZE 200M, 4> DATAFILE `/data3/file3.dat' SIZE 200M; SQL> CREATE TABLE T1 (a varchar2(25), ..., z varchar2(25)) 2> TABLESPACE TS1 3> STORAGE (INITIAL 198M NEXT 198M 4> MINEXTENTS 3 PCTINCREASE 0);
Setting PCTINCREASE to 0 is necessary to override the default setting (50). Now you have a table stored across three different disks, by manually preallocating the necessary (maximum) extent sizes at slightly less (1 percent as a general guideline) than the corresponding datafile sizes. The table is effectively striped across the disks. The stripe unit is one Oracle extent of 198MB each. Compared to RAID striping, this is a very large stripe unit because RAID stripe units are usually measured in multiples of KB or less. However, it is nonetheless effective and requires no additional hardware or software. On the other hand, if you have incorrectly sized the table, or especially if the grow-shrink behavior of the table is erratic, the maintenance for this approach quickly becomes unwieldy. This is when you want to turn to RAID as a replacement for Oracle striping, if striping is still desired despite all else.