?
Solved

The order of FROM tables in Oracle causing issues

Posted on 2012-03-21
7
Medium Priority
?
492 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

807 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