A misunderstanding caused by a sql_id and Hash_value conversion attempt

http://blog.csdn.net/bisal/article/details/38919181

In this article, we've talked about a hidden problem:

Quote Original:

"Use @dbsnake Daniel's SQL to know the one by one correspondence between SQL_ID and Hash_value:

Hide Issue 1:

There may be a problem here, the result is not accurate, the problem is in this SQL used in the algorithm, in another blog post will carefully explain the problem . ”

Issue Background :

Here, the following two SQL is used to get the Hash_value value for sql_id:

Select
Lower (Trim (' a43zhpuddcxwh ')) sql_id,
trunc (mod (SUM (InStr (' 0123456789abcdefghijklmnopqrstuvwxyz ', substr (lower (' Trim (' a43zhpuddcxwh ')), level, 1)- 1) * Power (+, Length (Trim (' A43ZHPUDDCXWH '))-level), power (2, +)) Hash_value
From dual
Connect by Level <= Length (Trim (' a43zhpuddcxwh '));

Select
Lower (Trim (' a43zhpuddcxwh ')) sql_id,
trunc (mod (SUM (InStr (' 0123456789abcdefghjkmnpqrstuvwxyz ', substr (lower (' Trim (' a43zhpuddcxwh ')), level, 1))-1) * Power (up to (' A43zhpuddcxwh ')), power (2, +)) Hash_value
From dual
Connect by Level <= Length (Trim (' a43zhpuddcxwh '));
The result is the first error and the second one is correct. Seemingly the same two sql Why did the result return an error?


FAQ:

1. What is sql_id and Hash_value?

Both of these fields are from the V$sql view, andOracle's official explanation is that

sql_id VARCHAR2 (+) SQL identifier of the parent cursor in the library cache

hash_value &NBSP; Number                Hash value of the parent statement in the library cache

9i and previous versions, generally with Hash_value to indicate a SQL, from 10g and later versions, generally with SQL_ID to indicate a SQL. Plainly, the (Library Cache) . Because SQL enters Oracle then performs a scheduled match , storage, and other operations. this way, both Hash_value and sql_id can indicate a SQL, but since 10g, the hash_value algorithm is different, so 10g V $SQL more than one old_hash_ The value field, which is backward compatible (the main purpose can be seen as a 9i to 10g version of the migration, ):

old_hash_value number old SQL HASH VALUE

2. How do sql_id and Hash_value convert ?

above description Sql_ id and Hash_value can be Indicates a SQL, mostly based on SQL text, Oracle uses the MD5 algorithm to hash, taking a different number of bits as sql_id and hash_value, is actually representative This SQL corresponds to Library cache object Tanel The Secret of Poder:

"Basically all I do are take the SQL ID, interpret it as a character base-32 encoded number and then take only the Lowes T 4 bytes worth of information (4 bytes in base-256) out of that number and that ' s the hash value.
“

So, sql_id is just another hash value of the library cache object name.

Actually, since 10g the full stories goes like this:

1) Oracle hashes the library cache object name with MD5, producing a-bit hash value
2) Oracle takes last, bits of the MD5 hash and this would be the sql_id (but it's shown in base-32 for brevity rather tha N in hex or as a regular number)
3) Oracle takes last a. Bits of the MD5 hash and this is the hash value (as seen in V$sql.hash_value).

President

Interpret sql_id as a base-32 encoded value of one byte and then take the lower 4 bytes (4 bytes of base-256)as Hash_value.

SQL_ID is another hash value for the library cache object name.

Starting with 10g, the algorithm changes to:

1) Oracle uses MD5 to hash the library cache object names, resulting in a 128-bit hash value.

2) Oracle takes the latter 64 bits of the MD5 hash as sql_id (but it is presented as a base-32 encoding, rather than using hexadecimal or regular values).

3) Oracle takes the MD5 hash value as a hash_value (that is, v$sql.hash_value).

Often speaking of base-x coding, to tell the truth, I do not understand, cited some predecessors on the principle of the introduction of this code, since no special needs, do not have to dig too deep, pay attention to the most need to focus on:

"The BASE32 principle is exactly the same as the Base64, so let's look at Base64 coding.

Base64 as its name implies that all ASC characters are represented by 64 display characters, 64 is 6Bits, and the ASC character has 256, which is 8Bits, very simple, take the minimum number of conventions, 24 bits, The implication is that 3 ASC characters are represented by 4 Base64 characters. That is, at the time of encoding, 3 sets of ASC characters, generating 4 Base64 characters, decoding 4 groups, and restoring 3 ASC characters. According to this principle, the string after encoding should be 1/3 longer than the original Base64.

Here the so-called code is to take 6Bits at a time, the converted value as the index number, using this index number, to a predefined character array of length 64 to replace the corresponding character; decoding is inverse, based on the character of the index value in the predefined array, and then a set of 8Bits to restore the ASC character.

The only difference between BASE32 and Base64 is that 256 ASC characters are represented by 32 characters, which means that 5 ASC characters can generate 8 base characters and vice versa.

Base64 are usually composed of "A-Z", "A-Z", 0-9 and "+" and "=" symbols. “

And then recount the conversion process above, that is, Oracle computes the MD5 hash of the SQL text, take the latter 64 bits as the sql_id, where the BASE-32 encoding is used to convert, where the base-32 transcoding of the visible character is 0123456789ABCDFGHJKMNPQRSTUVWXYZ. Then the 32 bits of the post-hash value are taken as Hash_value. In practice, however, a non-visible character ' \ ' is usually added to the end of the SQL text and then hashed.

Tanel Poder said the following:

"Library cache is physically still organized by the hash value, not sql_id. When you query a X$kglob or V$sql by sql_id, then Oracle just extracts the low 4 bytes from the sql_id and still Does the lookup by hash value.

So, despite only sql_id showing up everywhere in Enterprise Manager and newest Oracle views and scripts, the Hash_value is N ' t going anywhere, it's a fundamental building block of the library cache hash table. "

that is, although sql_id is typically used to represent a SQL after 10g, the library cache is actually physically or Hash_value organized using the. When querying X$kglob or v$sql viewsusing sql_id,oracle also extracts onlythe lower 4 bytes of S ql_id, still passing hash_ value to retrieve.

So while SQL _id is visible everywhere in EM and in Oracle views and scripts, the actual hash_value still works, which is the basis for building a library cache hash table.

He also gives a script for sql_id and Hash_value conversions, using the following sql:

Select    Lower (Trim (' &1 ')) sql_id  , trunc (mod (SUM (InStr (' 0123456789abcdfghjkmnpqrstuvwxyz ', substr ( Lower (Trim (' &1 ')), level,1))-1)                       *power (32,length (Trim (' &1 '))-level), Power (2,32))) Hash_valuefrom    Dualconnect by Level    <= Length (Trim (' &1 '))/

Now we can see what this string of characters in InStr means. is actually the visible character of the base-32 transcoding. can also explain the beginning of the article two why SQL seems to be the same , but the results are different, in fact, the base-32 transcoding use of the wrong.

Summary :

Everything has a cause and effect, began to encounter such a SQL, take it for granted is 0-9,a-z consecutive characters, but in fact here is used base-32 transcoding, is not a continuous character, so understanding the principle behind it, it helps to understand why this use, and not so use.

A misunderstanding caused by a sql_id and Hash_value conversion attempt