Page 490
Tables are generally the starting point for most storage management; they act as a yardstick of sorts that determines the storage needs of most other database components. For this reason, understanding the nature of the data contained in database tables (data types, number of null columns, and so on) will prove to be very beneficial to the savvy DBA.
A table's size increases as new rows are inserted, or existing rows are updated with larger values. The average length of a row and the number of rows are the main factors influencing a table's size.
Sizing a table is a conceptual task and should be done in the design phase, before the table is created. Issues, such as expected growth and storage parameter values, should be handled when deciding sizing parameters.
Consider the following CREATE TABLE example:
create table employee ( /* PART 1 */ emp_no char(4), emp_name varchar2(30), age number, dept_id char(4) ) tablespace EMPLOYEE_TS /* PART 2 */ storage ( initial 10M next 10M pctincrease 0 minextents 1 maxexents 50 freelists 1) pctfree 10;
The CREATE TABLE statement is made up of two parts: Part 1 (as labeled) is the mandatory table definition, where you indicate the key attributes such as columns, datatypes, and constraints. Part 2 deals with sizing issues related to the table and is what we are interested in. Let's look at an explanation of these sizing parameters:
Page 491
Indexes can be the most difficult type of segment to manage. Many application programmers (as well as DBAs) try to solve any database performance issue by asking for more indexes. Remember that more indexes almost always hurts update performance and may not always provide better SELECT performance. Keep close tabs on your indexes and remove any that are not being used.
Many unnecessary indexes can, over time, creep into your database and cause performance problems. Oracle almost always prefers performance to disk space thriftiness. Keep a record of every non-primary key index and the application the index has been created for, as well as the particular function or problem the index was created to solve. It is not unusual for several indexes to be created for a specific application. Without documentation, you may find yourself supporting indexes created for applications that no longer exist.
Page 492
Like rollback segments, the temporary tablespace(s) in an Oracle database require special monitoring. Temporary segments are held only as long as the transaction creating the segment is active. Therefore, high water marks are our metric, rather than how much space is being used at any one point in time. The goal is insuring that enough space exists for the largest transaction the system will have to process.
One of the most common problems facing database administrators deals with fragmentation of database objects. Fragmentation wastes space, causes performance problems, and makes administration of database objects more difficult than necessary. The term database fragmentation, however, is a class of problems, rather than a problem in and of itselfthere are several problem areas that fall under the generic term of database fragmentation, including fragmented database objects, fragmented tablespaces, chained rows, and migrated rows.
Database fragmentation is the result of rows being inserted, updated, and deleted, and objects being created and dropped. Thus, a newly created database is not fragmented. It's only after you've run one or more active applications on your database that you begin to see evidence of database fragmentation.
In the following sections, we'll look at four types of database fragmentation and outline ways you can fix your database, as well as prevent this from happening in the first place.
Tablespaces become fragmented through the erratic and unplanned dropping and re-creating of database objects within the tablespace. Tablespace fragmentation occurs when bubbles of free space become trapped between used extents in a tablespace. This happens when objects that reside in the tablespace are dropped. These bubbles of trapped space can be reused, but only if an object is created that fits in the space left by the object.
NOTE |
Tablespaces do not become fragmented through any row-level activity. This is a commonbut completely falsemisconception. |
For example, assume there are four tables in a tablespace: customer, employee, stock, and company. Each of these objects is comprised of one extent of 10MB each. The configuration of this tablespace will look like Figure 21.2. Note the free extent of 10MB at the end of the tablespace, and how closely packed these objects are. This is the goal, and what we should attempt to obtain at the end of the day.
Of course, we can't have this situation for long. Assume that due to performance requirements, we have to move the customer table into a different tablespace. The tablespace will now look like Figure 21.3. Notice the 10MB hole in the tablespace. If we need to create another object with a size less than or equal to 10MB, there is no problem. But assume we need to create a