5 Replies Latest reply: Jan 24, 2014 4:22 PM by Alvaro RSS

    locked table stats

    1037975

      Hi All,
      We actually locked a table stats (TABLE1) with intention to prevent the nightly auto gather stats from picking up from stats gathering.
      Understand that the nightly gather stats job kick start at 10pm daily (during wkday).

      The below manual stats gathering will run about 4 hours.
      If we trigger the below at 8.30pm, will the nightly gather stats (10pm) pick it up for stats gathering?
      As the first sql will unlock the stats and lock it only after completion which is about 12.30am.

      exec dbms_stats.unlock_table_stats('AC', 'TABLE1');
      exec dbms_stats.gather_table_stats(ownname=>'ac',tabname=>'TABLE1',estimate_percent => 50,cascade=>true, method_opt => 'for all columns size 254');
      exec dbms_stats.lock_table_stats('AC', TABLE1);

       

      thanks

        • 1. Re: locked table stats
          Baris Yildirim

          Hi,

          Please find out gather_table_stats defaults.

           

          for example, what is default of estimate_percent --> auto or fixed value

           

          Regards

          • 2. Re: locked table stats
            Baris Yildirim

            you can also disable automatic gather job

             

            exec dbms_scheduler.disable(’SYS.GATHER_STATS_JOB’);

             

            Regards

            • 3. Re: locked table stats
              Baris Yildirim

              For example

               

              select dbms_stats.get_param('estimate_percent') from dual;

               

              select dbms_stats.get_param('degree') from dual;

               

              compare default values with your stats command's value.

               

              Regards

              • 4. Re: locked table stats
                Gamblesk

                Didn't see your version mentioned but there may be no need to unlock them if you are updating them manually. Just add the force parameter to your call to gather_table_stats if your version has that option.

                 

                From the doc..

                 

                force

                Gather statistics of table even if it is locked

                • 5. Re: locked table stats
                  Alvaro

                  It depends, it may or it may not.

                   

                  As the default GATHER_AUTO mode is basically automatic pilot, oracle may or may not end up gathering stats for your table while it is unlocked, since the two statistics jobs overlap with each other for about two hours as you said.

                   

                  The most sensible thing to do would be to change time of your customized stats job, in my opinion. The force option mentioned above would work as well.

                   

                  By the way, you do realize that you're forcing histogram creation for every single column in your table? Is this what you're trying to achieve? Histograms may end up hurting your performance more than helping if you don't know exactly where to build them.