Old Habits

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.

About these ads

2 thoughts on “Old Habits

  1. 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.
      
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s