Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: chained rows

Re: chained rows

From: Robin Li <rli_at_nyp.org>
Date: Wed, 14 Jun 2000 10:01:33 -0400
Message-Id: <10528.109217@fatcity.com> 





Many thanks to Steve, Charlie and all.  



Yes, Steve, you are right. I did not analyze the SYS and SYSTEM, because
I think it's useless, even though there are chained rows, I can't
correct them. 



Also, Charlie sent me some info. that was extracted from MetaLink, in
case someone is interested, I pasted it here.






 table fetch continued row



 Could you provide a reason why the "table fetch continued row" is
always greater than zero. I have a third party
 monitoring rule that issues a warning if the table fetch continued row
value is greater than 0%(i.e. the percentage of rows
 retrieved that extent over than one block). For my Oracle 7.3.4
database, this is constantly the case (approximately
 .01%) . I have confirmed myself by checking V$SYSSTAT that this is
really true. Sure enoubh "table fetch continued
 row" is equal to 67. I have run a "ANALYZE ... LIST CHAINED ROWS" on
all my tables and there are no chained


 rows. I have also check CHAIN_CNT on DBA_TABLES and it is always 0. I
have checked the avg_row_len on


 DBA_TABLES and the maximum value is 182. I have no LONG data_types. The
maximum number of columns I have


 on a table is 13. So I don't understand I am getting a "table fetch
continued row" greater than zero. I am considering
 changing the warning threshold from the default (Greater than 0% ) to
Greater than .5%, but I just want some


 confirmation that I am not missing anything. 



 Rosemary Fleming 


 Oracle Certified Professional 




 From: Oracle, Helen Schoone 31-Mar-00 16:46 
 Subject: Re : table fetch continued row 



 Hi. It is possible that the value is due to row(s) in SYS-owned data
dictionary table(s). If the warning is bothersome, I
 would increase the threshold. 



 Regards, 


 Helen Schoone 


 Oracle Server EE Analyst 






 Row chaining questions



 I have table with 407 columns. The AVG_ROW_LEN is 1099. The number of
CHAIN_CNT is 4003. This 


 table is loaded with the sqlloader only. It has no inserts performed on
it other than what the 


 sqlloader performs. The stats above were gathered after the load was
performed, after an analyze, 


 and before any updates were issued against the table. 



 This 407 column table with approximately 1 million rows exists in an
instance using an 8K block 


 size and in an instance using a 16K block size. The following query
returns very different results 


 in the 2 instances. 



 SELECT SUBSTR(NAME,1,40), VALUE 



 FROM V$SYSSTAT 



 WHERE NAME = 'table fetch continued row' 



 The 8K instance reports 


 SUBSTR(NAME,1,40) VALUE 






 The 16K instance reports 


 SUBSTR(NAME,1,40) VALUE 






 I don't understand the huge difference in the chaining. What does this
value really indicate? Is 


 there any reason why this row chaining number would become so large
with a doubling of the block 


 size??? 



 Thanks for your help. 






 From: Donald Burleson 17-Feb-00 20:48 


 Subject: Re : Row chaining questions 



 Hi Reid, 



 Oracle has a known problem with table_fetch_continued_row, and it does
not accurately measure continued row fetches,
 and often over-reports the real value when a table has more than 256
columns. If you call-in a TAR, I'm sure Oracle
 could provide you with details. 



 Your better bet is to analyze the table and query CHAIN_CNT in
dba_tables. Hope this helps . . . 



 Don Burleson 


 burleson_at_frontiernet.net 






 From: Oracle, Reem Munakash 21-Feb-00 22:00 
 Subject: Re : Row chaining questions 



 Donald is right, tables with > 255 columns are broken into multiple
rowpieces, which are considered "chained" even if
 they all fit into one data block. These chained rowpieces are included
in CHAIN_CNT. 



 This was reported as bug 597152. However, this is supposed to be fixed
in Oracle8i which will no longer count rows that
 are chained all within the same block. 



 There is also bug, 853444 (which is still open under investigation by
development), which reports chaining of rows within
 one block. There is currently no information within this report as to
the circumstances which would cause this to happen.
 NOTE: if the rows are quite long we may need to split (chain) them over
multiple blocks so as to keep pctfree satisfied
 for each block. So it is actually possible to have rows chained
immediately upon insert. 



 Finally, if the records are updated and you have very little pctfree in
the block this will cause chaining. Are you analyzing
 right after the load before any possible updates? 



 Reem Munakash 


 Enterprise Server Analyst 




 From: Reid McKinley 22-Feb-00 18:10 


 Subject: Re : Re : Row chaining questions 



 Thanks for the responses. I will focus on the information in DBA_TABLES
when determining whether or not I have a
 row chaining problem. 



 Q:Are you analyzing right after the load before any possible updates? 
 A:Yes. Reference first posting in this thread. I'm loading, analyzing,
and checking the chain counts. 





 BTW, if this is the Don Burleson who wrote "High Performance Oracle8
Tuning", thanks for a very helpful resource. :) 






 From: Oracle, Reem Munakash 22-Feb-00 20:36 
 Subject: Re : Re : Re : Row chaining questions 



 I checked if there are any issues with chaining and < 255 columns in
Oracle8i, but found none. It is still possible you are
 hitting bug 853444, which is still open. 



 If you wouldl ike to know for sure, is it possible to send me a test
case so I can file a bug. Development will be able to
 tell us if this is a new issue. 



 Reem Munakash 


 Enterprise Server Analyst 




 From: Reid McKinley 24-Feb-00 15:13 


 Subject: Re : Re : Re : Re : Row chaining questions 



 I'd be glad to help in any way I can. What do you need me to send you? 






 From: Oracle, Reem Munakash 25-Feb-00 12:52 
 Subject: Re : Re : Re : Re : Re : Row chaining questions 



 Send me the 'create table' statement, and a sample loader file (only a
sample of the data). I will load the table and see the
 results of the row chaining information. If I see chained rows, then I
can file a bug and have development review the case.
 It would also help to know what Oracle block size you ar using. 



 Reem Munakash 


 Enterprise Server Analyst 






 From: Jag Anegondi 13-Jan-00 16:24 


 Subject: SYS table chaining (?) 



 RDBMS Version: 8.1.5.0.0


 Operating System and Version: Sun Sprac Solaris 2.6 
 Error Number (if applicable): 


 Product (i.e. SQL*Loader, Import, etc.): 
 Product Version: 



 SYS table chaining (?)



 Hi, 



 I have 4 Oracle users on my production and the application is a web
application. All connections are 


 made by these 4 oracle users by the end users through internet. I have
analyzed all the 4 users db 


 objects and chain count is 0 for all the tables. How ever, i could see
some chained or migrated rows 


 in the database. The only two users remaining are SYS and SYSTEM. If
SYS tables are chained how to 


 eliminate the chained rows? Its is 24X7 shop. Please guide. 



 Thanks 


 Jag anegondi






 From: Oracle, Helen Schoone 14-Jan-00 14:35 
 Subject: Re : SYS table chaining (?) 



 Hi. Out of curiosity, what is it you are using to see some
chained/migrated rows in the database? You would have to
 determine what objects are involved. If the objects are owned by user
SYS, there is not much you can do other than
 recreate the database--which may or may not resolve the fragmented
rows, depending on the cause of the fragmentation.
 If it is due to long data that causes a row to exceed the db_block_size
(minus overhead), then chaining is inevitable unless
 you can increase the db_block_size accordingly--which may introduce
other issues. It is important to determine what
 objects are involved (and then perhaps even what rows) in order to
determine any potential performance impact.



 Regards, 


 Helen Schoone 


 Oracle Server EE Analyst 




 From: Jag Anegondi 14-Jan-00 17:14 


 Subject: Re : SYS table chaining (?) 



 Hi, 



 I am using the following query to deterimine chaining or migration. 



 SELECT name,value from v$sysstat where name='table fetch continued
row'; 



 It is continuosly increasing. I analyzed all schemas except SYS and
SYSTEM and noticed CHAIN_CNT is 0 for these
 schemas. 



 My block_size is 8K. 



 Thanks 


 Jag






 From: Oracle, Helen Schoone 17-Jan-00 17:27 
 Subject: Re : Re : SYS table chaining (?) 



 Hi. Actually, there is a bug logged against 8.1.5 (858617) where this
statistic is not maintained accurately. Seems it is also
 incremented when accessing rows in multiple row pieces that are within
the same block--which is not true


 chaining/migration. The bug is currently being reviewed by development.
This may be what you are seeing.



 Regards, 


 Helen Schoone 


 Oracle Server EE Analyst 





Steve Adams wrote:


> 

> Hi Robin,

> 

> The chaining is probably in the schema that you did not analyze, namely SYS.

> That is, the data dictionary.

> It is perfectly normal, and almost untunable.

> 

> Regards,

> Steve Adams

> http://www.ixora.com.au/

> http://www.oreilly.com/catalog/orinternals/

> http://www.christianity.net.au/

> 

> -----Original Message-----

> From:   Robin Li [SMTP:rli_at_nyp.org]

> Sent:   Wednesday, June 14, 2000 4:22 AM

> To:     Multiple recipients of list ORACLE-L

> Subject:        chained rows

> 

> -- Hi all,

> 

> Our monitoring tool reports that there are chained rows in the database.

> We analyze the schema once a week and get no rows returned from:

> 

> select owner, table_name, chain_cnt/num_rows*100 "chain%"

> from dba_tables

> where num_rows <> 0 and chain_cnt <> 0;

> 

> The tech person from the monitoring tool's company told us that they

> determined chained rows by 'select value from v$sysstat where name

> ='table fetch continued row';

> 

> We do have a value returned from that query:

> 

> VALUE


> ----------

>      24132

> 

> My question is: how to find out which table has the chained row?

> 

> TIA


> Robin

> 

> ****************************************************************

> * Robin Li                                                     *

> * Technical Specialist, DBA           Phone#: (212) 297-3073   *

> * FCG Management Services             Fax#:   (212) 297-4231   *

> * 333 East 38th Street, 2nd fl.       E-mail: rli_at_nyp.org      *

> * New York, NY 10016                                           *

> ****************************************************************

> --

> Author: Robin Li

>   INET: rli_at_nyp.org

> 

> 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: ListGuru_at_fatcity.com (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).

> --

> Author: Steve Adams

>   INET: steve.adams_at_ixora.com.au

> 

> 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: ListGuru_at_fatcity.com (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).



-- 
Robin

****************************************************************


Received on Wed Jun 14 2000 - 09:01:33 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US