SQL Performance (MOSC)

MOSC Banner

Dont want to use "auto optimizer stats collection" still want to have its benefits by manual run

edited Apr 7, 2017 5:00AM in SQL Performance (MOSC) 32 commentsAnswered

hi Friends,

I have one question. we dont want to use the Automatic Statistics Collection because it is causing slight overhead and want to collect stats only for a single schema.

But because i like that is takes cares of stale statistics check etc i would like to use it.

I went through Doc ID 1233203.1 and found that we can call it manually too by running below command

exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

My question:

1. How can we limit it running to only our main PROD application schema? There are lot of schemas in the DB for which we dont want the stats to run. Is there such an option. I read about DBMS_STATS.SET_*_PREFS but didnt understand it exactly.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center