Re: How is possible that avg_data_blocks_per_key = 0 even with fresh statistics ?

From: <fitzjarrell_at_cox.net>
Date: Sun, 9 Dec 2007 13:29:47 -0800 (PST)
Message-ID: <ea1389e5-ca87-49e0-a79d-f80c25e3e220@e25g2000prg.googlegroups.com>

On Dec 8, 7:03 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Dec 7, 11:47 am, nicola.far..._at_info-line.it wrote:
>
> > Patching this database is not an option, at least now (big customer,
> > production database and so on)
> > But we have other customers with 9206 on the same platform. I'll check
> > next week how are their index statistics.
> > I did a quick check now but they don't seem to have the same problem.
> > They haven't compiled that specific table. They do have
> > num_rows=0 etc in the user_indexes statistics but for tables that are
> > actually empty (i checked only some. I'd need to write a script to
> > check every table/index couple).
> > On Metalink I haven't found anything about this, so I don't think
> > about a bug here. Certainly I am missing something but .. what ??
>
> Many people won't respond from cdos if you continue top posting.
> Posting at the bottom is much preferred ( or along the way ).
>
> The optimizer at times can get confused with tables and indexes that
> were analyzed when they were empty ( num_rows =0 etc ) but now have
> data loaded. As best I understand it almost any plan the optimizer
> might come up relating how to access an empty table may look similar
> so it can pick the wrong index, use full scan, a strange type of index
> access etc.
>
> So it comes down to the choice of getting statistics at the right time
> or trying anyhow or thinking about deleting the stats and using
> dynamic sampling.

You keep harping on this table being empty when stats are gathered, and it's been said more than once by the OP that this is not the case. Now you attempt to sling that hash in another form, yet fail to provide proof of your statements. So, let's see if that is really the case here; if the table is empty, stats are gathered and then the table is poplated and stats re-generated do they remain at 0? I think not:

SQL>
SQL> create table stats_test(

  2  	     mypk number,
  3  	     myval varchar2(8),
  4  	     constraint stats_test_pk
  5  	     primary key(mypk)

  6 )
  7 /

Table created.

SQL>
SQL> create index myval_idx
  2 on stats_test(myval)
  3 /

Index created.

SQL>
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(user, METHOD_OPT => 'for all columns size 1', CASCADE => True);

PL/SQL procedure successfully completed.

SQL>
SQL> select u.clustering_factor, u.avg_data_blocks_per_key, u.num_rows, u.distinct_keys
  2 from user_indexes u;

CLUSTERING_FACTOR AVG_DATA_BLOCKS_PER_KEY NUM_ROWS DISTINCT_KEYS

----------------- ----------------------- ---------- -------------
                0                       0          0             0
                0                       0          0             0

SQL>
SQL> insert all
  2 into stats_test
  3 values
  4 (1, 'Test 1')
  5 into stats_test
  6 values
  7 (2, 'Test 2')
  8 into stats_test
  9 values
 10 (3, 'Test 3')
 11 into stats_test
 12 values
 13 (4, 'Test 4')
 14 into stats_test
 15 values
 16 (5, 'Test 5')
 17 into stats_test
 18 values
 19 (6, 'Test 6')
 20 into stats_test
 21 values
 22 (7, 'Test 7')
 23 into stats_test
 24 values
 25 (8, 'Test 8')
 26 into stats_test
 27 values
 28 (9, 'Test 9')
 29 select * from dual;

9 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(user, METHOD_OPT => 'for all columns size 1', CASCADE => True);

PL/SQL procedure successfully completed.

SQL>
SQL> select u.clustering_factor, u.avg_data_blocks_per_key, u.num_rows, u.distinct_keys
  2 from user_indexes u;

CLUSTERING_FACTOR AVG_DATA_BLOCKS_PER_KEY NUM_ROWS DISTINCT_KEYS

----------------- ----------------------- ---------- -------------
                1                       1          9             9
                1                       1          9             9

SQL> Hmm, the re-calculated stats don't remain at 0 after the table is loaded. Have another suggestion? I don't, outside of a possible bug with 9.2.0.6 on Windows which hasn't been reported (or found) until now.

David Fitzjarrell Received on Sun Dec 09 2007 - 15:29:47 CST