SQL Sharing Criteria…

I’m a big fan of the Oracle documentation, but sometimes things go astray. A recent question by Aman Sharm about SQL sharing critera highlighted this…

In the 8iR3 manual, the decision process for finding matching SQL is listed as:

  1. The text string of an issued statement is hashed. If the hash value is the same as a hash value for an existing SQL statement in the shared pool, then Oracle proceeds to Step 2.
  2. The text string of the issued statement, including case, blanks, and comments, is compared to all existing SQL statements that were identified in Step 1.

In the 9iR1 manual it was rewritten to include all the steps for identifying the match in a single bullet point:

  1. The text of the statement issued is compared to existing statements in the shared pool.

    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.

    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.

So the first line is like a summary of the whole bullet point, which is then expanded.

In the 9iR2 manual, this was reverted back to individual bullet points, but the author/editor must have assumed the initial summary line was a separate point, so a new step was introduced into the documentation of the process:

  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.

And this is the way it stayed from 9iR2 to 11gR1. :(

I’ve raised a bug against the documentation, so it should get corrected now. :)

Cheers

Tim…

apple-touch-icon-57x57