Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> ALTER TABLE XXXX MONITOR (for partitions?)

ALTER TABLE XXXX MONITOR (for partitions?)

From: <Cherie_Machler_at_gelco.com>
Date: Wed, 08 May 2002 07:36:41 -0800
Message-ID: <F001.0045B7B9.20020508073641@fatcity.com>

We are using Oracle 9.0.1.2 and Sun Solaris 2.6.

Setting up for DBMS_STATS and gathering stale stats option. We've done an alter table xxx monitor and same for indexes so that we can only gather stats on tables and indexes where the stats have gotten stale. This seems to be working fine for us.

I noticed that after I just ran stats gathering on my partitioned tables, I've gathered stats for every single partition, even though most of them have not had any data changes - row count has remained the same.

This leads me to believe that ALTER TABLE xxxx MONITOR only works at the table level, not at the partition level. I went back to the SQL reference manual and looked at the ALTER TABLE XXXX MONITOR command and there doesn't seem to be a partition PART_XXXX clause.

Am I correct? Is there a way to set up for monitoring for stale stats for partitions in tables and indexes?

This is a big deal for us because the amount of data in our partitioned tables makes up a large percentage of our total data. Having to gather statistics every single time on those partitions unstead of just re-gathering stale statistics would add significantly to the amount of time required to re-gather statistics.

What scheme are people in the field using to gather statistics via DBMS_STATS for large partitioned tables?

Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: Cherie_Machler_at_gelco.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). Received on Wed May 08 2002 - 10:36:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US