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?
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.
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%'
March 20, 2017 - 2:53 pm UTC
Yep, that view is a good place to start the investigations.