What are "versions" of an SQL statement?

 

refer: http://www.ixora.com.au/q+a/0010/27021816.htm

 

From:Steve AdamsDate:27-Oct-2000 03:18Subject:  What are "versions" of an SQL statement?

---

I'm sure that I've answered this question before, but I cannot find the answer at the moment, so here it is again. Let us say that I want to parse the statement:

    select sum(discount) from sales where region = :1

Oracle will compute the hash value for this SQL statement and search the hash chain for that hash value via the library cache hash table looking for an existing cached SQL statement with exactly the same text. Before an execution plan for this statement prepared for another session can be shared however, Oracle has to check several things.

It has to make sure that this session is referring to the same database objects as the previous session - not objects of the same name in a different schema. The initialization parameters that influence the optimizer must match. The NLS environments must match. And finally, the types and lengths of bind variables must match.

If any of these checks fails, then Oracle regards it as a new version of the same statement and prepares a new execution plan. The existing version (or versions) are not discarded from the library cache, because it (they) might yet be reused by other sessions.

 

What do you mean by "many versions of a sharable SQL statement in the SQL AREA"?