February 14, 2002 - 2pm Central time zone
Reviewer: Chetan
Appreciate the patience taken by Tom to answer the question
February 15, 2002 - 10am Central time zone
Reviewer: A reader
Very clear answer
November 24, 2002 - 11am Central time zone
Reviewer: Frank from Canada
memory in use & session heap
March 12, 2003 - 12am Central time zone
Reviewer: Yakgna from India
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 ...
Followup March 12, 2003 - 7am Central time zone:
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
April 4, 2003 - 3pm Central time zone
Reviewer: Panjwani from Canada
The best article I have seen so far on SGA components
Total RAM requirements for an oracle install
April 7, 2003 - 10am Central time zone
Reviewer: Moh'd Saeed
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
Followup April 7, 2003 - 2pm Central time zone:
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.
October 13, 2004 - 11am Central time zone
Reviewer: AK from nj,usa
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 ?
Followup October 13, 2004 - 12pm Central time zone:
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")
February 9, 2005 - 6am Central time zone
Reviewer: SidHu from India
it is very good reply with all the complete information
Does shared_pool get remaining of variable pool
May 18, 2005 - 1pm Central time zone
Reviewer: Peter Tran from Houston, TX USA
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
Followup May 18, 2005 - 1pm Central time zone:
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?
May 18, 2005 - 1pm Central time zone
Reviewer: Peter Tran from Houston, TX USA
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
Followup May 18, 2005 - 3pm Central time zone:
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
June 20, 2005 - 12am Central time zone
Reviewer: reader from india
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
Followup June 20, 2005 - 9am Central time zone:
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?
June 28, 2005 - 3pm Central time zone
Reviewer: VA from New Jersey, USA
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
Followup June 28, 2005 - 4pm Central time zone:
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
June 29, 2005 - 2am Central time zone
Reviewer: Deepak from India
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...
Followup June 29, 2005 - 8am Central time zone:
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
June 29, 2005 - 1pm Central time zone
Reviewer: Deepak from India
Hi Tom,
The actual large pool size is 15728640 in our case. I wonder how it shows up 4 gigs.
Followup June 29, 2005 - 1pm Central time zone:
it would be "a product issue" with 9.0
What is buffer reads/Rights ans page read/writes?differences
July 8, 2005 - 6am Central time zone
Reviewer: Venkatesh from INDIA
Followup July 8, 2005 - 7am Central time zone:
does not compute.
December 31, 2005 - 10am Central time zone
Reviewer: A reader
October 24, 2006 - 5pm Central time zone
Reviewer: ocp_kir from india
wonderful explanation of SGA I had ever found anywhere.
October 24, 2006 - 7pm Central time zone
Reviewer: Mzahid from Vancouver BC, Canada
Confusing computation for SGA
August 1, 2007 - 7pm Central time zone
Reviewer: Raesch from CO USA
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?
Followup August 5, 2007 - 10am Central time zone:
instead of summing, get the details and see what number looks "wrong"
The v$sgastat contents
August 7, 2007 - 6pm Central time zone
Reviewer: Raesch from CO, USA
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.
Followup August 10, 2007 - 3pm Central time zone:
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
August 13, 2007 - 1pm Central time zone
Reviewer: Raesch from CO, USA
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?
Followup August 15, 2007 - 11am Central time zone:
yes
help me too slow clients
August 23, 2007 - 8am Central time zone
Reviewer: monjed from jordan
<code>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
Followup August 23, 2007 - 12pm Central time zone:
speechless.
that is all I can say.
|