High-Performance Oracle Database Applications:Performance And Tuning For Distributed Oracle Databases

Table Fragmentation

Again, it needs to be emphasized that table fragmentation does not cause performance problems. Rather, the row chaining that often accompanies table fragmentation will seriously impede performance. In fact, some Oracle DBAs have reported that extended tables (without row chaining) sometimes outperform tables that reside in a single extent. For detecting row chaining, see the next section.

Listing 7.7 shows a simple script can be run to see the number of times that a table has extended.

Listing 7.7 tblexts.sql lists all tables with more than 10 extents.

SET PAUSE OFF;
SET ECHO OFF;
SET LINESIZE 150;
SET PAGESIZE 60;

COLUMN c1  HEADING "Tablespace";
COLUMN c2  HEADING "Owner";
COLUMN c3  HEADING "Table";
COLUMN c4  HEADING "Size (KB)";
COLUMN c5  HEADING "Alloc. Ext";
COLUMN c6  HEADING "Max Ext";
COLUMN c7  HEADING "Init Ext (KB)";
COLUMN c8  HEADING "Next Ext (KB)";
COLUMN c9  HEADING "Pct Inc";
COLUMN c10 HEADING "Pct Free";
COLUMN c11 HEADING "Pct Used";
BREAK ON c1 SKIP 2 ON c2 SKIP 2

TTITLE "Fragmented Tables";

SELECT  substr(seg.tablespace_name,1,10) c1,
        substr(tab.owner,1,10)           c2,
        substr(tab.table_name,1,30)      c3,
        seg.bytes/1024                   c4,
        seg.extents                      c5,
        tab.max_extents                  c6,
        tab.initial_extent/1024          c7,
        tab.next_extent/1024             c8,
        tab.pct_increase                 c9,
        tab.pct_free                    c10,
        tab.pct_used                    c11
FROM    sys.dba_segments seg,
        sys.dba_tables   tab
WHERE   seg.tablespace_name = tab.tablespace_name
  AND   seg.owner = tab.owner
  AND   seg.segment_name = tab.table_name
  AND   seg.extents > 10
ORDER BY 1,2,3;

Listing 7.8 shows the listing from this script.

Listing 7.8 The results from the tblexts.sql script.

SQL> @tblexts

Thu Mar 14
                                                             page    1
                                                        Fragmented Tables
Table          Table        Size  Alloc Max Init    Next    Pct Pct  Pct
space   Owner  Ext          (KB)  Ext   Ext Ext(KB) Ext(KB) Inc Free Used
------- ----- -----------   ----- ----- --- ------- ------- --- ---- ----
MPTV    MPTVI  UST_CAT      5800   58   249  100    100      0   20   40
SYSTEM  SYS    AUD$         1724   11   249   12    840     50   10   40
               CHAINED_ROWS  684   57   249   12    12       0   10   40
               SOURCE$      1724   11   249   12    840     50   10   40

Row Fragmentation

Row fragmentation is one of the most problematic things that can happen to an Oracle system. This commonly occurs when an SQL UPDATE operation lacks sufficient room to expand the size of a row on its data block. When this happens, the row must extend onto the next available data block, causing an extra I/O when the row is accessed.

In Figure 7.7, the next five blocks are filled with rows. When an SQL update adds 1,500 bytes to row 1, the database chains to the next block. Finding no space, it chains to the next block, and the next block, before finding 1,500 bytes of free space on block 4. The fragment is stored in block 4, and a chain is established from the block header of block 1 to point to the next block header, and so on, until the fragment is located. Most databases have some type of “free list” at each block header to determine the total available space on each block.

Figure 7.7 An example of Oracle row chaining.

Any subsequent retrieval of row 1 will require the database to perform four physical block I/Os in order to retrieve the entire row. Since I/O time is usually the largest component of overall response time, this type of row fragmentation can greatly reduce performance.

Several preventative measures can be taken to avoid this situation. If the row will eventually contain all of its column values, and the values are of fixed length, the table could be defined with the parameter NOT NULL. This reserves space in the row when it is initially stored. If the row contains variable-length columns, then the PCTFREE parameter is increased to reserve space on each block for row expansion. By the way, this issue is not confined to Oracle. Most databases offer a utility that can be run periodically to check for row fragmentation. If fragments are found, the data must be exported to a flat file, the table redefined with different storage parameters, and the tables repopulated from a flat-file.

Listing 7.9 contains a script called chain.sql that will detect the number of chained rows for all Oracle tables. This script is a series of queries that acquire a list of all Oracle tables and write the ANALYZE TABLE syntax to an intermediate file, which is then executed to count the total number of chained rows for all tables. A chained row occurs when an SQL UPDATE operation has increased the size of a row, causing it to fragment onto another data block.

WARNING: Listing 7.9 creates table statistics that may force the use of the cost-based analyzer unless the database init.ora file parameter is set to RULE instead of CHOOSE.

Table of Contents