Gather_stats_job and dbms_stats
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';
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';
0