| dbms_stats.set_table_prefs and "LIST STALE" [message #593165] |
Wed, 14 August 2013 13:37  |
 |
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 #593174 is a reply to message #593165] |
Wed, 14 August 2013 14:13  |
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 #593177 is a reply to message #593175] |
Wed, 14 August 2013 14:44 |
|
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 |
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 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
