Lately been working a lot of upgrades to 11g. I know 12c is just around the corner 10gR2 was a pretty good release and there is still a large install base.
One the major items that are of concern when upgrading the database besides ensuring basic functionality is performance regressions mainly due to execution plan changes from using a new optimizer. With the introduction of the cost based optimizer years ago statistics about the data are extremely important for the optimizer to find the right plan for a query.
Prior to 10g collecting stats was a manual process or cron job and starting with 10g a nightly stats job was scheduled when you created a database. The default stats job was ok but many people simply collected stats on 10g the same as 9i or tried the 10g default stats job, had a bad experience and wrote it off completely. Now they are upgrading again to 11g and remember the bad experience they are writing of the 11g stats job and collecting stats they same way they did in 10g or even 9i.
When learning ride a bike did you ever fall at first? Have you tried a new food didn’t like it and maybe a few years later tried it again and found your tastes have changed? Have you ever tried software that maybe had issues and didn’t use it but found a later release to be much better? The default stats job in 11g is greatly improved. I am not going to say its perfect and there are not cases where manually collecting the stats will be required. What I will say is when upgrading to 11g don’t write off the default stats job without first testing it. Especially the auto sample size is greatly improved. A old post but a good one from Greg Rahn is a good read on why the new dbms stats is worth a try.
Hi All, I am on 11.2 on Linux. Need some help on automatic ( I mean Oracle’s default ) stats gathering. Let me confess that, I have read the Performance Tuning Manual of 11.2, but could not find answers for my questions. What is the job name for (Oracle’s default) stats gathering job in 11g? Where can I see the time this job runs? how can I change it? Where can I see the parameters with which stats is running? i.e. I want to check degree of parallelism, estimate percentage of the automatic stats collection job. Also I want the automatic stats collection job to not create any histograms, is that possible? with normal dbms_stats.gather_table_stats, I an set the method_opt and change the histogram setting, how to do it with the automatic stats job? Any help is much appriciated. Thanks in advance.
Would recommend disabling the default job and then creating a new job with the options you want.
SQL> SELECT owner, job_name, enabled FROM dba_scheduler_jobs; OWNER JOB_NAME ENABL ------------------------------ ------------------------------ ----- SYS XMLDB_NFS_CLEANUP_JOB FALSE SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE SYS FGR$AUTOPURGE_JOB FALSE SYS BSLN_MAINTAIN_STATS_JOB TRUE SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SYS HM_CREATE_OFFLINE_DICTIONARY FALSE SYS ORA$AUTOTASK_CLEAN TRUE SYS FILE_WATCHER FALSE SYS PURGE_LOG TRUE ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE OWNER JOB_NAME ENABL ------------------------------ ------------------------------ ----- ORACLE_OCM MGMT_CONFIG_JOB TRUE EXFSYS RLM$SCHDNEGACTION TRUE EXFSYS RLM$EVTCLEANUP TRUE 14 rows selected. SQL> BEGIN DBMS_SCHEDULER.DISABLE('BSLN_MAINTAIN_STATS_JOB'); END; / 2 3 4 PL/SQL procedure successfully completed. SQL> SELECT owner, job_name, enabled FROM dba_scheduler_jobs; OWNER JOB_NAME ENABL ------------------------------ ------------------------------ ----- SYS XMLDB_NFS_CLEANUP_JOB FALSE SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE SYS FGR$AUTOPURGE_JOB FALSE SYS BSLN_MAINTAIN_STATS_JOB FALSE SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SYS HM_CREATE_OFFLINE_DICTIONARY FALSE SYS ORA$AUTOTASK_CLEAN TRUE SYS FILE_WATCHER FALSE SYS PURGE_LOG TRUE ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE OWNER JOB_NAME ENABL ------------------------------ ------------------------------ ----- ORACLE_OCM MGMT_CONFIG_JOB TRUE EXFSYS RLM$SCHDNEGACTION TRUE EXFSYS RLM$EVTCLEANUP TRUE 14 rows selected.