RE: dbms_stats broken

I looked at bug 1890016 on MetalClink and I'm confused.

Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's test case? I thought this was a no-no??? If so, I'm wondering if it would it help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY, insert/update/delete test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL or the new 9i method that escapes me), then try DBMS_STATS w/STALE option.

Just a thought...

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Koivu, Lisa [mailto:Lisa.Koivu_at_Fairfieldresorts.com]
> Sent: Thursday, May 29, 2003 9:46 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: dbms_stats broken
>
>
> Hi Darrell & Kirti -
>
> It was late last night when I was looking at this. It seems
> I should be using GATHER EMPTY instead of GATHER STALE.
> However this will put statistics on partitions with no rows.
>
> When I load new partitions tomorrow (they are empty prior to
> the daily load), the number of rows inserted wouldn't reach
> 10% change. It would take over a week to reach 10% change in
> order for GATHER STALE to pick up on this and re-analyze
> these partitions. I don't want statistics saying there are 0
> records in a partition that is indeed loaded.
>
> I guess GATHER STALE would be much more useful if the 10%
> threshold could be modified, and/or the threshold could be
> partition specific.
>
> And Kirti, the bugs I have seen are 1192012, 1890016,
> 2157655. I thought I was running into 1890016.
>
> Looks like I'll have to code around this after all. Darn it
> all. I really am a monkey. Sheesh
>
> Lisa
>
>
> -----Original Message-----
> Sent: Thursday, May 29, 2003 12:30 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Lisa,
>
> Wow, you might be saving me from peril right now. I have tested this
> with a small set of tables with no problems (in and 8.1.7.4
> instance).
> I'm preparing to go 'schema' wide in the next week or so for further
> testing prior to implementing in production.
> I'd be very interested in more details of your problems in 8.1.7.4 and
> of course I'll post reports of testing to the list as well.
>
> For starters, here is the code I use to obtain a list of 'stale'
> qualified tables:
> ( For proper credit, I think I got this from asktom.oracle.com)
>
>
> set serverout on size 90000
>
> declare
> l_objList dbms_stats.objecttab;
> begin
> dbms_stats.gather_schema_stats
> ( ownname => '&1',
> options => 'LIST STALE',
> objlist => l_objlist );
> for i in 1 .. l_objlist.count
> loop
> --dbms_output.put_line( l_objlist(i).objtype );
> dbms_output.put_line( l_objlist(i).objname );
> end loop;
> end;
> /
>
>
> And the code to gather stats:
>
> set serverout on size 99000
>
> begin
> dbms_stats.gather_schema_stats(
> ownname=>'&1',
> options=>'GATHER STALE',
> cascade=>TRUE,
> degree=>8,
> granularity=>'ALL',
> method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1'
> );
> end;
> /
>
>
> Thanks,
> Darrell
>
>
> >>> Lisa.Koivu_at_Fairfieldresorts.com 05/28/03 09:24PM >>>
> Hello everyone,
>
> Is anyone using dbms_stats and gather stale or gather auto in 9.2?
> I'm
> trying to use dbms_stats gather schema stats with the stale option and
> it just isn't working in 8.1.7.4. This is documented on Metalink.
> I'd
> love to hear from someone else if this is fixed in 9.2 and if it can
> be
> reliably used.
>
> Thank you
> Lisa Monkey.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).