| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_STATS in 8.1.6
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" <Rich.Jesse_at_qtiworld.com> 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
> Rich.Jesse_at_qtiworld.com 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" <Rich.Jesse_at_qtiworld.com> 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
> > Rich.Jesse_at_qtiworld.com Quad/Tech
> > International, Sussex, WI USA
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: ctrassens_at_yahoo.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 Tue Feb 26 2002 - 11:28:20 CST
 |
 |