- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Row chaining
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-24-2005 10:17 PM
тАО11-24-2005 10:17 PM
Row chaining
Regards,
Mehul
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-24-2005 10:35 PM
тАО11-24-2005 10:35 PM
Re: Row chaining
There are database defragmentation procedures that may assist with this problem. They are probably available from metalink.oracle.com or technet.oracle.com
SEP
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-25-2005 01:59 AM
тАО11-25-2005 01:59 AM
Re: Row chaining
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-25-2005 03:13 AM
тАО11-25-2005 03:13 AM
Re: Row chaining
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-25-2005 06:43 PM
тАО11-25-2005 06:43 PM
Re: Row chaining
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-25-2005 06:47 PM
тАО11-25-2005 06:47 PM
Re: Row chaining
Regards,
Mehul
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО11-27-2005 12:44 PM
тАО11-27-2005 12:44 PM
Re: Row chaining
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