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
LVL 18
sventhanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
The case should make indexes less usable, which will lend itself toward table scans.
The simple equality can use indexes.

Since your CBO thinks the index method will be better, either you have little hash space available, making it seem like an artificially expensive operation or your indexes are weighted excessively low.

Are your statistics up to date?

Check pga_aggregate_target and/or  hash_area_size if using manual control.
What about optimizer_index_cost_adj, optimizer_index_caching  are they defaults?

Also is your CBO set to FIRST or ALL rows?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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)
0
PortletPaulfreelancerCommented:
such as this:
SELECT
       AL1.EN_CUST_NUMBER
     , AL3.RECENCY
     , AL3.FREQUENCY_DETAIL
FROM (
        SELECT  EN_CUST_NUMBER, DM_CUST_DKEY
        FROM DM.DM_CUST_DIM
        WHERE CUST_NUMBER = EN_CUST_NUMBER
        ) AL1
INNER JOIN DM.DM_CUST_SEG AL2            ON AL1.DM_CUST_DKEY = AL2.DM_CUST_DKEY
INNER JOIN DM.DM_SEGMENT_DIM AL3         ON AL2.DM_SEGMENT_DKEY = AL3.DM_SEGMENT_DKEY
INNER JOIN DM.DM_REF_DIM AL4             ON AL2.DM_TRAV_ON_SEGM_DKEY = AL4.DM_TRAV_ON_SEGM_DKEY
                                        AND AL2.DM_TRAV_ON_SEGM_DKEY = AL4.DM_TRAV_ON_SEGM_DKEY
INNER JOIN DM.DM_TRAVELER_STATUS_DIM AL5 ON AL2.DM_STATUS_DKEY = AL5.DM_STATUS_DKEY
WHERE AL2.REFERENCE_YEAR IN (2014)

Open in new window

btw: I don't know why the optimizer has followed the path it has - sorry. May be due to the sytles/types/number of indexes involved and perhaps the subquery will simplify the choices.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark GeerlingsDatabase AdministratorCommented:
Table statistics would be the first thing to check, but they were already mentioned.

Next, the physical distribution of the records for EN_CUST_NUMBER = 90909300 also makes a difference.  If there are very few records for this customer, and if they are all in the same data block (or in just a few data blocks) an index-based search will be fast.  But, if there are lots of records for this customer, and they are scattered among records for lots of other customers with only one (or very few) record(s) for this customer in each of the table's data blocks, then an index-based search will be slower than a full-table scan.  You may need a histogram on this column to help Oracle's query optimizer choose the best path automatically.

And yes, the other init parameters that ststuber mentioned may also affect this.
0
PortletPaulfreelancerCommented:
oops tried to correct an error in mine above (line 13 is redundant)
0
sventhanAuthor Commented:
Thanks so much for all the responses.

@SD

The optimizer mode is ALL_ROWS

@pp

I'll try your suggestion.
0
sdstuberCommented:
>>> The optimizer mode is ALL_ROWS

ok, what about the other stuff - in particular statistics on all tables/indexes involved?
0
sventhanAuthor Commented:
@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
0
Mark GeerlingsDatabase AdministratorCommented:
Do those stats include any histograms?
0
sventhanAuthor Commented:
Thanks for all the information!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.