Re: Automatic statistics don't work on 10g (job and statistics level OK)
- From: Fuad Arshad <fuadar@xxxxxxxxx>
- To: juancarlosreyesp@xxxxxxxxx, Oracle-L Freelists <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 24 Nov 2005 09:45:57 -0800 (PST)
as far a si recall it will only analyze the table if the rate of change is
more than 10%.
i might be wrong but that is what i've observed on my database runnning 10.1.0.4
Juan Carlos Reyes Pacheco <juancarlosreyesp@xxxxxxxxx> wrote:
I want to share something curious happened to me.
I am writing a course on tuning, and I was testing the automatic
gathering on 10g, only to be sure I created an empty table and waited
to see it analyzed to the next day, and in two days it was unanalyzed.
21:39:21 SQL> select last_analyzed from dba_tables where
owner='CACHUN' and table_name='TEST';
LAST_ANA
--------
next day,
08:16:47 SQL> select last_analyzed from dba_tables where
owner='CACHUN' and table_name='TEST';
LAST_ANA
--------
I checked
SELECT STATE FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
STATE
--------
SCHEDULED
statistics level was TYPICAL
And the job didn't executed I checked dba_optstat_operations.
I checked if the job was working
08:21:04 SQL> EXEC SYS.DBMS_SCHEDULER.RUN_JOB('GATHER_STATS_JOB');
PL/SQL procedure successfully completed.
08:24:06 SQL> select last_analyzed from dba_tables where
owner='CACHUN' and table_name='TEST';
LAST_ANAL
---------
20-NOV-05
And it was working, so I dropped and recreated the table using another name.
Then I query select * from cachun.test where object_name like 'A%', I
had the theory it was going to be analyzed if the job found it had
been tried to use, and nothing.
Then I continued with my test gather statitsics for fixed views and
sys, and it started to work. And from then is working ok, I promess
the first think I'm going to do after creating a database is gathering
sys and fixed views statistics, and I'm run my own gathering
statistics job, only to be sure :).
The only strange I did (it is a seed databaes where I created a very
few tables, less than 2 MB). Is the fact the schema where I created
the table was created automatically using datapump.
Does this happened to anybody or is the normal behaviour?
--
Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i
8 years of experience in Oracle 7,8i,9i,10g and developer 6i
--
//www.freelists.org/webpage/oracle-l
Other related posts:
