Help, Oracle’s yanking my Chain (count)

Despite all the hype, it turns out that Football was coming home only to change it’s underwear before going out again.
As the Azzuri may have said, “Veni Vidi Vici (ai penalti)”…or something like that.
Look, I’m over it, there are far more important things in life than football and it will not impinge further on this post. Well, not much.

What I’m going to explore here how you persuade Oracle to report the count of chained rows for a table in the _tables dictionary views. The examples that follow were run on Oracle 19c Enterprise Edition. I have observed the same behaviour all the way back to 11gR2.

What is a Chained Row ?

In Oracle, a chained row is one that is too big to fit into a single block.
Usually this happens because a row that fits within a single block is updated and becomes too big to fit into a block ( row migration), or the row is simply too big for a single block to start with.
Either way, reading such rows require additional blocks to be retrieved, which may impact on query performance.
There are other ways row chaining can occur but what we’re really interested in here is exactly how to identify which tables contain chained rows ( and how many).

A Tables with Chained Rows

Time for a simple example involving a table where the rows are created being too large to fit into a single block.
First, let’s check the block size of the tablespace we’re creating the table in :

TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
USERS                                8192

Now let’s create a table which includes rows larger than 8192 bytes ( 8K) :

We can see that there are some records which are larger than the tablespace block size…

TEAM           RECORD_LENGTH
-------------- -------------
GERMANY                12009
FRANCE                 12008
ITALY                  12007
SPAIN                  12007
DENMARK                   32
CZECH REPUBLIC            15
ARGENTINA                 10
PORTUGAL                   9
GREECE                     7
BRAZIL                     7

10 rows selected. 

DBMS_STATS

I mean, this is going to be a really short post, right ? If we want a stat like the chained row count we just need to run DBMS_STATS.GATHER_TABLE_STATS, don’t we ?

I mean, there’s even a column called CHAIN_CNT in the _tables views.
If we look at the column comments, we can confirm it’s purpose :

COMMENTS                                                                        
-------------------------------------------
The number of chained rows in the table

So, when we gather stats on the table…

…we should see this reflected in the _tables dictionary views…

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN LAST_ANALYZ SAMPLE_SIZE
---------- ---------- ------------ ---------- ----------- ----------- -----------
        10         10            0          0        4818 24-JUL-2021          10

Hmmm, it seems that DBMS_STATS is convinced that there are no chained rows at all in our table, despite sampling all 10 rows.

The ANALYZE command

Now, I’m old enough to remember a time when England were really bad at football and Italy…were still pretty good at football. This is back in the days before DBMS_STATS came along.
Then, in order to gather stats on a table, you had to run the ANALYZE command.

Looks like this command is still around and one of it’s purposes is to identify chained rows…

The CHAINED_ROWS table

We’ll come onto running ANALYZE to identify the individual chained rows in a table shortly. Before that, we need to ensure that there is a table to hold the results of this command.

Oracle provide a script for this purpose ( to be run as SYS as SYSDBA) :

$ORACLE_HOME/rdbms/admin/utlchain.sql

In order to access the table, I additionally ran :

You can point ANALYZE TABLE at a custom table of your own devising as long as it has the same structure as the CHAINED_ROWS table created by this script – e.g. :

Either way, you can now analyze the table and write the chained row details to whichever target table you’re using. In this case, we’ll use the default :

Table YEARS_OF_HURT analyzed.

I can now see that, as expected, there are four chained rows in the table :

HEAD_ROWID         ANALYZE_TIM
------------------ -----------
AAATiyAAMAAAAHrAAA 24-JUL-2021
AAATiyAAMAAAAHsAAC 24-JUL-2021
AAATiyAAMAAAAHuAAD 24-JUL-2021
AAATiyAAMAAAAHwAAD 24-JUL-2021

Not that using ANALYZE in this way does not change the stats recorded in the _TABLES dictionary views :

 CHAIN_CNT
----------
         0

Old-School Analysis

“You’ll win nothing with kids !”

Also…if the continued lack of an accurate chain count in USER_TABLES has left you feeling as sick as a parrot, you can always analyze your table just like Alan Hansen used to do…

Finally, we can see that the stats in USER_TABLES have been updated :

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
---------- ---------- ------------ ---------- ----------- -----------
        10         10            6          4        4829          10

It’s worth noting that, even in our restricted column selection, the EMPTY_BLOCKS and AVG_ROW_LEN values have changed in addition to CHAIN_CNT.
If you do decide you need to use analyze in this way, it may be worth re-executing DBMS_STATS afterwards to ensure the stats used by the CBO are accurate. Note that doing so will not overwrite the CHAIN_CNT value :

  NUM_ROWS     BLOCKS EMPTY_BLOCKS  CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE
---------- ---------- ------------ ---------- ----------- -----------
        10         10            6          4        4818          10

For the England Mens’ team, the next major tournament ( provided they qualify) is only next year, in 2022. That’s 56 years of schadenfreude if you’re using the Scottish Calendar.