Re: Optimizer change from 8i-9i
- From: zhu chao <zhuchao@xxxxxxxxx>
- To: Christian Antognini <Christian.Antognini@xxxxxxxxxxxx>
- Date: Wed, 11 May 2005 21:36:41 +0800
As Wolfgang said, I think although your autotrace shows RULE
optimizer, oracle maybe actually using CBO.
Can you enable SQL Trace and check whether it is actually parsed by RBO or =
CBO?
By the way, team, anyone aware of some common situlation that
autotrace can lie to us printing the execution plan and the optimizer
used?
Wolfgang, Thanks very much for you so detailed answer.
Can I ask one more question , about the=20
"the index tie-break rule being the only reasonable exception, all
the other possibilities are a bit far fetched IMO"
I never knew this, index tie-break rule.
Thanks.
By the way, Chao is my given name. We speel the name like "Family
Name-Given name":)
On 5/11/05, Christian Antognini <Christian.Antognini@xxxxxxxxxxxx> wrote:
> Hi
>=20
> > About the RBO change in 9i, can you show me a test case?
>=20
> E.g. complex_view_merging could lead to different execution plans.
> If I run the following statements in 8.1.7 and 9.2.0 I get different resu=
lts...
> (notice that both database have a "minimal" INIT.ORA that doesn't modify =
any optimizer parameter)
>=20
> ALTER SESSION SET optimizer_mode =3D rule;
>=20
> SELECT * FROM v$version WHERE rownum =3D 1;
>=20
> CREATE OR REPLACE VIEW min_zip_v AS
> SELECT city, min(zip) zip
> FROM addresses
> GROUP BY city;
>=20
> SET TIMING ON
> SET AUTOTRACE ON EXP
>=20
> SELECT adr.*
> FROM persons prs, addresses adr
> WHERE (adr.city, adr.zip) IN (SELECT city, zip FROM min_zip_v)
> AND adr.prs_id =3D prs.id
> AND prs.lastname =3D 'Meier';
>=20
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
>=20
> Elapsed: 00:00:26.01
>=20
> Execution Plan
> ----------------------------------------------------------
> SELECT STATEMENT Optimizer=3DRULE
> NESTED LOOPS
> NESTED LOOPS
> VIEW OF 'MIN_ZIP_V'
> SORT (GROUP BY)
> TABLE ACCESS (FULL) OF 'ADDRESSES'
> TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES'
> INDEX (RANGE SCAN) OF 'ADR_CITY' (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS'
> INDEX (UNIQUE SCAN) OF 'PRS_PK' (UNIQUE)
>=20
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
>=20
> Elapsed: 00:00:00.03
>=20
> Execution Plan
> ----------------------------------------------------------
> SELECT STATEMENT Optimizer=3DRULE
> FILTER
> SORT (GROUP BY)
> TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES'
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS (BY INDEX ROWID) OF 'PERSONS'
> INDEX (RANGE SCAN) OF 'PRS_LASTNAME' (NON-UNIQUE)
> TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSES'
> INDEX (RANGE SCAN) OF 'ADR_PRS_ID' (NON-UNIQUE)
> INDEX (RANGE SCAN) OF 'ADR_CITY' (NON-UNIQUE)
>=20
> Therefore don't think that the migration will be easy! Carefully plan it =
and, of course, do some load tests.
>=20
> HTH
> Chris
>=20
--=20
Regards
Zhu Chao
www.cnoug.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
