subpool no = mod(mod(dbms_utility.sqlid_to_sqlhash(sql_id), 131072), subpools) + 1
since
idn=dbms_utility.sqlid_to_sqlhash (sql_id)
the mapping can be written as:
subpool no = mod(mod(idn, 131072), subpools) + 1
where subpools is the number of used subpools, which can be observed by:
select * from v$latch_children where name = 'shared pool';
In the above formula, 131072 is the default Library cache hash table bucket count, which is specified by:
_kgl_bucket_count default 9
and Library cache hash table bucket count is computed by:
(2^_kgl_bucket_count * 256 = 2^(_kgl_bucket_count + 8))
default: 2^(9+8) = 2^17 = 131072
By the way, there are also other KGL hidden parameters:
_kgl_large_heap_warning_threshold default 52428800 (51200K or 50MB)
maximum heap size before KGL writes warnings to the alert log
_kgl_large_heap_assert_threshold default 524288000 (512000K or 500MB)
maximum heap size before KGL raises an internal error
_kgl_time_to_wait_for_locks default 15
time to wait for locks and pins before timing out
The above hash function can be used to monitor sql_id unbalanced distributions of subpools (see Blog: Shared Pool - Memory Allocation - unbalanced ).For instance, once seeing the error message:
ORA-04031: unable to allocate 184 bytes of shared memory ("shared pool","SELECT ...","SQLA^de798f19","179.kggfa")
you can find its subpool by:
mod(mod(to_number('de798f19', 'xxxxxxxx'), 131072), 7)+ 1 = 5
If majority of such errors occurs in subpool 5, it would be a hint pointing to the unbalanced distributions.
The above formula is verified in Oracle 11gR2 and 12c.
