Why my query plan changed ?
- From: Ajay Thotangare <ajayoraclel@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 15 Sep 2008 14:29:51 -0700 (PDT)
I am seeing lot of query plans are changing in database. Though not always bad
but I would like to know the reason why it changed.
I am not doing any changes in database(No DDL) and periodically taking snapshot
of sql(v$sql) queries and their plans(v$sql_plan) from memory. I am comparing
with sql_id if any plan changed and I often get plan changed messages.
I thought my cursor_sharing parameter is FORCE so it should reuse the existing
plan and so it should be always same but its not a fact.
As per my assumption for this scenario
(a) sql query/Plan gets flushed from memory and the they are regenerated as per
new bind values when same new queries comes and stored in memory for sometime
and again flushed and process repeats. So new plan is as per new bind variable
and so it might be changing
(1) Am I right ?
(2) Is there any view/column in database that will tell me why my sql plan is
changed ? reason could be
(a) due to bind values
(b) parameter changed
(c) any other factor
Thanks in advance
Other related posts:
