Database Tuning (MOSC)

MOSC Banner

Gather_stats_job and dbms_stats

edited Oct 11, 2012 9:31PM in Database Tuning (MOSC) 15 commentsAnswered
Hi,
GATHER_STATS_JOB is taking to much time, and I am suspecting that its analyzing more data than it needs, where its analyzing 100% rows for tables with 10Billion rows, (sample_size/num_rows from dba_tables)
Therefore, I changed "estimate_percent" to 10, however, the value is still high and not 10% so I create the following test:
create table test1 as select ....
dbms_stats.gather_table_stats(ownname => 'USER1', tabname => 'TEST1', estimate_percent => 10, method_opt => 'for all indexed columns size auto', cascade => true);
However, after running the command above I run the following SQL:
SQL> select table_name, num_rows, sample_size, sample_size/num_rows from dba_tables where owner = 'USER1';

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