This discussion is archived
4 Replies Latest reply: Jan 28, 2013 5:11 AM by Duc Nguyen RSS

DBMS_STATS.SET_TABLE_PREFS does not work as expected

765394 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    SAR submitted with Oracle Support.
  • 4. Re: DBMS_STATS.SET_TABLE_PREFS does not work as expected
    Duc Nguyen Newbie
    Currently Being Moderated
    Did you get the answer from the SAR you opened?

    Thanks,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points