Database Administration (MOSC)

MOSC Banner

Ways to improve statistics gathering

edited Jan 24, 2018 10:40AM in Database Administration (MOSC) 8 commentsAnswered

Hello

I'm on a database 11.2.4.

I need to gather statistics for daily partitions. I EXECUTE THE BELOW:

exec dbms_stats.gather_table_stats(ownname => 'SCHEMA', tabname => 'TABLE_NAME', partname => 'P_20170914', estimate_percent => 1, degree => dbms_stats.auto_degree, cascade => DBMS_STATS.AUTO_CASCADE);

It takes 35 minutes for each day and I only choose 1%.

1) Is there anything else I could do to use less rows for sample?

2) What does block_sample parameter do? Oracle help says:

    "Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics."

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