Content-Type: Text/Plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
dbmsstats get more statistics than analyze, if you compare the columns in
the dba_xxx tables, you will see analize don't get statistics for several
new features=0D
Clearly, experts says you DON'T have to use ANALYZE. =0D
I never heard about a reason to still continue using analyze, neither bug=
s
that justify that, maybe someone knows.=0D
=0D
-------Original Message-------=0D
=0D
From: oracle-l_at_freelists.org=0D
Date: 06/25/04 12:34:54=0D
To: oracle-l_at_freelists.org=0D
Subject: analyze vs dbms_stats=0D
=0D
I'm trying to get our shop to convert from analyze to dbms_stats.=0D
I'm running into some "strange" results though and wanted to see if I'm=0D
missing something or you have some advice.=0D
=0D
analyze command:=0D
ESTIMATE STATISTICS SAMPLE 30=0D
PERCENT=0D
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 254=0D
=0D
dbms_stats code I'm running:=0D
exec DBMS_STATS.GATHER_TABLE_STATS ( -=0D
ownname =3D> 'STATION_TEST', -=0D
tabname =3D> 'MEMBERS', -=0D
partname =3D> NULL, -=0D
estimate_percent =3D> 30, -=0D
block_sample =3D> FALSE, -=0D
method_opt =3D> 'FOR ALL COLUMNS SIZE 254', -=0D
degree =3D> 0, -=0D
granularity =3D> 'DEFAULT', -=0D
cascade =3D> TRUE, -=0D
stattab =3D> NULL, -=0D
statid =3D> NULL, -=0D
statown =3D> NULL, -=0D
no_invalidate =3D> FALSE);=0D
=0D
sample of stats info from analyze:=0D
Table STATION_TEST.MEMBERS=0D
- Number of rows : 14284780=0D
- Number of blocks : 1623961=0D
- Average row length : 192=0D
=0D
Column RESTRICTION_CODE=0D
- Number of distinct values : 0=0D
- Number of nulls : 14284780=0D
=0D
Column DAY_PHONE=0D
- Number of distinct values : 51=0D
- Number of nulls : 14273930=0D
=0D
Column EVE_PHONE=0D
- Number of distinct values : 44=0D
- Number of nulls : 14284620=0D
=0D
Column ISP_ID=0D
- Number of distinct values : 10=0D
- Number of nulls : 14284770=0D
=0D
sample of dbms stats generated statistics:=0D
Table STATION_TEST.MEMBERS=0D
- Number of rows : 14283597=0D
- Number of blocks : 1623961=0D
- Average row length : 193=0D
=0D
Column RESTRICTION_CODE=0D
- Number of distinct values : 0=0D
- Number of nulls : 14283597=0D
=0D
Column DAY_PHONE=0D
- Number of distinct values : 95=0D
- Number of nulls : 14273027=0D
=0D
Column EVE_PHONE=0D
- Number of distinct values : 0=0D
- Number of nulls : 14283467=0D
=0D
Column ISP_ID=0D
- Number of distinct values : 0=0D
- Number of nulls : 14283577=0D
=0D
Column USER_ID (NOT NULL)=0D
- Number of distinct values : 14283597=0D
- Number of nulls : 0=0D
=0D
As it turns out analyze in this test is far more accurate. For instance=0D
eve_phone actually has 66 distinct values.=0D
Any ideas on the disparity? I am going to try compute, but apples to=0D
apples reflect a big diff...=0D
Thanks=0D
- David=0D
----------------------------------------------------------------=0D
Please see the official ORACLE-L FAQ: http://www.orafaq.com=0D
----------------------------------------------------------------=0D
To unsubscribe send email to: oracle-l-request_at_freelists.org=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at http://www.freelists.org/archives/oracle-l/=0D
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html=0D
- Binary/unsupported file stripped by Ecartis --
- Type: image/gif
- File: IMSTP.gif
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Jun 25 2004 - 11:55:16 CDT
