Re: analyze vs dbms_stats
- From: "Juan Carlos Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jun 2004 12:54:04 -0400
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@xxxxxxxxxxxxx=0D
Date: 06/25/04 12:34:54=0D
To: oracle-l@xxxxxxxxxxxxx=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@xxxxxxxxxxxxx=0D
put 'unsubscribe' in the subject line.=0D
--=0D
Archives are at //www.freelists.org/archives/oracle-l/=0D
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
