Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

The order of FROM tables in Oracle causing issues

Posted on 2012-03-21
7
Medium Priority
?
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 1000 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 1000 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 74

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 74

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

618 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