Solved

The order of FROM tables in Oracle causing issues

Posted on 2012-03-21
7
487 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

820 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