Re: how to determine number of chained rows
From: Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org>
Date: Wed, 17 Apr 2013 08:49:26 -0400
Message-Id: <516E62160200000B0050112E_at_groupwise.gcrta.org>
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.
Date: Wed, 17 Apr 2013 08:49:26 -0400
Message-Id: <516E62160200000B0050112E_at_groupwise.gcrta.org>
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/1430233923> | Voicee App <http://voic.ee/> On Mon, Apr 15, 2013 at 5:57 PM, Jeffrey Beckstrom <JBECKSTROM_at_gcrta.org>wrote:Received on Wed Apr 17 2013 - 14:49:26 CEST
> 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