Sunday, April 10, 2016

sql_id and idn to shared pool subpool

Oracle shared pool is divided into a few of subpools(maximum 7). Each sql_id is mapped to its subpool by hash function:
    subpool no = mod(mod(dbms_utility.sqlid_to_sqlhash(sql_id), 131072), subpools) + 1
    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.