Hi,
I have one doubt regarding gathering STATS,
We have DBMS_JOB which gather whole schema stats on daily basis, (code is below) ,On 29 OCT i modifed some data in one table CD_ITEM and some index was rebuilt(and i made it from Non partitioned to Partitioned ) and therefore after that
i exclusive ran
analyze table CD_ITEM compute statistics.
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => '/* job_name: JOB_DAILY_GATHER_STATS */BEGIN dbms_stats.gather_schema_stats(ownname => ''APP_USER'', estimate_percent => NULL, cascade => TRUE); END;'
,next_date => to_date('30/10/2006 08:26:34','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(SYSDATE + 1, ''MI'')'
,no_parse => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/
commit;
SQL> select job,last_date,this_date,next_date from user_jobs where job=641;
JOB LAST_DATE THIS_DATE NEXT_DATE
---------- -------------------- -------------------- --------------------
641 30-OCT-2006 04:12:38 30-OCT-2006 08:26:39 30-OCT-2006 08:26:34
SQL> select table_name, last_analyzed from user_Tables where table_name in ('CD_ITEM','COPY_IMG','BAT_COPY','PYMT')
order by last_analyzed desc;
TABLE_NAME LAST_ANALYZED
------------------------------ --------------------
BAT_COPY 30-OCT-2006 08:35:27
COPY_IMG 30-OCT-2006 06:39:41
CD_ITEM 29-OCT-2006 02:02:09
PYMT 27-OCT-2006 00:16:52
So on 30 Oct whole schema was analyzed again by the DBMS_JOB why not that table got analyzed as i can see the
last_analyzed date for CD_ITEM table has not changed,
and PYMT table why does it is showing 27_OCT, is there some thing wrong that it missed this table(s) or what ?
Bit confused what went wrong, Can some please give an idea what am i missing.
Note:- Above mentioned tables are all Partitioned,
Thanks
[Updated on: Mon, 30 October 2006 11:03]
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
