Page 804
As you learned in Chapters 3 and 29, for datafiles supporting table or rollback tablespaces, RAID 3 or 5 is a good choice. RAID 0 is a good choice for indexes. RAID 1 is a good choice for redo logs. RAID may be used instead of Oracle striping, as just mentioned, but it may also be used in addition to Oracle striping. For example, in the previous example of a table striped across the datafiles, each of those datafiles might be striped on a individual RAID volume (set of disks), rather than simply being stored on a single (non-RAID) disk. This approach can provide substantial performance gains, but again requires complex and often constant DBA management, so choose your strategy wisely. Remember that although RAID 1, 3, and 5 can tolerate single disk losses, RAID is not a substitute for a good backup system!
One of your major goals is to avoid unnecessary fragmentation. One way to do this is by proper sizing, through estimation or measurement, and correct preallocation of extents. Also, avoid mixing tables of varying levels of activity, sizes, and known extent requirements in the same tablespaces. Extent fragmentation occurs when extents are allocated and deallocated within a tablespace. Extent fragmentation occurs in two ways: through free space fragmentation or through simple table fragmentation. Free extents are those that have never been allocated or that have been deallocated after their segment has been dropped. When a pattern of isolated pockets of free extents scattered throughout the tablespace occurs, this is known as swiss cheese fragmentation or bubbling. When a pattern of contiguous pockets of free extents occurs in runs throughout a tablespace, this is known as honeycomb fragmentation. In either case, you have free space fragmentation. When a table dynamically extends beyond its initial creation extents, this is said to be table fragmentation.
Although fragmentation is usually considered primarily a space issue, like all things in a database system, it has its effects on performance. Whether directly or indirectly, extent fragmentation triggers unnecessary dynamic extension, which you do not want occurring unnecessarily, from the standpoint of performance. If, in the lifetime of a tablespace, segment creation and dropping has created a swiss cheese effect, it detracts from performance by necessitating I/Os from multiple extents. Generally speaking, this usually isn't so bad. However, in the worst case, it can substantially slow down system performance. For example, suppose your PQO is configured based on precise data arrangements to help speed up full table (sequential) scans to a very large, hot table. After some time, that table becomes extent-fragmented. The PQO configuration becomes slower and slower because it was initially configured based on load-balancing the extents. In addition, what was initially a set of sequential reads from contiguous disk locations are now groups of sequential reads from various (random) disk locations, requiring more seek activity!
Use the following to help determine the degree of your table fragmentation:
SQL> SELECT SEGMENT_NAME, EXTENTS 2> FROM DBA_EXTENTS 3> WHERE EXENTS > 4 4> ORDER BY EXTENTS;
Page 805
TIP |
If any segments are returned, consider these too fragmented (unless you have planned these extents as part of Oracle striping or as part of a VLDB table, for example). To rectify this, do one of two things: |
Use the following to help determine the degree of your free space fragmentation:
SQL> SELECT TABLESPACE_NAME, COUNT(TABLESPACE_NAME) 2> FROM DBA_FREE_SPACE 3> ORDER BY TABLESPACE_NAME 4> GROUP BY TABLESPACE_NAME 5> HAVING COUNT(TABLESPACE_NAME) > 10;
NOTE Guideline: If any tablespaces are returned, consider these too fragmented. Issue the ALTER TABLESPACE <tablespace_name> COALESCE statement (for Oracle versions 7.3 and higher) to help rectify this. Rerun the query later. If some tablespaces are still returned and the fragmentation is high (greater than 5 ¥ the number of segments), you may want to follow the previous guidelines for each table, if feasible. Drop all the tables. Drop the tablespace. Delete the datafile(s). Re-create the tablespace. Re-create all the tables. Rerun your query to gather a benchmark against which to compare your future growth.n
TIP In order for SMON to be able to coalesce the free space of a tablespace, whether automatically or through the ALTER command, that tablespace must have its PCTFREE > 0. However, this may lead to undesirable, geometric growth of a table in the tablespace, unless the tables override the PCTFREE setting. Recommendation: Set PCTFREE to 1 for tablespaces and 0 for tables.Block fragmentation can occur two ways: through migrated rows or through chained rows. Migrated rows are rows that have been updated within a block, but have increased in size so that they exceed the free space remaining in its original block. They are reassigned to another block (off the freelist), and a pointer is maintained in the original block. Chained rows are those that are inserted or updated and whose size is such that it cannot fit in the free space of any block. They are split across blocks, using pointers. Chained rows can be avoided by proper sizing and setting of DB_BLOCK_SIZE. Migrated rows can be avoided by allowing enough update room (100 _ (PCTFREE + PCTUSED)). To determine the degree of your chained or migrated rows, run the following:
SQL> ANALYZE TABLE T1 COMPUTE STATISTICS; SQL> SELECT TABLE_NAME, CHAIN_CNT 2> FROM DBA_TABLES 3> WHERE CHAIN_CNT > 0 4> ORDER BY CHAIN_CNT;Previous | Table of Contents | Next