Home » RDBMS Server » Performance Tuning » Why din table fetch continued row increase after fetching every columns (12.1.0.2.0 SLES SP3)
Why din table fetch continued row increase after fetching every columns [message #649222] Fri, 18 March 2016 01:43 Go to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
Dear all,

I trying to understand the concepts of row chaining and migration, from http://www.akadia.com/services/ora_chained_rows.html, I try its examples

my block size is 8192 so I guess I have to double the size of the given examples

SYS@orac02_o112>SELECt value FROM v$parameter2 WHERE  name='db_block_size';

VALUE
---------------------------------------------------------------------------

8192



this is what I've done

first create a table
OLAF@berlin>
OLAF@berlin>CREATE TABLE row_mig_chain_demo (
  2    x int PRIMARY KEY,
  3    a CHAR(2000),
  4    b CHAR(2000),
  5    c CHAR(2000),
  6    d CHAR(2000),
  7    e CHAR(2000)
  8  );

Table created.

OLAF@berlin>
OLAF@berlin>SET ECHO OFF





next populate the table and monitor table fetch continued row


OLAF@berlin>
OLAF@berlin>INSERT INTO row_mig_chain_demo (x) VALUES (1);

1 row created.

OLAF@berlin>INSERT INTO row_mig_chain_demo (x) VALUES (2);

1 row created.

OLAF@berlin>INSERT INTO row_mig_chain_demo (x) VALUES (3);

1 row created.

OLAF@berlin>COMMIT;

Commit complete.

OLAF@berlin>
OLAF@berlin>column a noprint
OLAF@berlin>column b noprint
OLAF@berlin>column c noprint
OLAF@berlin>column d noprint
OLAF@berlin>column e noprint
OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 0     

OLAF@berlin>
OLAF@berlin>SELECT * FROM row_mig_chain_demo;

         X                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 0     

OLAF@berlin>
OLAF@berlin>SET ECHO OFF


next update the rows so that they are too big for the current block and monitor table fetch continued row, the results is as expected

Connected.
OLAF@berlin>
OLAF@berlin>column a noprint
OLAF@berlin>column b noprint
OLAF@berlin>column c noprint
OLAF@berlin>column d noprint
OLAF@berlin>column e noprint
OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 0     

OLAF@berlin>
OLAF@berlin>
OLAF@berlin>UPDATE row_mig_chain_demo SET a = 'z1', b = 'z2', c = 'z3' WHERE x = 3;

1 row updated.

OLAF@berlin>COMMIT;

Commit complete.

OLAF@berlin>UPDATE row_mig_chain_demo SET a = 'y1', b = 'y2', c = 'y3' WHERE x = 2;

1 row updated.

OLAF@berlin>COMMIT;

Commit complete.

OLAF@berlin>UPDATE row_mig_chain_demo SET a = 'w1', b = 'w2', c = 'w3' WHERE x = 1;

1 row updated.

OLAF@berlin>COMMIT;

Commit complete.

OLAF@berlin>
OLAF@berlin>SELECT * FROM row_mig_chain_demo;

         X                                                                      
----------                                                                      
         3                                                                      
         2                                                                      
         1                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 0     

OLAF@berlin>
OLAF@berlin>SELECT * FROM row_mig_chain_demo WHERE x = 3;

         X                                                                      
----------                                                                      
         3                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 0     

OLAF@berlin>
OLAF@berlin>SELECT * FROM row_mig_chain_demo WHERE x = 1;

         X                                                                      
----------                                                                      
         1                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 1     

OLAF@berlin>SET ECHO OFF




next update column d and e of one the the row so that it is bigger than than the current block and causes the roll to be chained to another block and monitor table fetch continued row

OLAF@berlin>
OLAF@berlin>column a noprint
OLAF@berlin>column b noprint
OLAF@berlin>column c noprint
OLAF@berlin>column d noprint
OLAF@berlin>column e noprint
OLAF@berlin>
OLAF@berlin>UPDATE row_mig_chain_demo SET d = 'z4', e = 'z5' WHERE x = 3;

1 row updated.

OLAF@berlin>COMMIT;

Commit complete.

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 1     

OLAF@berlin>
OLAF@berlin>SELECT x,a FROM row_mig_chain_demo WHERE x = 3;

         X                                                                      
----------                                                                      
         3                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 1     

OLAF@berlin>
OLAF@berlin>SELECT x,d,e FROM row_mig_chain_demo WHERE x = 3;

         X                                                                      
----------                                                                      
         3                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     

OLAF@berlin>
OLAF@berlin>SELECT * FROM row_mig_chain_demo;

         X                                                                      
----------                                                                      
         3                                                                      
         2                                                                      
         1                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     =>did not increase as expected 

OLAF@berlin>
OLAF@berlin>SELECT x,a FROM row_mig_chain_demo;

         X                                                                      
----------                                                                      
         3                                                                      
         2                                                                      
         1                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     

OLAF@berlin>
OLAF@berlin>SELECT x,e FROM row_mig_chain_demo;

         X                                                                      
----------                                                                      
         3                                                                      
         2                                                                      
         1                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     =>did not increase as expected

OLAF@berlin>
OLAF@berlin>SET ECHO OFF





the migrated information is as follow:

OLAF@berlin>
OLAF@berlin>column a noprint
OLAF@berlin>column b noprint
OLAF@berlin>column c noprint
OLAF@berlin>column d noprint
OLAF@berlin>column e noprint
OLAF@berlin>column f noprint
OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     

OLAF@berlin>
OLAF@berlin>SELECT count(e) FROM olaf.row_mig_chain_demo;

  COUNT(E)                                                                      
----------                                                                      
         1                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     

OLAF@berlin>
OLAF@berlin>ANALYZE TABLE olaf.row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

OLAF@berlin>
OLAF@berlin>SELECT chain_cnt
  2    FROM user_tables
  3   WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT                                                                      
----------                                                                      
         3                                                                      

OLAF@berlin>
OLAF@berlin>
OLAF@berlin>SET ECHO OFF




why din table fetch continued row increase after fetching every columns or fetching the columns that are supposedly migrated?

many thanks in advance
Re: Why din table fetch continued row increase after fetching every columns [message #649225 is a reply to message #649222] Fri, 18 March 2016 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You faked your output as SELECT * can't return only 1 column out of 6.
So all you posted is doubtful and do not deserve any waste of time.

Re: Why din table fetch continued row increase after fetching every columns [message #649226 is a reply to message #649225] Fri, 18 March 2016 02:41 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
You missed OP's use of NOPRINT to suppress the columns.
Re: Why din table fetch continued row increase after fetching every columns [message #649227 is a reply to message #649226] Fri, 18 March 2016 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed, my apologies. /forum/fa/1606/0/

Re: Why din table fetch continued row increase after fetching every columns [message #649234 is a reply to message #649227] Fri, 18 March 2016 05:45 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
It's because of the primary key on the X and the resulting plan differences you'll have because of that.

When you do select * on the small table without a predicate, it's reading the whole thing in, all the blocks. When you don't and add the where predicate to it, it uses the index.

The index takes it to the specific block, where it finds the row of interest is not there any more, walks the chain (and increments the stat count). The full table access doesn't have this "problem".

Sure technically (probably) within the cache it'll jump to the chained row but for whatever reason it doesn't increment it.

I hazard it is only trying to show us where "extra" IO has happened.


When I quickly tested it, all access with a predicate on X (thus using the PK access) incremented, no full accesses of the table did.

So:

SELECT * FROM row_mig_chain_demo; --no increment
SELECT * FROM row_mig_chain_demo where x=3; --always increment


[Updated on: Fri, 18 March 2016 05:48]

Report message to a moderator

Re: Why din table fetch continued row increase after fetching every columns [message #649237 is a reply to message #649234] Fri, 18 March 2016 07:06 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Ah, I missed the fact that is a PK on that table. Yes, that is the expected behaviour: no continued row fetch when doing a full table scan because you are looking at every block and will therefore get the migrated row eventually as the scan proceeds.

I've seen the same effect on Exadata. Migrated rows do not interrupt a Smart Scan with cell single block reads, unlike when a Smart Scan hits a locked row.
Re: Why din table fetch continued row increase after fetching every columns [message #649264 is a reply to message #649237] Sun, 20 March 2016 20:24 Go to previous message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
once again thanks for the previous answers!

with reference to http://www.akadia.com/services/ora_chained_rows.html


OLAF@berlin>
OLAF@berlin>column a noprint
OLAF@berlin>column b noprint
OLAF@berlin>column c noprint
OLAF@berlin>column d noprint
OLAF@berlin>column e noprint
OLAF@berlin>column f noprint
OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     

OLAF@berlin>
OLAF@berlin>SELECT count(e) FROM olaf.row_mig_chain_demo;

  COUNT(E)                                                                      
----------                                                                      
         1                                                                      

OLAF@berlin>
OLAF@berlin>SELECT a.name, b.value
  2    FROM v$statname a, v$mystat b
  3   WHERE a.statistic# = b.statistic#
  4  	AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE     
---------------------------------------------------------------- ----------     
table fetch continued row                                                 2     

OLAF@berlin>
OLAF@berlin>ANALYZE TABLE olaf.row_mig_chain_demo COMPUTE STATISTICS;

Table analyzed.

OLAF@berlin>
OLAF@berlin>SELECT chain_cnt
  2    FROM user_tables
  3   WHERE table_name = 'ROW_MIG_CHAIN_DEMO';

 CHAIN_CNT                                                                      
----------                                                                      
         3                                                                      

OLAF@berlin>
OLAF@berlin>
OLAF@berlin>SET ECHO OFF



Is there a way to differentiate between chained rows and migrated rows, I don't understand the rationale for the method used in the referenced url.

thanks a lot!
Previous Topic: Tablespace and Partition
Next Topic: Long running Query
Goto Forum:
  


Current Time: Wed Oct 17 22:56:26 CDT 2018