Solved

The order of FROM tables in Oracle causing issues

Posted on 2012-03-21
7
484 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:hydev
7 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 250 total points
ID: 37746393
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
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 250 total points
ID: 37746397
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
 

Author Comment

by:hydev
ID: 37746551
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 73

Expert Comment

by:sdstuber
ID: 37746976
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
 

Author Comment

by:hydev
ID: 37748939
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 37749115
RULE would do it,  you specified 10g so I discounted RBO as a possibility.
0
 

Author Comment

by:hydev
ID: 37749143
Yes so had I, until I came to check the stats and then noticed..
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now