Sorry, I haven't explained myself correctly. When you
have first run a dbms_stats to generate on partitioned
tables, analyze doesn't generate any. However, this
applies for compute statistics.
I think this article could explain you better than I:
Doc ID: Note:97207.1
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 24-JAN-2000
Last Revision Date: 28-MAR-2001
Problem Description
-------------------
You have run ANALYZE TABLE COMPUTE STATISTICS on a
partitioned table and the
statistics in dba_tables have not been replaced.
Solution Description
--------------------
You ran the dbms_stats package to get statistics on
the partitioned table with
granularity = ALL. By running the dbms_stats package
on the table the
global_stats column in dba_tables is set to YES.
You need to do the following to delete the stats
generated by the dbms_stats
package:
execute dbms_stats.delete_table_stats(ownname =>
'SCOTT', tabname =>
'ORDERS', cascade_parts => TRUE);
Once you have done that you can run the ANALYZE TABLE
and the statistics will
populate dba_tables.
Explanation
-----------
When analyzing a partitioned table with dbms_stats
package, the global_stats
flag is set. When it is set in this case you need to
use the dbms_stats package
to delete the statistics before the ANALYZE command
can be used on the table.
References
----------
[BUG:908138]
Additional Search Words
-----------------------
Regards.
--- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> Thanks for the advice, Christian, but I'm a little
> confused. What do you
> mean by "don't run statistics with analyze
> because it doesn't generate any"?
>
> Rich Jesse
> System/Database Administrator
> [EMAIL PROTECTED] Quad/Tech
> International, Sussex, WI USA
>
>
> -----Original Message-----
> Sent: Tuesday, February 26, 2002 3:33 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Well, it has some bugs with GATHER EMPTY and GATHER
> STALE on your release. However, I set it in some
> clients and it is working fine. As an advice, don't
> use those options, reduce the size of the bucket
> just
> to generate statistics quickly, lastly if you decide
> against DBMS_STATS, don't run statistics with
> analyze
> because it doesn't generate any.
>
> I set it with monitoring option and as I said it is
> working fine. I was cautious just to exclude tables
> that recently been truncated. However, it is working
> fine.
>
> Regards.
> --- "Jesse, Rich" <[EMAIL PROTECTED]> wrote:
> > Hey all,
> >
> > I'm looking to start CBO on an 8.1.6.0.0 DB on
> > Solaris 2.8. But looking
> > thru Metaclink, I see some potential problems with
> > some of the DBMS_STATS
> > package in this version, like with
> > GATHER_SCHEMA_STATS.
> >
> > Anyone have any suggestions as to "Yay" or "Nay"
> for
> > this on 8.1.6.0.0?
> > Unfortunately, this is another 3rd party app which
> > refuses to support any
> > other version (not sure about patchsets, though)
> of
> > Oracle, so I'm stuck
> > here for the time being. I'm leaning heavily
> > towards the cautious route of
> > using ANALYZE and reading DBA_TAB_MODIFICATIONS
> once
> > a week to see if I
> > should re-ANALYZE.
> >
> > TIA,
> > Rich Jesse
> System/Database
> > Administrator
> > [EMAIL PROTECTED] Quad/Tech
> > International, Sussex, WI USA
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jesse, Rich
> 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).
=====
Ing. Christian Trassens
Senior DBA & Unix Administrator
Phone: 0034-699240979
0034-649824704
__________________________________________________
Do You Yahoo!?
Yahoo! Sports - Coverage of the 2002 Olympic Games
http://sports.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christian Trassens
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).
