When we SELECT * from V$SGA (or show sga), we get the values of Fixed Size, Variable Size, Database Buffers and Redo Buffers. For e.g,
SQL>SELECT * FROM v$sga;
SQL>SELECT * FROM v$sga;
NAME VALUE
----------------------------- ----------
Fixed Size 2070096
Variable Size 234883504
Database Buffers 687865856
Redo Buffers 6316032
While the "Database Buffers" is a product of db_cache_size and db_block_size or the "Redo Buffer" is the value of log_buffer in INIT.ora, What is this fixed and variable size? How do wecompute Fixed Size and the Variable Size of a SGA? What are they indicative of? Here is theanswer for that.
Fixed SGArelease to release. It is “compiled” into the database. The fixed SGA contains a set of
variables that point to the other components of the SGA and variables that contain the
values of various parameters like db_block_size and so on (constants basically for the currently mounted database).The size of the fixed SGA have no control and it is generally very small. Think of this area as a “bootstrap” section of the SGA, something Oracle uses internally to find the other bits and pieces of the SGA.
The size of the fixed portion is constant for a release and a platform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters The fixed size component is out of your control. It varies from release to release and platform to platform. Thus It contains a collection of objects that point to other areas inside the rest of the SGA -- think of it as the "road map" to the rest of the SGA.
Variable portion
The variable portion is called variable because its size (measured in bytes) can be changed.For consideration of the variable size, it is useful to look at v$sgastat
SQL>compute sum of bytes on pool SQL>break on pool skip 1 SQL>set pages 200 SQL>set pause on SQL>select pool, name, bytes from v$sgastat order by pool, name; POOL NAME BYTES ------------ ----------------------------- ---------- java pool free memory 4194304 ************ ---------- sum 4194304 large pool PX msg pool 1076000 free memory 3118304 ************ ---------- sum 4194304 shared pool 1:kngisga 32088 AQ Propagation Scheduling 16000 ASH buffers 16252928 ASM file 19200 ASM map operations 3616 ASM rollback operations 2592 AW SGA 40 AWR Table Info (KEW layer 872 Active Session History - 1528 ArchLog Dest Array 7656 Array of cached attr 400 Auto tune undo info 96 BRANCH TABLE SEGMENTED AR 194016 Background process state 48 Banner Storage 2048 CCursor 13817392 CGS system incarn array 24864 Checkpoint queue 821248 Cleanup state objects 384 Client ID trace setting h 536 Client ID trace settings 3872 Closed Thread SCN Bitvec 8448 Cursor Stats 1152280 DBWR event stats array 192 DISPATCHER CONFIG ENTRY 88 DISPATCHER CONFIG TABLE 2048 DISPATCHERS INFO 2400 DML lock 195872 ENQUEUE STATS 11928 File Space Usage 11336 FileIdentificatonBlock 449664 FileOpenBlock 1600704 Global Context Heap descr 400 HTTP fixed headers 72 Heap0: KGL 4569328 JSX SGA 112 KCB Table Scan 40 KCB Table Scan Bitmap 248 KCB Table Scan Buffer 3981120 KCB buffer wait statistic 3352 KCB incremental ckpt entr 512 KCB private handles 1800 KCB undo bitvec 4096 KCB where statistics arra 25888 KCK type array 936 KESTB existence bitvec 16384 KESTB existence bitvec se 128 .......................... session idle latches 1280 sessions 932968 set_descriptor_array 43776 sga dev dict 56 sga listelement 2048 sga node map 16 sim cache nbufs 640 sim cache sizes 640 sim kghx free lists 8 sim lru segments 5120 sim segment hits 5120 sim segment num bufs 2560 sim state object 40 sim trace buf 5144 sim trace buf context 200 sim_knlasg 1280 simulator hash buckets 263168 simulator hash latch 20480 simulator latch/bucket st 11264 slave class sga anchor 88 sort segment handle 3872 spfile callback table: ks 448 spfile cleanup structure 16760 sql area 57846312 sql area:KOKA 44528 sql area:PLSQL 1687768 stat hash values 1456 state objects 5680 sys event stats 198688 sys event stats for Other 198688 system default language h 568 table definiti 27264 temp lob duration state o 3296 temporary foreign ref 2848 temporary table lock 3744 threshold ale 12192 time manager context 40 trace buf hdr xtend 35088 trace buffer 655360 trace buffer header array 35088 trace events array 68000 trace_knlasg 504 transaction 417296 trigger condition node 72 trigger defini 33936 trigger inform 4224 trigger source 30624 txncallback 54208 type object de 789536 where to latch num map 7304 work area tab 270144 writes stopped lock conte 24 writes stopped lock state 24 x$ksmfs table 24 x$rule_set 17608 xscalc 4000 xslongops 2592 xsoqmehift 2848 xsoqojhift 4128 xsoqophift 2848 xsoqsehift 3104 xssinfo 5952 ************ ---------- sum 226497208 buffer_cache 687865856 fixed_sga 2070096 log_buffer 6316032 ************ ---------- sum 696251984 621 rows selected. SQL>
So, in general -- the fixed size is computed for you when Oracle is compiled. You can do nothing to affect it. The variable size is MOST affected by java_pool_size + large_pool_size + shared_pool_size but other parameters will contribute to it (eg: every control_file will consume 256 bytes of variable size memory. If you have 4 control files, the SGA will have 1024 bytes set aside for them) in a small way.
Great sir
ReplyDelete