Home » RDBMS Server » Performance Tuning » dbms_stats.set_table_prefs and "LIST STALE" (11.2.0.3.0)
dbms_stats.set_table_prefs and "LIST STALE" [message #593165] Wed, 14 August 2013 13:37 Go to next message
johnp12
Messages: 5
Registered: February 2012
Junior Member
Hello,

I am running version 11.2.0.3.0

I want to set the 'STALE_PERCENT' for a specific table to 5% (vs the default which is 10%)

I am running the following:

declare
mylist dbms_stats.objecttab;
row_count number;
begin
dbms_stats.gather_database_stats(OPTIONS=>'LIST STALE',GRANULARITY=>'ALL',CASCADE=> TRUE,objlist=>mylist);
if (mylist.count > 0) then
for i in mylist.first .. mylist.last
loop
dbms_output.put_line(mylist(i).OWNNAME||'.'||mylist(i).OBJNAME||'.'||mylist(i).PARTNAME);
end loop;
end if;
end;

When I run this with the default value or 10% which I confirm by running:
select dbms_stats.get_prefs('STALE_PERCENT') from dual;
the table called BEN.BEN_EXT_RSLT does not show as stale.

If I change the global prefs to 5% by running
exec dbms_stats.set_global_prefs('STALE_PERCENT', 1);
and confirming the set value with
select dbms_stats.get_prefs('STALE_PERCENT') from dual;
then the table BEN.BEN_EXT_RSLT now does show as stale.


If I change the table prefs just for that 1 table to 5% (with global set back to defualt of 10%) by running
exec dbms_stats.set_table_prefs('BEN', 'BEN_EXT_RSLT_DTL', 'STALE_PERCENT', 1);
and confirming the set value with
select dbms_stats.get_prefs('STALE_PERCENT', 'BEN', 'BEN_EXT_RSLT') from dual;
the table BEN.BEN_EXT_RSLT DOES NOT show as stale.



So what I am seeing is if the global STALE_PERCENT value is decreased, this table shows as stale
but if I change the STALE_PERCENT value on the table level it is not.


Am I missing something? I assume that it should not matter whether the 'STALE_PERCENT' value
is changed globally or just for that 1 table, the same rsults should show listing all stale opbject?

thanks for any light you can shed on this

John

Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593173 is a reply to message #593165] Wed, 14 August 2013 14:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
That's not the correct way. STALE_PERCENT will only be used if you use GATHER STALE. You can find that in GATHER_SCHEMA_STATS options for different parameters.
Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593174 is a reply to message #593165] Wed, 14 August 2013 14:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ok let's do it other way, try assigning the percentage to more than 10%, something like 15%. And then verify on the table.

exec dbms_stats.set_table_prefs('BEN', 'BEN_EXT_RSLT_DTL', 'STALE_PERCENT', 15);


If this works, then it certainly has to do something with the default percentage of 10%.

[EDIT : Please ignore it, since I just tested and it works for me for less than 10% as well]

[Updated on: Wed, 14 August 2013 14:16]

Report message to a moderator

Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593175 is a reply to message #593173] Wed, 14 August 2013 14:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Go to page 3 in this pdf

Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593177 is a reply to message #593175] Wed, 14 August 2013 14:44 Go to previous messageGo to next message
johnp12
Messages: 5
Registered: February 2012
Junior Member
thanks. I read on page 3:

"However, it is occasionally necessary to change the default value of one of the statistics gathering parameters, which can be accomplished by using the DBMS_STATS.SET_*_PREF procedures. Parameter values should be changed at the smallest scope possible, ideally on a per-object bases. For example, if you want to change the staleness threshold for a specific table, so its statistics are considered stale when only 5% of the rows in the table have changed rather than the default 10%, you can change the STALE_PERCENT table preference for that one table using the DBMS_STATS.SET _TABLE_PREFS procedure. By changing the default value at the smallest scope you limit the amount of non-default parameter values that need to be manually managed."

so in my example that is what I was doing. Changing the staleness threshold for just 1 table, but the
dbms_stats.gather_database_stats(OPTIONS=>'LIST STALE',GRANULARITY=>'ALL',CASCADE=> TRUE,objlist=>mylist);
command does not seem to take the new percentage set with the set_table_prefs command. It does take the new percentage when I set it at the global level.

This does not seem correct......?
(set_global_prefs)
Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593179 is a reply to message #593177] Wed, 14 August 2013 14:55 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Quote:
If I change the table prefs just for that 1 table to 5% (with global set back to defualt of 10%) by running
exec dbms_stats.set_table_prefs('BEN', 'BEN_EXT_RSLT_DTL', 'STALE_PERCENT', 1);
and confirming the set value with
select dbms_stats.get_prefs('STALE_PERCENT', 'BEN', 'BEN_EXT_RSLT') from dual;
the table BEN.BEN_EXT_RSLT DOES NOT show as stale.


Isn't it quite obvious. With dbms_stats.get_prefs you will get only the global preferences. Global preferences are stored in the internal dictionary tables used by the statistics gather code.

Request you to post the complete SQL*Plus session.

[Updated on: Wed, 14 August 2013 15:05]

Report message to a moderator

Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593181 is a reply to message #593177] Wed, 14 August 2013 15:09 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Gee...how did I miss it, your DB version is 11g. So, it might be that the PUBLISH attribute in DBMS_STATS.GET_PREFS is set to 'FALSE'.

Run this -

SELECT DBMS_STATS.get_prefs('PUBLISH') FROM dual;

By default, it should be true, unless it is set to false
Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593188 is a reply to message #593181] Wed, 14 August 2013 16:17 Go to previous messageGo to next message
johnp12
Messages: 5
Registered: February 2012
Junior Member
Hi,

Thanks for the info, but when I ran SELECT DBMS_STATS.get_prefs('PUBLISH') FROM dual;
it returned TRUE
Re: dbms_stats.set_table_prefs and "LIST STALE" [message #593306 is a reply to message #593188] Thu, 15 August 2013 10:12 Go to previous message
johnp12
Messages: 5
Registered: February 2012
Junior Member
thanks for all your help. I think I have it working now. I started using the delte_prefs command when I want to go back to use globals and it seems to work now. I think my testing was not clean
Previous Topic: about index
Next Topic: identifying query problem
Goto Forum:
  


Current Time: Thu Mar 28 18:03:16 CDT 2024