Tuesday, April 12, 2011

Chained Rows (Part 1)

BusinessPresence_468x60

The table fetch continued row count in V$SYSSTAT is incremented by 1 each time Oracle accesses a row which spans more than 1 block:

SQL> SELECT STATISTIC#, VALUE TFCR
  2  FROM V$SYSSTAT
  3  WHERE NAME = 'table fetch continued row'
  4  /
 
STATISTIC#       TFCR
---------- ----------
       355    2313557
 
SQL>

The lower this value is the better but you should always look at it as a percentage of the total number of rows read. You can calculate this by adding together the following 2 values:

SQL> SELECT STATISTIC#, VALUE TSRG
  2  FROM V$SYSSTAT
  3  WHERE NAME = 'table scan rows gotten'
  4  /
 
STATISTIC#       TSRG
---------- ----------
       352 4122983672
 
SQL> SELECT STATISTIC#, VALUE TFBR
  2  FROM V$SYSSTAT
  3  WHERE NAME = 'table fetch by rowid'
  4  /
 
STATISTIC#       TFBR
---------- ----------
       354  698738408
 
SQL>

Then you can calculate the table fetch continued rows figure as a percentage of the total number of rows read like this:

SQL> SELECT TFCR/(TSRG+TFBR)*100 PERCENTAGE FROM
  2  (SELECT VALUE TFCR FROM V$SYSSTAT
  3   WHERE NAME = 'table fetch continued row'),
  4  (SELECT VALUE TSRG FROM V$SYSSTAT
  5   WHERE NAME = 'table scan rows gotten'),
  6  (SELECT VALUE TFBR FROM V$SYSSTAT
  7   WHERE NAME = 'table fetch by rowid')
  8  /
 
PERCENTAGE
----------
.047981964
 
SQL>

Or this:

SQL> SELECT
  2   (SELECT VALUE FROM V$SYSSTAT
  3    WHERE NAME = 'table fetch continued row') /
  4  ((SELECT VALUE FROM V$SYSSTAT
  5    WHERE NAME = 'table scan rows gotten') +
  6   (SELECT VALUE TFBR FROM V$SYSSTAT
  7    WHERE NAME = 'table fetch by rowid')) * 100 PERCENTAGE
  8  FROM DUAL
  9  /
 
PERCENTAGE
----------
.047981953
 
SQL>

Or this:

SQL> SELECT A.VALUE / (B.VALUE + C.VALUE) * 100 AS PERCENTAGE
  2  FROM   V$SYSSTAT A, V$SYSSTAT B, V$SYSSTAT C
  3  WHERE  A.NAME = 'table fetch continued row'
  4  AND    B.NAME = 'table scan rows gotten'
  5  AND    C.NAME = 'table fetch by rowid'
  6  /
 
PERCENTAGE
----------
.047981939
 
SQL>

The figures differ slightly. This could be caused by different rounding in each calculation. However, the numbers came from a very busy database. It is more likely that increases in the 3 statistics between 1 calculation and the next were the real reason.

You then need to decide what percentage of chained row reads to allow. This is the difficult bit as some people say that anything over 0.1% is too high whereas others say that up to 5% is acceptable.

No comments: