Oracle-L: RE: dbms_stats question

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Thu, 25 Jul 2002 13:43:23 -0800
Message-ID: <F001.004A2DA8.20020725134323@fatcity.com>

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:

insert into test values(1); commit;
SQL> EXEC DBMS_STATS.GATHER_Schema_STATS('HS',STATTAB => 'stats_temp1',statid => 'test1');
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: Harvinder.Singh_at_MetraTech.com

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: ListGuru_at_fatcity.com (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: kkennedy_at_firstpoint.com

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