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

On Dec 8, 7:03 pm, hpuxrac <johnbhur...@xxxxxxxxxxxxx> wrote:
On Dec 7, 11:47 am, nicola.far...@xxxxxxxxxxxx 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
.

Relevant Pages

  • Re: Warnings
    ... > optimizer is trying to make some kind of judgment based on the statistics, ... > and you also get the columns along with the no stats predicate. ... > statistics of those columns, and the statistics are not there. ... > The statistics for columns are generally created automatically by SQL ...
    (microsoft.public.sqlserver.clients)
  • Re: Warnings
    ... optimizer is trying to make some kind of judgment based on the statistics, ... statistics of those columns, and the statistics are not there. ... The statistics for columns are generally created automatically by SQL Server ... SQL might not be able to create stats for those columns. ...
    (microsoft.public.sqlserver.clients)
  • Re: programmatically updating table statistics
    ... is it possible to programmatically update table statistics by ... My DB-application fills an initially empty table with a huge number of ... You can change them by a either running stats jobs with DBMS_STATS such as: ... Oracle Ace Director & Instructor ...
    (comp.databases.oracle.server)
  • Re: How is possible that avg_data_blocks_per_key = 0 even with fresh statistics ?
    ... empty, they contain 9 and 13 rows respectively. ... On the slow site I tried collecting system statistics for a short time ... was truncated/empty when the stats were gathered? ...
    (comp.databases.oracle.server)
  • Re: We have the pitching, what about offense?
    ... "As an engineer I've always enjoyed ... suggest you look it up in a statistics book. ... although I took a course in it in college and have used/read stats ... Pedroia will do next year. ...
    (alt.sports.baseball.bos-redsox)