Home » RDBMS Server » Performance Tuning » Row Chaining Issue
Row Chaining Issue [message #221786] Wed, 28 February 2007 07:14 Go to next message
Messages: 41
Registered: August 2006

One of my table has got 3lakhs rows affected by row chaining.I have browsed net for the steps that involes to remove the row chaining.They have advised to move the table to the new segment.
alter table move; 

After that its showing no rows affected by row chaining.I have tested this on the Test DB.

Consider the live database,every time we cant move the table to remove the row chaining ,its not advisable also.Because one cant permanently avoid RM/RC issues.If this is the case,how could I remove the row chaining? Also if we move the table from one segment to an another new segment,it will create more undo data and more archive logs(Since its recreating the table in the new segment).

I had run the DB health check report on my live DB,it shows the Buffer Cache Hit Rate as 55% . Iam suspecting the row chaining issues for this low Buffer Cache Hit Rate,am I right ?please clear my doubt. If row chaining is not the reason for low Buffer Cache Hit Rate,how do I increase the Buffer Cache Hit Rate? Help needed.


Re: Row Chaining Issue [message #221794 is a reply to message #221786] Wed, 28 February 2007 08:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Buffer cache hit ratio based tuning is flawed.
Hit ratios are not a measure of tuning.
Search the forum more details. In fact you can pragmatically get
the buffer hit ratio you want !
Regarding row chaining, as you said it will be your life long process. In many cases you just cannot avoid. A proper table/block design might help (if a row is bigger than the block, the row will be chained).
Search the forum. More threads on this.

[Updated on: Wed, 28 February 2007 08:06]

Report message to a moderator

Re: Row Chaining Issue [message #221849 is a reply to message #221786] Wed, 28 February 2007 12:37 Go to previous messageGo to next message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just to follow up on this (since I apparently have some spare time for a change), yes there is plenty to search for here and in oracle documents on this.

But in general, if your row is bigger than your block, then there is nothing you can do about it. That is one cause.

The other is if you do updates to a row that causes it to grow in length such that it can no longer fit in the original block. If this is your situation, then you can consider increasing the pctfree attribute of your table. This will reserve space specifically to accomodate (at least some of) this growth.
Re: Row Chaining Issue [message #221910 is a reply to message #221849] Thu, 01 March 2007 00:18 Go to previous messageGo to next message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Heres the link for fixing chained rows without rebuilding the table

Ross Leishman
Re: Row Chaining Issue [message #221941 is a reply to message #221786] Thu, 01 March 2007 03:29 Go to previous messageGo to next message
Messages: 41
Registered: August 2006
Thanks to all who have responded to my post.
My question is to Ross Leishman,the link you have attached is good.
They have given the steps for Row migration,i really didnot find solution to Chaining issue.
Regarding Chaining, here is the quotes from that link:


"You can eliminate chained rows only by increasing your data block size. It might not be possible to avoid chaining in all situations. Chaining is often unavoidable with tables that have a LONG column or large CHAR or VARCHAR2 columns."

We cannot alter the oracle block size after the database creation.
And also i dont have LONG column in my table.
The conclusion is we cannot avoid the row chaining.But the database performance???
Regarding the row migration, the steps just move the affted rows from the original table to the temporary table and drops those rows from original table and then moves back the rows from the temporary rows to original table.Is that possible to remove the migrated rows just by moving the rows and copy back to the original.
Whats the concept behind this?can any one explain me?
Re: Row Chaining Issue [message #222023 is a reply to message #221941] Thu, 01 March 2007 08:34 Go to previous message
Messages: 118
Registered: February 2007
Senior Member
The concept is that if you have splitted rows then by deleting these rows from original table and inserting them back you insert them in one block, of course if the row can fill in one block.

But the question is another - why do you need to do that? Are you sure you are suffering from chained rows? If yes, how you realized and measured that? Probably you are suffering from compulsive tuning disorder? Here is a little insight about that:

Gints Plivna
Previous Topic: setting db_file_multiblock_read_count
Next Topic: parallel DML
Goto Forum:

Current Time: Sat Oct 22 06:49:24 CDT 2016

Total time taken to generate the page: 0.09191 seconds