Oracle shared pool hashing
Oracle Shared Pool
With Oracle, the shared pool size should be
increased when using MTS to accommodate MTS messaging, queuing, and
UGA requirements. In Oracle9i and beyond, use the large pool is used to prevent MTS from
affecting the shared pool areas. If using PQO, Oracle 9i and beyond requires the
The Shared Pool and MTS
The use of the multithreaded server option (MTS)
in Oracle requires a sometimes dramatic increase in the size of the
shared pool. This increase in the size of the shared pool caused by
MTS is due to the addition of the user global areas (UGAs) required
for sorting and message queues. If you are using MTS, you should
monitor the V$SGASTAT values for MTS-related memory areas, and
adjust the shared-pool memory allocations accordingly.
Note that in Oracle 8 and greater, if MTS is
being used, you should make use of the large pool feature to pull
the user global areas and MTS queues out of the shared-pool area.
This prevents the fragmentation problems that have been reported in
shared pools when MTS is used without allocating the large pool.
In current releases of Oracle the entire SQL
statement is used to generate the statement hash value. You should
never see duplicate hashes in 8i, 9i and subsequent releases. A
script to monitor for duplicate hash values is shown below.
rem: FUNCTION: Shows by user who has possible
rem: SQL reuse problems
heading 'Total Hash|Values'
heading 'SQL With|Same
column u_hash_ratio format 999.999
heading 'SQL Sharing|Hash'
Hash Value Report'
break on report
compute sum of total_hash on report
compute sum of same_hash on report
SEE CODE DEPOT FOR FULL SCRIPT
The script above produces a report similar to a
The report below shows which users are
generating SQL that hash to the same values. Once a user is
isolated, the script below can be run to find the bad SQL
Total Hash SQL
With SQL Sharing
USERNAME Values Same
129 0 100.000
6484 0 100.000
20 0 100.000
PASSMAP 2 0 100.000
QDBA 109 0 100.000
RCAPS 270 0 100.000
RCOM 342 0 100.000
REPORTS1 28 0 100.000
SECURITY_ADMIN 46 0 100.000
SYS 134 0 100.000
sum 7564 0