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  |
 |
juniordbanewbie
Messages: 250
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 #649234 is a reply to message #649227] |
Fri, 18 March 2016 05:45  |
Roachcoach
Messages: 1576
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 |
John Watson
Messages: 8960
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 |
|
juniordbanewbie
Messages: 250
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!
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 05:40:34 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
