SQL optimization method issue

From: Maoz Mussel <maoz_at_mindcti.com>
Date: Wed, 15 Dec 1999 08:56:51 +0200
Message-ID: <837dq4$plr$1@news.netvision.net.il>

Hi,

I'm running the following query on two databases, and gets different optimization
method, therefor different performance:

 SELECT c.ID, c.USERCODE, c.FIRSTNAME,

 c.LASTNAME, c.ADDR, c.TEL, c.COMPID,
 c.PROVIDERCODE, p.NAME, c.TRFCODE, c.REGDATE,
 c.LOTNUMBER, c.EMAIL, c.CYCLECODE,
 d.ACCOUNTBALANCE, d.NEWCALLSCHARGE, d.CREDITLIMIT,
 d.ACCOUNTSTATUS , d.STATUSREASON, y.SHORTDESC,
 t.SHORTDESC , d.STATUSDATE, d.CUSTANI

 FROM CUSTDATA d, customrs c, COMPANY p, CUSTACCTSTAT t, CUSTTYPE y  WHERE d.CUSTID = c.ID

 AND p.ID (+) = c.COMPID
 AND y.CODE (+) = d.custtype
 AND t.CODE (+) = d.ACCOUNTSTATUS

 AND ROWNUM <= 100
 AND d.CUSTANI LIKE 'P%'
 ORDER BY FIRSTNAME, LASTNAME Details:
1. Data on both databases is similar. First databases (production) is an OPS installed on Sun machine (8.0.6), the other (development) is one node installed on
NT machine.
2. Both databases using CHOOSE as optimizer mode, both have updated statistics.
3. On the development database, this query use NESTED LOOPS, on the production it
use HASH JOIN. Since CUSTANI on the WHERE clause is a (none-unique) index, using
NESTED LOOPS gets immediate results, while the other option takes few minutes.
4. When running this query with RULE hint on the production database, the result is
immediate.
5. I tried to ALTER SESSION of hash_join_enabled to false, limit the sort_area_size
on the production database in order to check if performance will increase, but face
no change.

I'm trying to understand the differences, and to find what can be change (parameters
or other definition) so the query will get immediate results even with no hints (as
it is in the development database).

Any help will be most appreciated.

Thanks,
Maoz Received on Wed Dec 15 1999 - 00:56:51 CST