Analyze this!

There’s a question on the Database General forum on OTN containing the following observation:

I have some chained rows in some of my tables. I have exported, truncated, import back the data in the table. Then collect the stats. But I have the same Chained_count. Nothing has changed.

If you export, truncate the table, then re-import the data, you expect to get rid of any migrated rows – but if a row is too big to fit in a single block, or has more than 255 columns and a non-null value after the 255th, (either of which will be reported as a chained row) it’s not going to change after exporting and importing; so the first question to ask is: “What’s the definition of the table ?” optionally followed by: “Can you run some SQL to check for very long rows.”

In this case the table definition showed that there couldn’t possibly be any long rows – so any figures for the chain_cnt must be from row migration – i.e. rows that got longer during an update at a time when there was not enough free space in the current block to allow for the growth. So how come the chain_cnt hasn’t changed after the export/truncate/import ?

There is one obvious (and fairly likely) idea that will spring to mind if you’ve spent many years using Oracle – and this idea prompts the question: “How, exactly, did you collect the stats after the import?”

If the chain_cnt was originally non-zero then someone must have used the analyze command to collect statistics at some point – the calls in the dbms_stats package don’t affect the chain_cnt. When you truncate a table most of the stats for the table will (in recent versions of Oracle) be cleared – but that won’t include the chain_cnt.

After re-importing the data the original chain_cnt would still be in place in the data dictionary, and if the person posting this question used the dbms_stats package to “collect the stats” the chain_cnt would remain unaltered. If he wants to get rid of that chain_cnt he will have to run “analyze table XXX delete statistics;” or use the analyze to collect the new statistics (and this would show that the chain_cnt really had dropped to zero).

Surprising CBO:

Here’s a little surprise, though, and the main reason for writing this note. I’ve got a table with a simple index on it. There are 10,000 rows in the table, and the indexed column has 1,000 tightly clustered rows for each value.

Watch what happens when I switch from using dbms_stats to using analyze (running 10.2.0.3 – but the same happens in 9i):

rem
rem     Script: chain_cnt.sql
rem     Author: Jonathan Lewis
rem     Dated:  June 2006
rem

execute dbms_stats.gather_table_stats()
set autotrace traceonly explain

select v1, v2  from t1 where n1 = 7;

--------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |  1000 |  8000 |    19 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |  1000 |  8000 |    19 |
|*  2 |   INDEX RANGE SCAN          | I1   |  1000 |       |     2 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1"=7)

analyze table t1 compute statistics;
select /*+ index(t1) */ v1, v2  from t1 where n1 = 7;

--------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |  1000 |  6000 |   905 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |  1000 |  6000 |   905 |
|*  2 |   INDEX RANGE SCAN          | I1   |  1000 |       |     2 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N1"=7)

The cost of the query changed so much after the analyze that I had to put in a hint to force Oracle to use the index.

I happen to have 8,852 rows in the table which migrated after a bulk update, and when I used the analyze command to set the chain_cnt the CBO changed the cost of the query. (The increase is “query selectivity * chain_cnt” viz: 0.1  * 8,852 = 886).

Two things to bear in mind: (a) you should be using dbms_stats and not the analyze command to collect object statistics – and Oracle Corp. has been saying that since version 8; but (b) even though dbms_stats does nothing to clear, set, export, or import the chain_cnt, the cost based optimizer will use the value if it’s there – so if you’re still waiting to change your stats collection procedures, watch out – when you change the procedures some of your execution paths may change for “no apparent reason”.

Footnote

If you decide to check a table for chained rows, be careful – if you use the basic analyze command to populate the chain_cnt column you need to “analyze delete” the statistics afterwards before gathering (or reloading) stats through a call to dbms_stats. It’s much better to create the chained_rows table (see @$ORACLE_HOME/rdbms/admin/utlchain.sql) and use the extended command: “analyze table xxx list chained rows;” as this doesn’t change the stats on the table and it identifies the rows which are chained.

Inevitably there’s another type of price to pay – this process will generate undo and redo as it runs because it is writing the list of chained rows into the chained_rows table.

Update (Aug 2021)

The same behaviour appears in 19.11.0.0