detecting chained rows without analyzing

[ncsmith]

Is there a way/table to check for chained/migrated rows without running analyze? The database is in rule mode and generating statistics will bring the user application to its knees.
I have heard the v$sysstat can be used but I am not sure.

[gpsingh]

table fetch continued row , name in v$sysstat shall indicate if there are chained / migrated rows but shall
not indicate which table.

But if u run query/DML from ur session at sqlplus , u can fire this after running ur code.

select STATISTIC#,value from v$mystat
2 where STATISTIC#=158;

STATISTIC# VALUE
---------- ----------
158 0

SQL>

If this value is > 0 then u should have chained/migrated rows.

Hope this helps.

[jmodic]

If you use

ANALYZE TABLE my_table LIST CHAINED ROWS;

only chained rows will be listed, while no other statistics used by the optimizer will be gathered, so it will not impact your aplications.

[Mr.Hanky]

You may have to run the UTLCHAIN.SQL to create the CHAINED_ROWS table first. Just do a DESC on the chained_rows table, if it is not there then run the utlchain.sql.

MH

[moff]

Hi,

If the init parameter 'optimiser_mode' is set to rule (optimiser_mode=rule) in the init.ora file then the rule based optimiser will always be used wether there are stats on the tables or not.
I would suggest setting this (if it's not already) to ensure your application uses the rule based optimiser.

Cheers
Moff.