18.7 Chained Rows Ratio

 < Day Day Up > 



Chained rows in Oracle Database are as a result of row migration or row chaining. Row chaining occurs when a row is too large for a single block and thus the row is "chained" or spread across multiple blocks. Row migration occurs when updating increases the row size, prohibiting the whole row from fitting in a single block. Thus, the row is migrated or moved elsewhere leaving only a pointer in the original block. It is possible that a row migration can lead to a row chain as well if the row becomes large enough. A lot of row chaining in a database is a big problem. Row chains are removed by table reconstruction, recreating the table, or by using export and re-import. Row chaining and migration can be minimized by leaving more empty space in each block when first inserting rows: set PCTFREE to a higher value. PCTFREE leaves empty space in a block by reserving more space for future updates. The downside to increasing PCTFREE is that rows will be less compacted and there will be fewer rows per block on average. High row to block density leads to faster access time because there are more rows read per block. The approach is dependent on requirements and behavior required by applications.

SELECT 'Chained Rows ' "Ratio" , ROUND( (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch continued row') / (SELECT SUM(value) FROM V$SYSSTAT    WHERE name IN ('table scan rows gotten', 'table fetch       by rowid')) * 100, 3)||'%' "Percentage" FROM DUAL;     Ratio            Percentage --------------   ---------- Chained Rows     0%

The chain count in my Accounts schema is negligible.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net