RE: ** chained rows fixing

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Gogala, Mladen   Sent: Friday, September 23, 2005 3:08 PM   To: 'ajoshi977_at_yahoo.com'; oracle-l_at_freelists.org   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_at_yahoo.com]   Sent: Friday, September 23, 2005 1:50 PM   To: oracle-l_at_freelists.org
  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.

--

http://www.freelists.org/webpage/oracle-l Received on Sat Sep 24 2005 - 09:55:01 CDT