Fwd: dbms_stats.auto_sample_size or sample size 20
- From: Jurijs Velikanovs <j.velikanovs@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 31 Jan 2006 17:51:49 +0000
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@xxxxxxxxx> 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@xxxxxxxxxx> 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
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@xxxxxxxxx> 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
- Follow-Ups:
- standby and Oracle licensing
- From: David
- References:
- dbms_stats.auto_sample_size or sample size 20
- From: Juan Carlos Reyes Pacheco
- Re: dbms_stats.auto_sample_size or sample size 20
- From: Jurijs Velikanovs
- Re: dbms_stats.auto_sample_size or sample size 20
- From: Ray Stell
Other related posts:
- » Fwd: dbms_stats.auto_sample_size or sample size 20
- standby and Oracle licensing
- From: David
- dbms_stats.auto_sample_size or sample size 20
- From: Juan Carlos Reyes Pacheco
- Re: dbms_stats.auto_sample_size or sample size 20
- From: Jurijs Velikanovs
- Re: dbms_stats.auto_sample_size or sample size 20
- From: Ray Stell