In version 9.2 Oracle changed the way the SGA allocates memory by implementing subpools; each subpool contains all of the necessary memory structures so this allows sessions to access shared areas without incurring the latching waits earlier releases experienced. Unfortunately version 9.2.0.1 through 9.2.0.4 could still throw ORA-04031 errors when a subpool filled, as Oracle provided no mechanism to add memory when necessary. This was initially corrected in the 9.2.0.5 patchset and has been steadily improved in subsequent releases. Let’s look at how the SGA was allocated in 9.2.0.4 and how it’s changed by looking at the current allocation methodology from 11.2.0.3.
Implementing subpools was a major improvement in SGA management as multiple subpools could be allocated based upon the setting for shared_pool_size; subpools in 9.2 were allocated for each 128 M of shared pool size, so implementing a 300 M shared pool would allocate two subpools (any memory less than 128 M would be allocated between the available subpools). A session would use a subpool rather than the entire shared pool area as in earlier releases, which reduced latch contention on shared pool resources since the same resources were available across all of the allocated subpools (the only difference would be the memory allocated to a given subpool). This improved performance and reduced waits on shared pool resources, a major iprovement given that with releases 9.2 onward, enterprises were more heavily using their Oracle databases. One caveat of this implementation is that free memory allocated to one subpool cannot be re-allocated to another subpool that may need it (this has not changed in 11.2). Because of this and the absence of a free memory subpool version 9.2.0.4 threw ORA-04031 errors even though the sga_max_size (the only automatic sga parameter available in version 9) far exceeded the configured shared_pool_size. Three options existed to deal with this situation — increase the shared pool allocation to allow longer periods before database restarts, manually reduce the shared pool to one subpool using the _kghdsidx_count hidden parameter to set the subpool count to 1 or patch the installation to 9.2.0.5 to implement a free memory subpool. A client of mine is stll using version 9.2.0.4 (you read that correctly); the database currently in use could not be patched above 9.2.0.4 so the mentioned hidden parameter was set and the shared pool allocation was increased to 6 GB (and you read that correctly, too). The SGA memory allocations for that database became:
-- All allocations:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (1): 6794772480 6480
shared pool (Total): 6794772480 6480
-- Allocations matching "%":
SUBPOOL NAME SUM(BYTES) MB
------------------------------ -------------------------- ---------- ----------
shared pool (1): free memory 5434421008 5182.67
sql area 544520240 519.29
miscellaneous 326241176 311.13
library cache 141230784 134.69
Checkpoint queue 102420480 97.68
db_block_hash_buckets 48438128 46.19
KQR M PO 43589976 41.57
KQR M SO 43075712 41.08
event statistics per sess 18588960 17.73
FileOpenBlock 16607024 15.84
partitioning d 14965512 14.27
sim memory hea 11223472 10.7
KGLS heap 8254832 7.87
KQR L PO 6558040 6.25
KQR L SO 5532704 5.28
sessions 4475120 4.27
FileIdentificatonBlock 4369960 4.17
PL/SQL MPCODE 3327848 3.17
dictionary cache 3229952 3.08
enqueue 3115136 2.97
PL/SQL DIANA 2764200 2.64
1M buffer 2099200 2
processes 1932000 1.84
db_handles 1740000 1.66
PX subheap 1234872 1.18
errors 407896 .39
parameters 276640 .26
KGK heap 80600 .08
table definiti 11144 .01
KQR S SO 10752 .01
trigger source 9264 .01
trigger defini 5088 0
joxs heap init 4240 0
temporary tabl 2696 0
KQR X PO 2576 0
fixed allocation callback 2176 0
PLS non-lib hp 2088 0
trigger inform 984 0
38 rows selected.
Reboots became less frequent but this is not the best solution for such a problem. Unfortunately it will be a while before this database can be upgraded to take advantage of the improvements available in 11.2.0.3. The situation improved greatly with versions 10.2 and 11.2; the free memory subpool, subpool 0, was implemented along with the sga_target automatic memory management parameter, which allowed the DBA to set boundaries for the sga allocations; no longer was the upper bound the only setting that could be made and this provided the added benefit of dynamic SGA resizing on the part of the DBA. Looking at the same output for an 11.2.0.3 instance we find:
SQL> @tpt_public/sgastatx %
-- All allocations:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (0 - Unused): 75497472 72
shared pool (1): 159383552 152
shared pool (Total): 234881024 224
-- Allocations matching "%":
SUBPOOL NAME SUM(BYTES) MB
------------------------------ -------------------------- ---------- ----------
shared pool (0 - Unused): free memory 75497472 72
shared pool (1): free memory 31739824 30.27
KGLH0 15379820 14.67
SQLA 12579244 12
KGLS 8575184 8.18
ASH buffers 8388608 8
KCB Table Scan Buffer 4194816 4
dbwriter coalesce buffer 4194816 4
KSFD SGA I/O b 4190272 4
row cache 3799364 3.62
KGLSG 3686916 3.52
event statistics per sess 3245824 3.1
KGLHD 2844388 2.71
ksunfy : SSO free list 2821248 2.69
obj stats allocation chun 2580480 2.46
KQR M PO 2553860 2.44
write state object 2490120 2.37
dbktb: trace buffer 2457600 2.34
kglsim hash table bkts 2097152 2
dirty object counts array 2097152 2
PLMCD 2081172 1.98
private strands 1907712 1.82
PLDIA 1890492 1.8
KTI-UNDO 1853064 1.77
file state object 1631496 1.56
db_block_hash_buckets 1574912 1.5
KSKQ SGA NODEINFO 1359872 1.3
message pool freequeue 1256056 1.2
FileOpenBlock 1032460 .98
SGA - SWRF Metric CHBs 983000 .94
prmtzdini tz region 801412 .76
VIRTUAL CIRCUITS 637536 .61
KQR M SO 628224 .6
FileIdentificatonBlock 627236 .6
KKSSP 552048 .53
krdrsb read violation arr 552000 .53
sys event stats 552000 .53
Checkpoint queue 513280 .49
KOKTD 492612 .47
character set object 480240 .46
resize request state obje 472000 .45
KGLDA 455860 .43
lock state hash table 453376 .43
dbgefgHtAddSK-1 450864 .43
procs: ksunfy 434400 .41
transaction 430540 .41
db_files 421288 .4
enqueue 411032 .39
PRTDS 364232 .35
ksdhng: els blkrs cache 357120 .34
buffer handles 340804 .33
max allowable # log files 337600 .32
NETWORK BUFFER 327840 .31
SYSTEM PARAMETERS 323268 .31
FAST SPACEUSAGE 312328 .3
DML lock 304436 .29
TXN TABLE SEGMENTED ARRAY 281132 .27
object queue hash buckets 278528 .27
KSK VT POOL 275564 .26
work area tab 270144 .26
SQLP 263776 .25
KGNFS scontext 262264 .25
KESTB existence bitvec 262144 .25
change tracking recovery 262144 .25
Wait History Segment 257920 .25
parameter table block 252356 .24
KQR S PO 240656 .23
ktlbk state objects 235008 .22
kglsim heap 230280 .22
kglsim object batch 199920 .19
parameter string values 191232 .18
PX subheap 187904 .18
PRTMV 183240 .17
ksfd: components i/o stat 177408 .17
latch nowait fails or sle 177280 .17
BRANCH TABLE SEGMENTED AR 169644 .16
kmgsb circular statistics 162304 .15
KEWS sesstat values 160704 .15
KQR L SO 156672 .15
KSK SGA 154976 .15
ksuloi: long op statistic 154752 .15
LRMPD SGA Table 153664 .15
Sort Segment 152384 .15
SGA - SWRF Metric Eidbuf 151696 .14
Nettimeout Histogram 151280 .14
object queue 131200 .13
kglsim timestamps 131072 .13
kglsim Java timestamps 131072 .13
enqueue resources 121756 .12
KGLA 121228 .12
KGX 112696 .11
ksv slave class 111032 .11
alter system errs: kspnfy 109568 .1
branch 79244 .08
MS alert log 72704 .07
trace events array 72000 .07
RTA Boundary Info thread 71808 .07
modification 71704 .07
character set memory 69712 .07
kglsim Java per-gran bkt 67200 .06
kglsim per-gran bkt 67200 .06
parameter handle 66332 .06
simulator hash buckets 66048 .06
qmps connections 65280 .06
KFK idtab_kfksga 60180 .06
UNDO STAT INFO 59904 .06
channel context areas 59520 .06
pso tbs: ksunfy 58200 .06
network connections 56000 .05
Space Background Task Des 56000 .05
KTC latch subh 55276 .05
KQR L PO 53248 .05
ksws service events 52352 .05
dsktab_kfgsg 51988 .05
dbkea msg body 51200 .05
process group array 47368 .05
KYWM RPLY CROSS INSTANCE 46080 .04
UNDO INFO SEGMENTED ARRAY 45164 .04
KGSK scheduler 44648 .04
Parameter Table 43888 .04
ksdhng: el wtr cache 43648 .04
Temporary Tables State Ob 43540 .04
constraints 42988 .04
ktgCircBuf:MinActScn Trc 40020 .04
KGKP randnum 40000 .04
KGKP randnum shadow 40000 .04
per_pg_set_descriptor_arr 39168 .04
txncallback 39116 .04
channel handle 38172 .04
KCB where statistics arra 37312 .04
KGSKI schedule 37128 .04
sys event stats for Other 36800 .04
kfklsodtab 36296 .03
Managed Standby Proc Arra 35840 .03
call 34308 .03
Closed Thread SCN Bitvec 33792 .03
SEQ S.O. 33728 .03
ksdhng: cbuf 32768 .03
hash bucket array allocat 32768 .03
poisoned memory 32768 .03
sga blobs 32768 .03
event descriptor table 32304 .03
file # translation table 32044 .03
PLS SGA hp 31868 .03
messages 31200 .03
KTCN: Row Change Segmente 30688 .03
kghx free lists 30384 .03
java static objs 29092 .03
kso: sched delay history 28896 .03
resize operation history 28804 .03
SQLK 28476 .03
SGA structure for ktslgt 26696 .03
KTSL lat struct 26624 .03
LGWR-network Server info 25344 .02
krsv_notifier_cb 25320 .02
CGS system incarn array 24864 .02
pesom.c:Global 24732 .02
kewr MMON Remote Flush Re 24576 .02
kebm test replies 23552 .02
post/wait queues 23280 .02
kglsim size of unpinned m 22848 .02
kglsim size of pinned mem 22848 .02
ArchLog Dest Array 22272 .02
KFD extent enqueue obj 21616 .02
file iostats dynamic seg 21404 .02
RULEC 21236 .02
Rec Ping Per Thread Info 21128 .02
dummy 20848 .02
locator state object 20388 .02
dgtab_kfmdsg 19568 .02
ufgtab_kfmdsg 19568 .02
simulator latch/bucket st 19456 .02
KTCN: Obj Invalidation Se 18108 .02
kwqmncini-slv 18000 .02
ENQUEUE STATS 17080 .02
multiblock re 16932 .02
x$rule_set 16804 .02
spfile cleanup structure 16752 .02
pesldT03_Instance 16672 .02
kgnfs authp 16432 .02
KTSL reco struct 16384 .02
parallel_max_servers 16200 .02
1:kngisga 16112 .02
distributed_transactions- 16080 .02
TBS STAT 16000 .02
AQ Propagation Scheduling 16000 .02
osp allocation 15936 .02
ksbxic obj 15668 .01
kgnfs stats array 15624 .01
KEWS statistic metadata 15624 .01
message pool context area 14720 .01
kxfpdp pointers 14400 .01
xslongops 14304 .01
PARALLEL T RECO LATCH 14040 .01
ksfd: consumer group i/o 13824 .01
name-service table 13352 .01
SUBPOOL NAME SUM(BYTES) MB
------------------------------ -------------------------- ---------- ----------
shared pool (1): ktg hash buckets 12812 .01
memory transfer history 12804 .01
kcbl state objects 12800 .01
enqueue_hash 12744 .01
monitoring co 12384 .01
log_checkpoint_timeout 12360 .01
ksfv subheap 12336 .01
KTF MAPPINGS 12288 .01
dbke_incid_cache_init 12000 .01
kcbl seq io throughput 12000 .01
ksdhng: fblkrs cache 11904 .01
kglsim count of pinned he 11424 .01
kglsim count of unpinned 11424 .01
File Space Usage 11340 .01
Prefetch history buffer 11296 .01
groups_kfgbsg 11264 .01
kqf runtime defined table 11188 .01
qesmmaInitialize: pa_qesm 11088 .01
where to latch num map 11080 .01
param hash values 10972 .01
parameter blocks 10972 .01
dbktb: trace pool 10800 .01
segmented arrays 10704 .01
sched job slv 10400 .01
SAGE commit cache node Al 10240 .01
object queue hash table d 10240 .01
archive_lag_target 9620 .01
flashback_marker_cache_si 9196 .01
block iostats dynamic seg 9120 .01
fencing reid 8992 .01
instance cnxn information 8928 .01
KSKQ SGA 8896 .01
media recovery state obje 8688 .01
ksgm sga instance configu 8584 .01
SQL Memory Manager Base W 8576 .01
KSQ event description 8460 .01
kwqicaqe2kc1 8208 .01
change notification obj m 8200 .01
change notification regis 8200 .01
LGWR debug module memory 8192 .01
ksfv instance 8096 .01
dlo fib struct 8020 .01
Saved job ids 8000 .01
ksdhng: blkers cache 7936 .01
ksunfy: is parent statist 7800 .01
jsksncb: 2 7696 .01
ksmd unit test 1 7632 .01
KGSK sga 7544 .01
ksv slave 7372 .01
kcrfa structures 7192 .01
ksv reaper 6892 .01
sht bkt arr allo 6656 .01
state objects 6460 .01
PMON blockers 6400 .01
SGA - SWRF DrvMet Runtime 6368 .01
kcbl statistics 6144 .01
kdlwss 6072 .01
kebm slave descriptors 5704 .01
KKTIN 5696 .01
xssinfo 5560 .01
reserved entries for all 5440 .01
row cache child latch 5408 .01
KQF runtime defined table 5208 0
ksunfy: system-global sta 5200 0
ksu: ksusg_emptypxsta 5200 0
KFG SO child 5176 0
sim trace buf 5140 0
sim segment hits 5120 0
recov_kgqbtctx 5080 0
parameter text value 5028 0
KKBTD 5000 0
ksu:stats_fre 4980 0
State object subpools 4896 0
L2 file map array 4896 0
ksz parent 4856 0
KTUR HIST INFO 4840 0
jsksncb: 9 4696 0
SGA - SWRF RawMet Runtime 4656 0
OS file lock 4600 0
event-class map 4600 0
Transportable DB Converte 4484 0
UNDO INFO HASH 4352 0
ksb action records 4224 0
RTA read control file ind 4224 0
Service-level ID trace se 4196 0
KFK SGA 4160 0
joxs heap 4152 0
kwqmncini-tbl 4136 0
KFG state obj 4132 0
SWRF Alerted 4116 0
qmn tasks 4108 0
kglsim hash table 4104 0
jsksncb: 4 4104 0
kkj jobq wor 4104 0
cinfo_kfnsg 4100 0
KCB undo bitvec 4096 0
kxfpsga snapshots 4096 0
kwqmncal: allocate buffer 4088 0
threshold ale 4080 0
peshm.c:Global 4072 0
KKKI consumer 4072 0
pesom.c:Object 4072 0
KTSJ subheap 4072 0
kso req alloc 4072 0
alert threshol 4072 0
resumable 4068 0
jsksncb: 3 4056 0
KTU avail cache 4024 0
KEWS aggregation objs 4020 0
KGX rowlist 4000 0
ksu:ksunf_fre 3996 0
cp cmon array 3972 0
keswx:plan en 3936 0
cp connection 3848 0
quiescing session 3816 0
kss unit test 3816 0
jsksncb: 7 3784 0
JSX SGA 3732 0
ktcnc child latches 3712 0
plugin datafile array 3612 0
ksim cached group entry l 3584 0
list 3584 0
ASM scan context 3556 0
xscalc 3556 0
cp srv array 3524 0
vips_package_incident_det 3500 0
temporary table lock 3492 0
cp pool array 3460 0
KCB buffer wait statistic 3352 0
KSK OSS PLAN 3352 0
Client ID trace settings 3300 0
OS proc request holder 3300 0
I/O stat latches 3224 0
KEWS statistic maps 3192 0
analytic workspace 3172 0
KSFQ buffer pool 3172 0
ASM generic network state 3172 0
sort segment handle 3140 0
ksws service object 3104 0
latch recovery structures 3096 0
kfmsg 3088 0
ksir State Object 3044 0
statement queuing 3044 0
ASM rollback operations 3044 0
ktcnqrow columns 3036 0
redo allocation latch(es) 3016 0
SHRINK STAT 2996 0
SGA structure for KPON kp 2952 0
jsksncb: 6 2928 0
name-service recovery 2916 0
sched job queue 2916 0
KFM state obj 2916 0
PX server msg stats 2904 0
PX QC msg stats 2904 0
KEWS fixed SGA 2896 0
Parameter Handle 2884 0
State object pools 2880 0
DISPATCHERS INFO 2864 0
Result Cache: State Objs 2852 0
SHARED SERVERS INFO 2852 0
ksbtnfy: infrequent actio 2824 0
KTI latch structure 2808 0
buffer_pool_desc_array 2808 0
KTPR HIST TB 2808 0
bloom filter 2788 0
ASM map operations 2788 0
KSI resource types 2704 0
grptab_kfgsg 2692 0
reservation state object 2672 0
KTCN: Hash Table Segmente 2660 0
ASM KFFD SO 2660 0
change tracking state cha 2660 0
ASM kfk state object 2660 0
sim lru segments 2560 0
sim segment num bufs 2560 0
KTCN: query inv Segmented 2536 0
kcfis state object 2532 0
Online Datafile Move sess 2532 0
stat hash values 2488 0
name-service entry 2468 0
alert_ext 2444 0
temporary foreign ref 2436 0
file # to first dba, exte 2412 0
property service SO 2404 0
block media rcv state obj 2404 0
kdlxdup swappl 2400 0
iosts_kfmdsg 2400 0
diagv_incident 2312 0
vips_pkg_incident_candida 2312 0
vincident 2312 0
ksvr msg cache 2288 0
ksir PrivOp State Object 2276 0
temp lob duration state o 2276 0
name-service request 2276 0
vnot_exist_incident 2244 0
cross-platform compliance 2228 0
latch hash-value table 2212 0
SUBPOOL NAME SUM(BYTES) MB
------------------------------ -------------------------- ---------- ----------
shared pool (1): latch wait-event table 2212 0
latchnum to latch map 2212 0
SGA struct - SWRF Metrics 2160 0
KEWS statistic name 2120 0
INCIDENT 2112 0
kpummst global in the SGA 2060 0
SGA - SWRF Metrics WCTime 2056 0
SGA - SWRF Metrics ksuTim 2056 0
kfkid hash 2052 0
kfkhsh_kfdsg 2052 0
namhsh_kfdsg 2052 0
Result Cache: Bloom Fltr 2048 0
dev2node map 2048 0
KQR S SO 2048 0
keswxNotify:tabPlans 2048 0
Banner Storage 2044 0
active checkp 2040 0
ksdhng: blkrs cache 1984 0
ksdhng: wtr cache 1984 0
kfklsod list 1976 0
KXFR Hash Array latches a 1920 0
vproblem 1916 0
kglsim used list arr 1904 0
KQF optimizer stats table 1876 0
os statistics 1872 0
KWQDL child latches 1856 0
vproblem2 1848 0
HM_FINDING 1848 0
vproblem1 1784 0
kglsim alloc latch area 1768 0
kglsim latch area 1768 0
ksfd: file i/o stats poin 1732 0
vproblem_int 1716 0
PX server deq stats 1692 0
PX QC deq stats 1692 0
procs_kfgbsg 1672 0
vproblem_lastinc 1652 0
kwrsnfy: kwrs 1612 0
ts encryption key afn rev 1604 0
ksfd: block i/o stats poi 1600 0
post stats 1556 0
event classes 1552 0
KEWS External IDs 1536 0
KXFR Psuedo Hash Array fo 1536 0
krvxdta 1520 0
DDE_USER_ACTION_PARAMETER 1456 0
vhm_run 1452 0
HM_RECOMMENDATION 1452 0
rules engine aggregate st 1412 0
ksbsa pointer array 1412 0
HM_RUN 1388 0
KTA latches 1320 0
IPS_PACKAGE 1320 0
dispatcher rate 1312 0
KTCNQROW colnames 1292 0
sim_knlasg 1280 0
invalid low rba queue 1280 0
KSVR SGA 1256 0
kgnfs htab array 1224 0
MTTR advisory context 1224 0
standby event stats 1216 0
AWR Table Info (KEW layer 1212 0
SGA structure for kelr 1200 0
vips_file_copy_log 1188 0
vinc_meter_info 1188 0
KTC latches 1144 0
vips_pkg_file 1124 0
PROBLEM 1124 0
Log Archive Config Contex 1092 0
kglsim recovery area 1088 0
log_simultaneous_copies 1088 0
qm_init_sga:qmdpsg 1064 0
SQL Workarea Histogram 1056 0
ADR_CONTROL 1056 0
Cleanup state objects 1056 0
kfasga 1048 0
LISTEN ADDRESS TABLE 1024 0
KESTB existence bitvec se 1024 0
SERVICE NAMES TABLE 1024 0
PARAMETER TABLE 1024 0
DISPATCHER CONFIG TABLE 1024 0
sga listelement 1024 0
PRESENTATION TABLE 1024 0
Active Session History - 1016 0
KCB tablespace encryption 1008 0
vips_package_history 992 0
SGA - AWR Metric RBSM str 992 0
INC_METER_SUMMARY 992 0
replication session stats 992 0
HM_MESSAGE 992 0
event statistics ptr arra 992 0
ksunfy : array of session 992 0
PQ/BizCard 960 0
qesbl old rows 960 0
IPS_PROGRESS_LOG 924 0
INC_METER_INFO 924 0
KSXP test table columns d 924 0
IPS_PACKAGE_HISTORY 924 0
dir_ext 924 0
IPS_CONFIGURATION 924 0
vips_package_file 924 0
DDE_USER_ACTION_DEF 924 0
EM_DIAG_JOB 924 0
kkj jobq slav 896 0
KTCTSNL subheaps 880 0
qmtb_init_data 864 0
EM_USER_ACTIVITY 860 0
relmd_ext 860 0
INC_METER_CONFIG 860 0
EM_TARGET_INFO 860 0
kponfy 856 0
krvxdka 840 0
dbkea msgs 800 0
DDE_USER_ACTION 792 0
PICKLEERR 792 0
PX list of chunk lists 736 0
VIEW 728 0
INC_METER_IMPT_DEF 728 0
vtest_exists 728 0
vincident_file 728 0
IPS_FILE_COPY_LOG 728 0
INC_METER_PK_IMPTS 728 0
IPS_PACKAGE_UNPACK_HISTOR 728 0
kebm slave reply 720 0
KCB incremental ckpt entr 680 0
SWEEPERR 660 0
vips_file_metadata 660 0
IPS_PACKAGE_INCIDENT 660 0
vproblem_bucket_count 660 0
IPS_FILE_METADATA 660 0
HM_INFO 660 0
IPS_REMOTE_PACKAGE 660 0
DFW_CONFIG_ITEM 660 0
parameter value memory 652 0
sim cache nbufs 640 0
sim cache sizes 640 0
ktfa dynamic SGA allocati 632 0
pesom.c:latch array 624 0
latches for protecting ws 624 0
latches for protecting fs 624 0
kgnfs ctab array 624 0
pso child tracebuf ptrs 600 0
processes 600 0
logout storm management 600 0
HM_FDG_SET 596 0
INCIDENT_FILE 596 0
INCCKEY 596 0
vshowcatview 596 0
IPS_PACKAGE_FILE 596 0
vproblem_bucket1 596 0
vproblem_bucket 596 0
v_nfcinc 596 0
object level stat table 588 0
KGLNA 576 0
KGI Session State 564 0
system default language h 540 0
database NCHAR language h 540 0
database creation languag 540 0
kzsrs filename 532 0
DFW_CONFIG_CAPTURE 528 0
v_actinc 528 0
vshowincb 528 0
VIEWCOL 528 0
vshowincb_i 528 0
vips_package_main_problem 528 0
PQ/ResSched 520 0
pesom.c:subheap ds array 520 0
KTU lat struct 520 0
primem_kfmdsg 516 0
ket (AutoTask) SGA 508 0
KSFV SGA 504 0
trace_knlasg 500 0
ksacm service obj array 480 0
ksunfy: nodes of hierarch 480 0
kea advisor definition ca 480 0
ADR_INVALIDATION 464 0
DDE_USER_INCIDENT_TYPE 464 0
v_actprob 464 0
v_ipsprbcnt 464 0
vem_user_actlog1 464 0
DDE_USER_INCIDENT_ACTION_ 464 0
v_inc_meter_info_problem 464 0
vips_package_main_int 464 0
v_swperrcount 464 0
vem_user_actlog 464 0
v_inccount 464 0
vips_package_size 464 0
v_ipsprbcnt1 464 0
v_incfcount 464 0
KTU latch cleanup 456 0
process count for each CI 452 0
ksb process so list 452 0
ksunfy : array of SSO fre 452 0
ksb cic process list 452 0
ENQUQUE FREELISTS 448 0
ksb ci process list (each 436 0
kglsim sga 432 0
SUBPOOL NAME SUM(BYTES) MB
------------------------------ -------------------------- ---------- ----------
shared pool (1): KTI latches 432 0
PG latch table 416 0
client/application info l 416 0
ksuloi: child latches for 416 0
enqueue_hash_chain_latche 416 0
session idle latches 416 0
kkae edition name cache 404 0
KKJ SGA 400 0
kodosgi kopfdo 400 0
ams_xaction 396 0
kpscad: kpscscon 392 0
kccsgehtt 360 0
kelt translation table 356 0
latch classes 352 0
SGA Param Source Table 344 0
obj stats hash table 336 0
KQFUTTAB COLS 332 0
ksdhng: cache history 320 0
KTU latches 320 0
MinActScn Array 320 0
quiesce system context 312 0
latches for protecting ls 312 0
KKJ WRK LAT 312 0
channel sga anchor 296 0
kelr system metrics table 296 0
msg Q child latches 288 0
done Q child latches 288 0
sess Q child latches 288 0
KSI Indexes 284 0
Prefetch history buffer a 284 0
Array of cached attr 280 0
branch so 276 0
KYWM CROSS INSTANCE 272 0
Client ID trace setting h 268 0
Service-level trace setti 268 0
qesmmaInitialize: ia_qesm 264 0
dbgefgHtInit-1 264 0
qesmmaInitialize: ta_qesm 264 0
KCB Table Scan Bitmap 256 0
dpslut_kfdsg 256 0
X$KSVII table 256 0
Pre-Warm Initialized Seti 256 0
Monitor Modification Hash 256 0
dbwriter coalesce bitmap 256 0
KEWS sysstat values 256 0
grplut_kfgsg 256 0
X$KSVIT table 256 0
kebm slave message 248 0
kxfpSO qref freelists 240 0
KTC latch cleanup 240 0
kxfpSO q freelists 240 0
slave class sga anchor 240 0
SGA structure for AWR (KE 224 0
DBWR event stats array 216 0
Global Context Heap descr 212 0
KYWM SGA 208 0
kglsim free obj list 204 0
kglsim free heap list 204 0
rules engine context 200 0
SGA structure for KPON ks 200 0
trace buffer header array 200 0
array 2 for shared redo b 192 0
array 1 for shared redo b 192 0
Wait event pointers 192 0
Processor group descripto 192 0
koh dur heap 188 0
PLS chunk 188 0
PLS cca hp desc 188 0
LISTEN ADDRESS ENTRY 176 0
KTCNQROW colname ptrs 172 0
krvxlctx 168 0
log file size history arr 168 0
incr ckpt write count arr 168 0
SGA structure for KWQDL h 164 0
kzekm heap descriptor 164 0
kzxslnfy: 0 164 0
broker globals 160 0
kfmdsg 156 0
kso req alloc heapds 152 0
policy hash table descrpt 152 0
Result Cache: Cache Mgr 152 0
dbgdInitEventGrp: eventGr 152 0
ksu:ksusg_emptyinfo 144 0
KSRPC I/O Stat array 144 0
KKJ SHRD WRQS 144 0
ksleid alloc 144 0
namhsh_kfgsg 144 0
kkzias 144 0
PX subheap desc 140 0
ksdhng: cache ctx 136 0
ktsj_init_sga proc desc 132 0
ktud-usd hash table 132 0
ksfd I/O slave ctx 128 0
KGSKI scheduler heap 1 de 128 0
Result Cache: Memory Mgr 128 0
pesom.c: Pesom_Global_Hea 128 0
ksim cached group entry s 128 0
kdlwl_sga_subheap 128 0
pesom.c: Global_Sub_Heap 128 0
peshm.c: Global_Sub_Heap 128 0
KUPP subheap desc 128 0
KGSKI scheduler heap 2 de 128 0
sim trace buf context 128 0
set_descriptor_array 128 0
tablespace encryption key 120 0
fixed allocation callback 120 0
SGA - SWRF Time Model Bas 116 0
qesmmaInitialize: oa_qesm 112 0
qesmmaInitialize: 112 0
qksbgsg 112 0
Undo Meta Data 108 0
kwqmncini-stat 108 0
event list to post commit 108 0
SCHEDULING POLICY VECTOR 104 0
peshm.c:latch 104 0
ksfv subheap descriptor 104 0
joxs struct 100 0
kqlpWrntoStr:string 100 0
knlsg 96 0
dskm rac entity id 96 0
KTPR SUBHEAP 96 0
Label Cache Heap descrptr 92 0
Read Only Database Accoun 92 0
Policy Cache Heap descrpt 92 0
Auto tune undo info 92 0
Role related heap 92 0
Role graph heap 92 0
kfdsga 88 0
KTCN queries hash table 88 0
KTCNC inv hashtable 88 0
KTCN global operands ht 88 0
VM OSD context 88 0
KTCN global clauses ht 88 0
KSIR SGA 88 0
kgnfs gid list 88 0
DISPATCHER CONFIG ENTRY 84 0
ksim client list 84 0
obj hash table pointers 84 0
diag fixed SGA 84 0
plis struct 80 0
hot latch diagnostics 80 0
heap_kfsg 80 0
kgsk subheap descriptor 80 0
Security Class heap 80 0
KSZ sga subheap descripto 80 0
ksb rolling migration sta 80 0
SERVICE NAME ENTRY 76 0
dbwr outstanding ios per 72 0
bcast ack latency base ar 72 0
latch recovery alignment 72 0
pending bcast scn array 72 0
HTTP fixed headers 72 0
Temporary storage for RTA 72 0
kglsim latches 68 0
kglsim alloc latches 68 0
KSFD Cache Hints for IO R 68 0
kpssnfy: kpsssgct 68 0
ksacm service root 68 0
kfgsga 68 0
X$KSVIS table 64 0
PARAMETER ENTRY 64 0
kebm run-once actions 64 0
dbwr actual working sets 64 0
knlu_init_btree:init 64 0
copy latch aux 64 0
KRBMROS ANCHOR 60 0
error message file name 56 0
Storage for kcrr state ob 56 0
kspload:comment 56 0
X$KSFQP ANCHOR 56 0
kks sga 48 0
SCHEDULING POLICY TABLE 48 0
ksfd SGA state 48 0
KTCN global operators 48 0
Background process state 48 0
kelr other metrics table 48 0
sim state object 48 0
PRESENTATION ENTRY 48 0
pspool_kfsg 44 0
KTC txn rsrc cnt 44 0
KTI freelists 44 0
KTCTSNL freelists 44 0
ksfm state object 44 0
PX msg pool struct 40 0
dbktb: sga ctx 40 0
kscdnfyinitnext 40 0
kcrrorlerr.alo 40 0
KWQDL SGA Ddtc gen cleanu 40 0
KWQDL SGA Dtc gen cleanup 40 0
knstsg 40 0
kscdnfyinitprev 40 0
SAGE commit cache Allocat 40 0
kjxgrrr SGA 40 0
time manager context 40 0
event list array to post 36 0
sga dev dict 36 0
KGSKI sga 36 0
SUBPOOL NAME SUM(BYTES) MB
------------------------------ -------------------------- ---------- ----------
shared pool (1): Log Archive Trace PID Lis 36 0
L2 file global fob 36 0
qtree_kwqspqctx 32 0
qtree_kwqbsgn 32 0
Prefetch client count per 32 0
ksuloi: long op free list 32 0
ksdhng: cbufs 32 0
ksuloi: long op used list 32 0
parallel kcbibr 32 0
dbwriter coalesce struct 32 0
qtree_kwqbspse 32 0
kjxgrvr SGA 28 0
KGKP sga 28 0
kfgbsg 28 0
KTI pool states 28 0
plwppwp:PLW_STR_NEW_VAL_V 28 0
SGA structure for KWQDL s 24 0
AW SGA 24 0
KCB Table Scan 24 0
ksfd shared pool recovery 24 0
block_sizes_array 24 0
KSN WaitID 24 0
writes stopped lock conte 20 0
KRSF SGA 20 0
dbkea msgq 20 0
krvxmctx 20 0
writes stopped lock state 20 0
kspd run-time context 20 0
ksbxic sga 20 0
dbgefgHtInit-2 20 0
ktsj_init_sga task stat 20 0
SGA Obsolete Param Source 20 0
x$ksmfs table 16 0
kscdnfyinithead 16 0
ksuloi: garbage collectio 16 0
tablespace encryption mas 16 0
ptr to sessions under idl 16 0
plwppwp:PLW_STR_NEW_LEN_V 16 0
kodosgi kodos 16 0
generic process shared st 12 0
namrec_kfdsg 12 0
kfkid hrec 12 0
dbkea sga 12 0
kfkrec_kfdsg 12 0
KTC variable sga 12 0
KTU SGA 12 0
prirec_kfmdsg 12 0
plwspv:PLW_STR_NEW_VAL 12 0
L2 enabled array 12 0
buffers waiting for write 12 0
SGA structure for ADR lay 8 0
dbwr working sets kcbdbws 8 0
dbwr suspend/resume ptr a 8 0
kdlxdup swapp 8 0
dia* process descriptor 8 0
kfnvsga 8 0
KCFIS SGA 8 0
sga node map 8 0
KSRPC I/O Stat array ptr 8 0
diag descriptor 8 0
plwppwp:garbage handle 8 0
UNDO INFO 8 0
dispatcher service names 8 0
parallel kcbibr dbwr bitv 8 0
KTRSGA 8 0
service names array 8 0
dbwr suspend/resume array 8 0
kga sga 4 0
osp pool handles 4 0
ksunfy: in-use PSO Numa A 4 0
Wait History Array 4 0
DBWR array of pointers to 4 0
sim kghx free lists 4 0
ksunfy: Reserved PSO Numa 4 0
kfiosg 4 0
listener addresses 4 0
IO_SHARES_KGSKCAP 4 0
kscdnfyglobalflags 4 0
kolbsgi: KOLB's SGA initi 4 0
kscdnfyinitflags 4 0
RVWR post requested array 4 0
LGWR post requested array 4 0
dbwr message active flag 4 0
kcbbl2df L2 disabling fla 4 0
KEWS sesstat seg tbl 4 0
kohsg 4 0
kolfsgi: KOLF's SGA initi 4 0
874 rows selected.
SQL>
Notice there are MANY more SGA memory areas in 11.2.0.3 than there were in 9.2.0.4, yet even with that, the automatic memory management has improved dramatically as I have yet to see an ORA-04031 error raise its ugly head when database memory resources are properly sized for the expected number of connections and anticipated workload. Notice also that subpool 0 is reported and it’s listed as free memory; this pool is used to allocate free memory from the general pool to any subpool that may need it. ORA-04031 errors won’t occur until there is no free memory available based upon the sga_max_size setting and the database cannot allocate needed resources based on connection count and workload volume.
Automatic SGA management in recent releases of Oracle is far superior to that originally implemented in versions prior to 9.2 due, in no small part, to the subdivision of the shared areas into discrete subpools. Latch contention is at a minimum and automatic subpool management frees the DBA to address more important tasks. Yes, ORA-04031 errors can still occur but Oracle reports not only that the allocation failed but also which subpool is affected. The general solution for correcting those errors remains the same (increasing the value for the sga_max_size parameter followed by a restart) but the errors should be fewer and farther between, if they occur at all.
