Re: Free Shared pool memory
Title: RE: Free Shared pool memory Nope! Like many questions to the list, I hadn't checked metalink first. Thanks! - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L 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 128032 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... I
Re: Free Shared pool memory
Brain was clearly not engaged when I wrote the last answer - I have a set of results I produced some time back when calibrating init.ora parameters with memory usage. (Out of date by now since it was 8.0). Simple test: Double the size of sessions in the init.ora, various entries in v$sgastat will increase in size, even though you haven't changed shared_pool_size. The shared_pool_size is just one component of the "shared pool". 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 17:41 > >Another possible explanation is that SHARED_POOL_SIZE is not >the actual size of the Shared Pool, but rather a starting >point to which Oracle adds extra space for some reason? > -- 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).
Re: Re: Free Shared pool memory
It is often the case that Telecomms companies end up with a very large SGA. 3Gb does sound a little suspicious - but it would be silly to judge it without knowing more background, such as total number of users, number of applications embedded within the database, number of CPUs, nature of work, quality of code. And, of course, with 8.1.6.3 it is possible that the memory is being "wasted" to work around some bug with shared SQL handling that might be such an unusual bug that only that particular company has come across it. 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 18:05 Jonathan Lewis, Hi, lewis,have you ever see any big system with large shared pool size? This week I saw a mobile telecom system running ops 8163, have 8GB sga, with 3GB of shared_pool_size and About 5GB of data buffer.(Physical memory is 16GB) I never configured a system with shared_pool_size larger than 200MB(my current system is 200MB).But that oracle is installed by oracle china, so i wonder if this kind of configuration is reasonable? I do not have enough time to analyze that system, but I do not think Oracle china is giving the customer the right parameter about shared_pool, can you give your opinion? Thanks. -- 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).
Re: Free Shared pool memory
I'm probably telling you guys something you already know, but at least it gives me a chance to write something on the list... Notice that the show sga command output doesn't contain a line saying "shared pool size", but instead says "variable size". That's because it's the size of the variable part of the SGA, which includes the row/dictionary cache, the library cache (usually together referred to as the shared pool) plus a number of other structures including the hash buckets and the buffer header structures of the buffer cache, the lru/lruw/whatever structures and more. Therefor Variable Size will always be bigger than shared pool size. I hope this answered somebody's question somewhere ;-) Mogens Jonathan Lewis wrote: 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'; NAMEBYTES -- -- free memory 18208352 miscellaneous 2378964 DML locks 12 PLS non-lib hp 2096 trigger inform944 PL/SQL MPCODE 1146204 PL/SQL DIANA 1223360 PX subheap 123476 db_block_hash_buckets 1411080 sessions 377300 KGK heap48124 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 callback1280 library cache89490788 simulator trace entries24 sql area187432036 table columns 19520 processes 123380 partitioning d 152976 db_block_buffers 1088 event statistics per sess 607600 -- sum 331067288 SQL> show parameter shared_pool_size NAMETYPEVALUE --- --- - shared_pool_sizestring 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.TABMO
RE: Re: Free Shared pool memory
How does this grab you? FTLP> show sga Total System Global Area 2.2596E+10 bytes Fixed Size 103396 bytes Variable Size1120354304 bytes Database Buffers 2.1475E+10 bytes Redo Buffers1064960 bytes Got a bad application? Throw more hardware at it! QRO! QRO! > -Original Message- > can you give your opinion? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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).
Re: Re: Free Shared pool memory
Jonathan Lewis, Hi, lewis,have you ever see any big system with large shared pool size? This week I saw a mobile telecom system running ops 8163, have 8GB sga, with 3GB of shared_pool_size and About 5GB of data buffer.(Physical memory is 16GB) I never configured a system with shared_pool_size larger than 200MB(my current system is 200MB).But that oracle is installed by oracle china, so i wonder if this kind of configuration is reasonable? I do not have enough time to analyze that system, but I do not think Oracle china is giving the customer the right parameter about shared_pool, can you give your opinion? Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(ChinaOracle User Group) === 2003-01-02 02:28:00 ,you wrote£º=== >I think it's safe to say that if the free memory is always very large >then you can reinterpret it as 'wasted memory'. > >If the free memory is alway very small, I don't think it is possible >to make any decision without know the application. It is possible >that you need to increase the shared pool slightly (good app), it is >also possible that your shared pool is just about the right size >(great >app) , but it is possible that your application design has a flaw in >it. > > >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 05:39 > > >> >>Is it Correct to Look at FREE Memory in the Shared Pool ? >>Memory when used once thereafter when NO Longer in use does the FREE >Memory again Come up ? >>Are there any ideal Values for percentage of Free memory for the >Shared Pool >> >>The Respective Hybrid Application mostly uses Bind Variables >> >>Thanks >> >> >> >>-- >>Please see the official ORACLE-L FAQ: http://www.orafaq.net >>-- >>Author: VIVEK_SHARMA >> 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). >> > >-- >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). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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).
Re: Free Shared pool memory
The SHARED_POOL_RESERVED_SIZE is indeed defaulting to 15728640, which is 5% exactly of SHARED_POOL_SIZE... The reserved area is subtracted from the Shared Pool, so subtracting that amount from the difference still leaves 765,848 bytes. As a number, that doesn't divide by any of the powers-of-2 (i.e. 1024, 512, 256, etc) until you get down to 8, 4, and 2, so it kind of bugs me... Another possible explanation is that SHARED_POOL_SIZE is not the actual size of the Shared Pool, but rather a starting point to which Oracle adds extra space for some reason? The "unlatched data structure" explanation might be a cop-out, but I get suspicious when I see a statistic named "miscellaneous", which in itself is a cop-out for a database engine. Miscellaneous? You've got to be kidding! Life is miscellaneous when you step back... :-) > > 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'; > > NAMEBYTES > -- -- > free memory 18208352 > miscellaneous 2378964 > DML locks 12 > PLS non-lib hp 2096 > trigger inform944 > PL/SQL MPCODE 1146204 > PL/SQL DIANA 1223360 > PX subheap 123476 > db_block_hash_buckets 1411080 > sessions 377300 > KGK heap48124 > 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 callback1280 > library cache89490788 > simulator trace entries24 > sql area187432036 > table columns 19520 > processes 123380 > partitioning d 152976 > db_block_buffers 1088 > event statistics per sess 607600 > -- > sum 331067288 > > SQL> show parameter shared_pool_size > > NAMETYPEVALUE > --- --- -
RE: Free Shared pool memory
Title: 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 128032 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 12 PLS non-lib hp
RE: Free Shared pool memory
Title: Message did somebody say free beer?! -Original Message-From: Tim Gorman [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 02, 2003 9:44 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Free Shared pool memory 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 18208352miscellaneous 2378964DML locks 12PLS non-lib hp 2096trigger inform 944PL/SQL MPCODE 1146204PL/SQL DIANA 1223360PX subheap 123476db_block_hash_buckets 1411080sessions 377300KGK heap 48124State objects 267420message pool freequeue 124552Checkpoint queue 885168enqueue_resources 222912db_files 370988KGFF heap 649844KQLS heap 1709904dictionary cache 12670280table definiti 3228transactions 171264ksfv subheap 4248fixed allocation callback 1280library cache 89490788simulator trace entries 24sql area 187432036table columns 19520processes 123380partitioning d 152976db_block_buffers 1088event 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! - Original Message - From: "Jonathan Lewis" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 02, 2003 3:28 AM Subject: Re: Free Shared pool memory > > I think it's safe to say that if the free memory is always very large> then you can reinterpret it as 'wasted memory'.> > If the free memory is alway very small, I don't think it is possible> to make any decision without know the application. It is possible> that you need to increase the shared pool slightly (good app), it is> also possible that your shared pool is just about the right size> (great> app) , but it is possible that your application design has a flaw in> it.> > > 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> >
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'; NAMEBYTES -- -- free memory 18208352 miscellaneous 2378964 DML locks 12 PLS non-lib hp 2096 trigger inform944 PL/SQL MPCODE 1146204 PL/SQL DIANA 1223360 PX subheap 123476 db_block_hash_buckets 1411080 sessions 377300 KGK heap48124 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 callback1280 library cache89490788 simulator trace entries24 sql area187432036 table columns 19520 processes 123380 partitioning d 152976 db_block_buffers 1088 event statistics per sess 607600 -- sum 331067288 SQL> show parameter shared_pool_size NAMETYPEVALUE --- --- - shared_pool_sizestring 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).
Re: Free Shared pool memory
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 18208352miscellaneous 2378964DML locks 12PLS non-lib hp 2096trigger inform 944PL/SQL MPCODE 1146204PL/SQL DIANA 1223360PX subheap 123476db_block_hash_buckets 1411080sessions 377300KGK heap 48124State objects 267420message pool freequeue 124552Checkpoint queue 885168enqueue_resources 222912db_files 370988KGFF heap 649844KQLS heap 1709904dictionary cache 12670280table definiti 3228transactions 171264ksfv subheap 4248fixed allocation callback 1280library cache 89490788simulator trace entries 24sql area 187432036table columns 19520processes 123380partitioning d 152976db_block_buffers 1088event 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! - Original Message - From: "Jonathan Lewis" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, January 02, 2003 3:28 AM Subject: Re: Free Shared pool memory > > I think it's safe to say that if the free memory is always very large> then you can reinterpret it as 'wasted memory'.> > If the free memory is alway very small, I don't think it is possible> to make any decision without know the application. It is possible> that you need to increase the shared pool slightly (good app), it is> also possible that your shared pool is just about the right size> (great> app) , but it is possible that your application design has a flaw in> it.> > > 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 05:39> > > >> >Is it Correct to Look at FREE Memory in the Shared Pool ?> >Memory when used once thereafter when NO Longer in use does the FREE> Memory again Come up ?> >Are there any ideal Val
Re: Free Shared pool memory
I think it's safe to say that if the free memory is always very large then you can reinterpret it as 'wasted memory'. If the free memory is alway very small, I don't think it is possible to make any decision without know the application. It is possible that you need to increase the shared pool slightly (good app), it is also possible that your shared pool is just about the right size (great app) , but it is possible that your application design has a flaw in it. 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 05:39 > >Is it Correct to Look at FREE Memory in the Shared Pool ? >Memory when used once thereafter when NO Longer in use does the FREE Memory again Come up ? >Are there any ideal Values for percentage of Free memory for the Shared Pool > >The Respective Hybrid Application mostly uses Bind Variables > >Thanks > > > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: VIVEK_SHARMA > 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). > -- 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).
Free Shared pool memory
Is it Correct to Look at FREE Memory in the Shared Pool ? Memory when used once thereafter when NO Longer in use does the FREE Memory again Come up ? Are there any ideal Values for percentage of Free memory for the Shared Pool The Respective Hybrid Application mostly uses Bind Variables Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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).
