RE: ** chained rows fixing
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <MGogala@xxxxxxxxxxxxxxxxxxxx>, <ajoshi977@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 24 Sep 2005 10:48:22 -0400
Mladen:
Amazing! I had no idea that 85 percent free would magically shrink rows
larger than the usable space in a single block!
A Joshi:
I hope you've done the usual analysis that your exercise is actually
worthwhile, and of course if you are truly generating chained rows less than
the usable length of a block size on a recurrent basis causing you a
significant cost in transaction times in continued row fetching, then a
little extra engineering to prevent future problems may be in order.
Typically, but not always, rows tend grow in length early in their
existence. Then, either as details are filled in, some status changes, or
they simply age out of interest, the row length stabilizes. Some rows, of
course, are "born" full length, and some may be continually changing in
length forever.
Enabling row movement by status or age and keeping a relatively high pctfree
(or small allowed number of rows per block) in partitions that are subject
to rows of changing row length may be helpful to you.
There are any number of schemes for engineering this, and which particular
scheme is optimal for you will depend on the cost of implementation versus
the savings due to fewer continued row fetches (and possibly the potential
storage and table scan efficiency if "can't change in length any more" rows
are in high density and potentially compressed partitions versus partitions
of low density housing rows likely to be dynamic in length.)
Eliminating the practice of building row skeletons in bulk inserts followed
by "filling in the blanks" may also be helpful in preventing row chaining in
the first place. If the architecture of your applications requires such
skeleton row insertion, you *may* benefit from using default value strings
that are easily searched to pad the original insert to something like the
75th percentile of column length for mature rows (or 100% if the mature row
column length is a constant).
Of course if your row chaining is due to rows longer than the block size,
you might experiment with using a larger block size (with all due deference
to the mantra that multiple block sizes in a database won't help
performance, and understanding that that is often true.)
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Gogala, Mladen
Sent: Friday, September 23, 2005 3:08 PM
To: 'ajoshi977@xxxxxxxxx'; oracle-l@xxxxxxxxxxxxx
Subject: RE: ** chained rows fixing
Alter table AJOSHISTABLE rebuild tablespace system storage ( PCTFREE 85
PCTUSED 10);
That would guarantee that no chained rows will ever be found in this
table.
If anyone here is working for Seagate, Fujitsu or Maxtor, I will gladly
accept your donations.
--
Mladen Gogala
Ext. 121
----------------------------------------------------------------------------
--
From: A Joshi [mailto:ajoshi977@xxxxxxxxx]
Sent: Friday, September 23, 2005 1:50 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: ** chained rows fixing
I plan to increase the pctfree to prevent chained rows in future. Is there
any thing else I need to do. I assume a simple delete and insert will
eliminate chained row. Do I need anything else. Can some provide a generic
script for this. I have all the chained row in table chained_rows. Any other
guidelines. Thanks. Help is appreciated.
Other related posts:
