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.

2 thoughts on “GATHER_STATS_JOB job log history

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>