What are "versions" of an SQL statement? _versions of the sql statement-优快云博客

解析了在Oracle中SQL语句如何被解析为不同版本的过程。当一个SQL语句被提交时，Oracle会根据多个因素判断其是否可以使用已有的执行计划，如数据库对象的一致性、初始化参数等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

From:	Steve Adams
Date:	27-Oct-2000 03:18
Subject:	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.