Why my query plan changed ?

From: Ajay Thotangare <ajayoraclel_at_yahoo.com>
Date: Mon, 15 Sep 2008 14:29:51 -0700 (PDT)
Message-ID: <629415.9909.qm@web57510.mail.re1.yahoo.com>

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       

Received on Mon Sep 15 2008 - 16:29:51 CDT

Original text of this message