Home » RDBMS Server » Performance Tuning » statspack table fetch continued row
statspack table fetch continued row [message #251057] Thu, 12 July 2007 05:55 Go to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hello all,
I have statspack reports indicating that

table fetch continued row should be investigated as there are many 000s reported. This equates to chaining.

Now I have done all the obvious like

select table_name from all_tables where chain_cnt >0;

this returns no rows.

all tables have been analyzed with the following

dbms_stats.gather_database_stats(NULL,FALSE,'FOR ALL COLUMNS SIZE 1',NULL,'DEFAULT',TRUE);

I have checked metalink and the documents all seem to indicate chaining on tables but I dont have chaining on tables nas seen from the above sql

can anyone help with this.

regards

Alan
Re: statspack table fetch continued row [message #251068 is a reply to message #251057] Thu, 12 July 2007 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_stats doesn't gather information about row chaining.
You have to use analyze for this.

Regards
Michel
Re: statspack table fetch continued row [message #251121 is a reply to message #251057] Thu, 12 July 2007 07:29 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

the dbms was to analyze the database, this is carried out on a weekly basis. The report of chaining is coming out of statspack reports. My question is how does statspack generate a figure for table fetch continued row when a select table_name from all tables where chain_cnt >0 produces 0 results.

regards

Alan
Re: statspack table fetch continued row [message #251131 is a reply to message #251121] Thu, 12 July 2007 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select name, value from v$sysstat where name='table fetch continued row';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
table fetch continued row                                              2311

Regards
Michel
Re: statspack table fetch continued row [message #251161 is a reply to message #251057] Thu, 12 July 2007 09:57 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
table fetch continued row = 14184836


rgds

alan
Re: statspack table fetch continued row [message #251173 is a reply to message #251161] Thu, 12 July 2007 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't ask for the value, I show you how StatsPack gives you the number.
Wasn't this your last question?

Regards
Michel
Re: statspack table fetch continued row [message #251182 is a reply to message #251057] Thu, 12 July 2007 10:33 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

ok, understood,

the actual question was

statspack tells me that there are table fetch continued rows but chain_cnt from all tables says 0 chaining.

question, how is this possible?

regards

Alan
Re: statspack table fetch continued row [message #251186 is a reply to message #251057] Thu, 12 July 2007 10:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
See Michel's response statspack table fetch continued row [message #251068 above
Re: statspack table fetch continued row [message #251187 is a reply to message #251182] Thu, 12 July 2007 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because StatsPack get statistics that Oracle collects during execution. So this is reality.
Chaint_count may be not accurate for many reasons, from "you didn't calculate them" to "there is bug".

Regards
Michel

[Updated on: Thu, 12 July 2007 10:51]

Report message to a moderator

Re: statspack table fetch continued row [message #251188 is a reply to message #251186] Thu, 12 July 2007 10:51 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

hi,
table fetch continued row suggests chaining.

all_table chain_cnt is 0 zero nothing

the question still stands.

Does the system have chaining or not?


regards

Alan
Re: statspack table fetch continued row [message #251191 is a reply to message #251057] Thu, 12 July 2007 11:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Does the system have chaining or not?
YES! Deal with it.
Re: statspack table fetch continued row [message #251215 is a reply to message #251191] Thu, 12 July 2007 13:05 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

ok, but how?

how can I identify the chained rows if none appear from all_tables?

Alan
Re: statspack table fetch continued row [message #251220 is a reply to message #251057] Thu, 12 July 2007 13:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What part of "See Michel's response statspack table fetch continued row [message #251068 above " do you NOT understand?
>You have to use analyze for this.
You need to use ANALYZE to find & report CHAINED ROWS.

[Updated on: Thu, 12 July 2007 13:11] by Moderator

Report message to a moderator

Re: statspack table fetch continued row [message #251224 is a reply to message #251220] Thu, 12 July 2007 13:13 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

might be you can suggest which table i would like to analyze?

alan
Re: statspack table fetch continued row [message #251231 is a reply to message #251057] Thu, 12 July 2007 13:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SELECT TABLE_NAME FROM DBA_TABLES
Re: statspack table fetch continued row [message #251266 is a reply to message #251231] Thu, 12 July 2007 14:52 Go to previous messageGo to next message
alanm
Messages: 282
Registered: March 2005
Senior Member

and how would I know which table_name to select. What would be the select statement?

alan
Re: statspack table fetch continued row [message #251317 is a reply to message #251057] Thu, 12 July 2007 20:42 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>and how would I know which table_name to select.
The way to be most thorough is to ANALYZE all of the tables.
This way you can be sure that you found all the chained rows.

Or you could try to do some intelligent guessing.
CHAINED ROWS result from tables being UPDATED; especially those table where columns start empty upon INSERT & then data (usually text data such as comments) get added later or over time.
If your application has tables that have the profile described above, then you could try by ANALYZE them first.
Previous Topic: Buffer Pool
Next Topic: optimatize query??
Goto Forum:
  


Current Time: Fri Dec 09 19:47:25 CST 2016

Total time taken to generate the page: 0.08329 seconds