Home » RDBMS Server » Performance Tuning » STATS and Last Analyzed Column
STATS and Last Analyzed Column [message #200384] Mon, 30 October 2006 11:01
Messages: 294
Registered: February 2006
Senior Member

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.

    ( 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));


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,

[Updated on: Mon, 30 October 2006 11:03]

Report message to a moderator

Previous Topic: Tuning SQL statement in PL/SQL
Next Topic: Tuning the query - Outer join
Goto Forum:

Current Time: Wed Aug 23 16:36:29 CDT 2017

Total time taken to generate the page: 0.04885 seconds