Well, the results seem to indicate that the saved statistics are taken before
gathering new statistics. When I read the documentation, I see "stattab User stat
table identifier describing where to save the current statistics." I guess this can
be read as "save the current statistics in stattab before computing the new
statistics." Nothing like clarity of expression. This is why tech writers earn the
big bucks 8-)
Kevin Kennedy
First Point Energy Corporation
If you take RAC out of Oracle you get OLE! What can this mean?
-----Original Message-----
Sent: Thursday, July 25, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are testing dbma_stats package to gather statistics for our schema???
we have created 1 table 'test' with 1 column
abc number
we are doing following steps:
1) insert into test values(1);
commit;
2) SQL> EXEC DBMS_STATS.GATHER_Schema_STATS('HS',STATTAB => 'stats_temp1',statid
=>
'test1');
3) SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where
table
_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
test 1 1 0
4) insert into test values(2);
commit;
5) SQL> EXEC DBMS_STATS.GATHER_Schema_STATS('HS',STATTAB => 'stats_temp1',statid
=>
'test2');
6) SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where
table
_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
test 2 1 0
7) now if i do,
SQL> EXEC DBMS_STATS.DELETE_Schema_STATS('HS');
8) If i try to import
SQL> EXEC DBMS_STATS.import_Schema_STATS('HS',STATTAB => 'stats_temp1',STATID
=>
'TEST1');
THIS DOES NOT POPULATE THE STATSISTICS IN USER_TABLES
9) And if i do like this:
SQL> EXEC DBMS_STATS.import_Schema_STATS('HS',STATTAB => 'stats_temp1',STATID
=>
'TEST2');
it shows output num_rows=1 whereas it is expected to have num_rows=2
3) SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where
table
_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
test 1 1 0
WHY THE FIRST IMPORT not poplulaing the stats column AND SECOND IMPORT have 1
rows and not 2 ROWS ?????????
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: kkennedy
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
