Re: SQL Query tuning help

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Tue, 10 Sep 2002 21:29:45 +0200
Message-Id: <22528.293374@fatcity.com>

DENNIS WILLIAMS wrote:
>
> 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.
>
> SELECT am.lid, am.name
> FROM am, so, sa
> WHERE so.lid = am.lid
> AND so.key_ = sa.so_key
> AND am.active = 1
> AND so.code = 11
> AND sa.ret = 'SB'
> ORDER BY am.name
>
> Tables:
> am - 250,000 rows, 220,000 rows have active = 1, the others are 0.
> so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique,
> code has 12 values, evenly distributed.
> sa - 1.3 million rows, ret has 281 values, fairly evenly distributed.
> so_key is pretty unique.
>
> Now, you'll probably say there is essentially a 1-1 relationship between so
> and sa. You are right, but the developer insists this flexibility is
> essential.
>
> The query executes in 16 seconds and returns 185 rows. This is felt to be
> too slow for an online lookup screen.
>
> explain plan results:
>
> SELECT STATEMENT Cost = 2955
> SORT ORDER BY
> HASH JOIN
> HASH JOIN
> TABLE ACCESS FULL SA
> TABLE ACCESS FULL SO
> TABLE ACCESS FULL AM
>
> Here is what I've tried so far:
>
> Using hints to force Oracle to use indexes.
>
> Query Plan
> ----------------------------------------------------------------------------
> ----
> SELECT STATEMENT Cost = 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
>
> Timing result 25 minutes
>
> Next I tried creating new indexes that combine both the accessing column as
> well as the retrieved column, thinking that Oracle could get the result from
> the index block and not need to retrieve the data block.
> create index test1 on am (lid, active);
> create index test2 on sa (so_key, code);
>
> SELECT STATEMENT Cost = 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
>
> Hinting so Oracle will use the new indexes, for one table Oracle uses the
> index only and for the other table, Oracle hits both the index and table
> itself. Response time is slightly longer than the original query. At this
> point I'm fresh out of ideas, so any ideas would be appreciated. Thanks.
>
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com <mailto:dwilliams_at_lifetouch.com>

Dennis,

   I note that your select list is only made of columns from am. Your entry points are so.code and sa.ret, the second one being the most selective. I don't think that on such a volume a nested loop would be any better than a hash join between the two, so this part of the Oracle plan needs no change. However, a nested loop is probably what you need with am.

I would try things such as

SELECT am.lid, am.name
FROM am
WHERE am.lid in (SELECT so.lid

                 from so, sa
                 WHERE so.key_ = sa.so_key
                   AND so.code = 11
                   AND sa.ret = 'SB')

AND am.active = 1
ORDER BY am.name

which may give the same plan as your first example; if this is the case, perhaps that

SELECT am.lid, am.name
FROM (SELECT so.lid

      from so, sa
      WHERE so.key_ = sa.so_key
      AND so.code = 11
      AND sa.ret = 'SB') x,
     am

WHERE am.lid = x.lid
AND am.active = 1
ORDER BY am.name

will give a better result. If it still doesn't, try the ORDERED hint after the first SELECT. If it still doesn't, add USE_NL(am) after ORDERED but I'd rather avoid it. Received on Tue Sep 10 2002 - 14:29:45 CDT