Dennis,
I noticed HASH JOINS in your query. Did you look at playing around with =
the
value of HASH_AREA_SIZE and/or SORT_AREA_SIZE as well as adjust
HASH_MULTIBLOCK_IO_COUNT?
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
Disappointments are inevitable in Life, but discouragement is optional. =
You
decide!
- The opinions and statements above are entirely my own and not those =
of my
employer or clients **
> -----Original Message-----
> From: DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM]
> Sent: Tuesday, September 10, 2002 2:17 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL Query tuning help
>=20
>=20
> Thanks everyone for your wonderful suggestions. And thanks=20
> for leaving the
> "hey stupid" off your reply header :-)
>=20
> Rachel - Thanks for the bitmapped idea. These tables don't=20
> change often, so
> that may be a good alternative.
>=20
> Iain - Thanks so much for the detailed suggestions.
>=20
> Rick - Good sanity check, yes, I analyzed the tables.
>=20
> Jared - RET has 281 values, pretty evenly distributed
>=20
> Cary - Query returns 185 rows.
>=20
> Bill - Thanks for the suggestions and insights.
>=20
> Stephane - Good notice that only am values are used. Guess that is =
why
> Oracle accessed the data blocks anyway with my new indexes.=20
> Duh. Good ideas.
>=20
> Jeff - Thanks for the "Mickey Mouse" tag. I may need that in=20
> the future.
> Previously this data was on an old mainframe and the business=20
> itself was
> restricted by the inflexibility. My gut reaction was that they
> overcompensated.=20
>=20
> Thanks everyone for the wonderful ideas. I was just given a=20
> hot project, so
> it may be a day or two before I get a chance to explore all=20
> of them, but
> I'll let you know.
>=20
> =A0
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>=20
>=20
> =A0
> -----Original Message-----
> Sent: Tuesday, September 10, 2002 2:19 PM
> To: Multiple recipients of list ORACLE-L
>=20
>=20
> I am trying to tune a SQL query on Oracle 8.1.6. I have tried several
> optimizations, but
> so far have made no improvements. I would appreciate any suggestions.
>=20
> SELECT am.lid, am.name
> FROM am, so, sa
> WHERE so.lid =3D am.lid
> AND so.key_ =3D sa.so_key
> AND am.active =3D 1
> AND so.code =3D 11
> AND sa.ret =3D 'SB'
> ORDER BY am.name
>=20
> Tables:
> am - 250,000 rows, 220,000 rows have active =3D 1, the others are =
0.
> so - 1.3 million rows, lid has 250,000 distinct values,=20
> key_ is unique,
> code has 12 values, evenly distributed.
> sa - 1.3 million rows, ret has 281 values, fairly evenly=20
> distributed.
> so_key is pretty unique.
>=20
> Now, you'll probably say there is essentially a 1-1=20
> relationship between so
> and sa. You are right, but the developer insists this flexibility is
> essential.
>=20
> The query executes in 16 seconds and returns 185 rows. This=20
> is felt to be
> too slow for an online lookup screen.
>=20
> explain plan results:
>=20
> SELECT STATEMENT Cost =3D 2955
> SORT ORDER BY
> HASH JOIN
> HASH JOIN
> TABLE ACCESS FULL SA
> TABLE ACCESS FULL SO
> TABLE ACCESS FULL AM
>=20
> Here is what I've tried so far:
>=20
> Using hints to force Oracle to use indexes.
>=20
> Query Plan
> --------------------------------------------------------------
> --------------
> ----
> SELECT STATEMENT Cost =3D 62031
> SORT AGGREGATE
> NESTED LOOPS
> HASH JOIN
> TABLE ACCESS BY INDEX ROWID SA
> INDEX FULL SCAN SO_KEY3
> TABLE ACCESS BY INDEX ROWID SO
> INDEX RANGE SCAN PRG_CODE3
> TABLE ACCESS BY INDEX ROWID AM
> INDEX UNIQUE SCAN LID6 =20
>=20
> Timing result 25 minutes
>=20
> Next I tried creating new indexes that combine both the=20
> accessing column as
> well as the retrieved column, thinking that Oracle could get=20
> the result from
> the index block and not need to retrieve the data block.=20
> create index test1 on am (lid, active);
> create index test2 on sa (so_key, code);
>=20
> SELECT STATEMENT Cost =3D 2951
> SORT AGGREGATE
> HASH JOIN
> HASH JOIN
> INDEX FULL SCAN TEST2
> TABLE ACCESS FULL SO
> TABLE ACCESS BY INDEX ROWID AM
> INDEX RANGE SCAN TEST1
> =20
> Hinting so Oracle will use the new indexes, for one table=20
> Oracle uses the
> index only and for the other table, Oracle hits both the=20
> index and table
> itself. Response time is slightly longer than the original=20
> query. At this
> point I'm fresh out of ideas, so any ideas would be=20
> appreciated. Thanks.
>=20
> =20
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>
>=20
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --=20
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing =
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --=20
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --=20
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>=20
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing =
Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Tue Sep 10 2002 - 17:18:53 CDT
