The order of FROM tables in Oracle causing issues

Hi,

We are executing 2 PL/SQL statements on our Oracle 10g server and we are getting some interesting results.  The first piece of SQL takes over 1 minute to run, the second piece of SQL takes about 0.1 seconds.  We have repeated this on different days to ensure it's not database usage, the only difference is the the order of the table listed in the FROM statement.

Example 1:
select sum(T69330.STOCK_QTY) as c1,
               T13269.COMP_DIV as c2,
               T13269.STY_RET_TYPE as c3
          from 
               DW_TIME T14149  ,DW_PRODUCTS T13269  ,DW_DAILY_STOCK_AND_MSS T69330 
          where  ( T13269.PKID = T69330.PRODUCT_ID 
          and T13269.COMP_DIV = 'CARAVANNING' 
          and T14149.CAL_DATE = TO_DATE('2012-03-19' , 'YYYY-MM-DD') 
          and T14149.PKID = T69330.TIME_ID ) 
          group by T13269.COMP_DIV, T13269.STY_RET_TYPE

Open in new window


Example 2
select sum(a.STOCK_QTY) as c1,
               p.COMP_DIV as c2 ,
               p.STY_RET_TYPE  as c3
             from dw_daily_stock_and_mss a, dw_time t, dw_products p
where ( p.pkid = a.product_id
and p.COMP_DIV = 'CARAVANNING'
and T.CAL_DATE = TO_DATE('2012-03-19' , 'YYYY-MM-DD')
and  t.pkid = a.time_id )
group by p.COMP_DIV , p.STY_RET_TYPE

Open in new window


The reason that there are some dodgy alias names in the first example is that the SQL is being generated by a rather large blue chip BI solution.  This was taking too long to run so we examined it and then tested it to find the bottleneck.  This was how we discovered that moving the FROM statement around speeded up the query.

My question therefore is why?

Any help greatly appreciated.

Mike
hydevAsked:
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.

Wasim Akram ShaikCommented:
the simple reason for this is the change of explain plans in both scenarios..

when you had placed the table prior oracle has executed a different execution plan and when you placed it at other place oracle has executed a different execution plan..

are both tables got analyzed?

at times this may happen when the stats get old..
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
Kelvin SparksCommented:
Try upgrading to ANSI syntax

select sum(a.STOCK_QTY) as c1,
               p.COMP_DIV as c2 ,
               p.STY_RET_TYPE  as c3
FROM dw_daily_stock_and_mss a INNER JOIN dw_products p ON
p.pkid = a.product_id INNER JOIN dw_time t ON t.pkid = a.time_id
WHERE p.COMP_DIV = 'CARAVANNING' AND
T.CAL_DATE = TO_DATE('2012-03-19' , 'YYYY-MM-DD')
group by p.COMP_DIV , p.STY_RET_TYPE

Kelvin
0
hydevAuthor Commented:
Thanks for those.  As the SQL is generated by an OBI tool I'm not sure I can enforce the ANSI specification.  I will have a look at the stats generation though.

Thanks

Mike
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
I know this question is closed, but both answers above require a bit of clarification.

The order of the tables should have absolutely no bearing on the execution plan.
When it does, it's either a bug in the optimizer, or the optimizer has reached an internal cutoff of join permutations.  Prior to 10g this was controlled with the optimizer_max_permutations initialization parameter.  In 10g that parameter has become hidden with an underscore prefix _optimizer_max_permutations.

So, for the first answer, yes, the plan likely changed, but the reason the plan changed hasn't been identified.  

For the second answer, if switching to ANSI syntax made a difference then I'd lean toward more likely a bug in the optimizer.  


In neither case should statistics make a difference.  Not that up-to-date and accurate statistics are bad, but stale/accurate is irrelevant in this context.

Generate a 10053 trace file for each plan and you'll see what the optimizer was "thinking" when it chose each one.
0
hydevAuthor Commented:
Thanks for the update but I subsequently discovered that someone had set the database to RULE based optimisation. As soon as I put it back and to ALL ROWS they took the same time to execute.

Mike
0
sdstuberCommented:
RULE would do it,  you specified 10g so I discounted RBO as a possibility.
0
hydevAuthor Commented:
Yes so had I, until I came to check the stats and then noticed..
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.