 |
|
Oracle shared pool hashing
Don Burleson
|
Inside the
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
large pool.
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.
shared_hash.sql
Rem:
rem: FUNCTION: Shows by user who has possible
rem: SQL reuse problems
rem:
column total_hash
heading 'Total Hash|Values'
column same_hash
heading 'SQL With|Same
Hash'
column u_hash_ratio format 999.999
heading 'SQL Sharing|Hash'
ttitle 'Shared
Hash Value Report'
spool shared_hash.lst
break on report
compute sum of total_hash on report
compute sum of same_hash on report
select
a.username,
count(b.hash_value) total_hash,
count(b.hash_value)-count(unique(b.hash_value)) same_hash,
(count(unique(b.hash_value))/count(b.hash_value))*100 u_hash_ratio
from
dba_users a,
v$sqlarea b
SEE CODE DEPOT FOR FULL SCRIPT
group by
a.username;
clear computes
The script above produces a report similar to a
previous one.
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
statements.
Total Hash SQL
With SQL Sharing
USERNAME Values Same
Hash Hash
------------------------------ ----------
--------- -----------
AULTM
129 0 100.000
DCARS
6484 0 100.000
MCNAIRT
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
|
