Home » RDBMS Server » Performance Tuning » STATS and Last Analyzed Column
STATS and Last Analyzed Column [message #200384] Mon, 30 October 2006 11:01
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
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

Previous Topic: datafile random read average time
Next Topic: Advice on Hints
Goto Forum:
  


Current Time: Sat Dec 10 22:38:40 CST 2016

Total time taken to generate the page: 0.08596 seconds