Chetan, February 14, 2002 - 2:27 pm UTC
Appreciate the patience taken by Tom to answer the question
A reader, February 15, 2002 - 10:16 am UTC
Very clear answer
Frank, November 24, 2002 - 11:06 am UTC
memory in use & session heap
Yakgna, March 12, 2003 - 12:06 am UTC
Tom,
how did you get that 'memory in use' under java pool and 'session heap' under large pool. i miss that in my output.. please let me know if i missed something ...how are they calculated from if they aren't there in the v$sgastat.
ge$kumar@devadw.world>select pool, name , bytes from v$sgastat order by pool;
POOL NAME BYTES
=========== ========================== ==========
java pool free memory 33554432
*********** ----------
sum 33554432
large pool free memory 8388608
*********** ----------
sum 8388608
shared pool enqueue 171860
KGK heap 3756
KQR S PO 57600
KQR M PO 264212
.
.
.
log_buffer 656384
*********** ----------
sum 34664348
Thanks ...
March 12, 2003 - 7:48 am UTC
you cannot see that which is not there.
run something "java". you have all free memory, no one is using it.
run something using "shared server". you have all free memory in the large pool.
SGA - Fixed & Variable
Panjwani, April 04, 2003 - 3:56 pm UTC
The best article I have seen so far on SGA components
Total RAM requirements for an oracle install
Moh'd Saeed, April 07, 2003 - 10:43 am UTC
Hi Tom:
If I am trying to calculate the total RAM requirements for running an Oracle instance what else besides the size of the SGA and the b/g processes should I account for ?
Am I correct that the free physical RAM available should at the very least accomodate the SGA?
How do I accurately compute the RAM requirements of the b/g process and the user sessions?
Does the equation change if I am using the dedicated server mode connection or shared server mode ?
Thanks for helping me clarify this issue.
MSaeed
April 07, 2003 - 2:13 pm UTC
Well, the OS install guide gives you the bare minimum required on your OS. Start there.
Yes, the SGA should fit in real memory -- and there better be stuff left over. I don't like ROT (rules of thumb) but... in dedicated server mode, 50% of real memory is a starting MAX for the SGA, in shared server, 80%.
You need to figure out how many ram YOUR application will consume per connection. Use only SQL? No PLSQL - you'll use less server memory. Frequently load 10,000 rows into a PLSQL index by table? You'll need more. Do you sort tons of data and keep a large sort_area_retained_size? etc etc etc (eg: your mileage may vary). ROT says say 1m of ram per connected session on "average".
In shared server -- the UGA is in the SGA -- so your SGA will be larger due to this (you'll have a big large pool for hold the UGA). In dedicated server -- the UGA is in the PGA, so your SGA is smaller but your dynamically allocated process memory is larger (hence the 50/80 ROT above).
In short -- testing and benchmarking will get you right. Everything else is a huge "swag".
Number don't seem to add up.
AK, October 13, 2004 - 11:31 am UTC
On 9.2.0.5.0, I ran the following :
select sum (value)/(1024*1024) from v$parameter where name in
(
'large_pool_size',
'java_pool_size',
'shared_pool_size'
)
SUM(VALUE)/(1024*1024)
----------------------
192
SQL> show sga;
Total System Global Area 542077376 bytes
Fixed Size 737728 bytes
Variable Size 285212672 bytes
Database Buffers 251658240 bytes
Redo Buffers 4468736 bytes
The Variable size here is approx. 272 MB.
Why the discrepancy. What am I missing ?
October 13, 2004 - 12:17 pm UTC
80 meg -- well, each of the pools in 9i are rounded up to granules (4/8/16 meg boundaries). so, each of the pools could be off by 15.9 meg. ther is the log buffer, there are data structures (eg: max files -> array of structures, max this /that or the other thing -> array of structures) in the variable size.
The variable pool is a mish mash of everything -- the three parameters you choose are the biggest contributors, but not by any means the only ones (and remember the granules)
(see the original answer, it actually goes into why the shared pool isn't going to be limited by "the shared_pool")
SidHu, February 09, 2005 - 6:09 am UTC
it is very good reply with all the complete information
Does shared_pool get remaining of variable pool
Peter Tran, May 18, 2005 - 1:23 pm UTC
Hi Tom,
We have the following in the init.ora:
db_block_size 16384
sga_max_size 42955978792
db_cache_size 32212254720
large_pool_size 1577058304
java_pool_size 16777216
shared_pool_reserved_size 104857600
shared_pool_size 536870912
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 32,212,254,720
Fixed Size 788,520
Redo Buffers 5,517,312
Variable Size 10,737,418,240
----------------
sum 42,955,978,792
I have a DBA telling me that the shared pool is:
shared pool = sga_max_size - db_cache_size - large_pool_size - java_pool_size.
Essentially, the shared_pool can be bigger than the 512M indicated in the init.ora file.
Is this true? I don't think so. The shared_pool_size should not be bigger than the size specified in the init.ora parameter. At least, that's my understanding.
Thanks,
-Peter
May 18, 2005 - 1:27 pm UTC
In 9i and before -- yes.
In 10g and later -- no.
Here is excerpt from the upcoming 2nd edition of Expert One on One:
....
One last comment about the shared pool and the parameter, SHARED_POOL_SIZE. In Oracle 9i and before, there is no direct relationship between the outcome of the query:
ops$tkyte@ORA9IR2> select sum(bytes) from v$sgastat where pool = 'shared pool';
SUM(BYTES)
----------
100663296
and the SHARED_POOL_SIZE parameter:
ops$tkyte@ORA9IR2> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 83886080
other than the fact that the SUM(BYTES) FROM V$SGASTAT will always be larger than the SHARED_POOL_SIZE. The shared pool holds many other structures that are outside the scope of the corresponding parameter. The SHARED_POOL_SIZE is typically the largest contributor to the shared pool as reported by the SUM(BYTES), but it is not the only contributor. For example, the parameter, CONTROL_FILES, contributes 264 bytes per file to the 'miscellaneous' section of the shared pool. It is unfortunate that the 'shared pool' in V$SGASTAT and the parameter SHARED_POOL_SIZE are named as they are, since the parameter contributes to the size of the shared pool, but it is not the only contributor.
In Oracle 10g and above however, you should see a direct one to one correspondence between the two, assuming you are using manual SGA memory management (that is, you have set the shared_pool_size parameter yourself):
ops$tkyte@ORA10G> select sum(bytes)/1024/1024 mbytes
2 from v$sgastat where pool = 'shared pool';
MBYTES
----------
128
ops$tkyte@ORA10G> show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 128M
This is a relatively important change as you go from Oracle 9i and before to 10g. In Oracle 10g, the shared_pool_size parameter controls the size of the shared pool, whereas in Oracle 9i and before, it was just the largest contribution to the shared pool. You would want to review your 9i and before actual shared pool size (based on v$sgastat) and use that figure to set your shared_pool_size parameter in Oracle 10g and above. The various other components that used to add onto the size of the shared pool now expect that memory to have been allocated for them by you.
....
Ok, so my shared_pool is really that large?
Peter Tran, May 18, 2005 - 1:52 pm UTC
Hi Tom,
Thanks for the amazingly quick response.
You wrote, "In Oracle 10g, the shared_pool_size parameter controls the size of the shared pool, whereas in Oracle 9i and before, it was just the largest contribution to the shared pool."
From my understanding:
variable pool = shared_pool + large_pool + java_pool
10,737,418,240 = shared_pool + 1,577,058,304 + 16,777,216
That means my shared_pool = 9,143,582,720
My init.ora setting has the shared_pool set at 512M, so what's really taking up most of remaining memory? What's using a majority of the 8.6G of memory?
Is having a 8.6G auto-allocated to the shared_pool in 9i excessively large? Would this memory be better allocated to the buffer cache?
Thanks,
-Peter
May 18, 2005 - 3:33 pm UTC
what is you sga max size.
the variable will report the difference between the CURRENT and MAX size of your sga as well.
so that 8.6gig could easily just be "extra stuff you could grow the actual variable components to be over time but are not using at all yet"
sga java and large pool ZERO
reader, June 20, 2005 - 12:54 am UTC
if Yakgna from India will make her JAVA_POOL_SIZE = 0, and LARGE_POOL_SIZE = 0 and allocating all the released memory to DB_BUFFER_CACHE and SHARED_POOL.
I am assuming it will increase the performance of her database. am i right, or this configuration is not posslible.
regards
June 20, 2005 - 9:53 am UTC
the possible answers to " it will increase the performance of her database" are:
a) yes it will be beneficial to performance, maybe they were starved for memory and truly never use these components so releasing them for something else would be beneficial.
b) no - it won't affect performance at all, maybe they have gobs of memory and changing this won't change a thing
c) it will be a NEGATIVE thing performance wise, because they use these pools at other times during the day, just not right now.
Can you help me understand?
VA, June 28, 2005 - 3:49 pm UTC
Oracle 9.2.0.6
Here are my relevant init.ora parameters
db_block_size = 8192
db_cache_size = 100M
log_buffer = 5242880
shared_pool_size = 50M
java_pool_size = 35000000
workarea_size_policy = AUTO
pga_aggregate_target = 100M
Here is what I get from v$sga/v$sgastat
SGA Size
NAME BYTES
-------------------- --------------
Fixed Size 731,672
Variable Size 201,326,592
Database Buffers 117,440,512
Redo Buffers 5,529,600
--------------
sum 325,028,376
I can see how db buffers is close to 100M which is what I specified.
Why is variable size 200M? Here is v$sgastat
SGA Usage
POOL NAME BYTES
-------------------- ------------------------------ --------------
<unnamed pool> buffer_cache 117,440,512
log_buffer 5,506,048
fixed_sga 731,672
******************** --------------
sum 123,678,232
java pool free memory 45,596,672
memory in use 4,734,976
******************** --------------
sum 50,331,648
large pool free memory 50,208,768
PX msg pool 122,880
******************** --------------
sum 50,331,648
shared pool miscellaneous 37,149,480
sql area 25,822,704
library cache 14,481,120
XDB Schema Cac 5,159,064
free memory 4,058,112
KQR L PO 3,286,680
dictionary cache 3,229,952
KGLS heap 1,794,384
PL/SQL DIANA 1,454,680
KQR M PO 1,235,632
KQR X PO 765,544
PL/SQL MPCODE 559,992
session param values 302,016
joxlod: in ehe 275,344
PL/SQL PPCODE 265,776
errors 160,184
sim memory hea 157,744
KQR M SO 125,624
subheap 102,312
PX subheap 90,776
KQR L SO 65,544
KGK heap 33,272
KQR S SO 20,480
trigger defini 15,928
PL/SQL SOURCE 14,504
table definiti 13,312
parameters 12,728
trigger source 3,784
PLS non-lib hp 3,328
trigger inform 2,816
fixed allocation callback 480
******************** --------------
sum 100,663,296
--------------
sum 325,004,824
Questions
1. I set java_pool_size to 35M, so why is it taking up 50M above?
2. I guess large_pool_size=50M is the default?
3. Similar to (1), I set shared_pool_size to 50M, why is it taking up 100M?
4. I am using dedicated server mode and no Java stuff, so I guess I can set java_pool_size=0, large_pool_size=0 and get 100MB of memory back from the SGA back to the OS free RAM, right?
Thanks a lot
June 28, 2005 - 4:01 pm UTC
1) the java pool is 48m, not 50m
ops$tkyte@ORA10G> select 50331648/1024/1024 from dual;
50331648/1024/1024
------------------
48
units of allocation in the SGA with 9i and up are done in granules, I'll guess your granule size is 16m -- select * from v$sga_dynamic_components will confirm that. You asked for 35 meg, but if the units are 16meg, that would be 16*3 = 48.
2) documentation says.... 0 if dbwr_io_slaves is not set and parallel query is not enabled, otherwise derived from the parallel parameters....
3) the shared pool is not 100m, it is 96m. The shared pool in 9i = (YOUR_REQUESTED_SIZE) + overheads for everything else that comes out of there.
here, you can grab this while it is there (note: if you are reading this in the future and this link doesn't work please don't ask "where is it", it means it is gone)
http://oracle.apress.com//betabooks/betabook.html?betabook_id=30
that is the beta of my memory chapter -- on memory use by Oracle. 10g will change this a bit and that is documented there.
4) java sure, large pool -- maybe not, not if you are using parallel.
that pga aggregate target looks small, but read that chapter so you'll understand how it all fits together.
Wrong Large Pool Size
Deepak, June 29, 2005 - 2:43 am UTC
Hi Tom,
I am using Oracle 9.0.1.1.1 on W2K Box.
I executed the follwing queries...
SQL> select name,value
2 from v$system_parameter
3 where name='large_pool_size';
NAME VALUE
-------------------- -------------------
large_pool_size 15728640
1 row selected.
SQL> compute sum of bytes on pool
SQL> break on pool skip 1
SQL> select pool, name, bytes
2 from v$sgastat
3 order by pool, name;
POOL NAME BYTES
----------- -------------------- -------------------
java pool free memory 3641344
memory in use 4747264
*********** -------------------
sum 8388608
large pool free memory 4294269956
session heap 697340
*********** -------------------
sum 4294967296
shared pool 1M buffer 1049088
FileIdentificatonBlo 323292
ck
FileOpenBlock 1349112
KGK heap 3756
KGLS heap 1008724
KSXR receive buffers 1060000
PL/SQL DIANA 796560
PL/SQL MPCODE 166792
PL/SQL PPCODE 5988
POOL NAME BYTES
----------- -------------------- -------------------
shared pool PLS non-lib hp 2068
PX subheap 3168
VIRTUAL CIRCUITS 522200
character set object 317180
dictionary cache 456096
enqueue 321472
event statistics per 2673300
sess
fixed allocation cal 60
lback
free memory 45266796
joxlod: in ehe 309928
joxs heap init 4220
library cache 2859396
message pool freeque 772672
ue
miscellaneous 4846092
parameters 3196
POOL NAME BYTES
----------- -------------------- -------------------
shared pool sessions 779880
sql area 1848864
table definiti 864
transaction 356828
trigger defini 168
trigger inform 1048
trigger source 56
*********** -------------------
sum 67108864
db_block_buffers 33554432
fixed_sga 282616
log_buffer 524288
*********** -------------------
sum 34361336
38 rows selected.
I just want to draw your attention to the large pool related results in the above listings.
large_pool_size 15728640
large pool free memory 4294269956
session heap 697340
*********** -------------------
sum 4294967296
Why there is such a difference in large pool size?
Please help...
June 29, 2005 - 8:55 am UTC
as I doubt you have a 4gig large pool, it would look like an issue in the v$ number doesn't it.
given that windows cannot even address that amount of memory.
actual size
Deepak, June 29, 2005 - 1:22 pm UTC
Hi Tom,
The actual large pool size is 15728640 in our case. I wonder how it shows up 4 gigs.
June 29, 2005 - 1:31 pm UTC
it would be "a product issue" with 9.0
What is buffer reads/Rights ans page read/writes?differences
Venkatesh, July 08, 2005 - 6:37 am UTC
July 08, 2005 - 7:56 am UTC
does not compute.
A reader, December 31, 2005 - 10:59 am UTC
ocp_kir, October 24, 2006 - 5:23 pm UTC
wonderful explanation of SGA I had ever found anywhere.
Mzahid, October 24, 2006 - 7:25 pm UTC
Confusing computation for SGA
Raesch, August 01, 2007 - 7:52 pm UTC
Tom, read through some of those great explanations.
So, thought, I'll bring my problem to you.
I have a 9.2.0.5 on HPUX PARISC 12CPU, 16GB SuperDome.
We've a 8TB db, with 250 - 400 Transactions per second.
SGA regions Size in Bytes
------------------------------ ----------------
Database Buffers 7,482,638,336
Fixed Size 751,912
Redo Buffers 10,760,192
Variable Size 2,919,235,584
----------------
sum 10,413,386,024
The various components are:
db_block_size 16,384
sga_max_size 10,413,386,024
db_cache_size 5,251,268,608
db_keep_cache_size 134,217,728
db_recycle_cache_size 2,097,152,000
java_pool_size 16,777,216
large_pool_size 117,440,512
log_buffer 10,487,808
shared_pool_size 2,147,483,648
pga_aggregate_target 2,013,265,920
I reallocated about 800MB from "Variable Size" to the Buffers to get the size above.
When I execute the query:
select sum(bytes)/1024/1024/1024 from v$sgastat where pool = 'shared pool';
My result shows : 17,179,869,186.5313
What is the result that I'm seeing?
August 05, 2007 - 10:16 am UTC
instead of summing, get the details and see what number looks "wrong"
The v$sgastat contents
Raesch, August 07, 2007 - 6:38 pm UTC
Tom,
Please find the detail at that time from the sgastat.
POOL NAME BYTES
NULL fixed_sga 751,912
NULL buffer_cache 7,482,638,336
NULL log_buffer 10,752,000
shared pool errors 558,152
shared pool enqueue 2,834,576
shared pool KGK heap 320,600
shared pool KQR L PO 2,916,880
shared pool KQR L SO 93,184
shared pool KQR M PO 9,131,208
shared pool KQR M SO 185,352
shared pool KQR S SO 17,800
shared pool KQR X PO 360,672
shared pool db_files 5,302,600
shared pool sessions 797,680
shared pool sql area 331,251,624
shared pool 1M buffer 1,049,600
shared pool KGLS heap 10,330,336
shared pool OUTLNCACHE 6,512
shared pool PX subheap 354,728
shared pool db_handles 928,000
shared pool parameters 130,384
shared pool PX msg pool 8,261,568
shared pool free memory 1,875,976,224
shared pool PL/SQL DIANA 4,177,952
shared pool FileOpenBlock 15,431,456
shared pool PL/SQL MPCODE 7,369,240
shared pool PL/SQL PPCODE 83,656
shared pool library cache 87,195,632
shared pool miscellaneous 68,919,656
shared pool pl/sql source 1,424
shared pool KGSK scheduler 665,048
shared pool KGSKI schedule 41,872
shared pool MTTR advisory 205,968
shared pool PLS non-lib hp 2,088
shared pool joxs heap init 1,272
shared pool partitioning d 2,815,064
shared pool sim memory hea 3,575,448
shared pool table definiti 39,480
shared pool temporary tabl 456
shared pool transaction co 1.84E+19
shared pool trigger defini 54,072
shared pool trigger inform 3,728
shared pool trigger source 23,016
shared pool type object de 104,208
shared pool Checkpoint queue 230,411,520
shared pool dictionary cache 3,229,952
shared pool sim trace entries 884,736
shared pool KSXR receive buffers 1,034,000
shared pool session param values 1,388,832
shared pool db_block_hash_buckets 13,569,040
shared pool FileIdentificatonBlock 15,431,592
shared pool file # translation table 480,024
shared pool event statistics per sess 9,961,560
shared pool fixed allocation callback 1,008
large pool free memory 117,440,512
java pool free memory 16,777,216
However, after bouncing the database the problem has disappeared and the count comes to 2592MB while the Variable size is 2784MB. The observation is that transactions are zipping by, meaning fast, but long reporting queries are failing.
August 10, 2007 - 3:04 pm UTC
well, I asked only for "what appears wrong" - but you just clued me on on something "reporting queries"
are you by chance using parallel query - there are cases where that can muck up the v$ numbers (roll them over) leading to some (harmless) results like that.
Extensive use of PQ
Raesch, August 13, 2007 - 1:26 pm UTC
Tom,
Yes, your're right. We are using PQs extensively. On this 12 CPU box, we're setting the MAX Parallel Servers to 24.
The DB is a Hybrid, a combination of file loading and querying by reporting teams and helpdesk reps for statuses.
We did bring down the object parallels, but are relying on controlling the usage through resource groups and specifying thq Parallels in the queries.
Overall the performance seems to have degrated extesively and the values in the v$sgastat perplexed us.
Can we then surmise that the skewed values in the sgastat was because of the PQs messing the numbers?
August 15, 2007 - 11:10 am UTC
yes
help me too slow clients
monjed, August 23, 2007 - 8:37 am UTC
Hi TOME please i need your help
i have system in my work and its too slow on the clients
the server
CPU XEON 3000 x86 family 15 model 4
Mainbord
Ram 2 Gega
Eathernet 10/100/1000 connected to 10/100 switch
lan speed 100
windows 2000 server
oracle 9i database
app server 10g
number of user on the system maximum 25
and this is all the information
please Help me Tome
if you want any other in formation tell me
PPPPPllllllllleeeeeaaaaaasssssssseeeeeee ;-(
this is the user create command
--------------------------------------------------------------------
==================================================
CREATE TABLESPACE HMIS DATAFILE
'D:\ORACLE\ORADATA\ORA90\HMIS1.DBF'
SIZE 450M AUTOEXTEND ON NEXT 100M MAXSIZE 800M,
'D:\ORACLE\ORADATA\ORA90\HMIS2.DBF'
SIZE 440M AUTOEXTEND ON NEXT 100M MAXSIZE 800M
DEFAULT STORAGE (INITIAL 64K NEXT 2048K
MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 1);
==================================================
CREATE TABLESPACE HMS_IND DATAFILE
'D:\ORACLE\ORADATA\ORA90\HMS_IND1.DBF'
SIZE 350M AUTOEXTEND ON NEXT 100M MAXSIZE 800M,
'D:\ORACLE\ORADATA\ORA90\HMS_IND2.DBF'
SIZE 340M AUTOEXTEND ON NEXT 100M MAXSIZE 800M
DEFAULT STORAGE (INITIAL 64K NEXT 2048K
MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 1);
==================================================
conn system/manager
create user hms identified by ISMTCHC default tablespace hmis;
grant connect, resource, dba to hms ;
CONN HMS/ISMTCHC
-------------------------------------------------------------------------
SQL> select * from v$sgastat where pool='shared pool';
POOL NAME BYTES
----------- -------------------------- ----------
shared pool 1M buffer 1049088
shared pool Checkpoint queue 141152
shared pool DML lock 100408
shared pool FileIdentificatonBlock 323292
shared pool FileOpenBlock 695504
shared pool KGK heap 3756
shared pool KGLS heap 949024
shared pool KSXR pending messages que 226636
shared pool KSXR receive buffers 1060000
shared pool PL/SQL DIANA 504140
shared pool PL/SQL MPCODE 302192
POOL NAME BYTES
----------- -------------------------- ----------
shared pool PL/SQL PPCODE 6860
shared pool PLS non-lib hp 2068
shared pool VIRTUAL CIRCUITS 266120
shared pool character set object 324548
shared pool dictionary cache 3235220
shared pool enqueue 171860
shared pool errors 2416
shared pool event statistics per sess 1356600
shared pool fixed allocation callback 100
shared pool free memory 4092084
shared pool joxlod: in ehe 298568
POOL NAME BYTES
----------- -------------------------- ----------
shared pool joxs heap init 4220
shared pool ksm_file2sga region 148652
shared pool library cache 12160228
shared pool message pool freequeue 772672
shared pool miscellaneous 4014800
shared pool processes 127800
shared pool sessions 395760
shared pool sql area 17404824
shared pool table definiti 224
shared pool temporary tabl 7092
shared pool transaction 182376
POOL NAME BYTES
----------- -------------------------- ----------
shared pool trigger defini 168
shared pool trigger inform 1140
shared pool trigger source 56
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SQL> show sga;
Total System Global Area 361525500 bytes
Fixed Size 282876 bytes
Variable Size 92274688 bytes
Database Buffers 268435456 bytes
Redo Buffers 532480 bytes
SQL>
SQL>
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SQL> show parameter shared_pool_size;
NAME TYPE VALUE
------------------------------------ ----------- ---------
shared_pool_size big integer 50331648
SQL>
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SQL> show parameter shared;
NAME TYPE VALUE
------------------------------------ ----------- ---------
hi_shared_memory_address integer 0
max_shared_servers integer 20
shared_memory_address integer 0
shared_pool_reserved_size big integer 1677721
shared_pool_size big integer 50331648
shared_servers integer 1
shared_server_sessions integer 165
SQL>
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SQL> select FREE_SPACE,REQUEST_MISSES,
REQUEST_FAILURES from v$shared_pool_reserved;
(no row SELECTED)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
SQL> select name, bytes/1024/1024 "MB" from v$sgastat
where pool = 'shared pool' order by bytes desc;
NAME MB
-------------------------- ----------
sql area 18.8199081
library cache 10.3398705
free memory 5.37222672
miscellaneous 3.84454346
event statistics per sess 1.29375458
KSXR receive buffers 1.01089478
1M buffer 1.00048828
KGLS heap .91702652
message pool freequeue .736877441
FileOpenBlock .663284302
dictionary cache .621208191
NAME MB
-------------------------- ----------
PL/SQL DIANA .532772064
sessions .377426147
character set object .309513092
FileIdentificatonBlock .308315277
joxlod: in ehe .284736633
VIRTUAL CIRCUITS .253791809
PL/SQL MPCODE .244285583
KSXR pending messages que .216136932
transaction .173927307
enqueue .163898468
ksm_file2sga region .141765594
NAME MB
-------------------------- ----------
Checkpoint queue .134613037
processes .121879578
DML lock .095756531
temporary tabl .007530212
joxs heap init .004024506
KGK heap .003582001
PLS non-lib hp .001972198
errors .001796722
trigger inform .001186371
table definiti .000911713
fixed allocation callback .000095367
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SQL> select * from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 282876
Variable Size 92274688
Database Buffers 268435456
Redo Buffers 532480
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
SQL> show parameter cach
NAME TYPE VALUE
------------------------------------ ----------- ----------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string OFF
db_cache_size big integer 268435456
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
NAME TYPE VALUE
------------------------------------ ----------- ----------
optimizer_index_caching integer 0
session_cached_cursors integer 0
SQL>
August 23, 2007 - 12:22 pm UTC
speechless.
that is all I can say.
Variable SGA size
Arindam, September 17, 2010 - 12:11 pm UTC
Hi Tom,
I have very small but basic queries.
1) Why Variable SGA size is called to be "Variable"?
2) Why could not the database buffer and log buffer be counted in Variable size?
3) If I specify SGA_MAX_SIZE,SHARED_POOL_SIZE,DB_CACHE_SIZE and LOG_BUFFER only.
Now I think SGA_MAX_SIZE=SHARED_POOL_SIZE+DB_CACHE_SIZE+LOG_BUFFER approx.
I create the database.
Then as per the requirement I specify LARGE_POOL_SIZE and JAVA_POOL_SIZE later. Now should the size of other parameters shrink?
September 17, 2010 - 1:04 pm UTC
1) because it is not fixed - it is variable based on your init.ora parameters.
2) because we felt we should segregate them out - they were special enough to be "all by themselves"
3) there is sga_max_size - and there is sga_size. sga_max_size is what the SGA can grow to and could be much larger than your formula.
if you set the sga_size - and you set the shared_pool and db_cache_size, then shared pool and db cache size would be the LOWER bound for those pools.
eg: you can set sga_size = 1g, shared_pool_size = 256m, db_cache_size = 256m. The the shared pool and cache would not go below 256m - but could go above it. If you set the sga_max_size to 2g, then you could change the size of the sga up to 2g online.
Variable SGA size
Arindam, September 17, 2010 - 10:44 pm UTC
Hi Tom,
can you please elaborate #2. Also for #3 I hope it will be the same concept used for 9i.
September 20, 2010 - 1:33 pm UTC
what needs elaboration? We decided that we wanted to have them be called out separately, they were special enough to us to get their own names, their own nomenclature. They are reported separate and distinct from the variable size pools "just because"
and I don't see how SGA automatic memory management - new in 10g - can be the same concept as 9i?? It didn't exist in 9i, it is a new concept.
SGA components
A reader, September 30, 2010 - 2:24 pm UTC
Hi Tom,
I have small queries.
Why are Variable SGA components like shared pool,large pool and java pool are having "pool" word at the end of the name? why not cache?
Also why the log buffer are not named as cache?
Is there any significance or just names which are carried from earlier releases?
September 30, 2010 - 2:29 pm UTC
because they are a pool of memory - a pool of long term allocated memory.
They are not caching stuff in the manner of the buffer cache (which caches buffers in memory that are always stored permanently on disk). The hold data structures like a parsed SQL statement. If we need the space used by some parsed SQL statement - we don't flush it to disk and free it up, we just free it up. We are not caching a parsed SQL statement there - if we were - the parsed SQL statement would "live" somewhere else permanently.
It is just a shared pool of memory that all sessions have access to - to access data structures.
SGA components
A reader, September 30, 2010 - 2:33 pm UTC
Hi Tom,
Thanks,
So are you saying that Log buffer and Log cache are synonymous?
September 30, 2010 - 3:06 pm UTC
it is called the redo log buffer. It is a buffer - where we store (buffer) redo information before writing it out to disk.
We don't cache redo, we buffer it for a short while and then write it out.
SGA components
A reader, September 30, 2010 - 9:18 pm UTC
Hi Tom,
I can understand now why it is Redo buffer. But could you please explain why we use the term library cache and data dictionary cache inside shared pool?
October 01, 2010 - 5:49 am UTC
because they are shared memory structures that are caches (someone, a programmer, wrote the code to cache some library data - the area of memory they decided to cache it in came from a structure called the shared pool - a shared pool of memory that the programmers programming the database could allocate memory from)
I think you are getting way to far wrapped up in semantics here - they are what they are.
Unused memory counted in "Variable Size"
En, April 07, 2011 - 3:14 pm UTC
I'm hoping for a quick confirmation about something that I have not found
explicitly mentioned in the above. So while I'm providing specifics, they are only meant as an example.
The basics: Solaris 9, Oracle 9iR2 (64-bit) using DISM. (I'm the sysad, we've only got a part-time DBA at the moment, and his analysis is we need to up the buffer cache.)
So sga_max_size is set quite high (but reasonable given physical memory in the server) to allow room for growth, and this is reflected in the "Variable Size". The pools are individually set based on actual workload.
SQL> show sga
Total System Global Area 7519138384 bytes
Fixed Size 741968 bytes
Variable Size 6442450944 bytes
Database Buffers 1073741824 bytes
Redo Buffers 2203648 bytes
How do I figure out how much of that Variable Size is unused, so I can grow my buffer cache and those pools that 9i can dynamically adjust?
Using the query you provided above (and leaving out individual line items):
compute sum of bytes on pool
SQL> break on pool skip 1
SQL> select pool, name, bytes
2 from v$sgastat order by pool, name;
java pool free memory 33554432
*********** ----------
sum 33554432
...
large pool free memory 134217728
*********** ----------
sum 134217728
...
shared pool free memory 493617832
*********** ----------
sum 637534208
...
buffer_cache 1073741824
fixed_sga 741968
log_buffer 2180096
*********** ----------
sum 1076663888and totaling up the numbers on each "sum" line, I get 1,881,970,256 bytes. The difference between this total and the Variable Size is about 4.5GB, which I am expecting to be allocated but unused SGA memory.
Q1. Is my conclusion that the difference between Variable Size and the sum of bytes in v$sgastat is available SGA memory correct?
Q2. Is there another, or a better way to find out how much room for growth there is in the SGA?
Thanks!
April 12, 2011 - 1:26 pm UTC
Re: Unused memory counted in "Variable Size"
En, May 12, 2011 - 4:43 pm UTC
I do find that interesting, and am kicking myself for not finding it on my own. Thanks much!
raid data
sandeep, June 24, 2011 - 11:55 am UTC
hi tom,
its a interview question ,how one should know if there are number of harddisk exist in raid ,in which disk my data exists like tablespace xyz;
June 24, 2011 - 1:07 pm UTC
if I was asked:
... how one should know if there are number of
harddisk exist in raid ...
I would answer "go ask the storage guys, unless it is in ASM managed storage, I didn't take care of it"
select * from dba_data_files
for the second one.