| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Free Shared pool memory
RE: Free Shared pool memoryNope! Like many questions to the list, I hadn't checked metalink first. Thanks!
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_at_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 bytesSQL> 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)
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 0SQL> exit
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demonco.uk]
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 <ORACLE-L_at_fatcity.com>
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: jonathan_at_jlcomp.demon.co.uk
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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: Tim_at_SageLogix.com 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: ListGuru_at_fatcity.com (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).Received on Sat Jan 04 2003 - 11:03:41 CST
 |
 |