dbms_stats
- From: Thomas Day <tomdaytwo@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 24 Mar 2009 14:43:12 -0400
I'm either missing something badly or the Oracle manual is using English
with their own meanings attached to the words (which don't quite synch up
with my understanding of English).
Oracle 10.2 on AIX
We have two identical databases. We'd like to gather stats on one database,
export them to a stats table, copy the stats table to the other database and
import those stats.
It almost seems to work. Except that we keep getting inconsistancies. The
schemata are locked on both databases. We use force=>true to import the
stats.
But some indexes just weren't getting the right data. Eventually I ended up
running the test below.
It appears that the index has statistics gathered on it because it has
values in num_rows and sample_size. When I export it to my test stats table
it doesn't appear to add a row. Then I gather stats on the index, export it
again, and it adds a row.
So, did it have good stats to start with? Did it have any stats? If it
did, why didn't export add those stats to the stat table? If it didn't have
stats to begin with, how can I tell which indexes and tables have stats that
will successfully export?
this_is_a_db.this_is_a_userid> SELECT NUM_ROWS, SAMPLE_SIZE FROM
USER_INDEXES WHERE INDEX_NAME = 'this_is_a_primary_key_index';
NUM_ROWS SAMPLE_SIZE
---------- -----------
4 4
this_is_a_db.this_is_a_userid> SELECT NUM_ROWS, SAMPLE_SIZE FROM
USER_IND_STATISTICS WHERE INDEX_NAME = 'this_is_a_primary_key_index';
NUM_ROWS SAMPLE_SIZE
---------- -----------
4 4
this_is_a_db.this_is_a_userid> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
9
this_is_a_db.this_is_a_userid> EXEC
DBMS_STATS.EXPORT_INDEX_STATS('DSSSTAR','this_is_a_primary_key_index','','TEST1')
PL/SQL procedure successfully completed.
this_is_a_db.this_is_a_userid> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
9
this_is_a_db.this_is_a_userid> EXEC
DBMS_STATS.GATHER_INDEX_STATS('DSSSTAR','this_is_a_primary_key_index',FORCE=>TRUE
);
PL/SQL procedure successfully completed.
this_is_a_db.this_is_a_userid> EXEC
DBMS_STATS.EXPORT_INDEX_STATS('DSSSTAR','this_is_a_primary_key_index','','TEST1'
)
PL/SQL procedure successfully completed.
this_is_a_db.this_is_a_userid> SELECT COUNT(*) FROM TEST1;
COUNT(*)
----------
10
When I copied the stats table over to the other database I was able to
update the index's stats via an import. But when I just did the export on
database A, copied it to B and imported the index stats it had null num_rows
and null sample_size. It wasn't until I gathered the stats on A before the
export that it worked.
So I repeat my question. If the index had values for num_rows and
sample_size did it have statistics or not? How can I tell where I have to
gather stats before I do the export?
Other related posts:
