RE: 9.2.0.6 Optimizer issue

From: Ignizio, Richard <richard.ignizio_at_PaeTec.com>
Date: Fri, 6 May 2005 11:32:38 -0400
Message-ID: <D63E582D09D77648A106C164FC7C4832010373C7@mail1-corp.corp.paetec.com>

Thanks for the response.

As of right now I do not back up old statistics, but I might make it a = practice now.=20

None of the tables where dropped or recreated. Columns were added to a = few tables but I need to verify if any of the tables in the query were = effected.=20

The database was shutdown before the application upgrade for backups, = would that erase the contents of the sys.col_usage$ table?

The tables in the query are application tables, Materialized views and = one Remote table.

Would histograms affect whether Oracle chose to do a NL or a HJ?

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Friday, May 06, 2005 11:21 AM
To: Ignizio, Richard
Cc: oracle-l_at_freelists.org
Subject: Re: 9.2.0.6 Optimizer issue

Did the application upgrade touch the tables used in this query such=20 that they would have been dropped and recreated? This would wipe out the =

predicate usage history on which "size auto" depends when deciding which =

columns to collect histograms on.

You can validate that by checking table sys.col_usage$. Or simply=20 compare the statistics from before (you DO backup your statistics, don't =

you) to now and see if some histograms went missing.

Ignizio, Richard wrote:
> Here is the oddity/question, Last weekend we had an application =
upgrade =3D
> and I had to re-analyzed the database. The difference in the way I =3D
> gathered the stats changed as well for the method_opt I changed 'FOR =
ALL =3D
> INDEXED COLUMNS SIZE SKEWONLY' to 'FOR ALL INDEXED COLUMNS SIZE AUTO'. =
=3D
> Last week the report ran in less than 15 minutes. Would this change be =
=3D
> the make that much of a difference?

>=20
> I normally do not analyze the database often because the database is =
=3D
> running optimally and there are no Huge changes to the database.=3D20

>=20

> 9.2.0.6 32-bit
> Sun Solaris 2.8

>=20
>=20

> Rich

>=20
>=20

> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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

Received on Fri May 06 2005 - 11:37:03 CDT