Improvements in Automatic SGA Management from 9.2 Through 11.2

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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles