Sorry for that :( Can you see it now? Jurijs =================================================== It is interesting enough what often people think they are reducing statistics gathering time by decreasing the estimate_percent parameter. Take a look on the test below. According to the test results using 100% as sample size for the particular table is quicker then 20%. Juan – unfortunately there is no an universal answer to your question. Statistics gathering is very case specific think. Sample size =100% delivers 100% precise statistics and the statistics gathering process is not necessary slower then using 20% or 10%. Some times 1% sample size is good enough to give the optimizer all necessary information. Sometimes 100% statistics doesn't help to improve situation. This is why some one pays DBA-s for the work they are doing ;) J. SYS:testj102> select * from v$version SYS:testj102> / BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SYS:testj102> drop table test_gather_stat SYS:testj102> / Table dropped. SYS:testj102> create table test_gather_stat tablespace users as SYS:testj102> select lpad('a',1000,'a') text from dba_objects where rownum <=40000 SYS:testj102> / Table created. SYS:testj102> insert --+ APPEND SYS:testj102> into test_gather_stat a select * from test_gather_stat SYS:testj102> / 40000 rows created. SYS:testj102> commit; Commit complete. SYS:testj102> insert --+ APPEND SYS:testj102> into test_gather_stat a select * from test_gather_stat SYS:testj102> / 80000 rows created. SYS:testj102> commit; Commit complete. SYS:testj102> insert --+ APPEND SYS:testj102> into test_gather_stat a select * from test_gather_stat SYS:testj102> / 160000 rows created. SYS:testj102> commit; Commit complete. SYS:testj102> insert --+ APPEND SYS:testj102> into test_gather_stat a select * from test_gather_stat SYS:testj102> / 320000 rows created. SYS:testj102> commit; Commit complete. SYS:testj102> select count(*) from test_gather_stat; COUNT(*) ---------- 640000 SYS:testj102> select bytes/1024/1024 from dba_segments where segment_name = 'TEST_GATHER_STAT'; BYTES/1024/1024 --------------- 725 SYS:testj102> SYS:testj102> ---------------------------------------------------- SYS:testj102> SYS:testj102> alter system flush BUFFER_CACHE; System altered. SYS:testj102> set timing on SYS:testj102> begin sys.dbms_stats.gather_table_stats(ownname=>null,tabname=>'TEST_GATHER_STAT',esti mate_percent=>20); end; SYS:testj102> / PL/SQL procedure successfully completed. Elapsed: 00:00:19.05 SYS:testj102> set timing off SYS:testj102> select NUM_ROWS, SAMPLE_SIZE from dba_tables where table_name = 'TEST_GATHER_STAT'; NUM_ROWS SAMPLE_SIZE ---------- ----------- 639570 127914 SYS:testj102> SYS:testj102> SYS:testj102> alter system flush BUFFER_CACHE; System altered. SYS:testj102> set timing on SYS:testj102> begin sys.dbms_stats.gather_table_stats(ownname=>null,tabname=>'TEST_GATHER_STAT',esti mate_percent=>100); end; SYS:testj102> / PL/SQL procedure successfully completed. Elapsed: 00:00:18.02 SYS:testj102> set timing off SYS:testj102> select NUM_ROWS, SAMPLE_SIZE from dba_tables where table_name = 'TEST_GATHER_STAT'; NUM_ROWS SAMPLE_SIZE ---------- ----------- 640000 640000 - Show quoted text - On 1/25/06, Juan Carlos Reyes Pacheco wrote: > Hi, could you give an advice on this please? > > What is better to se sample size to 20 or use dbms_stats.auto_sample_size > > Reading on asktom I found that some times used a 100 sample size, when > using dbms_stats.auto_sample_size, > > Becuase I'm an oracle standard database user, I can think in using a > 20% sampling size. > > Any comment? > > Thank you :) > -- > http://www.freelists.org/webpage/oracle-l > > > -- Jurijs +44 7738 013090 (GMT) ============================================ http://otn.oracle.com/ocm/jvelikanovs.html