4 Replies Latest reply: Jan 28, 2013 7:11 AM by Duc Nguyen RSS

    DBMS_STATS.SET_TABLE_PREFS does not work as expected

    765394
      Hi there!

      I am experiencing rather strange behaviour of the DBMS_STATS.SET__PREFS proceduree.

      I want to achieve that the statistics on the table is gathered with degrre = 4 in parallel and I want to achieve that by setting table preferences and not explicitely mentioning degree in the call to gather table statistics.

      This is what I've done:

      -first check global preferences:

      select dbms_stats.get_prefs('DEGREE', 'GLOBAL') from dual;
      ======================================
      NULL

      Then get table preferences :

      select
      dbms_stats.get_prefs('DEGREE', 'DARL', 'EIGNUNGSEINZELERGEBNISSE' )
      from dual;
      ================================================
      NULL

      Then I try to set DEGREE to 4:

      begin
      dbms_stats.set_table_prefs('DARL', 'EIGNUNGSEINZELERGEBNISSE', 'DEGREE', '4');
      end;

      and after that I query preferences:

      select
      dbms_stats.get_prefs('DEGREE', 'DARL', 'EIGNUNGSEINZELERGEBNISSE' )
      from dual;
      ====================================================
      4

      But when I run

      begin
      dbms_stats.gather_table_stats('DARL', 'EIGNUNGSEINZELERGEBNISSE' );
      end;

      statistics is not gathered in parallel..

      I just need to mention degree explicitely- and it runs in parallel:

      begin
      dbms_stats.gather_table_stats('DARL', 'EIGNUNGSEINZELERGEBNISSE' , degree => '4' );
      end;


      Now - I simply checked the prosesses each command produced...in the latter example one could clearly see th 4 parallel processes.

      To be honest, I dont't now in which catalog view could I look up if the resulting statistics gathering operation ran in parallel or not.


      And the table is 5,4 G and partitioned..it takes a while to gather statistics, with all the other parameters as they are.

      What is going on here.. did I forget something. Is there any other parameter that needs to be set for the preferences to function properly?

      Database version is 11.2

      Edited by: Reggy on Nov 26, 2012 2:25 PM

      Edited by: Reggy on Nov 27, 2012 8:18 AM
        • 1. Re: DBMS_STATS.SET_TABLE_PREFS does not work as expected
          Max Seleznev
          I just checked and got the same result in 11.2 database.

          At first I noticed the following in the description of the procedure:

          ...DBMS_STATS may use serial execution if the size of the object does not warrant parallel execution.

          I checked on 4G table and 250G table with the same result.

          The second thing I noticed was how default degree is calculated in the procedure:
          ...
            degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
          ...
          Please note the use of currently deprecated DBMS_STATS.GET_PARAM procedure that is replaced by GET_PREFS. The former would not pick up preferences set on the table level. If you set global preferences using DBMS_STATS.SET_GLOBAL_PREFS it would be used as the default (I verified).

          Of cause depending on your situation you might find that solution unacceptable. I this case you can still explicitly specify the degree as you did before.

          Looks like a bug to me, but of cause I have no confirmation of it yet.

          Hope it helps.
          • 2. Re: DBMS_STATS.SET_TABLE_PREFS does not work as expected
            765394
            Hi Max!

            Thanks very much for taking time to explore this.. I know, this paragraph , about oracle perhaps still using serial execution, despite degree set, is really disturbing.



            It really means we do not have control at all- and also what criteria then does oracle use to decide if the table is big or not..


            As for

            degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

            I interpret it this way:

            -default value for degree is obtained from the function get_param
            -but you can override this by executing set_table_prefs for degree

            so it still should work. But it does not work. The question is why?

            As for my implementation of gather_statistics, I may as well mention degree explicitely- that is no problem.
            But I just want to understand what is going on here - since this problem brings the whole concept of hierarchy of preferences down. Why do we need set_table_prefs then at all ?

            Edited by: Reggy on Nov 27, 2012 8:40 AM
            • 3. Re: DBMS_STATS.SET_TABLE_PREFS does not work as expected
              Max Seleznev
              SAR submitted with Oracle Support.
              • 4. Re: DBMS_STATS.SET_TABLE_PREFS does not work as expected
                Duc Nguyen
                Did you get the answer from the SAR you opened?

                Thanks,