asked on

Oracle SQL performance tuning

Experts -

I've this SQL running slow when using nested loop but not with Hash Join as explained below. I'm just looking for the reason why its NOT using Hash Join as a optimal method to execute this SQL.

1) When the criteria AND AL1.CUST_NUMBER = AL1.EN_CUST_NUMBER is used Oracle is choosing a nested loop join that runs in 10 minutes. It takes 3300 sec just to scan DM_CUST_DIM.
2) When I replace this wiht the with a case statement (commented out), Oracle is choosing a HASH JOIN that runs in 1 minute.

Is there any explanation?

SELECT AL1.EN_CUST_NUMBER,
AL3.RECENCY,
AL3.FREQUENCY_DETAIL
FROM DM.DM_CUST_DIM AL1,
DM.DM_CUST_SEG AL2,
DM.DM_SEGMENT_DIM AL3,
DM.DM_REF_DIM AL4,
DM.DM_TRAVELER_STATUS_DIM AL5
WHERE AL1.DM_CUST_DKEY = AL2.DM_CUST_DKEY
AND AL2.DM_SEGMENT_DKEY = AL3.DM_SEGMENT_DKEY
AND AL2.DM_TRAV_ON_SEGM_DKEY = AL4.DM_TRAV_ON_SEGM_DKEY
AND AL2.DM_STATUS_DKEY = AL5.DM_STATUS_DKEY
--**********************************************************
-- AND AL1.EN_CUST_NUMBER = 90909300
--*********************************************************
AND AL2.REFERENCE_YEAR IN (2014)

AND AL1.CUST_NUMBER = AL1.EN_CUST_NUMBER -- Replaced by case statemtn below to influence better optimizer choice
/* AND CASE
WHEN AL1.EN_CUST_NUMBER = AL1.CUST_NUMBER THEN 'Y'
ELSE 'N'
END = 'Y' */

Thanks,
sve

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

what does this do?

SELECT AL1.EN_CUST_NUMBER
FROM DM.DM_CUST_DIM AL1
WHERE AL1.CUST_NUMBER = AL1.EN_CUST_NUMBER

If this is responsive, try using this as a nested subquery ( or via; with ... as)

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

oops tried to correct an error in mine above (line 13 is redundant)

sventhan

ASKER

Thanks so much for all the responses.

@SD

The optimizer mode is ALL_ROWS

@pp

I'll try your suggestion.

Sean Stuber

>>> The optimizer mode is ALL_ROWS

ok, what about the other stuff - in particular statistics on all tables/indexes involved?

sventhan

ASKER

@SD

Its automatic memory management. pga_aggregate_target is 0.

The value of optimzer_index_cost_adj(100) and optimizer_index_caching(0) are defaults.

The stats are less than a week old. I'm planning to update the STATS to see if I get any difference.

Thanks,
Sve

Mark Geerlings

Do those stats include any histograms?

sventhan

ASKER

Thanks for all the information!