*_tab_modifications changes visible much faster...
jan van mourik, January 13, 2002 - 9:36 pm UTC
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
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'
Ronald, December 06, 2004 - 4:15 am UTC
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?
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
Ronald, December 06, 2004 - 10:01 pm UTC
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.
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
sven, April 12, 2005 - 10:07 am UTC
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?
Graeme, April 18, 2006 - 7:10 am UTC
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!
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
Graeme, April 18, 2006 - 11:52 am UTC
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!
A reader, May 10, 2006 - 3:43 am UTC
great ideas
THanks
dbms_stats.lock_table_stats
Ben, December 04, 2006 - 11:59 am UTC
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?
December 04, 2006 - 1:20 pm UTC
why, if you don't care, just ignore it.
rchan, March 20, 2007 - 12:15 pm UTC
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.
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
Alberto Dell'Era, March 20, 2007 - 3:20 pm UTC
> 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
Michel SALAIS, November 15, 2008 - 12:41 pm UTC
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
A reader, September 16, 2009 - 11:05 am UTC
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.
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)
A reader, September 16, 2009 - 5:39 pm UTC
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 ?
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.
A reader, September 17, 2009 - 8:59 am UTC
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 ?
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"