Note:396940.1
How many Subpools will I have by default?
The number of subpools is calculated using a simple algorithm. First, a subpool must be at least 128MB in 9i releases and at least 256MB in 10g releases. Bugs and internal tests show that subpools around 500M will work most efficiently and in future versions, we may see higher default sizes in the algorithm. Second, there can be one subpool for every four CPUs on the system, up to 7 subpools. The number of subpools can be explicitly controlled using the init.ora parameter _kghdsidx_count. There is no parameter to explicitly control the size of each subpool.
If someone configured an 12-CPU system with a 300MB shared pool on 9i, Oracle will create two subpools, each of size 150MB. If the shared pool size was increased to 500MB, Oracle will create three subpools, each of size 166MB.
Because 128MB (and even 256MB on 10g) can be small compared to some cursors, the memory per subpool will likely need to be increased. There is no parameter to change the minimum size of the subpool; the only recourse is to decrease the number of subpools for a given shared pool size or increase the shared pool size so the size of the subpools increase. Please remember that increasing the size of the shared pool does not necessarily increase the size of the subpool, because the number of subpools can increase if there are many CPUs on the system.
How do I control the number of subpools used?
To see the number of subpools used currently issue
select a.ksppinm “Parameter”,
b.ksppstvl “Session Value”,
c.ksppstvl “Instance Value”
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and a.ksppinm like ‘%kghdsidx%’;
or issue
select count(*) from x$kghlu;
The parameter _kghdsidx_count controls the number of subpools used. Setting the value of this parameter to one “reverts” the shared pool behavior back to 8.1.7 behavior, e.g., one subpool.
SQL> alter system set “_kghdsidx_count”=
or add this in the pfile
“_kghdsidx_count”=
NOTE: Subpools are created at startup when the SGA is created. In both examples above, the database has to be restarted to change the number of subpools created. Any change to the _kghdsidx_count will change the number of subpools in the Large Pool as well.
Are all ORA-04031 errors reported in the alert log?
No. Some errors only show up at the client workstation. They may or may not be reported to the DBA as the user may reissue the command or set of application steps and not see the error again.
You can monitor if users have been seeing ORA-04031 using this code
From website http://www.ixora.com.au/
connect / as sysdba
——————————————————————————-
—
— Script: shared_pool_lru_stats.sql
— Purpose: to check the shared pool lru stats
— For: 8.0 and higher
—
— Copyright: (c) Ixora Pty Ltd
— Author: Steve Adams
—
— NOTE: Per Bug 3352753, this may not work with 9i / added kghlushrpool to Steve’s code for 9i/10g
——————————————————————————-
>column kghluops heading “PINS AND|RELEASES”
column kghlunfu heading “ORA-4031|ERRORS”
column kghlunfs heading “LAST ERROR|SIZE”
column kghlushrpool heading “SUBPOOL”
select
kghlushrpool,
kghlurcr,
kghlutrn,
kghlufsh,
kghluops,
kghlunfu,
kghlunfs
from
sys.x$kghlu
where
inst_id = userenv(‘Instance’)
How can we see a breakdown of the data in the “miscellaneous” structure in V$SGASTAT?
When running a query on v$sgastat, there are cases where you will see a very large value for “miscellaneous”. Until Oracle 10g Release 2, the basic design of the SGA structure internally remained unchanged. There were table entries for various memory “comments” in the data dictionary where we keep statistics on the largest memory “comments” or structures. The smaller structures are lumped together in the memory “comment” called miscellaneous because we tracked only a handful of structures. If you run
select * from v$sgastat where pool=’shared pool’;
on an Oracle7 or Oracle 8i database, you will see a short list of memory “comments” reported back. However, with additional memory “comments” or structures added in later versions of 9.2.0.x and 10.1.x, this became a problem and a redesign was necessary to allow more table entries in the data dictionary for statistics on more of the memory structures. Running the query above on an Oracle 10g Release 2 database will report back a long list of memory structures.
Per bug 3663344, there were occasional inconsistencies in the statistics reported in v$sgastat due to memory structures growing and shrinking over time. Once a memory structure reached an internally controlled size, we moved the data about the memory structure from the general purpose table entry, “miscellaneous”, to a specific memory structure table entry. The problem was usually seen with negative numbers in some memory structures in v$sgastat or at times you would see an unusually large value in “miscellaneous”.
The only way to “drill-down” into the way memory is allocated in the “miscellaneous” area is to get a heapdump trace. You can issue this command
alter system set events ‘4031 trace name HEAPDUMP level 536870914’;
NOTE: Setting this event at the instance level will generate large files and if the 4031 error occurs frequently, you will get many trace files. This can impact performance and hang (and in some cases crash a database). Turn this event off using
alter system set events ‘4031 trace name HEAPDUMP off’;
and at the next occurances of the 4031 problem you will get a breakdown of the memory in the SGA and also the breakdown of memory used in the top five subheaps in the SGA. In this scenario, you would expect one or more of the largest subheaps listed in this trace to be within ‘miscellaneous’. Unfortunately, there isn’t a way to see the entire breakdown within ‘miscellaneous’, but we only need to be concerned about larger than expected entries within ‘miscellaneous’.
To get an immediate memory dump use these steps
alter system set events ‘immediate trace name heapdump level 536870914’;
or
sqlplus /nolog
connect / as sysdba
alter system set max_dump_file_size=unlimited;
oradebug setmypid
oradebug unlimit
oradebug dump heapdump 536870914
oradebug tracefile_name
oradebug close_trace
Close the SQL*Plus session and find the heapdump trace file listed in the ‘oradebug tracefile_name’ command above.
If the problem is actually associated with permanent memory structures (tracked under the ‘miscellaneous’ table entry), there is not a way to get information on these memory areas unless you set the event 10235 level 65536. This event should only be set under direction from Oracle Support.