RE: how to determine number of chained rows

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeffrey Beckstrom Sent: Wednesday, April 17, 2013 8:49 AM
To: tanel_at_tanelpoder.com
Cc: oracle-l-freelists; oracle-db-l
Subject: Re: how to determine number of chained rows

Thanks that did it w/out creating a lot of rollback.
>>> Tanel Poder <tanel_at_tanelpoder.com> 4/15/13 5:06 PM >>>
One way to estimate the impact of chained rows is to just look into the "table fetch continued row" statistic - you can run your query/workload and measure this metric from v$sesstat (with snapper for example). And one more way to estimate the total number of chained pieces would be to run something like SELECT /*+ FULL(t) */ MIN(last_col) FROM t and see how much the "table fetch continued row" metric increases throughout the full table scan. The last_col would be the (physical) last column of the table. Note that if your wide row is chained into let's say 4 pieces, then you'd see the metric increase by 3 for a row where 4th row piece had to be fetched.

I've written about this here:

http://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/

--
*Tanel Poder*
Enkitec (The Exadata Experts)
Training <http://blog.tanelpoder.com/seminar/> | Troubleshooting<http://blog.tanelpoder.com/>
| Exadata<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/14302339
| 23>
| Voicee App <http://voic.ee/>


On Mon, Apr 15, 2013 at 5:57 PM, Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org>wrote:



> DBMS_STATS does not compute number of chained rows.

> analyze table x list chained rows - this does but if the table is 

> large and has lots of chained rows, then need a lot of rollback space 

> for rows inserted into the chained_rows table.

>

> Is there another way to just get a count of chained rows?

>

>

> Jeffrey Beckstrom

> Database Administrator

> Greater Cleveland Regional Transit Authority Information Systems

> 1240 W. 6th Street

> Cleveland, Ohio 44113

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>



--
http://www.freelists.org/webpage/oracle-l




--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l