Home » RDBMS Server » Server Administration » Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi (Oracle 11.2.0.4. OS: AIX 6)
Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662133] Tue, 18 April 2017 11:21 Go to next message
abhi7394
Messages: 6
Registered: April 2017
Junior Member
Hello Everyone,
the scenerio is like below. Every night after 23.00 stats on one table is calculated using one unix script which has below command. Table is partition table with 4 partitions.
v_part := 'DAT_'||to_char(sysdate,'YYYYMMDD');

dbms_stats.gather_table_stats(ownname => null, tabname => 'T_FAC_STOCK_S24', partname => v_part, estimate_percent => dbms_stats.auto_sample_size, degree => 4, force => true);

In the morning at 11.00 on April 18th when i checked the LAST_ANALYZED column of dba_tables it is showing below.

OWNER TABLE_NAME LAST_ANALYZED
------------------------------ ------------------------------ -------------------
ODSFE3 T_FAC_STOCK_S24 17-04-2017 00:10:54

According to above output stats were not calculated on the script run of 17 April 23:00 . But then i checked dba_tab_stats_history and i got below output.
OWNER TABLE_NAME PARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
ODSFE3 T_FAC_STOCK_S24 15-APR-17 01.00.54.360524 AM +02:00
ODSFE3 T_FAC_STOCK_S24 DAT_20170414 15-APR-17 08.30.16.958906 AM +02:00
ODSFE3 T_FAC_STOCK_S24 DAT_20170414 15-APR-17 03.38.16.819252 PM +02:00
ODSFE3 T_FAC_STOCK_S24 15-APR-17 03.38.16.819252 PM +02:00
ODSFE3 T_FAC_STOCK_S24 DAT_20170415 16-APR-17 12.15.09.720862 AM +02:00
ODSFE3 T_FAC_STOCK_S24 16-APR-17 12.15.09.720862 AM +02:00
ODSFE3 T_FAC_STOCK_S24 16-APR-17 12.40.10.563678 PM +02:00
ODSFE3 T_FAC_STOCK_S24 DAT_20170416 17-APR-17 12.10.54.320661 AM +02:00
ODSFE3 T_FAC_STOCK_S24 17-APR-17 12.10.54.320661 AM +02:00
ODSFE3 T_FAC_STOCK_S24 DAT_20170417 18-APR-17 12.25.22.288880 AM +02:00
ODSFE3 T_FAC_STOCK_S24 18-APR-17 12.25.22.288880 AM +02:00

OWNER TABLE_NAME PARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
ODSFE3 T_FAC_STOCK_S24 DAT_20170417 18-APR-17 01.00.39.981961 AM +02:00
ODSFE3 T_FAC_STOCK_S24 18-APR-17 01.00.39.981961 AM +02:00

Now i need your expertise in this situation. I want to understand should there be a difference between these 2 columns , if yes then why and if no then what i am doing wrong here.

Thanks
Abhishek

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662135 is a reply to message #662133] Tue, 18 April 2017 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result, resize them to have each row on a single line.
Give the headings only once per query result.
In short, do everything that can make your post easy to read.

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662136 is a reply to message #662133] Tue, 18 April 2017 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator


Crossref: http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=86605&SkipA=0

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662137 is a reply to message #662136] Tue, 18 April 2017 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I want to understand should there be a difference between these 2 columns
which 2 columns?

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662138 is a reply to message #662137] Tue, 18 April 2017 12:22 Go to previous messageGo to next message
abhi7394
Messages: 6
Registered: April 2017
Junior Member
Hello Everyone,
Sorry for posting without proper format. Thanks for letting me know. I am posting it again.
the scenerio is like below. Every night after 23.00 stats on one table is calculated using one unix script which has below command. Table is partition table with 4 partitions. 

DECLARE 
    v_part VARCHAR2(50); 
    v_nb   PLS_INTEGER; 
BEGIN 
    IF To_number(To_char(SYSDATE, 'HH24')) >= 22 THEN 
      -- entre 22h et 00h on calcule les stats pour la partition de la date courante : 
      -- T_FAC_STOCK_S24 : si exécuté avant 00h 
      v_part := 'DAT_' 
                ||To_char(SYSDATE, 'YYYYMMDD'); 
    ELSE 
      -- entre 00h et 22h on calcule les stats pour la partition de J-1 
      -- T_FAC_STOCK_S24 : si exécuté après 00h 
      v_part := 'DAT_' 
                ||To_char(SYSDATE - 1, 'YYYYMMDD'); 
    END IF; 

    SELECT Count(*) 
    INTO   v_nb 
    FROM   user_tab_partitions 
    WHERE  table_name = '${STAT_NOM_TABLE}' 
           AND partition_name = v_part; 

    IF v_nb > 0 THEN 
dbms_output.Put_line('Analyse la table ${STAT_NOM_TABLE}, pour la partition ' || v_part); 

dbms_stats.Gather_table_stats(ownname => NULL, tabname => '${STAT_NOM_TABLE}', partname => v_part, estimate_percent => dbms_stats.auto_sample_size, degree => 4, FORCE => TRUE); 
    ELSE 
      dbms_output.Put_line('Analyse la table ${STAT_NOM_TABLE}'); 

      dbms_stats.Gather_table_stats(ownname => NULL, tabname => '${STAT_NOM_TABLE}', estimate_percent => dbms_stats.auto_sample_size, degree => 4, FORCE =>TRUE); 
    END IF; 
END; 

/

In the morning at 11.00 Am 18th April when i checked the LAST_ANALYZED column of dba_tables it is showing below. 

OWNER            TABLE_NAME                     LAST_ANALYZED 
------------     -----------------               ------------------- 
ODSFE3          T_FAC_STOCK_S24       17-04-2017 00:10:54
According to above output stats were not calculated on the script run of 17 April 23:00 . But then i checked dba_tab_stats_history and i got below output. 

OWNER          TABLE_NAME 		   PARTITION_NAME 		      STATS_UPDATE_TIME 
-----------   -------------             -------------------------       ------------------------------ 
ODSFE3        T_FAC_STOCK_S24 			                         15-APR-17 01.00.54.360524 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          DAT_20170414                      15-APR-17 08.30.16.958906 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          DAT_20170414                      15-APR-17 03.38.16.819252 PM +02:00 
ODSFE3        T_FAC_STOCK_S24          			                 15-APR-17 03.38.16.819252 PM +02:00 
ODSFE3        T_FAC_STOCK_S24          DAT_20170415                      16-APR-17 12.15.09.720862 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          			                 16-APR-17 12.15.09.720862 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          			                 16-APR-17 12.40.10.563678 PM +02:00 
ODSFE3        T_FAC_STOCK_S24          DAT_20170416                      17-APR-17 12.10.54.320661 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          			                 17-APR-17 12.10.54.320661 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          DAT_20170417                      18-APR-17 12.25.22.288880 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          			                 18-APR-17 12.25.22.288880 AM +02:00 
ODSFE3        T_FAC_STOCK_S24          DAT_20170417                      18-APR-17 01.00.39.981961 AM +02:00 
ODSFE3        T_FAC_STOCK_S24 			                         18-APR-17 01.00.39.981961 AM +02:00
Now i need your expertise in this situation. I want to understand should there be a difference between these LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_history columns , if yes then why and if no then what i am doing wrong here.

Thanks
Abhishek

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662139 is a reply to message #662138] Tue, 18 April 2017 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what do you see when you query DBA_TAB_PARTITIONS; instead of DBA_TABLES?

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662140 is a reply to message #662139] Tue, 18 April 2017 12:39 Go to previous messageGo to next message
abhi7394
Messages: 6
Registered: April 2017
Junior Member
Hello BlackSwan
I dont have the output now. But the the LAST_ANALYZED column of DBA_TAB_PARTITIONS has same value as DBA_TABLES

Thanks
Abhishek.

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662141 is a reply to message #662140] Tue, 18 April 2017 12:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The reality is that LAST_ANALYZED column only gets a new value when the actual statistics values change.
If the underlying data has NOT changed then LAST_ANALYZED won't change.
You can NOT use LAST_ANALYZE value to know when DBMS_STATS was last run for this object.

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662142 is a reply to message #662141] Tue, 18 April 2017 12:53 Go to previous messageGo to next message
abhi7394
Messages: 6
Registered: April 2017
Junior Member
Hello BlackSwan
I am confused now. when you say underlying data you mean table data or stats data.?
Table data is changing almost every hour. It is an OLTP environment. I have always read on oracle links that this column can tell you when was the last time stats were gathered.
Please explain me the concept.

Thanks

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662143 is a reply to message #662142] Tue, 18 April 2017 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
observe that value of STATS_UPDATE_TIME is always the day after the partition_name date.
If the data in partitions before "yesterday" does not change, then the "current" statistic values are still valid;
regardless of how long ago they were collected.
Why should Oracle waste I/O & CPU to calculate the same results that are now correct & valid.

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662144 is a reply to message #662143] Tue, 18 April 2017 13:30 Go to previous messageGo to next message
abhi7394
Messages: 6
Registered: April 2017
Junior Member
If i talk about latest partition DAT_20170417, then data in this partition is constantly changing. After 17-04-2017 00:10:54 data has changed a lot in this partition. still on 18th April morning 11 AM , more than 24 hours, it is showing this date.
I also pasted script for collecting stats. in that you can see that it will also run for one day old partition, because data keep on changing in sysdate-1 partition.
Thanks

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662145 is a reply to message #662144] Tue, 18 April 2017 13:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to step back & view the status of the table from 10,000 meters.
The sole purpose of the statistics is so that the Cost Based Optimizer will make correct choices about how best to return result set.
The fact that a column like QTY_ONHAND changes from 83 to 71, won't impact how the CBO returns the result set.
Or another column changes from NULL to contain a value won't impact CBO's choice.
Simply put LAST_ANALYZED value can NOT be used to know the last time DBMS_STATS was invoked.

SQL> desc all_tab_modifications
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER					    VARCHAR2(128)
 TABLE_NAME					    VARCHAR2(128)
 PARTITION_NAME 				    VARCHAR2(128)
 SUBPARTITION_NAME				    VARCHAR2(128)
 INSERTS					    NUMBER
 UPDATES					    NUMBER
 DELETES					    NUMBER
 TIMESTAMP					    DATE
 TRUNCATED					    VARCHAR2(3)
 DROP_SEGMENTS					    NUMBER
Above is used to decide if/when statistics will really be (re)calculated.
I believe about 10% of the rows need to have changed before new statistics are gathered.

Report message to a moderator

Re: Help:difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_hi [message #662147 is a reply to message #662145] Tue, 18 April 2017 14:30 Go to previous message
abhi7394
Messages: 6
Registered: April 2017
Junior Member
Hello BlackSwan,
Ok lets leave last_analyzed for now. I have another doubt.
I am sorry i was not clear with data. in this table there are high numbers of rows insertion and deletion. so apart from column values , row count also changes.
As you said 10% of rows need to changed before new statistics, yes i agree with that and i have seen data modifications are more than 10%.The partition of SYSDATE will be created on SYSDATE 22:00. After that rows are inserted into it till SYSDATE+1. for example partition of 17th April will be created on 17th April 23.00 and data will be inserted and deleted from it till 18th April 22.00.As it is more than 10% oracle will gather the stats and update it but it is not the case here.
second if we calculate stats manually even when data is changed less than 10%, will oracle gather the stats or not. In automatic stats gathering it will not gather but what about manual.?

Thanks
Abhishek

Report message to a moderator

Previous Topic: Install Oracle 10g R2
Next Topic: last_analyzed column is not getting updated after gather stats
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 28 13:42:48 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.