SQL> insert into TEST_STATS select * from CH_ACCT_INT_BREAKUP where rownum<10001;

10000 rows created.

SQL> insert into TEST_STATS select * from CH_ACCT_INT_BREAKUP where rownum<10001;

10000 rows created.

SQL>SQL> commit;

Commit complete.

SQL>SQL> select count(*) from TEST_STATS;

COUNT(*)---------- 30000

I again gathered stats on my table with estimate_percent 1%. Now, Oracle has my previous stats record for 10000 rows and had inserted more 20000 rows to the table. So, gathering stats on table should show me Number of rows as 30000. because this is the total no. of rows present on my table.

But above output shows num_rows value lesser than 30000 rows (on first run) and even more than actual no. of rows in table, also the sample size is less ( as compared with output of default value of estimte_percent taken earler).

On several attempts of running the same, the output varies on every run while the estimte_percent is kept constant as 1.If i again gather stats by keeping default value of estimate_percent, output shows NUM_ROWS & SAMPLE_SIZE as 30000 ( which should be the same)

1) How is NUM_ROWS , AVG_ROW_LEN & SAMPLE_SIZE columns behviour inter linked w.r.t "table stats " ?2) How does Oracle evaluate " estimate_percent " value while stats gathering. Does it evaluate as gathering stats of 1 % of rows only ( when specified estimate_percent => 1) of the table. OR it evaluates full table rows but at 1% chunks.

1) Not sure what you mean by this question. These statistics are calculated/estimated when you gather stats. They are some of the table stats, so not sure what you mean by "inter linked".

2) It only uses a percentage of the total rows in the table, which is why it doesn't give perfect results. It is taking a small sample of the data and using that to guess the real statistics.

3) If you don't specify an estimate percent, Oracle will do the default, which is auto sample size. In 11g onward, this is what you should use as it is the most efficient and most accurate of the samples. Maria Colgan discusses this topic here: