Home > Oracle, Performance > Chained or migrated rows

Chained or migrated rows


What is the easiest way to identify chained or migrated rows, specially you want to just check few rows, they are migrated / migrated or not.  Here is the test.  Prepare the test table with PCTFREE 0.

SQL> create table t1 (c1 varchar2(4000)) pctfree 0 ;
Table created.

SQL> select table_name,PCT_FREE from user_tables;
TABLE_NAME PCT_FREE
------------------------------ ----------
T1 0
SQL> insert into t1 values('A');
1 row created.
SQL> commit ;
Commit complete.
SQL> insert into t1 select * from t1;
1 row created.
..........
SQL> insert into t1 select * from t1;
1024 rows created.
SQL> commit ;
Commit complete.

SQL> select count(*) from t1 ;
COUNT(*)
----------
 2048

Identify few ROWIDs and read directly from the table using the ROWID.

SQL> select rowid from t1 where rownum < 5;</pre>
ROWID
------------------
AAAVFWAAEAAARCrAAA
AAAVFWAAEAAARCrAAB
AAAVFWAAEAAARCrAAC
AAAVFWAAEAAARCrAAD
select /*+gather_plan_statistics */ count(*) from t1 where rowid='AAAVFWAAEAAARCrAAD';
SQL> select /*+gather_plan_statistics */ count(*) from t1 where rowid='AAAVFWAAEAAARCrAAD';
COUNT(*)
----------
 1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST '));
PLAN_TABLE_OUTPUT
-----------------------------------
SQL_ID 4y8yk9tzffmt1, child number 0
-------------------------------------
select /*+gather_plan_statistics */ count(*) from t1 where
rowid='AAAVFWAAEAAARCrAAD'
Plan hash value: 1355481156
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | TABLE ACCESS BY USER ROWID| T1 | 1 | 1 | 1 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------
Note
-----
 - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

19 rows selected.
<pre>

It just access one block since I am reading using the ROWID with just one buffer get.  Make sure no one else doing a transaction against the table,  as a consistent read can drive up buffer gets.  Since I am visiting only one block, it is clear that my row is not chained and is self contained in the same block.  So, how can I prove otherwise.  I am going to update the with a bigger value and that will lead into migrated rows as the PCTFREE is 0.

SQL> update t1 set c1='AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
2048 rows updated.
SQL> commit ;
Commit complete.
SQL> select /*+gather_plan_statistics */ count(*) from t1 where rowid='AAAVFWAAEAAARCrAAD';
COUNT(*)
----------
 1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST '));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID 4y8yk9tzffmt1, child number 0
-------------------------------------
select /*+gather_plan_statistics */ count(*) from t1 where
rowid='AAAVFWAAEAAARCrAAD'
Plan hash value: 1355481156
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time <strong><span style="color: #ff0000;">| Buffers |</span></strong>
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | TABLE ACCESS BY USER ROWID| T1 | 1 | 1 | 1 |00:00:00.01<span style="color: #ff0000;"><strong> | 2 |</strong></span>
----------------------------------------------------------------------------------------------
Note
-----
 - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold</pre>
Now a single row read visiting two blocks, just because the row is migrated. But I wanted to make sure, that the rows are really migrated.
<pre>SQL> analyze table t1 list chained rows into chained_rows;
analyze table t1 list chained rows into chained_rows
*
ERROR at line 1:
ORA-01495: specified chain row table not found</pre>

Wow my chained row table is not existing, let me create it.

<pre>SQL> create table CHAINED_ROWS (
 2 owner_name varchar2(30),
 3 table_name varchar2(30),
 4 cluster_name varchar2(30),
 5 partition_name varchar2(30),
 6 subpartition_name varchar2(30),
 7 head_rowid rowid,
 8 analyze_timestamp date
 9 );
Table created.

SQL> analyze table t1 list chained rows into chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows ;
COUNT(*)
----------
 2033
SQL> select count(*) from t1 ;
COUNT(*)
----------
 2048</pre>

Indeed, rows are chained – 2033 rows are chained out of 2048.    I am going to fix the migrated rows by moving the table.

SQL> alter table t1 move ;
Table altered.
SQL> select rowid from t1 where rownum < 5;
ROWID
------------------
AAAVGpAAEAAARDrAAA
AAAVGpAAEAAARDrAAB
AAAVGpAAEAAARDrAAC
AAAVGpAAEAAARDrAAD
SQL> select /*+gather_plan_statistics */ count(*) from t1 where rowid='AAAVGpAAEAAARDrAAD';
COUNT(*)
----------
 1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST '));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 2bjscjdbg8x2a, child number 0
-------------------------------------
select /*+gather_plan_statistics */ count(*) from t1 where
rowid='AAAVGpAAEAAARDrAAD'
Plan hash value: 1355481156
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time <strong><span style="color: #ff0000;">| Buffers |</span></strong>
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
| 2 | TABLE ACCESS BY USER ROWID| T1 | 1 | 1 | 1 |00:00:00.01 <strong><span style="color: #ff0000;">| 1 |</span></strong>
----------------------------------------------------------------------------------------------
Note
-----
 - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold</pre>

Yes, my table is back to normal – ROWID access going with just one buffer get.  Let me confirm that.

SQL> truncate table chained_rows ;
Table truncated.
SQL> analyze table t1 list chained rows into chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows ;
COUNT(*)
----------
 0

I felt, this is an easy way to check the chained / migrated rows.  Not sure there are exceptions.

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: