9.2 How do I…Detect row chaining and migration in tables?Problem
I have reason to believe that my database is generating more I/O than it should. How can I find out whether particular tables are suffering from row chaining or row migration?
Technique
Row migration occurs when a database block does not contain enough free space to accommodate an update statement. Suppose, for example, that block number AAAAAB contains two rows. If a user issues an update statement that updates one of these rows, and the updated row no longer fits in block number AAAAAB, Oracle8 migrates the row. That is, the server moves the row to another block and maintains a pointer to the new block in the row’s original block.
Row chaining, in contrast, occurs when no single database block is large enough to accommodate a particular row. This is common when a table contains several large CHAR or VARCHAR2 data types. If the database block size is 2048 bytes and a row is 3000 bytes long, for example, this row will not fit in any single database block; it will reside in multiple database blocks. An unpleasant side effect of both chaining and migration is that the Oracle8 server must read more than one database block to read a single row.
The ANALYZE command can reveal, on a table-by-table basis, which tables contain migrated and/or chained rows. A table to hold information about these migrated/chained rows must exist prior to using the ANALYZE command for this purpose, and you will build that table first.
Steps
1. Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the UTLCHAIN.SQL script to create the CHAINED_ROWS table. This script is supplied by Oracle corporation; under Windows NT it resides in the \orant\rdbms80\admin subdirectory. The results of this operation appear in Figure 9.3.
2. Use the START command to load and execute the CHP9_3.SQL script, which appears in Listing 9.1.
Listing 9.1 Creating the CHAIN and MIGRATE tables in the CHP9_3.SQL script
1 DROP TABLE migrate09;
2 --
3 CREATE TABLE migrate09
4 (attr1 char(900),
5 attr2 char(200))
6 PCTFREE 0
7 PCTUSED 99;
8 --
9 INSERT INTO migrate09 (attr1) VALUES ('1');
10 INSERT INTO migrate09 (attr1) VALUES ('2');
11 --
12 UPDATE migrate09 set attr2 = 'a';
13 --
14 DROP TABLE chain09;
15 --
16 CREATE TABLE chain09
17 (attr1 char(2000),
18 attr2 char(2000));
19 --
20 insert into chain09 values ('a', 'b');
In lines 3 through 7, the script creates a table called MIGRATE09. Script lines 16 through 18 create a second table called CHAIN09. The script also inserts two rows into CHAIN09 and one row into MIGRATE09.
The two tables CHAIN09 and MIGRATE09 are shown in Figure 9.4 and are composed of attributes of data type CHAR. Oracle8 will right-pad fields of this type with blanks so that they assume their maximum length. The values of PCTFREE and PCTUSED are not very realistic, but they do help to induce row migration for the purposes of this example.
3. Use the START command to load and execute the CHP9_4.SQL script to determine whether any chained or migrated rows exist in the CHAIN09 or MIGRATE09 tables.
How It Works
Step 1 creates the CHAINED_ROWS table in the WAITE account’s default tablespace. Step 2 creates two tables that will subsequently suffer from row chaining, row migration, or both. In step 3, the CHP9_4.SQL script issues the ANALYZE command with the list chained rows into clause to insert the ROWIDs of any chained or migrated rows in the tables CHAIN09 or MIGRATE09 into the CHAINED_ROWS table. The final command in the script queries the CHAINED_ROWS table, exposing any chained or migrated rows. The script and its output appear in Figure 9.5.
The script results in Figure 9.5 indicate that there is one chained or migrated row in both the CHAIN09 and MIGRATE09 tables.
Comments
This How-To indicated rows in the CHAIN09 and MIGRATE09 tables that Oracle8 either chained or migrated. The next logical question is “Are the rows described in CHAINED_ROWS really chained rows, or might they be migrated rows?” More compelling still is this question: “How can I resolve this situation?” How-To 9.3 is the place to look for answers.
You can also detect the presence of chained rows by querying the system statistic called “table fetch continued row” in the V$SYSSTAT dynamic performance table. This table and a query to check for chained-row activity are displayed in Figure 9.6.
Running this query repeatedly and examining its results over time will help you ascertain that there is chained-row activity in the database. Unfortunately, the query only indicates that somewhere in the database, some chained rows exist, and some user or application has read them. In other words, with respect to row chaining, the V$SYSSTAT table only indicates that you have some more work to do. If you have no idea where the chained rows exist, then the best plan of attack is to use the ANALYZE command with the list chained rows into clause on a regular basis and examine the CHAINED_ROWS table.
Proactive DBAs attempt to prevent chaining and migration. This is easy to say and hard to do unless the DBA has intimate knowledge of the data that will eventually reside in the database. Skillful use of the PCTFREE parameter in conjunction with awareness of the row changes caused by typical update statements can minimize or eliminate row migration. The difficult task is to quantify the typical update statement. A DBA seeking to prevent row migration must be armed with similar knowledge. Specifically, during database creation it must choose the database block size to accommodate the largest row that will ever be stored in any table. This is a tall order for most production database administrators.