Previous | Table of Contents | Next

Page 795

Chapter 32

Tuning I/O

In this chapter

Page 796

In general, tuning I/O may be thought of simply as an extension, or refinement, of physical design. When you initially do your physical design, you are working from your best quantitative estimates. If you reach the stage in which you must tune I/O because performance is unacceptable, you work from actual measurements of your application as it runs for some peak periods of time. Tuning I/O in Oracle consists mainly of tuning the underlying physical structures of the segments (tables and indexes) that make up a database. These include tablespaces—made up of extents, in turn made up of blocks—and datafiles, which are the operating system (OS) entities supporting these Oracle physical structures. These and other Oracle structures are covered in depth in Part VI, "Managing the Oracle Database," so this chapter won't go too deeply into their definitions and functions in this chapter, except to reemphasize certain relevant concepts along the way.

I/O (Input/Output) means reads and writes. In database terms, more specifically for DML, SELECTs are the reads and INSERTs, UPDATEs, and DELETEs are the writes. A DDL, (a CREATE, ALTER, or DROP) is always a writing operation. Hence, reading from and writing to any Oracle structure is considered an I/O issue for the purposes of this chapter. For example, issuing a SELECT statement generates a read from one or more index and/or from one or more table. It also generates some minimal redo log information. Issuing an INSERT, UPDATE, or DELETE will generate reads and writes from one or more index and/or table, roll back data, and redo log information. This last fact leads to a more subtle, general fact: Reading is simply reading but writing is reading and writing. How can you write information to a block that has yet to be read into the database buffer cache? Aside from the direct path capability of SQL*Loader, you can't.

So, when you consider tuning I/O in Oracle, not only do you consider tuning tablespaces, extents, blocks, and datafiles, but you also consider tuning rollback segments and redo logs (because user DML generates all these kinds of I/O). Hence, the following sections cover each of these kinds of I/O: tablespaces and datafiles, extents and blocks, rollback segments, and redo logs. Similar to previous tuning chapters on memory and application issues, you will encounter overlaps of tuning I/O with tuning memory and tuning I/O with tuning the application.

Remember, any I/O operation requires reading Oracle data blocks into the database buffer cache first, before any further activity can take place. This is an example of where tuning memory and tuning I/O overlap. Let's consider a different idea. Suppose you have an OLTP application that heavily reads and writes from only a few tables out of several. Those tables need to be placed in separate tablespaces, and further, on separate disks. This is an example of where tuning the application and tuning I/O overlap. A high-level look at tuning tablespaces and datafiles is a good place to start your study.

Tuning Tablespaces and Datafiles

As you know from Part VI, tablespaces are Oracle structures for physical storage. A tablespace stores a collection of segments: tables and indexes. A tablespace maps to one or more datafiles at the OS level. You learned the concept of application typing in Chapters 3, "Physical Database Design, Hardware, and Related Issues," and 30, "Application Tuning," and how this affects your physical design. Recall that in order to have a proper physical layout, tablespaces

Page 797

(and their datafiles) are separated as much as possible on different disks. Separating tablespaces on different disks can eliminate, or at least reduce, disk contention.

Disk contention occurs when you have multiple users or programs attempting to access the same disk at the same time. For example, if you have two tables that must be joined together very often, such as DEPARTMENTS and EMPLOYEES tables, their tablespaces should usually be separated on two different disks. So should their indexes, because attempting to access either the tables or the indexes on the same disk results in the same contention for the resource. Ideally, then, these four segments will exist in four tablespaces residing on four different disks. Other methods, such as clustering, allow these segments to coexist on the same disk. You explore Oracle's version of the clustering technique, referred to as one of the exotic solutions, in Chapter 29, "Performance Tuning Fundamentals."

Partitioning Tablespaces

As you learned in Chapters 3 and 29, you want your Oracle physical layout so that

Remember that on initial creation, a user's default tablespace and default temporary tablespace point to SYSTEM. Change these if they were not CREATEd properly by doing the following:

SQL> ALTER USER <user>
       2> DEFAULT TABLESPACE <tablespace>
     3> TEMPORARY TABLESPACE TEMP;

To determine whether DATA (or INDEX) tablespaces can coexist, you need to classify tables by their level of activity, in addition to typing the application.

For example, suppose you have a DSS application, in which during normal operation all tables are read-only (except when they are bulk loaded). Does this mean that you can put all the tables on only one disk? Not at all! In fact, consider that there might be, for the sake of illustration, 10 tables that are heavily read out of, for example, 40 tables. Of those 10, 7 are accessed concurrently, and 4 are almost always joined in usage. You should ideally have at least 9 disks for the data alone!

How do you arrive at this figure? You need 7 separate disks, because 7 are accessed concurrently, including the 4 tables frequently being joined. You need at least one more for the remaining three (10_7) heavily read tables that are not concurrently accessed, and at least one more for the remaining 30 (40_10) tables—that is, if one disk could hold all those 30 tables! In any case, like application typing, this is activity classification: classifying tables by their frequency of access, or in other words, how active they are. If a table is very active,

Previous | Table of Contents | Next