Automatic statistics don't work on 10g (job and statistics level OK)

From: Juan Carlos Reyes Pacheco <juancarlosreyesp_at_gmail.com>
Date: Thu, 24 Nov 2005 09:23:27 -0400
Message-ID: <cd4305c10511240523u487a4d9bg797cac360665b380@mail.gmail.com>

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

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 24 2005 - 07:26:09 CST