Row chaining
Title: RE: Running multiple instances on a [large] server I see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak
RE: Row chaining
ak Smart-alek answer: Apply one of the methods to eliminate migrated rows, and if the problem doesn't go away, you know you have some chained rows ;-) Chained rows are a little difficult to diagnose. Look at the value for avg_row_len - is it near the db_block_size? I haven't tried this, but if you really want to go to the trouble, you could create a table named CHAINED_ROWS, run ANALYZE . . . LIST CHAINED ROWS. The create a SQL statement that will execute the VSIZE function on each column and sum the values. Then run this statement on each rowid in CHAINED_ROWS. Now you see the reason for my initial suggestion. I would suggest that you not get too paranoid about getting CHAINED_ROWS to zero. But if your wait statistics starts to show table fetch continued row as significant, you definitely need to fix the problem. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row chaining
AK, hi, row chaining is because the big row(row length is bigger than the block size), while row migration is because of small pctfree and updated rows. So, just look at the length of the rows. If chained rows, no way(unless you use larger block size and the row does not contains really long column like long/long row data type), if migrated rows, you can move the table to repair the migrated rows. Good luck Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) === 2003-03-11 07:14:00 ,you wrote£º=== RE: Running multiple instances on a [large] serverI see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row chaining
Tom Kyte has an excellent method of finding which are chained and which are migrated published in a recent issue of Oracle magazine. I don't know which one, possibly the latest, but will check when I get back in the office. In the meantime, the information may be on his web site. A good starting point... http://asktom.oracle.com/pls/ask/f?p=4950:8:104855126678244125::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:358341515662, If you have Oracle magazine, though, he answers your specific question. [EMAIL PROTECTED] 3/11 10:40a AK, hi, row chaining is because the big row(row length is bigger than the block size), while row migration is because of small pctfree and updated rows. So, just look at the length of the rows. If chained rows, no way(unless you use larger block size and the row does not contains really long column like long/long row data type), if migrated rows, you can move the table to repair the migrated rows. Good luck Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) === 2003-03-11 07:14:00 ,you wroteú*=== RE: Running multiple instances on a [large] serverI see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row chaining
I have written two programs to deal with this: 1) a plsql procedure that shows all the LIO for a chained row system wide. This works like sar: ser serveroutput on execute oraperf.analyze_lio(10, 1); 2) an object row chainer analyzer, this will find which SQL statements and what objects have LIOs for row chaining and how much. Program 1 will be available from Oraperf.com Anjo. On Tuesday 11 March 2003 07:49, you wrote: ak Smart-alek answer: Apply one of the methods to eliminate migrated rows, and if the problem doesn't go away, you know you have some chained rows ;-) Chained rows are a little difficult to diagnose. Look at the value for avg_row_len - is it near the db_block_size? I haven't tried this, but if you really want to go to the trouble, you could create a table named CHAINED_ROWS, run ANALYZE . . . LIST CHAINED ROWS. The create a SQL statement that will execute the VSIZE function on each column and sum the values. Then run this statement on each rowid in CHAINED_ROWS. Now you see the reason for my initial suggestion. I would suggest that you not get too paranoid about getting CHAINED_ROWS to zero. But if your wait statistics starts to show table fetch continued row as significant, you definitely need to fix the problem. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, March 11, 2003 9:14 AM To: Multiple recipients of list ORACLE-L I see some values 0 for chaint_cnt in dba_tables . How do I know if this is chained rows or migrated rows ? Any hits . Thanks, ak -- Anjo Kolk http://www.oraperf.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Row Chaining table fetch continued row
Hi all, I am checking Row-Chaining in one of Production Environment. But at the end I am not able to reconcile my results. I would appreciate if someone help me doing that.. After analyzing one table I see that it has 4202 Chained Rows. Then I run following SQL SQL select value 2 from v$sysstat 3 where name = 'table fetch continued row'; VALUE -- 951 SQL Select my.value 2 From v$mystat my, 3 V$sysstat sys 4 Where my.statistic# = sys.statistic# 5 And sys.name = 'table fetch continued row'; VALUE -- 0 Now I do SQL select * from prasanta.tdpsv ; After this I run following queries again, SQL Select my.value 2 From v$mystat my, 3 V$sysstat sys 4 Where my.statistic# = sys.statistic# 5 And sys.name = 'table fetch continued row' VALUE -- 0 SQL select value 2 from v$sysstat 3 where name = 'table fetch continued row' VALUE -- 960 Shouldn't the above two values be 4202 and 5153, as per description of 'table fetch continued row' ?? Am I missing something TIA, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajesh Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Row Chaining table fetch continued row
v$tables will report what the system or session has encountered since instance startup. Analyze or similar will report the actual number in the table. If an instance has been running for several days and the ...fetch continued row... is low, why bother? Rajesh Dayal wrote: Hi all, I am checking Row-Chaining in one of Production Environment. But at the end I am not able to reconcile my results. I would appreciate if someone help me doing that.. After analyzing one table I see that it has 4202 Chained Rows. Then I run following SQL SQL select value 2 from v$sysstat 3 where name = 'table fetch continued row'; VALUE -- 951 SQL Select my.value 2 From v$mystat my, 3 V$sysstat sys 4 Where my.statistic# = sys.statistic# 5 And sys.name = 'table fetch continued row'; VALUE -- 0 Now I do SQL select * from prasanta.tdpsv ; After this I run following queries again, SQL Select my.value 2 From v$mystat my, 3 V$sysstat sys 4 Where my.statistic# = sys.statistic# 5 And sys.name = 'table fetch continued row' VALUE -- 0 SQL select value 2 from v$sysstat 3 where name = 'table fetch continued row' VALUE -- 960 Shouldn't the above two values be 4202 and 5153, as per description of 'table fetch continued row' ?? Am I missing something TIA, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajesh Dayal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
