a.
SQL Sharing Criteria
Oracle automatically determines whether a SQL statement or PL/SQL block being
issued is identical to another statement currently in the shared pool.
Oracle performs the following steps for the comparison:
1. The text of the statement issued is compared to existing statements in the
shared pool.
2. The text of the statement is hashed. If there is no matching hash value, then the
SQL statement does not currently exist in the shared pool, and a hard parse is
performed.
3. If there is a matching hash value for an existing SQL statement in the shared
pool, then Oracle compares the text of the matched statement to the text of the
statement hashed to see if they are identical. The text of the SQL statements or
PL/SQL blocks must be identical, character for character, including spaces,
case, and comments. For example, the following statements cannot use the
same shared SQL area:
这里既然第1步和第三步有什么不同，好象都是比较text of the statement和existing statements in the shared pool？
b.Library cache misses can occur on either the parse step or the execute step when
processing a SQL statement.
When an application makes a parse call for a SQL statement, if the parsed
representation of the statement does not already exist in the library cache, then
Oracle parses the statement and stores the parsed form in the shared pool. This is a
hard parse. You might be able to reduce library cache misses on parse calls by
ensuring that all shareable SQL statements are in the shared pool whenever
possible.
If an application makes an execute call for a SQL statement, and if the executable
portion of the previously built SQL statement has been aged out (that is,
deallocated) from the library cache to make room for another statement, then Oracle
implicitly reparses the statement, creating a new shared SQL area for it, and
executes it. This also results in a hard parse. Usually, you can reduce library cache
misses on execution calls by allocating more memory to the library cache.
这个execute阶段的age out是怎么理解的，我想，如果age out了，分析阶段是怎么得到有这个语句的? |
