Skip to Main Content
  • Questions

  • Two identical queries with same parameter values have different execution plans ..

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Travis.

Asked: March 16, 2017 - 3:37 pm UTC

Last updated: March 20, 2017 - 2:53 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hello and thanks for your time.

We noticed an odd behavior of Oracle 12.1 query plan selection for the same query. The query generated from .Net Entity Framework has an inefficient query plan than if we run the same query in SQL Developer. When we say “same”, we mean identical queries with same parameter values but they both have different SQL IDs.

The query generated from .Net Entity Framework takes 35 seconds. Then using Oracle Enterprise Manager (OEM) we took that sql and literally copy-paste into SQL Developer with same parameter values and it runs under 100 milliseconds. The difference is because the query run in SQL Developer has a better execution plan i.e., uses the correct index. The query run from .net uses a different index which is very inefficient.

Do you know why Oracle is picking different execution plans for 2 identical queries with same parameter values on the same database? All parameters are NUMBER type.


Thanks!!

and Chris said...

Well if they've got different SQL_ids, then they're not the same statement!

Caveat: Oracle Database considers whitespace and case when check if statements are "the same". So it thinks:

select * from dual;


and

SELECT *
FROM   DUAL;


Are different statements:

select sql_id, sql_text from v$sql
where  lower(sql_text) like 'select *%dual';

SQL_ID         SQL_TEXT              
4z1cmanx1p8b5  SELECT * FROM   DUAL  
a5ks9fhw2v9s1  select * from dual


So if there's minor differences when you run it in SQL Dev, Oracle Database will have to reparse the query.

So why are the plans different?

There's many, many reasons this could be true. A couple of common ones:

- Differences in the session settings between .NET and SQL Dev
- The query in your app was parsed some time ago. The plan generated was (or appeared to be) the best available at that time. But things have changed and it's no longer the best. But it's still cached!

You could test the second theory by flushing the .NET plan out of the cursor cache and seeing if that helps:

https://blogs.oracle.com/mandalika/entry/oracle_rdbms_flushing_a_single

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hmmm! This may be a critical suggestion

Allen, March 16, 2017 - 5:05 pm UTC

Seeing the same thing in another .NET app using an ORM. Is there any way to report the execution plan in a readable manner to see these differences side-by-side? Also, does a server bounce flush these plans? Finally, since you state that query literal format is a differentiator, do you have any best practice recommendations to try to always get a 'match' or perhaps better stated, avoid creating 2 plans for the same literal, or is this 'optimization' pointless?
Chris Saxon
March 16, 2017 - 6:06 pm UTC

For details on various ways to get execution plans, read:

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

Yep, a server bounce flushes the plans because they're held in memory. So restarting the instance clears these out.

If you put SQL in PL/SQL, then Oracle Database canonicalizes to strip out whitespace and convert to uppercase. There isn't really any other "easy" option.

Thanks, followip

A reader, March 16, 2017 - 5:23 pm UTC

I understand the reason for 2 separate SQL IDs hence I mentioned it. However, what’s puzzling is the 2 different execution plans. The sqls were run 1 sec apart numerous times and no data updates were going on that time. So the point being that data was static during these tests. We also flipped the WHERE clauses so that a different SQL ID is generated but the SQL Developer query always picked the correct query plan and the .net query didn’t. And yes we also flushed the Shared Sql Area before the test.

Which session settings do you think could be affecting this. I can understand session settings affecting the number of rows that are being retrieved in a block/chunk but not sure which session setting affects the database servers query execution plan.
Chris Saxon
March 16, 2017 - 6:08 pm UTC

We really need to see the plans including the predicate and note sections. We're just guessing otherwise.

Followup question

Travis Hunt, March 16, 2017 - 6:23 pm UTC

I understand the reason for 2 separate SQL IDs hence I mentioned it. However, what’s puzzling is the 2 different execution plans. The sqls were run 1 sec apart numerous times and no data updates were going on that time. So the point being that data was static during these tests. We also flipped the WHERE clauses so that a different SQL ID is generated but the SQL Developer query always picked the correct query plan and the .net query didn’t. And yes we also flushed the Shared Sql Area before the test.

Which session settings do you think could be affecting this. I can understand session settings affecting the number of rows that are being retrieved in a block/chunk but not sure which session setting affects the database servers query execution plan.

Cheking session optimizer options

Cesar, March 17, 2017 - 7:55 pm UTC

Use the table V$SES_OPTIMIZER_ENV to identify the optimizer mode.
There are other parameter which can effect the optimizer, but this is most probably ....

select * from V$SES_OPTIMIZER_ENV
--where sid = 375
where 1=1
and name like '%optimizer_mode%'

Chris Saxon
March 20, 2017 - 2:53 pm UTC

Yep, that view is a good place to start the investigations.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.