topic Re: Row chaining in Operating System - HP-UX

Row chaining

mehul_3 — Fri, 25 Nov 2005 06:17:29 GMT

If locally managed table space is enabled then will it eliminates row chaining issue.Our database has a locally managed tablespace(Oracle9i) and executed a sql-script to see any row chaing.I observed that there is a 4 to 5% of row chainging for some tables which is most oftenly used.Will it create any performance issue and need to eliminate the issue by importing table which has a row chaining.

Regards,
Mehul

Re: Row chaining

Steven E. Protter — Fri, 25 Nov 2005 06:35:37 GMT

Shalom mehul,

There are database defragmentation procedures that may assist with this problem. They are probably available from metalink.oracle.com or technet.oracle.com

SEP

Re: Row chaining

Hein van den Heuvel — Fri, 25 Nov 2005 09:59:20 GMT

4-5% row chaining does not sound like a major performance issue to me. Surely there is bigger fish to fry.

Best I understand it, row chaining has nothing to do with whether tablespaces are locally managed or not. That only determines where/how the administration for free pages is handled. Row chaining is all about block sizes and PCTFREE no?

When I feed your opening line: "locally managed table space is enabled then will it eliminates row chaining" into google is comes up with several relevant articles for your question. I suggest you check some of those out first, and then come back with detailed question. For example:

a) http://www.embarcadero.com/resources/tech_papers/managingspace.pdf

b) http://www.dbasupport.com/oracle/ora9i/rowchaining.shtml

hth,
Hein.

Re: Row chaining

Eric Antunes — Fri, 25 Nov 2005 11:13:01 GMT

Hi mehul,

Chaining is a serious problem and hard to solve...

Chaining happens when a row that fitted into one block (at insert time) needs, after been updated, 2 blocks: this will strongly impact performance for some tables. You can "feel" this impact if you try to update rows from a highly chained table...

Here are the thing you need is to:

1 - Be sure you have the chained_rows table in SYSTEM: if you don't have this table, run @$ORACLE_HOME/rdbms/admin/utlchain.sql

2 - Execute the following query (never run the analyze table on SYS and SYSTEM tables):

select 'ANALYZE table '||owner||'.'||table_name||' LIST CHAINED ROWS;'
from all_tables
where owner not in ('SYS', 'SYSTEM')
order by owner;

3 - Execute the result of above query

4 - Check the chained_rows table:

select count(*), table_name
from system.chained_rows
group by table_name
having count(*) > 50
order by 1 desc;

5 - For the tables above you need to check the percentage of CHAINED ROWS/TOT ROWS and decide if you have a chaining situation (more than 5%?)

6 - Avoiding future chaining:

In problematic tables increase PCTFREE, for example, from 20 to 30 (you may need to decrease PCTUSED since PCTUSED+PCTFREE cannot exceed 100):

ALTER TABLE .
PCTFREE x
PCTUSED y

7- Solving the present chaining rows:

a) BE SURE no one is logged to the database
b) BE SURE you have a good backup
c) Do FOR EACH TABLE:

alter table .
disable all triggers;

drop table migrated_rows; -- the first time this will fail

create table migrated_rows as
select orig.*
from . orig, system.chained_rows cr
where orig.rowid = cr.head_rowid and
cr.table_name = '';

select count(*) from migrated_rows; -- Confirm this is the same count that the one in chained_rows for this table

delete from .
where rowid in (select head_rowid from system.chained_rows where table_name = '');

commit;

insert into .
select * from migrated_rows;

commit;

alter table .
enable all triggers;

PS: this should be a database thread.

Best Regards,

Eric Antunes

Re: Row chaining

Yogeeraj_1 — Sat, 26 Nov 2005 02:43:12 GMT

hi mehul,

Changes in Database storage parameters like pctfree/pctused can avoid row chaining/migration in the future. You will need to make some calculations of course.

Actually, row chaining can happen without updates: all you need is a few of the rows to be larger than the block size.

hope this helps too

kind regards
yogeeraj

Re: Row chaining

mehul_3 — Sat, 26 Nov 2005 02:47:58 GMT

I can't change the size of pctused/pctfree as I used locally managed tablespace.

Regards,
Mehul

Re: Row chaining

Ariel Cary — Sun, 27 Nov 2005 20:44:00 GMT

Mehul,

From what I read, I think you meant ROW MIGRATION, which is different from ROW CHAINING. While the latter is almost always unavoidable (most times it has to do with LOBs), the former can be solved for example with the nice procedure provided by Eric. In particular, I believe it would be effective for the 4-5% of row migration you have; depending on the size of the table, you can also consider 'alter table move' but be advised you have to rebuild the associated indexes afterwards since they'll become unusable. Bottom line is that you need to schedule a downtime. However, I wouldnt do that unless I find that 4-5% is substantially affecting performance.

On the other hand, in locally managed tablespace you CAN define PCTFREE, but you CANNOT set PCTUSED (that's a common misconception). So, it would be more important to adjust the PCTFREE value of the segment to avoid row migration to happen in new blocks (extents).

Regards,

Ariel