| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: chained rows
So whats the rule??
You are inserting data for the first time. Your block can accomodate say 10
and a half rows. Will the 11 th row be chained or inserted into a new block
all together.
I also got some chin_cnt data after analyzing my table. There was no update
in the table. Block size ia 8K and avg_row_size is 500 approx.
Thanks
Sandeep
-----Original Message-----
From: Robin Li [mailto:rli_at_nyp.org]
Sent: Wednesday, June 14, 2000 11:07 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: chained rows
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.
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, namelySYS.
> 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 **************************************************************** * 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 --------------------------------------------------------------------Received on Wed Jun 14 2000 - 13:01:52 CDT
 |
 |