oracle 主键 hash,SQL_ID,HASH_VALUE,ADDRESS 的疑惑

Well the address can’t be part of a SQL lookup mechanism as the whole point of the library cache hash table is that you can look up a cursor using hash value fast *when you don’t know its address*

But yep, SQL_ID is just the lowest 64 bits of MD5 hash of the object name. Hash value is 32 lowest bits. The MD5 hash value is stored in X$KGLOB.KGLNAHSV as well so its possible to compare them.

I guess SQL_ID looks more human readable to people who don’t like numbers :)

I still use hash_value wherever possible but in some views they’ve left only SQL_ID in (like v$sql_shared_cursor for example).

The address is useful for rare cases where two different SQL statements hash to the same hash value (so the cursors have same hash but different address in shared pool).

However, as the sql_address points to a parent cursor (which may have multiple child cursors under it), then as of 10g+ you should also use sql_child_number from v$session and match it to child_number in v$sql to find the actual child cursor executed.