Avatar of sventhan
sventhanFlag for United States of America 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
Oracle Database

Avatar of undefined
Last Comment
sventhan

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
PortletPaul

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

Thanks so much for all the responses.

@SD

The optimizer mode is ALL_ROWS

@pp

I'll try your suggestion.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Sean Stuber

>>> The optimizer mode is ALL_ROWS

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

@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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
sventhan

Thanks for all the information!