No question at this time
DBA Top 10
1 M. Cadot 17000
2 A. Kavsek 14300
3 B. Vroman 10300
4 M. Hidayathullah ... 8400
5 T. Boles 4900
6 P. Wisse 4400
7 J. Schnackenberg 3500
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 800
9 T. P 800
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48097
Total active users1702
Act. users last 24h1
Act. users last hour0
Registered user hits last week237
Registered user hits last month1339
Go up

difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_history
Next thread: java version
Prev thread: oracle architecture

Message Score Author Date
Hello Everyone, the scenerio is like below. Every...... abhishek jindal Apr 18, 2017, 18:02
Crossref: http://www.orafaq.com/forum/t/203247...... Michel Cadot Apr 18, 2017, 18:56
Hi, In Addition to above reference, please chec...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Mirza Hidayathullah Baig Apr 19, 2017, 11:19

Follow up by mail Click here


Subject: difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_history
Author: abhishek jindal, India
Date: Apr 18, 2017, 18:02, 242 days ago
Os info: AIX 6
Oracle info: 11.2.0.4
Message: 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 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
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_history
Author: Michel Cadot, France
Date: Apr 18, 2017, 18:56, 242 days ago
Message:
Crossref:
http://www.orafaq.com/forum/t/203247/

Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: difference between LAST_ANALYZED of dba_tables and STATS_UPDATE_TIME column of dba_tab_stats_history
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Apr 19, 2017, 11:19, 242 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi,

In Addition to above reference, please check this mos note disparity between num_rows and count(*) of table.

Disparity between num_rows from dba_tables and count(*) from the table, even if new stats exists using DBMS_STATS (Doc ID 795979.1)

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here