Few days back , Martin had posted a series of post on a issue where GATHER_STATS_JOB was failing silently for a large object. If you have missed it, you can check following links
Automated statistics gathering silently fails
Automated Statistics Gathering Silently Fails #2
Automatic Statistics Gathering Fails #3
This post is based on some of the discussions on the thread. Please note that this is not intended to discuss bugs (if any) associated with the job
Gather_stats_job was introduced with Oracle 10g to gather statistics for database objects which has stale statistics (10% of data has changed, you can query dba_tab_modifications) or the tables for which the statistics has not been gathered (new tables created/truncated). This job runs during the maintenance window i.e every night from 10 P.M. to 6 A.M. and all day on weekends. This schedule can be though changed and process is documented in Metalink aka My Oracle Support Note 579007.1 –
This feature brought relief to lot of DBA’s as they did not have to write shell scripts to gather stats and could rely on this job to do the work. But slowly people realized that it does not fit in their environment and slowly recommendation turned from “Enabled ” to “Disabled “
1) Should I schedule job run for every night?
As documented this job gathers stats on the tables which have got 10% of data changes since last run. So this will not touch the tables for which the data changes are less then 10%. Also due to rolling invalidation feature , sql cursors will not be immediately invalidated (Refer to oracle forums discussion and Fairlie Rego’s post)
If you are still not happy , you can change the maintenance window timings to suit the schedule (say on weekends)
In 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor. All these 3 run in all maintenance windows. New maintenance windows introduced with 11g are
You can check the name and state of this job using following query
SQL> SELECT client_name, status FROM dba_autotask_operation; CLIENT_NAME STATUS ---------------------------------------------------------------- -------- auto optimizer stats collection ENABLED auto space advisor ENABLED sql tuning advisor ENABLED
Window – Description
MONDAY_WINDOW – Starts at 10 p.m. on Monday and ends at 2 a.m.
TUESDAY_WINDOW – Starts at 10 p.m. on Tuesday and ends at 2 a.m.
WEDNESDAY_WINDOW -Starts at 10 p.m. on Wednesday and ends at 2 a.m.
THURSDAY_WINDOW – Starts at 10 p.m. on Thursday and ends at 2 a.m.
FRIDAY_WINDOW -Starts at 10 p.m. on Friday and ends at 2 a.m.
SATURDAY_WINDOW – Starts at 6 a.m. on Saturday and is 20 hours long.
SUNDAY_WINDOW -Starts at 6 a.m. on Sunday and is 20 hours long.
To enable the job (in case it is disabled)
BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /
You can check history/run duration of these jobs by using following query
select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION from DBA_AUTOTASK_JOB_HISTORY where JOB_START_TIME >systimestamp -7 and client_name='sql tuning advisor' CLIENT_NAME JOB_NAME JOB_STATUS JOB_START_TIME JOB_DURATION ------------------------------ ------------------------------ --------------- -------------------------------------------------- -------------------- sql tuning advisor ORA$AT_SQ_SQL_SW_521 SUCCEEDED 30-MAR-12 04.00.01.698038 PM AMERICA/LOS_ANGELES +000 00:22:13 sql tuning advisor ORA$AT_SQ_SQL_SW_493 SUCCEEDED 01-APR-12 04.00.02.701398 PM AMERICA/LOS_ANGELES +000 00:42:26 sql tuning advisor ORA$AT_SQ_SQL_SW_522 SUCCEEDED 31-MAR-12 04.00.07.642613 PM AMERICA/LOS_ANGELES +000 00:00:37
2) I do not want gather_stats_job to gather stats on some of my tables.
This requirement can arise due to following points
a)There are tables for which you have set the stats manually
b) There are queries for which you know your old stats will work fine
c) Tables are big and gather_stats_job is silently failing ( Again refer to Martin’s Post)
d) Tables for which histograms cannot not be gathered or vice versa
e) Tables for which you would like to estimate fixed percent of blocks
For all these situations. you can use DBMS_STATS.LOCK_TABLE_STATS and gather stats manually with force =>true to override locked statistics. For big partitioned tables you can use COPY_TABLE_STATS and APPROX_GLOBAL AND PARTITION feature.
Oracle 11g also has enhancement to gathering stats on partitioned tables where in you can gather INCREMENTAL stats for partitions and oracle will automatically update global stats for table.This approach has advantage as we don’t scan table twice and reduces the time to gather stats drastically. You need to use DBMS_STATS.set_table_prefs procedure to set Incremental stats gathering to true.
Note that first time, stats gathering will take more time as oracle will create object called synposes for each paritition. Subsequent runs of gather_stats_job will be faster. Refer to Optimizer group post for more info on copy_table_stats and 11g incremental stats feature. ( Due to bug copy_table_stats does not alter low/high value. Details can be found here )
To fix histogram issue, DBMS_STATS.SET_PARAM can be used to modify the default attributes e.g By default, GATHER_STATS_JOB will gather histograms, which can be confirmed by running below query
select dbms_stats.get_param('method_opt') method_opt from dual; METHOD_OPT -------------------------------------------------------------------- FOR ALL COLUMNS SIZE AUTO
To disable histogram capture, use
exec dbms_stats.set_param('method_opt', 'FOR ALL COLUMNS SIZE 1');
In case you wish to capture histograms for some of the tables, then you can use
exec dbms_stats.set_param('method_opt', 'FOR ALL COLUMNS SIZE REPEAT')
i.e Collects histograms only on the columns that already have histograms
Starting Oracle 11g database you can use DBMS_STATS.SET_*_PREFS to take care of point (d) and (e) i.e you can change the default gather options for particular table. Details can be found here
This is not exhaustive list and I hope that this will grow so that we can have Recommendation for GATHER_STATS_JOB status to be set to “SCHEDULED” 🙂
This Post Has 9 Comments
Thanks for the links guys :-). And also for the pointers on how better control is available under 11g. There is also the ability to set the % change when tables are deemed stale at the table level that could help fine-tune automated stats gathering.
All in all I think it is turning into a much better utility, as more control over it is passed back to the DBA/Developer.
Thanks Martin for your comments 🙂
There is issue going with our customer about gathering stats.
It will be good for us if the stats are gathered automatically for the table whose data is changed by 10%. Just have written the automated scripts below. Please let me know the below scripts will gather a statitics automatically.
Will the below scripts will gather a stats automatically.
dbms_stats.gather_table_stats(ownname => ‘username,tabname=> ‘tablename’
estimate_percent => 30,cascade=> TRUE ,no_invalidate=> false);
Please let me know if the above package will not gather stats automatically.
Please tell me the correct dbms procedure to gather stats automatically when the data of table is changed by 10%.
I will be very thankful if you will sent me the dbms procedure else i have to run manually every day.
Why don’t you enable gather_stats_job. It does the same thing.
The above scripts we have to run manually when the user faces any performance problem.
I want to make the script to run automatically when the data of table change by 10%.
It would be great if you will sent me the automated stats collection scripts.
I previously set maintenance windows (monday, tuesday, ecc..) with start time at 02:00 am and duration of 20 hours.
And I got statistics collected every 4 hours. In fact I saw this in docs:
In the case of a very long maintenance window, all automated maintenance tasks except Automatic SQL Tuning Advisor are restarted every four hours. This feature ensures that maintenance tasks are run regularly, regardless of window size.
At a certain point I set start time 02:30 am and duration 90 minutes.
So I would expect optimizer statistics only run between 02:30 am and 04:00 am
Instead they continue to run apparently every 4 hours generating problems…
How can I prevent this?
OPERATION START_TIME END_TIME
—————————— ————————- ————————-
gather_database_stats(auto) 120419 10:06:06.9779 120419 10:29:12.3702
gather_database_stats(auto) 120419 06:05:47.8204 120419 06:31:04.5265
Is maintenance window setting changes dynamic or do I have to restart anything when changing them?
Thanks in advance,
It’s surprising to have this kind of behavior. Can you check the output of following query and ensure that duration is correctly specified as 90 mins
select WINDOW_NAME,repeat_interval,duration from dba_scheduler_windows
Ah, I found it.
Thursday window was 02:00 – 22:00
I changed it on thursday itself at 02:15 (an amusing night… 😉
So I changed the duration while the window was already open… I presume it is not dynamic.
Now (on Friday) the same modification I did on Thursday for Friday window is correctly in place.
Yesterday to solve the problem I had to disable the Thursday window and then re-enable it today
Thanks for your answer anyway.
One more question:
in OEM there is a field name “priority” for a scheduler window.
It defaults to low for the weekday windows (btw: I’m on 184.108.40.206).
I didn’t find precise references to the implications of setting it to high… any pointers?
By default window is created with LOW prioirty. If there are two overlapping windows with low and high priority. High priority window takes precedence and it is opened.