This content has been marked as final. Show 5 replies
Can advise how can I get the chain cnt without risking my existing statistis?analyze table <TABLE_NAME> list chained rows into <CHAINED_ROWS_TABLE>;
select owner_name, table_name,count(*) from <CHAINED_ROWS_TABLE> group by owner_name, table_name;
CHAINED_ROWS_TABLE should be created with UTLCHAIN.SQL script
"Can advise how can I get the chain cnt without risking my existing statistis?"
see the 'table fetch continued row' system or session statistic. by evaluating that first, you migth avoid looking for something that is not there.
alternatively, you might snap this value with a explicit FTS (that selects all columns).
You should not need to gather chained rows statistics daily. So you could have special "one-off" ANALYZE TABLE runs once a month or once a quarter for only those tables where you suspect or know row chaining is occurring and is impacting performance. Before you run the ANALYZE you can backup the table statistics using DBMS_STATS.EXPORT_TABLE_STATS and then restore the statistics later (after you've generated your own report on Row Chaining) using DBMS_STATS.IMPORT_TABLE_STATS.
Hemant K Chitale
Chewy wrote:It would be useful to know why you want this information, and whether you are aware of the three ways in which chained rows may get reported (and it varies with versions of Oracle). You have row migration - which is an update anomaly that can usually be addressed, "proper" row chaining when the content a row is too long to fit into a single block, and "intra-block" row chaining when the the number of columns in a row exceeds 255 (and a column past 255 has been populated).
Can advise how can I get the chain cnt without risking my existing statistis?
And also will analyze table <tab_name> estimate statistics; overwrite my existing histograms?
If you're just keen to keep an eye on possible threats then Mihael's suggestion is probably the most sensible thing you can do.
Using the estimate statistics option to populate the chain_cnt would overwrite your histograms - more signiflcantly, even in the absence of histograms it could affect execution plans because the optimizer will factor the chain_cnt into the cost calculations if it has been set. (Update: I forgot I'd done a little demo in this blog item: http://jonathanlewis.wordpress.com/2009/04/30/analyze-this/ )
Edited by: Jonathan Lewis on Mar 5, 2013 2:01 PM