0579-0581

Previous Table of Contents Next

Page 579

2.  ( select head_rowid from chained_rows where table_name = `TABLE' ); 

3. Delete the chained or migrated rows from the original table.

    delete from table where rowid in ( select head_rowid from chained_rows where table_name = `TABLE'); 

4. Transfer the rows in the new table back to the original table.

    insert into table select * from new_table; 

5. Drop the new table.

    drop table new_table; 

You should analyze the table again. Rows that remain are chained rows; rows that were removed are migrated rows. To remove the chained rows, re-create the table with a higher pctfree value. The steps are

1. Perform an export on the database table.

    exp file=/tmp/filename compress=y indexes=y grants=y constraints=y tables=sample userid=user/password 

2. Drop the current database table with the drop table command.
3. Re-create the database table with a higher pctfree value.

    create table sample   ( first_column  VARCHAR2(10),     second_column VARCHAR2(10),     . . .   ) storage (initial 1024K next 1024K minextents 1          maxextents 249 pctfree 90); 

4. Import the data into the re-created table with the imp utility.

    imp file=/tmp/filename full=y ignore=y userid=name/password 

TIP

Always be sure to take a database backup before performing operations that require you to make changes to database objects.

Analyze the table again. If chained rows still exist, they might be impossible to eliminate without re-creating the database with a new database block size . It is sometimes impossible to eliminate all chaining from a database, especially in databases that store information in LONG or RAW column types.

Page 580

Dynamic Extension

Whenever you create a table, you must decide how large it should be, how fast it should grow, and how often its data will change. Unfortunately, the only way to gauge a table's growth is to rely on experience and trends. For that reason, you must deal with dynamic extension.

Every database object is created with an initial size. Information is added to the table or index until there is no more room left in the initial space allocation. Then the size of the table is incremented by a fixed amount. This is called dynamic extension.

NOTE

Increases in table or index size through dynamic extension do not usually occur in contiguous segments of storage.

Allocation is based on the arguments passed in the storage clause of the create table or create index SQL commands. If no storage clause is specified, the default storage parameters defined in the tablespace definition are used. Consider the following statement:

 storage (initial x next y minextents a maxextents b pctincrease m) 

The arguments control how large each extension is and the size to which the object is capable of extending. To determine the initial size of the database object, multiply the size of initial extent by minextents. When the amount of data in the table and the index exceeds the initial allocation, another extentof size nextis allocated. This process continues until the amount of free space in the tablespace is exceeded or until the number of extents is exceeded. Be careful not to set the value for PCTINCREASE too high. When using PCTINCREASE, keep an eye on the size of the next extents when the table or index grows. You can always adjust these parameters later.

Dynamic extension causes problems with database performance; recursive calls are generated because of requests from the data dictionary that are not currently in cache. Use the following query to determine whether excessive dynamic extension is occurring:

 select owner, segment_name, sum(extents) from dba_segments where segment_type in (`TABLE', `INDEX') group by owner, segment_name order by owner, segment_name / 

Monitor the extents closely to ensure that the number of extents is not too close to the value set in maxextents. It is necessary to re-create the table periodically with a single extent. Unless striping a table by intentionally forcing it to take extents, table extents should be closely monitored and kept as few as possible to maximize query performance on a table. The steps for re-creating a table are as follows :

Page 581

1. Export the database table with the exp utility. Don't forget to export indexes and constraints.

    exp file=/tmp/filename compress=y indexes=y grants=y constraints=y tables=sample userid=user/password 

2. Drop the database table with the drop table command.
3. Re-create the table with an initial extent that is large enough to hold the entire table.

    create table sample   ( first_column  VARCHAR2(10),     second_column VARCHAR2(10),     . . .   ) storage (initial 50M next 1024K minextents 1          maxextents 249); 

4. Import the data with the imp utility:

    imp file=/tmp/filename full=y ignore=y userid=name/password 

Indexes are much simpler. The steps are

1. Drop the index with the drop index command.
2. Re-create the index with the create index command. Make sure that the initial extent is large enough to accommodate the entire index.

If you do not resize tables and indexes periodicallyand correctly a table can max out, meaning it has extended to the size dictated by the maxextents storage parameter. To fix it, issue the following SQL command:

 alter table table_name (storage maxextents extent_size); 

The maximum extent for a database object is determined by the block size and the operating system. Consult the Oracle Installation and Configuration Guide to determine what limits are imposed. Not knowing the maximum extension of a database object and not adequately monitoring database objects as they approach this size can effectively shut down the database for production users.

Fragmentation

Fragmentation of the tablespaces on which the database objects reside also reduces performance. Tablespaces are initially allocated as contiguous units of storage. Likewise, database objects are created within the tablespaces as contiguous units of storage. As objects extend these blocks, however, they are generally not contiguous with the previous blocks of data.

As tables are created, dropped, and extended, the number of contiguous blocks of free space can increase. For example, a tablespace might have 1MB of free spacebut all in 1KB blocks. If you issued a create table command with an initial extent of 50KB, it would fail because it could not allocate a contiguous amount of data in which to create the table. This is a common scenario in environments in which tables or indexes are frequently added and dropped.

Previous Table of Contents Next