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. |
About Post Author
Preetam says:
I need to update last stats gather status and rebuild index status, is there any query related to index rebuild
Yannick Jaquier says:
Not getting the question…
Venkat says:
dba_tab_stats_history AND dba_optstat_operations both are helpful Thanks!
Yannick Jaquier says:
Welcome !
Nasir says:
SELECT * FROM dba_optstat_operations ORDER BY start_time DESC;
Thanks above query helped me a lot,
Yannick Jaquier says:
Happy to have helped 🙂