Fwd: dbms_stats.auto_sample_size or sample size 20

From: Jurijs Velikanovs <j.velikanovs_at_gmail.com>
Date: Tue, 31 Jan 2006 17:51:49 +0000
Message-ID: <d6f0def50601310951y4e1a4f75r5d56a37cd37c7c89@mail.gmail.com>

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 <juancarlosreyesp_at_gmail.com> 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

On 1/31/06, Ray Stell <stellr_at_cns.vt.edu> wrote:


>

> My thoughts exactly.

>

>

>

> On Tue, Jan 31, 2006 at 05:18:25PM +0000, Jurijs Velikanovs wrote:

> > SXQgaXMgaW50ZXJlc3RpbmcgZW5vdWdoIHdoYXQgb2Z0ZW4gcGVvcGxlIHRoaW5rIHRoZXkgYXJl

>



--
Jurijs
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html



--
Jurijs
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html


--
http://www.freelists.org/webpage/oracle-l




text/plain attachment: test.txt

Received on Tue Jan 31 2006 - 11:51:49 CST