RE: 9.2.0.6 Optimizer issue
- From: "Ignizio, Richard" <richard.ignizio@xxxxxxxxxx>
- To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
- Date: Fri, 6 May 2005 11:32:38 -0400
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@xxxxxxxxxxxxx]
Sent: Friday, May 06, 2005 11:21 AM
To: Ignizio, Richard
Cc: oracle-l@xxxxxxxxxxxxx
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
> --
> //www.freelists.org/webpage/oracle-l
>=20
--=20
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
