DBMS_STATS Does Not Count Chained Rows

I tested this example on Oracle 11.2. First you need a table with some chained rows. An easy way to do this is to create a table with pctfree = 0 then add lots of data to each row:

SQL> create table chaining_test

  2  pctfree 0

  3  as select object_id

  4  from dba_objects

  5  where rownum < 5000

  6  /

Table created.

SQL> alter table chaining_test add

  2  (owner varchar2(30),

  3   object_name varchar2(128))

  4  /

Table altered.

SQL> update chaining_test x set owner =

  2  (select owner from dba_objects

  3   where object_id = x.object_id)

  4  /

4999 rows updated.

SQL> update chaining_test x set object_name =

  2  (select object_name from dba_objects

  3   where object_id = x.object_id)

  4  /

4999 rows updated.

SQL>

You can still use analyze to count the chained rows:

SQL> analyze table chaining_test compute statistics

  2  /

Table analyzed.

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

      4906

SQL>

If you then run dbms_stats.gather_table_stats against the table, the chain_cnt is not updated although, in this case, it makes no difference as the number of chained rows has not changed:

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'chaining_test');

PL/SQL procedure successfully completed.

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

      4906

SQL>

However, if you delete the statistics:

SQL> analyze table chaining_test delete statistics

  2  /

Table analyzed.

SQL>

... then run dbms_stats.gather_table_stats against it again:

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'chaining_test');

PL/SQL procedure successfully completed.

SQL>

The chain_cnt is left as zero:

SQL> select chain_cnt from user_tables

  2  where table_name = 'CHAINING_TEST'

  3  /

CHAIN_CNT

----------

         0

SQL>

ANALYZE (Part 1)

(Tested on an Oracle 10 database.)

I was going through some old notes recently and was reminded of the ANALYZE command. This allows you to calculate statistics on a table or index. The optimizer can then use these to improve the efficiency of subsequent DML on that object. Oracle have said for a long time now that you should DBMS_STATS in preference to ANALYZE. However, I thought it might still be useful to look at ANALYZE in case you find it in an old SQL script yourself.

In the example below, a table is created and its rows counted. At this point, NUM_ROWS (the number of rows in the table when it was last analyzed) is blank. SAMPLE_SIZE (the number of rows used for the last ANALYZE) is also blank. The table is then analyzed with the COMPUTE STATISTICS option. This analyzes the entire table and populates these two columns in the process.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 100 ROWS option. You might expect SAMPLE_SIZE to be 100 afterwards but it is not. I will be returning to this in a future post.

The table is then analyzed with the ESTIMATE STATISTICS SAMPLE 20 PERCENT option. You might expect SAMPLE_SIZE to be 306 afterwards but it is not. I will be returning to this too in a future post.

Finally, the table is analyzed with the DELETE STATISTICS option. This removes the statistics so the NUM_ROWS and SAMPLE_SIZE columns become null again:

SQL> create table andrew
  2  as select * from dba_tables
  3  /

Table created.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
      1531

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL> analyze table andrew compute statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 100 rows
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew estimate statistics
  2  sample 20 percent
  3  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------
      1531        1531

SQL> analyze table andrew delete statistics
  2  /

Table analyzed.

SQL> select num_rows, sample_size
  2  from dba_tables
  3  where table_name = 'ANDREW'
  4  /

  NUM_ROWS SAMPLE_SIZE
---------- -----------


SQL>