how long execution plan for PreparedStatment live on Database

How long execution plan for a Prepared statement remains valid at database. let say i create a prepared statement for an update statment and my code excute that statement 500 times so i understand that during first call the excution plan or some other setup done for executing prep statement will be created at database side and will be valid for subsequent calls. My doubt is how long that execution plan live on Db

is it bound to PreparedStatement or Connection Object, in other words once done with my update if i close prep statement and commit transaction what haapens to that plan which was created on DB for my prep statement, if i create a new prepared statement object with same SQL string in the same connection what will happen now.

Thanks

Yogesh,
There are two execution plans. There is the one that lives in Java in the database driver. This one is linked to the connection object. In practice people use a connection pool and the prepared statement cache is linked to the connection that lives on in the pool. Therefore it has a longer life.

There is also the execution plan that lives on the database. It can potentially live longer than the connection.

Thanks Jeann,

It was helpful. I was thinking some what similar. So even if i create new object of PreparedStatement with same connection and Same SQL it will use that already compiled query plan of the connection.

It would be really great if you can point me to some article/resources on internet.

Thanks