Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Yasvand.

Asked: March 05, 2020 - 11:28 am UTC

Last updated: March 05, 2020 - 2:30 pm UTC

Version: 19C

Viewed 1000+ times

You Asked

Hi Tom,

Ive been trying to locate the automatic stats gather job's code but I'm not sure if I'm looking at the right place.

Im sure its called by gather_st_job_continuous_proc but I'm not able to get the code.

I'm trying to create a PL/SQL to gather stats manually akin to automatic stats gather but with few changes. If I get the code it would be pretty helpful.

and Chris said...

I'm not sure what you mean by "get the code".

But in any case, rather than cobble together your own manual routines there's a much better way:

Set table preferences!

The automatic job will pick up these settings. And any manual call will default to these values too (though you can override them).

exec dbms_stats.set_table_prefs ( 'HR', 'EMPLOYEES', 'STALE_PERCENT', 1 );
exec dbms_stats.set_table_prefs ( 'HR', 'DEPARTMENTS', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE 1' );
exec dbms_stats.set_table_prefs ( 'SH', 'SALES', 'DEGREE', 4 );


There's also procedures to set these at the schema, database, and globally.

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.