----- Original Message -----
Sent: Thursday, January 02, 2003 9:45
AM
Subject: RE: Free Shared pool
memory
Tim,
I am pretty sure you are aware of Metalink note 100666.1 where
it says that the free_memory value is unreliable if shared_pool_reserved_size
is a non-zero value. Bug# 370903 .
BTW This is what it is on 9202 ...
oraclei@elara-NCS1>
sys
SQL*Plus: Release 9.2.0.2.0 - Production on Thu Jan 2 11:22:09
2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected.
SQL> show sga
Total System Global Area 1244448032 bytes
Fixed
Size
742688 bytes
Variable
Size
973078528 bytes
Database
Buffers 268435456
bytes
Redo
Buffers
2191360 bytes
SQL> SELECT SUM(bytes)/1024/1024 FROM
v$sgastat WHERE pool = 'shared pool';
SUM(BYTES)/1024/1024
--------------------
848
SQL> SELECT SUM(bytes) FROM v$sgastat WHERE pool = 'shared
pool' ;
SUM(BYTES)
----------
889192448
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 104857600
shared_pool_size
big integer 771751936
shared_server_sessions
integer 0
shared_servers
integer 0
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release
9.2.0.2.0 - 64bit Production
With the Partitioning,
Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.2.0 - Production
______________________________________________________
Rajendra Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any
opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion
is an art!
-----Original Message-----
From:
Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 02, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Free Shared pool memory
I hadn't heard the historic explanation before,
so I'll pass on that.
As far as the 16MB is concerned - I believe
the free memory includes any free space
left in
the shared_pool_reserved_size.
Since the shared_pool_reserved_size defaults
to 5% of the shared_pool_size (I think) it isn't
necessarily a surprise that you have 16MB
of
free memory when your shared_pool size if
320MB.
(On the other hand, is the reserved
size supposed to
be extracted from the main
pool, or additional too the
main pool)
The latching thing is always good for a cop-out.
I suspect that v$sgastat would become a major
bottle neck if it were always latched and updated
in real time. So it seems very likely that it
would
always be wrong.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____England______January 21/23
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To:
Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 02 January 2003 15:13
Sorry for being so vague, but sometimes I can't help
it...
It was my understanding in the Oracle7 days that the name of
the
statistic "free memory" was actually a verb and a
noun (i.e. as in
"free Nelson Mandela" or "free
Willy"), and the number shown alongside
this statistic
was the cumulative number of bytes freed in the Shared
Pool. In other words, every time "N" bytes were freed from the
Shared
Pool, then the statistic was incremented by
"N". At least, this
explanation would have
accounted for the absurdly huge numbers seen in
the
V$SGASTAT view for this statistic in those versions and the
unreliability in attempting to add the numbers seen in V$SGASTAT
to
sum to SHARED_POOL_SIZE...
Then, sometime in the Oracle8 or Oracle8i timeframe, the
meaning of
the statistic was changed so that the term
"free memory" became what
everyone had thought it was,
an adjective and a noun (i.e. as in "free
beer" or
"free time"). A much more useful statistic, certainly...
Is this true? If not, is it close?
The sum of the information in V$SGASTAT still does not add
to
SHARED_POOL_SIZE, though (query from v8.1.7.4.0
shown below):
SQL> select name, bytes from
v$sgastat
2 where pool =
'shared pool';
NAME
BYTES
--------------------------
----------
free
memory
18208352
miscellaneous
2378964
DML
locks
120000
PLS non-lib
hp
2096
trigger
inform
944
PL/SQL
MPCODE
1146204
PL/SQL
DIANA
1223360
PX
subheap
123476
db_block_hash_buckets
1411080
sessions
377300
KGK
heap
48124
State
objects
267420
message pool
freequeue 124552
Checkpoint
queue
885168
enqueue_resources
222912
db_files
370988
KGFF
heap
649844
KQLS
heap
1709904
dictionary
cache
12670280
table
definiti
3228
transactions
171264
ksfv
subheap
4248
fixed allocation
callback 1280
library
cache
89490788
simulator trace
entries 240000
sql
area
187432036
table
columns
19520
processes
123380
partitioning
d
152976
db_block_buffers
10880000
event statistics per
sess 607600
----------
sum
331067288
SQL> show parameter shared_pool_size
NAME
TYPE VALUE
------------------- ------- ---------
shared_pool_size string 314572800
I'm curious about the 16,494,488 bytes difference. Is it
possible
that V$SGASTAT is another "unlatched" data
structure in memory,
allowing errors in the interest
of eliminating contention? There are
other
similar structures in the SGA (i.e. the data structure
underlying table MONITORING statistics later flushed to
SYS.TABMOD$)...
Thanks for any and all insight!
--
Please see the official ORACLE-L
FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego,
California -- Mailing list and web
hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling
of 'ListGuru') and in
the message BODY, include a line
containing: UNSUB ORACLE-L
(or the name of mailing
list you want to be removed from). You may
also
send the HELP command for other information (like subscribing).
