GATHER_STATS_JOB job log history
Following my concrete example with DBMS_SQLTUNE package, another questions from developers came to me. They noticed explain plan changed and they wanted to know if it’s linked to statistics… In reality the question I had to answer was showing them that explain changed because they gathered statistics, even if they claimed they did not do anything (maybe a DBA did gathered statistics but who knows)…
With Oracle Enterprise Manager 11g Release 1 Grid Control you can easily have the list of gathered objects, but unfortunately only for LAST run (unless I’m wrong). Maybe a bug but my Grid Control is also not showing me up history of last run of GATHER_STATS_JOB (!!), you can fortunately get it with SQL:
SQL> col job_name for a30
SQL> set lines 150
SQL> select * from
(SELECT log_date,job_name,status,actual_start_date,run_duration
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name='GATHER_STATS_JOB'
ORDER BY log_id DESC)
where rownum<=2;
LOG_DATE JOB_NAME STATUS
--------------------------------------------------------------------------- ------------------------------ ------------------------------
ACTUAL_START_DATE
---------------------------------------------------------------------------
RUN_DURATION
---------------------------------------------------------------------------
15-OCT-11 06.03.45.509690 AM +02:00 GATHER_STATS_JOB SUCCEEDED
15-OCT-11 06.00.04.833297 AM +02:00
+000 00:03:41
14-OCT-11 10.08.47.991413 PM +02:00 GATHER_STATS_JOB SUCCEEDED
14-OCT-11 10.00.02.106387 PM +02:00
+000 00:08:46
Another way of getting it:
SQL> set lines 150 SQL> col OPERATION for a30 SQL> col TARGET for a5 SQL> col START_TIME for a40 SQL> col END_TIME for a40 SQL> select * from dba_optstat_operations order by start_time desc; OPERATION TARGE START_TIME END_TIME ------------------------------ ----- ---------------------------------------- ---------------------------------------- gather_database_stats(auto) 17-OCT-11 10.00.04.700040 PM +02:00 17-OCT-11 10.06.22.846634 PM +02:00 gather_database_stats(auto) 15-OCT-11 06.00.04.902913 AM +02:00 15-OCT-11 06.03.45.238556 AM +02:00 gather_database_stats(auto) 14-OCT-11 10.00.02.846139 PM +02:00 14-OCT-11 10.08.47.847201 PM +02:00 gather_database_stats(auto) 13-OCT-11 10.00.05.705022 PM +02:00 13-OCT-11 10.03.40.920097 PM +02:00 gather_database_stats(auto) 12-OCT-11 10.00.03.516212 PM +02:00 12-OCT-11 10.04.06.077478 PM +02:00 gather_database_stats(auto) 11-OCT-11 10.00.04.323912 PM +02:00 11-OCT-11 10.04.19.991405 PM +02:00 gather_database_stats(auto) 10-OCT-11 10.00.03.998390 PM +02:00 10-OCT-11 10.06.25.212708 PM +02:00 gather_database_stats(auto) 08-OCT-11 06.00.05.349668 AM +02:00 08-OCT-11 06.07.58.159991 AM +02:00 gather_database_stats(auto) 07-OCT-11 10.00.06.537036 PM +02:00 07-OCT-11 10.13.14.905061 PM +02:00 gather_database_stats(auto) 06-OCT-11 10.00.04.281280 PM +02:00 06-OCT-11 10.04.46.006857 PM +02:00 gather_database_stats(auto) 05-OCT-11 10.00.04.528585 PM +02:00 05-OCT-11 10.04.53.280236 PM +02:00 gather_database_stats(auto) 04-OCT-11 10.00.04.859905 PM +02:00 04-OCT-11 10.04.06.237360 PM +02:00 gather_database_stats(auto) 03-OCT-11 10.00.04.539033 PM +02:00 03-OCT-11 10.06.03.698098 PM +02:00 gather_database_stats(auto) 01-OCT-11 06.00.03.928703 AM +02:00 01-OCT-11 06.09.39.288774 AM +02:00 gather_database_stats(auto) 30-SEP-11 10.00.04.683938 PM +02:00 30-SEP-11 10.12.05.517891 PM +02:00 gather_database_stats(auto) 29-SEP-11 10.00.04.601296 PM +02:00 29-SEP-11 10.06.50.656127 PM +02:00 gather_database_stats(auto) 28-SEP-11 10.00.04.674606 PM +02:00 28-SEP-11 10.04.38.091107 PM +02:00 gather_database_stats(auto) 27-SEP-11 10.00.05.192875 PM +02:00 27-SEP-11 10.07.30.950347 PM +02:00 gather_database_stats(auto) 26-SEP-11 10.00.05.285779 PM +02:00 26-SEP-11 10.11.26.997192 PM +02:00 gather_database_stats(auto) 24-SEP-11 06.00.04.786099 AM +02:00 24-SEP-11 06.11.00.648547 AM +02:00 gather_database_stats(auto) 23-SEP-11 10.00.06.465039 PM +02:00 23-SEP-11 10.07.53.603396 PM +02:00 gather_database_stats(auto) 22-SEP-11 10.00.05.062780 PM +02:00 22-SEP-11 10.08.18.389783 PM +02:00 gather_database_stats(auto) 21-SEP-11 10.00.04.478700 PM +02:00 21-SEP-11 10.07.11.143672 PM +02:00 gather_database_stats(auto) 20-SEP-11 10.00.04.006786 PM +02:00 20-SEP-11 10.03.49.751709 PM +02:00 gather_database_stats(auto) 19-SEP-11 10.00.11.977395 PM +02:00 19-SEP-11 10.26.51.780588 PM +02:00 gather_database_stats(auto) 17-SEP-11 06.00.04.258010 AM +02:00 17-SEP-11 06.08.14.187435 AM +02:00 26 rows selected.
But you can't have job log in a table and can't have the details of old runs... You sure ? Sometimes it's great to reinvent hot water and discover that a nice functionality is there since Oracle 10g. Means, yes you can know when statistics on a table have changed by using DBA_TAB_STATS_HISTORY table (job details is unfortunately not available):
SQL> set lines 180
SQL> select owner, table_name, stats_update_time
from dba_tab_stats_history
where owner='WCMS'
and table_name='DM_SYSOBJECT_R'
order by owner, table_name, stats_update_time desc;
OWNER TABLE_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ---------------------------------------------------------------------------
WCMS DM_SYSOBJECT_R 17-OCT-11 08.35.20.596509 AM +02:00
WCMS DM_SYSOBJECT_R 12-OCT-11 08.36.20.462195 AM +02:00
WCMS DM_SYSOBJECT_R 11-OCT-11 08.35.51.765837 AM +02:00
WCMS DM_SYSOBJECT_R 10-OCT-11 08.36.37.942588 AM +02:00
WCMS DM_SYSOBJECT_R 05-OCT-11 08.36.08.089924 AM +02:00
WCMS DM_SYSOBJECT_R 04-OCT-11 08.36.07.548707 AM +02:00
WCMS DM_SYSOBJECT_R 03-OCT-11 08.35.57.698962 AM +02:00
WCMS DM_SYSOBJECT_R 28-SEP-11 10.08.05.109658 AM +02:00
WCMS DM_SYSOBJECT_R 27-SEP-11 08.35.19.509380 AM +02:00
WCMS DM_SYSOBJECT_R 23-SEP-11 08.59.27.205902 AM +02:00
10 rows selected.
With above query I can easily see when statistics have changed. I can't know if they have been computed manually or by GATHER_STATS_JOB but time of the gathering give some clues of who/what has done it...
So to have number of objects per day which had their statistics changed (including GATHER_STATS_JOB and manual gathering) you can use:
SQL> set lines 150
SQL> set pages 200
SQL> select to_char(stats_update_time,'yyyy-mm-dd') as stats_update_time, count(*)
from dba_tab_stats_history
group by to_char(stats_update_time,'yyyy-mm-dd')
order by 1 desc;
STATS_UPDATE_TIME COUNT(*)
--------------------------------------------------------------------------- ----------
2011-10-17 822
2011-10-16 54
2011-10-15 263
2011-10-14 292
2011-10-13 217
2011-10-12 1046
2011-10-11 1022
2011-10-10 990
2011-10-09 36
2011-10-08 188
2011-10-07 220
2011-10-06 195
2011-10-05 990
2011-10-04 939
2011-10-03 963
2011-10-01 180
2011-09-30 241
2011-09-29 186
2011-09-28 976
2011-09-27 236
2011-09-26 267
2011-09-24 176
2011-09-23 302
2011-09-22 231
2011-09-21 239
2011-09-20 139
2011-09-19 253
2011-09-17 191
2011-09-16 245
29 rows selected.





I need to update last stats gather status and rebuild index status, is there any query related to index rebuild
Not getting the question…
dba_tab_stats_history AND dba_optstat_operations both are helpful Thanks!
Welcome !
SELECT * FROM dba_optstat_operations ORDER BY start_time DESC;
Thanks above query helped me a lot,
Happy to have helped 🙂