RE: SQL Query tuning help

From: Nicoll, Iain \(Calanais\) <iain.nicoll_at_calanais.com>
Date: Tue, 10 Sep 2002 20:21:53 +0100
Message-Id: <22528.293367@fatcity.com>

Dennis,

If you use the ordered hint and have sa then so then am and also hint to use the index on sa(ret) then I think that would be about the best as you'd be starting with the best filter ie 1.3m/281 giving less than 5000 on average
(assuming ret is indexed). I don't know if you'd have to through in an
use_nl also.

Iain Nicoll

-----Original Message-----
From: DENNIS WILLIAMS [mailto:DWILLIAMS_at_LIFETOUCH.COM] Sent: Tuesday, September 10, 2002 8:19 PM To: Multiple recipients of list ORACLE-L Subject: SQL Query tuning help

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>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

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

Received on Tue Sep 10 2002 - 14:21:53 CDT