Databases Reference
In-Depth Information
Identify Migrated Rows from Statistics
To measure whether queries accessing this update block are accessing the data via a migrated row, examine the
table
fetch continued row
statistic. We will query the new rowid after the update and compare with a single-row query
using the old rowid using the script in Listing 16-10.
SQL> select 'x' from d14.myobj_dmltest
2 where rowid='AAAG2tAAAAAAADkAAA';
<
−
New rowid
-
x
Listing 16-10.
lst16-10-tbcr.sql
SQL> select stat.name,
2 sess.value value
3 from v$mystat sess,
4 v$statname stat
5 where stat.statistic# = sess.statistic#
6 and stat.name like 'table fetch%'
7 and sess.value > 0
8 order by 1
9 /
Statistic Value
------------------------------------------- ---------
table fetch by rowid 1
SQL> select 'x' from d14.myobj_dmltest
where rowid='AAAG2tAAAAAAACDADe';
<
−
Old rowid
-
x
SQL> select stat.name,
... Code from Listing 16-10
Statistic Value
------------------------------------------- ----------
table fetch by rowid 1
table fetch continued row 4
<
−
Indicates row migration
■
you may be wondering why the previous query showed a 4 instead of a 1 for the
table fetch continued row
statistic. This has to do with how the rows are physically stored inside an HCC compression unit (Cu). A row in an HCC
Cu is chained across the multiple blocks that comprise the Cu. A single-row read operation follows the
nrid
pointer from
block-to-block inside a Cu to retrieve each of the row pieces for the row. Each time it does so, the
table fetch continued
row
statistic is incremented. To learn more about how data is physically stored in an HCC Cu, please see the How it Works
section in recipe 16-3.
Note
Search WWH ::
Custom Search