Identifying Oracle Tables with Chained Rows Oracle Tips by Burleson Consulting

The Remote DBA should always monitor and fix row chaining whenever feasible. However, chained rows will always exist in cases where row length exceeds the database block size. The identification of these tables that do have chained rows is important because of their use of RAW and LONG RAW data columns.

The excessive I/O caused by chained rows can degrade an entire database. To illustrate this problem, consider the following example. An application initially loads data rows that have many VARCHAR columns into 8K blocks. Because the VARCHAR columns are unpopulated, they only consume 80 bytes, and we can fit 90 80-byte rows onto our 8K block size, reserving 10 percent of the block for growth by setting pctfree=10, as shown in Figure 10-15.

Figure 10-93: Small unexpanded rows in a datablock

Several weeks after the initial load of these rows, an update job is run that expands the VARCHAR columns from 4 bytes each to 900 bytes each. At update time, Oracle checks to see if there is room for the row to expand on the data block. Since the pctfree=10 only reserved 800 bytes, Oracle must chain the row. Oracle retrieves the next data block, only to find that it also does not have room to accept the expanded row. This process continues until Oracle abandons using the freelists, and raises the high-water mark for the table and places the expanded row onto a fresh 8K block. In Figure 10-16, we can see that this causes a huge amount of I/O, both at update time and for subsequent retrieval operations. In practice, Oracle is intelligent enough to see that subsequent blocks will not have room for the expanded rows. After a few tries to chain onto subsequent blocks, Oracle gives up and raises the high-water mark to get a fresh block for the chained row.

Figure 10-94: Excessive row chaining with Oracle rows

So, what could we have done to fix this huge mess? Because we know that each expanded row consumes 900 bytes, we can adjust pctfree to only allow nine rows on each data block instead of the original 90 rows per block. This way, when the rows expand, there will be room on the data block without any rows chaining. Basically, we want Oracle to unlink the data block from the freelist after nine 80-byte rows (720 bytes) are added to the block, and about 7,500 bytes of free space remain. In Figure 10-17, setting pctfree=92 will make the block unlink from the freelist when it is more than 8 percent full.

Figure 10-95: Storing rows with room for expansion

NOTE: Please remember that when you do these calculations, a rough approximation is best. You cannot be exact because Oracle reserves space on each block for the block header and footer.

While this may look like space is being wasted on the block, what we are really doing is making room for the later updates. Now, when the subsequent rows are expanded, there is enough room for each row to expand on the original block, as shown Figure 10-18.

Figure 10-96: A data block after row expansion

Remember, chained rows are bad because they cause excessive I/O, and there are only two causes for chained rows:

•  pctfree is set too low to accommodate row expansion.

• The row length exceeds the database block size.

Identifying Chained Rows

The following script can be run to quickly identify tables in your database that contain chained rows. Note that the use of this script is predicated on the use of Oracle’s analyze command to populate the chain_cnt and num_rows columns of the Remote DBA_tables data dictionary view. Also note that this script does not include tables that contain RAW or LONG column datatypes, since such columns cause long rows that commonly span database blocks. Later in this chapter we will examine STATSPACK scripts that can be used to track chained rows over time.

chained_rows.sql

A properly tuned database should not have any row chaining, because the Remote DBA has set pctfree high enough to accommodate row expansion. Hence, the report shown here may be used as a database integrity check. Any excessive chained rows should be immediately investigated.

We can also extend the report to show tables that have long rows. Because of the large row length of some tables with RAW or BLOB columns, you will see chained rows because the row length will exceed the db_block_size, forcing the huge rows to chain onto many blocks.

chained_rows.sql

Next is an example of the output from this report. Note that many tables have an average row length greater than 4K. If your database supports a large block size, you can remove these chained rows by rebuilding the database with an 8K or 16K block size.

Again, we must remember that chained rows cause excessive I/O because multiple blocks must be read to access the data. When chained rows are found, the chains can be repaired by reorganizing the tables and resetting pctfree to a lower value. Now that we understand how to identify row chaining, let’s look at techniques for identifying sparse tables.

Identifying Tables with Long Rows

With the introduction of the RAW and LONG RAW datatypes, many Oracle tables have table row lengths that exceed the block size. Of course, these long rows will always chain onto several data blocks, and there is nothing that the Remote DBA can do about this chaining except to redefine the whole database with larger block sizes. However, RAW and LONG RAW datatypes can often be redefined in Oracle8, 8i or 9i as BLOB, CLOB, or NCLOB datatypes. When using these datatypes, Oracle will automatically move Large Objects (LOBs) into offline storage when the column length becomes excessive (greater then 4,000 bytes), thereby preventing chained rows.

Remember to use caution here. If a LOB storage clause is not specified and the LOB is too large then the LOB and its LOB index are collocated with the base table which will cause performance problems.

However, the Remote DBA still needs to know which tables in their database contain these large rows. The listing here shows an extract from an Oracle database showing all tables where the average row length is greater than one-fourth of the block size:

Here is a sample listing. Here we see all of the tables that have long row lengths.

For example, consider the Oracle table that has an average of 1,700 bytes per row, stored on a 4K block size. For the sake of a simple example, let’s say that 4K = 4,000, even though we know that it is really 4,096 bytes. After two rows are added to the block, 3,400 bytes are consumed, and 600 bytes remain in the block. If pctfree=10, the block must reach 3,600 bytes to be removed from the freelist, and the block will remain on the freelist even though it cannot hold another entire row. The third row will not chain, and Oracle will grab another free block from the freelist. This is illustrated in Figure 10-19.

Figure 10-97: A data block on the freelist without room to accept a row

Now that we see how to monitor the chaining for our tables, let’s revisit the sparse table concept and see how to detect sparse tables.

Identifying Sparse Tables

Sparse tables generally occur when a table is defined with multiple freelists, the table has heavy insert and delete activity, and the deletes are not parallelized. For example, a table with 20 freelists that has 20 concurrent insert processes is purged by a single process, causing all of the free blocks to go to only one of the 20 freelists. This causes the table to extend, even though it may be largely empty. Extension occurs because each freelist is unaware of the contents of other freelists.

A sparse table can usually be detected by selecting tables whose actual size (number of rows times average row length) is greater than the size of the next extent for the table. Of course, we must set the number of freelists to the number of simultaneous insert or update operations, so we cannot reduce the number of freelists without introducing segment header contention.

Identifying Chained Rows, Identifying Tables with Long Rows, Identifying Sparse Tables

(avg_row_len) in the data dictionary view and the number of rows (num_rows) with a weekly table analyze (i.e., analyze table xxx estimate statistics). The query here selects tables that contain multiple freelists with more than one extent where there is excessive free space:

sparse.sql

Next is an example of the output from this script. When we multiply the number of rows in the table by the average row length, we approximate the actual consumed size of the data within the table. We then compare this value with the actual number of allocated bytes in the table. The idea is that a sparse table will have far more allocated space than consumed space.

As we stated earlier, sparse tables are caused by an imbalance in multiple freelists, and are evidenced by tables that are continuing to extend although they are not very full. In the preceding example, we might take a closer look at the KOCLU, VBRP and TST99 tables because they have a high number of extents while they are largely empty.

Next, let’s take a look at a very important concept in object tuning. As we have repeatedly noted, anything that can be done to reduce I/O will improve performance, and resequencing table rows can result in dramatic reductions in expensive disk I/O.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

---

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.