Re: dbms_stats.gather_schema_stats make not be computing stats

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Thu, 21 Nov 2002 00:53:42 -0800
Message-ID: <F001.005089F9.20021121005342@fatcity.com>

No - it means you have monitoring set on (at least) 207 tables, and the 207 of them have had a modification (ins/upd/del) since the monitoring was enabled.

This data is used to determine whether the stats are stale or not. Its documented somewhere, but I think its around the 10% mark before marked as stale.

Also, there are a number of bugs in different versions where gather_stale either does not work, or misses some objects etc...I'd recommend 8174 if you plan on relying on it

hth
connor

• Gillian <zmkj04_at_yahoo.com> wrote: >
  > This is an 8.1.7.0.0 data on Solaris 8.
  >
  > I set monitoring to YES in all 14000+ tables.
  > In the sys.dba_tab_modifications table there are 207
  > tables.
  > Question: Does this mean that there are 207 tables
  > with STALE staistics? From what I have read, that
  > answer is suppose to be - yes.
  > When I run the following script nightly, the number
  > of tables (all owned by DBADMIN), in the
  > sys.dba_tab_modifications is not decreasing:
  > execute dbms_stats.gather_schema_stats ('DBADMIN',
  > NULL, FALSE, 'FOR ALL COLUMNS SIZE 1', NULL,
  > 'DEFAULT', TRUE, NULL, NULL, 'GATHER STALE',
  > 'LIST');
  > Here is a sample output from the
  > sys.dba_tab_modifications table:
  > TABLE_NAME TIMESTAMP
  > ------------------------------ ---------
  > PS_VENDOR_WTHD_JUR 20-NOV-02
  > PS_VNDR_ADDR_SCROL 20-NOV-02
  > PS_VNDR_BANK_ACCT 20-NOV-02
  > PS_VNDR_CNTCT_SCR 20-NOV-02
  > PS_VNDR_LOC_SCROL 20-NOV-02
  > PS_VNDR_URL 18-NOV-02
  > PS_VOUCHER 20-NOV-02
  > PS_VOUCHER_LINE 20-NOV-02
  > PS_XE_XREF_TMW 19-NOV-02
  > 207 rows selected.
  > SQL> select last_analyzed from dba_tables where
  > table_name='PS_XE_XREF_TMW';
  > LAST_ANAL
  > ---------
  > 20-OCT-02
  > Question:
  > I do not understand why last_analyzed is Oct 20th,
  > when I have the dbms_stats.gather_schema_stats (see
  > above) script running nightly. Montoring is YES is
  > dba_tables.
  > AAArrgghhh . . . am I missing something?
  >
  > Thanks
  >
  > Gillian
  >
  >
  >
  > ---------------------------------
  > Do you Yahoo!?
  > Yahoo! Mail Plus - Powerful. Affordable. Sign up now
  

  ---

Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

---

Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

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).

Received on Thu Nov 21 2002 - 02:53:42 CST