Skip to Main Content
  • Questions
  • 9i: gather_schema_stats, 'GATHER STALE' vs. 'GATHER AUTO'

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jan.

Asked: January 13, 2002 - 3:36 pm UTC

Answered by: Tom Kyte - Last updated: September 17, 2009 - 1:06 pm UTC

Category: Database - Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Tom,

what's the difference between 'gather stale' and 'gather auto'? How does auto determine which objects to analyze? Is the *_tab_modifications still being used? Auto seems to pick up on changes much faster than stale...
If all tables are set to 'monitoring', which one would be preferable?

From the documentation:
'GATHER AUTO' - gather all necessary statistics utomatically. Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When 'GATHER AUTO' is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings will be ignored. Also, return a list of objects processed.
'GATHER STALE' - gather statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale.

Thanks, jan

and we said...

I'll have to research further to find what the real difference is -- but it appears that auto does use the tab modifications table, from the doc:

<quote>
ALTER_DATABASE_TABLE_MONITORING Procedure

This procedure enables or diables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Equivalent to issuing ALTER TABLE ... MONITORING (or NOMONITORING) individually. It is recommended to enable monitoring if you use gather_database_stats or gather_schema_stats with the GATHER AUTO or GATHER STALE options.
</quote>




and you rated our response

  (14 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

*_tab_modifications changes visible much faster...

January 13, 2002 - 9:36 pm UTC

Reviewer: jan van mourik from Houston, TX

Some further observations....
- I did notice that I get results in the *_tab_modifications table very fast now, after a couple of minutes... In 8i it takes a couple of hours before you see changes in this view. Is this just Windows behaviour (I'm testing on XP actually) or the same on unix?
- When you add a new table (or index), 'list auto' will show that table in the objlist right away. Don't know when it will re-analyze when monitoring hasn't been set on that table though..

jan

Tom Kyte

Followup  

January 14, 2002 - 7:30 am UTC

Its changed -- you can even flush it if you want manually in 9i.

What I've found is:

Both use _tab_modifications views .

The differences are :

* GATHER AUTO : Oracle determines the best estimate percent for object .
Oracle also uses a default parallelism degree based on init.ora parameters .

* GATHER STALE : You should provide estimate-percent and degree parameter





regarding use of 'LIST AUTO'

December 06, 2004 - 4:15 am UTC

Reviewer: Ronald from Fortress Hill, Hong Kong

Since we wish to call gather_table_stats with specific parameters for a few tables and at the same time take advantage of the gather stale feature we first call list auto and take the list from there and call gather_table_stats from there.

This approach works fine for ordinary table, but for partitioned tables the table name will get listed several times in the list.

scott@mt27> l
1 declare
2 v_stale_tab_list DBMS_STATS.ObjectTab;
3 v_stale_tab_owner VARCHAR2(30) := '';
4 v_stale_tab VARCHAR2(30) := '';
5 v_cnt number := 0;
6 begin
7 DBMS_STATS.Gather_Schema_Stats(ownname=>null, Options=>'LIST AUTO',
8 ObjList=>v_stale_tab_list);
9 FOR I IN 1..v_stale_tab_list.Count LOOP
10 v_stale_tab_owner := trim(v_stale_tab_list(i).ownname);
11 v_stale_tab := trim(v_stale_tab_list(i).objname);
12 -- IF trim(both ' ' from v_stale_tab_list(i).objtype) = 'TABLE' THEN
13 DBMS_OUTPUT.PUT_LINE(v_stale_tab_owner || ', '
14 || v_stale_tab || ', ' || v_stale_tab_list(i).objtype) ;
15 v_cnt := v_cnt + 1;
16 -- end if ;
17 end loop ;
18 DBMS_OUTPUT.PUT_LINE('tables ' ||v_cnt) ;
19* end ;
scott@mt27> /
SCOTT, TBAD_EXTRACTS, TABLE
SCOTT, TBAD_EXTRACTS, TABLE
SCOTT, TBAD_EXTRACTS, TABLE
SCOTT, TBAD_EXTRACTS, TABLE
SCOTT, TBAD_EXTRACTS, TABLE
SCOTT, TRAMPAGES, TABLE
SCOTT, TRAMPAGES, TABLE
SCOTT, TRAMPAGES, TABLE
SCOTT, TRAMPAGES, TABLE
SCOTT, TRAMPAGES, TABLE
SCOTT, AFX_PK, INDEX
SCOTT, AFX_PK, INDEX
SCOTT, AFX_PK, INDEX
SCOTT, AFX_PK, INDEX
SCOTT, AFX_PK, INDEX
.
.
.

do you have a “Smart” way to handle this?

Tom Kyte

Followup  

December 06, 2004 - 11:42 am UTC

don't use list? use gather stale?

what was the purpose of writing code, what was the goal?

regarding use of ‘LIST AUTO’

December 06, 2004 - 10:01 pm UTC

Reviewer: Ronald from Fortress Hill, Hong Kong

Quoted,
> don't use list? use gather stale?
>
> what was the purpose of writing code, what was the goal?

since gathering column histogram is very time consuming, I was just trying to find a way to custom the gather stale a bit, to just collect histogram only for tables I know necessary.

for example in this way list auto found 10 tables needed stats refresh and I know only 3 of them needed histograms so I can pass to them different method_opt parameter. and the stats gathering can hopefully consume much less time.

Tom Kyte

Followup  

December 07, 2004 - 10:00 am UTC

then you'll have to write a little more code to skip over the redundant tables, something like:

if ( i = 1 or (v_stale_tab_list(i).ownname <> v_stale_tab_list(i-1).ownname
or
v_stale_tab_list(i).objname <> v_stale_tab_list(i-1).objname))
then
dbms_stats.....

GATHER AUTO

April 12, 2005 - 10:07 am UTC

Reviewer: sven

Hi Tom,

Could you please explain what are the criteria for considering table as "need new statistics" using DBMS_STATS.GATHER_SCHEMA_STATS (option: "GATHER AUTO").

Thanks,

sven

10g - monitoring on individual tables no longer possible?

April 18, 2006 - 7:10 am UTC

Reviewer: Graeme from London UK

Hi tom,

Under 10g the ability to switch monitoring on and off on an individual table has been deprecated. We were wanting to exclude certain tables (highly dynamic ones) from being marked stale while leaving the statistics gathering of the less dynamic tables to those being marked stale.

The purpose of this is allow deletion of statistics on the dynamic tables, turning monitoring off (and so prevent them being marked stale and being candidates for gathering stats on stale tables) and allow a dynamic sampling level of 2 to determine the plans on queries accessing these tables. We wanted to do this rather than set dynamic sampling to 4 or above as this could have a rather drastic affect on our existing production system.

I'm having difficulty thinking of any way to automatically achieve under 10g the functionality that was available under 9i. Do you have any ideas?

Thanks!

Tom Kyte

Followup  

April 18, 2006 - 9:57 am UTC

lock them...

ops$tkyte@ORA10GR2> create table t as select * from all_users;

Table created.

ops$tkyte@ORA10GR2> exec dbms_stats.lock_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
BEGIN dbms_stats.gather_table_stats( user, 'T' ); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 1


ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    34 |  1326 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |    34 |  1326 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

ops$tkyte@ORA10GR2> set autotrace off
 

Great stuff

April 18, 2006 - 11:52 am UTC

Reviewer: Graeme from London

Thanks tom, great turn around :-)

I've tested using gather_schema_stats with and without the GATHER STALE option and in both cases it skips the locked table without an error (as it says in the lock_table_stats documentation). This is exactly what we needed, Thanks again!

May 10, 2006 - 3:43 am UTC

Reviewer: A reader from Darwin Australia

great ideas

THanks

dbms_stats.lock_table_stats

December 04, 2006 - 11:59 am UTC

Reviewer: Ben from Minneapolis usa

Since there is no way to turn off table monitoring in 10g and dbms_stats.lock_table_stats is a workaround, is there a way to clear the dba_tab_modifications view for all the activity associated with monitoring? Would be nice to have a way to clear this view in 9i or 10g without having to actually run stats. Is there a way?

Tom Kyte

Followup  

December 04, 2006 - 1:20 pm UTC

why, if you don't care, just ignore it.

March 20, 2007 - 12:15 pm UTC

Reviewer: rchan from NJ,USA

After executing DBMS_STATS.LOCK_TABLE_STATS on a table, what table/view will tell me the table is lock. Aside from getting an error when trying to generate stats on the table/index.
Tom Kyte

Followup  

March 20, 2007 - 12:43 pm UTC

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2102.htm#sthref1723

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select stattype_locked from user_tab_statistics where table_name = 'T';

STATT
-----


ops$tkyte%ORA10GR2> exec dbms_stats.lock_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select stattype_locked from user_tab_statistics where table_name = 'T';

STATT
-----
ALL

March 20, 2007 - 3:20 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

> Aside from getting an error when trying to generate stats on the table/index.

Tom missed that, He scanned too fast :) ... there's the "force" parameter in all procedures of dbms_stats to enable you to gather stats when the stats are locked if you want/need.

autostats_target in 10g

November 15, 2008 - 12:41 pm UTC

Reviewer: Michel SALAIS from France

In 10g, default values are parameterized. The value of the parameter autostats_target affects the target objects when collecting statistics with options => 'gather auto'

Gather Auto stats

September 16, 2009 - 11:05 am UTC

Reviewer: A reader

Hi - Currently we have a production system where the stats are getting updated as the following

Daily stats
------------

SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => '<schema name>'
,Options => 'GATHER AUTO'
,Method_Opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO'
,Degree => DBMS_STATS.AUTO_DEGREE
,Cascade => TRUE
,No_Invalidate => TRUE);
END;

Weekly Stats
-------------

SYS.DBMS_STATS.GATHER_SCHEMA_STATS (
OwnName => '<schema name>'
,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
,degree => DBMS_STATS.AUTO_DEGREE);
END;

BEGIN
SYS.dbms_stats.gather_dictionary_stats;
END;

We want to change this and do a better job of analyzing the change in data and coming up with a good solution.

These are some parts of the analysis that we have done

1. Some tables are static and nothing new gets inserted
=> Can these tables have the stats locked and never get updated ?

2. Some tables get very high number of inserts (like few hundreds of thousands) every day. We have been benchmarking the data change for abt 2 weeks and have the % of data change which is about 1%.
=> Can we just do a gather stale and these tables will have new stats everyday ?

So what it will boil down to is a gather stale job everyday and then a gather schema stats every week. I am not sure about the gather dictionary stats - Is that required and what does it do ?

Thanks.

Tom Kyte

Followup  

September 16, 2009 - 5:35 pm UTC

1) sure, but if they are static, gather auto will skip them anyway

2) if you only touch 1% of the data, we will not gather stale on them, it takes about 10% modification



as for dictionary stats, does your dictionary change much (do you drop/create/truncate/have objects growing rapidly allocating thousands of extents)

or does the dictionary remain more or less the same (most of them do)

September 16, 2009 - 5:39 pm UTC

Reviewer: A reader

For no. 2 if the change is only 1% of the data, we dont need to do gather auto nor gather stale ? Will weekly gather schema stats suffice ?

The dictionary doesnt change too often. So we can skip that too ?


Tom Kyte

Followup  

September 16, 2009 - 5:51 pm UTC

if you change 1% of the data, all I'm saying is - we will not gather stale against that segment.

do you need to? Maybe

consider - you have a date field populated by sysdate. This value always increases.


you have 1,000,000 rows in the table. 10% of that is about 100,000.

Now, today - sept 16th 2009, if you ask "how many rows in the table are between 17-sep-2009 and 30-sep-2009", the answer is "zero"

Next month, after you added 75,000 records, you ask the same question - the answer is 75,000

but what would the optimizer think the answer is? Might that affect your plans?


depending on how the data is used, you might need to gather for some non-stale tables too.

there is no one single answer.

September 17, 2009 - 8:59 am UTC

Reviewer: A reader

So how do you suggest we go about with determining the best method of stats ?
We currently have gather_auto option. What does that do ? Is that almost the same as gather_stale ?
Tom Kyte

Followup  

September 17, 2009 - 1:06 pm UTC

... So how do you suggest we go about with determining the best method of stats ? ...

the only way I know is to understand your data. If you don't, just go auto and see what happens. If you do that however and it goes awry, you will be back at "undertstanding your data"