OraFAQ Forum: Performance Tuning » statspack table fetch continued row

Home » RDBMS Server » Performance Tuning » statspack table fetch continued row

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

statspack table fetch continued row [message #251057]

Thu, 12 July 2007 05:55

alanm
Messages: 284
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

Report message to a moderator

Re: statspack table fetch continued row [message #251068 is a reply to message #251057]

Thu, 12 July 2007 06:21

Michel Cadot
Messages: 68664
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

Report message to a moderator

Re: statspack table fetch continued row [message #251121 is a reply to message #251057]

Thu, 12 July 2007 07:29

alanm
Messages: 284
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

Report message to a moderator

Re: statspack table fetch continued row [message #251131 is a reply to message #251121]

Thu, 12 July 2007 07:46

Michel Cadot
Messages: 68664
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

Report message to a moderator

Re: statspack table fetch continued row [message #251161 is a reply to message #251057]

Thu, 12 July 2007 09:57

alanm
Messages: 284
Registered: March 2005

Senior Member

hi,
table fetch continued row = 14184836

rgds

alan

Report message to a moderator

Re: statspack table fetch continued row [message #251173 is a reply to message #251161]

Thu, 12 July 2007 10:24

Michel Cadot
Messages: 68664
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

Report message to a moderator

Re: statspack table fetch continued row [message #251182 is a reply to message #251057]

Thu, 12 July 2007 10:33

alanm
Messages: 284
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

Report message to a moderator

Re: statspack table fetch continued row [message #251186 is a reply to message #251057]

Thu, 12 July 2007 10:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

See Michel's response statspack table fetch continued row [message #251068 above

Report message to a moderator

Re: statspack table fetch continued row [message #251187 is a reply to message #251182]

Thu, 12 July 2007 10:51

Michel Cadot
Messages: 68664
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

alanm
Messages: 284
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

Report message to a moderator

Re: statspack table fetch continued row [message #251191 is a reply to message #251057]

Thu, 12 July 2007 11:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Does the system have chaining or not?
YES! Deal with it.

Report message to a moderator

Re: statspack table fetch continued row [message #251215 is a reply to message #251191]

Thu, 12 July 2007 13:05

alanm
Messages: 284
Registered: March 2005

Senior Member

ok, but how?

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

Alan

Report message to a moderator

Re: statspack table fetch continued row [message #251220 is a reply to message #251057]

Thu, 12 July 2007 13:10

BlackSwan
Messages: 26766
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

alanm
Messages: 284
Registered: March 2005

Senior Member

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

alan

Report message to a moderator

Re: statspack table fetch continued row [message #251231 is a reply to message #251057]

Thu, 12 July 2007 13:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT TABLE_NAME FROM DBA_TABLES

Report message to a moderator

Re: statspack table fetch continued row [message #251266 is a reply to message #251231]

Thu, 12 July 2007 14:52

alanm
Messages: 284
Registered: March 2005

Senior Member

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

alan

Report message to a moderator

Re: statspack table fetch continued row [message #251317 is a reply to message #251057]

Thu, 12 July 2007 20:42

BlackSwan
Messages: 26766
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.

Report message to a moderator

Previous Topic:	Buffer Pool
Next Topic:	optimatize query??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 24 07:57:16 CDT 2024