Skip to Main Content
  • Questions
  • Gather stale stats at table level using prefs as stale_percent

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manoj.

Asked: September 01, 2015 - 5:48 pm UTC

Last updated: September 18, 2015 - 3:25 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi,
I need to gather table level stats using customised stale_percent value stored in some xyz table.when I run below code in a stored procedure,the code gather stats for the table even if it was not stale .What parameter or options are there to execute table stats if it is stale at 10% (default) or as per customized value.


IF stale_percent > 0 then
dbms_stats.set_table_prefs(owner,table_name,'STALE_PERCENT',stale_percent);
END IF;

dbms_stats.gather_table_stats
(ownname,tabname,estimate_percent,block_sample,method_opt,degree,granularity,cascade,no_invalidate);

IF stale_percent > 0 then
dbms_stats.delete_table_prefs(owner,table_name,'STALE_PERCENT');
END IF;

Thanks
Manoj

and Chris said...

Calling dbms_stats.gather_table_stats will always gather stats for the table you provide.

If you want to have a custom stale_percent for each table, just set this once for each table as you did in your first dbms_stats.set_table_prefs call.

Then call dbms_stats.gather_schema_stats (or gather_database_stats) with the options set to "GATHER_STALE" (or rely on the automatic job to do it for you).

There's no need to delete your preference again (unless you want them to return to the default).

For example, let's create two tables and set one to have a stale % of 90, the other 5%. Both have 100 rows:

create table high_pct (x not null) as 
 select rownum r from dual connect by level <= 100;
 
create table low_pct (x not null) as 
 select rownum r from dual connect by level <= 100;

exec dbms_stats.set_table_prefs(user,'HIGH_PCT','STALE_PERCENT',90);
exec dbms_stats.set_table_prefs(user,'LOW_PCT','STALE_PERCENT',5);


After we call table stats, both are analyzed:

exec dbms_stats.gather_table_stats(user,'HIGH_PCT');
exec dbms_stats.gather_table_stats(user,'LOW_PCT');

select table_name, last_analyzed from user_tables
where  table_name like '%PCT';

TABLE_NAME LAST_ANALYZED     
---------- -------------------
LOW_PCT    01/09/2015 11:18:50
HIGH_PCT   01/09/2015 11:18:50


If we delete half the rows, we only want LOW_PCT to be reanalyzed. Provided you call gather_schema_stats with the "GATHER STALE" option, this is what happens:

delete high_pct where x <= 50;
delete low_pct where x <= 50;

commit;

exec dbms_lock.sleep(15); -- just wait to make the difference obvious

exec dbms_stats.gather_schema_stats(user, options => 'GATHER STALE');

select table_name, last_analyzed from user_tables
where  table_name like '%PCT';

TABLE_NAME LAST_ANALYZED     
---------- -------------------
LOW_PCT    01/09/2015 11:19:05 <-- only this is reanalyzed
HIGH_PCT   01/09/2015 11:18:50


If you omit the option, then both are reanalyzed:

exec dbms_stats.gather_schema_stats(user);

select table_name, last_analyzed from user_tables
where  table_name like '%PCT';

TABLE_NAME LAST_ANALYZED     
---------- -------------------
LOW_PCT    01/09/2015 11:23:43 <-- both have the same time
HIGH_PCT   01/09/2015 11:23:43 <-- both have the same time

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

We miss the Real Tom

A reader, September 01, 2015 - 11:48 pm UTC

This is where we all miss the real Tom.

He doesn't provide the script like this, he will share the execution spool to us. We miss you Tom.
Connor McDonald
September 02, 2015 - 2:34 pm UTC

We miss Tom too!

If you have ideas or suggestions on how the new Ask Tom site should work then head to https://community.oracle.com/community/database/asktom/content?filterID=contentstatus%5Bpublished%5D~objecttype~objecttype%5Bidea%5D to submit and vote on ideas.

We all miss Tom

Chris, September 02, 2015 - 9:35 pm UTC

however I find the response format here very useful / concise ;)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library