Gather stats - ESTIMATE_PERCENT

All posts relating to Oracle database administration.

Moderator: Tim...

Gather stats - ESTIMATE_PERCENT

Postby manishkumar08 » Mon Mar 25, 2013 7:41 am

Hi Tim,

Greetings !!

I was working on table statistics ... mainly on " ESTIMATE_PERCENT " part.
Below are my observations:-

I had created one table, and populated 10000 rows.
Code: Select all
SQL> select count(*) from TEST_STATS;

  COUNT(*)
----------
     10000

SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where TABLE_NAME='TEST_STATS';

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----------- ----------- ---------
TEST_STATS



On checking last_analyzed, sample_size, avg_row_len etc. . Output was NULL because stats was not gathered.
So, i gathered stats for my table TEST_STATS with default ESTIMATE_PERCENT (i.e 100%).
Code: Select all
SQL>
SQL> exec dbms_stats.gather_table_stats('FSRSTG_HOST','TEST_STATS');

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where TABLE_NAME='TEST_STATS';

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----------- ----------- ---------
TEST_STATS                          10000          73       10000 25-MAR-13


Here it shows 10000 rows was the total row count of table while stats gathering , 73 as avg row length and 10000 as sample stats size.
Now, further i populated the table with more 20000 rows.

Code: Select all
 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.
Code: Select all
SQL> exec dbms_stats.gather_table_stats('FSRSTG_HOST','TEST_STATS',ESTIMATE_PERCENT=>1)

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where TABLE_NAME='TEST_STATS';

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----------- ----------- ---------
TEST_STATS                          29796          73        4570 25-MAR-13


SQL> exec dbms_stats.gather_table_stats('FSRSTG_HOST','TEST_STATS',ESTIMATE_PERCENT=>1)

PL/SQL procedure successfully completed.

SQL>
SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where TABLE_NAME='TEST_STATS';

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----------- ----------- ---------
TEST_STATS                          30726          73        4786 25-MAR-13

SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats('FSRSTG_HOST','TEST_STATS',ESTIMATE_PERCENT=>1)

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where TABLE_NAME='TEST_STATS';

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----------- ----------- ---------
TEST_STATS                          30281          73        4673 25-MAR-13

SQL>
SQL>


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)

Code: Select all

SQL> exec dbms_stats.gather_table_stats('FSRSTG_HOST','TEST_STATS')

PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,SAMPLE_SIZE,LAST_ANALYZED from dba_tables where TABLE_NAME='TEST_STATS';

TABLE_NAME                       NUM_ROWS AVG_ROW_LEN SAMPLE_SIZE LAST_ANAL
------------------------------ ---------- ----------- ----------- ---------
TEST_STATS                          30000          73       30000 25-MAR-13

SQL>




Queries based on above oracle behaviour :-

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.

3) how will oracle decide for " auto sample size" ?

Thanks & Regards.
Manish Kumar
manishkumar08
Member
 
Posts: 32
Joined: Wed Feb 24, 2010 7:40 am

Re: Gather stats - ESTIMATE_PERCENT

Postby Tim... » Mon Mar 25, 2013 7:57 am

Hi.

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:

http://optimizermagic.blogspot.co.uk/20 ... stics.html

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 7 guests

cron