RE: 9i - Dynamic SGA - SGA_MAX_SIZE
BTW, my debate of this issue was completely incorrect. As a colleague from This list has noted, SGA is created using shmget and shmat, not malloc and calloc. The only explanation might be that the mechanism is similar to the one of malloc. -- Mladen Gogala Oracle DBA -Original Message- Jessica Mao Sent: Tuesday, August 05, 2003 6:34 PM To: Multiple recipients of list ORACLE-L don't know if it's still true, i played with sga_max_size and db_cache_size a year ago on 9.0 Solaris8 F15k w/o much luck. when sga_max_size = 64G and db_cache_size = 32G, db started impressingly fast. but db server cpu sys% jumped up to 90+ during warming-up tests, leaving usr% close to 0. tests never finished. reduced sga_max_size = 40G and db_cache_size = 32G, db cpu still around 50% sys. sga_max_size = 18G and db_cache_size = 16G, db performing ok. after that gave up sga_max_size, increased db_cache_size to 24G (sga_max_size was then auto set to 24G). db performing ok. db_cache_size = 32G (= sga_max_size). took 32 min to start db. db performing ok. db_cache_size = 40G (just curious), took 40min to start db. db performing ok. -Jessica --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
don't know if it's still true, i played with sga_max_size and db_cache_size a year ago on 9.0 Solaris8 F15k w/o much luck. when sga_max_size = 64G and db_cache_size = 32G, db started impressingly fast. but db server cpu sys% jumped up to 90+ during warming-up tests, leaving usr% close to 0. tests never finished. reduced sga_max_size = 40G and db_cache_size = 32G, db cpu still around 50% sys. sga_max_size = 18G and db_cache_size = 16G, db performing ok. after that gave up sga_max_size, increased db_cache_size to 24G (sga_max_size was then auto set to 24G). db performing ok. db_cache_size = 32G (= sga_max_size). took 32 min to start db. db performing ok. db_cache_size = 40G (just curious), took 40min to start db. db performing ok. -Jessica --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Just think, Nirvana is only one bullet away! -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: 9i - Dynamic SGA - SGA_MAX_SIZE To continue down the Stephen's path, shutdown abort will complete very quickly and will drastically decrease the size of SGA. What is more, the very busy environment Will not be very busy any more. Perhaps very loud, but not very busy. -- Mladen Gogala Oracle DBA -Original Message- Kirtikumar Deshpande Sent: Tuesday, August 05, 2003 12:35 PM To: Multiple recipients of list ORACLE-L U R Welcome. Although, reducing SGA size is technically possible, it can be a very expensive operation in a busy environemt and can take a long time to complete. I have not run any specific tests to see how long it takes to reduce SGA by a certain amount, but you can imagine what must be done (find least used blocks/age out blocks, keep them from re-use, and finally 'detach' them etc..) - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity
Re: 9i - Dynamic SGA - SGA_MAX_SIZE
Hi!
I made a simple C program and tested shmget in linux using strace, the test
didn't show any malloc() or brk() call, while when using malloc explicitly,
brk() was called. This was on linux, I am too lazy to install a compiler on
my test solaris machine, if anyone is interested, just run this with truss
and check output.
#include sys/ipc.h
#include sys/shm.h
int i;
char c;
main() {
//c = malloc(555);
i = shmget(0, 1000, 511+IPC_CREAT+IPC_EXCL);
}
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, August 08, 2003 11:39 PM
Mladen,
But then again, shmget and shmat use malloc/calloc internally, it just
differs in that shmget/shmat create the memory space and then make a
public
handle so that multiple processes can access the memory allocated. (Very
simplisticly put)
So in a certain sense it is more than just a similar mechanism.
What was this discussion about anyway ;)
-Mensaje original-
De: Mladen Gogala [mailto:[EMAIL PROTECTED]
Enviado el: viernes, 08 de agosto de 2003 16:09
Para: Multiple recipients of list ORACLE-L
Asunto: RE: 9i - Dynamic SGA - SGA_MAX_SIZE
BTW, my debate of this issue was completely incorrect. As a colleague from
This list has noted, SGA is created using shmget and shmat, not malloc and
calloc.
The only explanation might be that the mechanism is similar to the one of
malloc.
--
Mladen Gogala
Oracle DBA
-Original Message-
Jessica Mao
Sent: Tuesday, August 05, 2003 6:34 PM
To: Multiple recipients of list ORACLE-L
don't know if it's still true, i played with sga_max_size and
db_cache_size
a year ago on 9.0 Solaris8 F15k w/o much luck.
when sga_max_size = 64G and db_cache_size = 32G, db started impressingly
fast. but db server cpu sys% jumped up to 90+ during warming-up tests,
leaving usr% close to 0. tests never finished. reduced sga_max_size = 40G
and db_cache_size = 32G, db cpu still around 50% sys. sga_max_size = 18G
and
db_cache_size = 16G, db performing ok.
after that gave up sga_max_size, increased db_cache_size to 24G
(sga_max_size was then auto set to 24G). db performing ok. db_cache_size =
32G (= sga_max_size). took 32 min to start db. db performing ok.
db_cache_size = 40G (just curious), took 40min to start db. db performing
ok.
-Jessica
--- Mohammed Shakir [EMAIL PROTECTED] wrote:
Kirti
Thanks for the info.
I could not raise dynamically the size of db_cache_size and did not
know why, until I noticed the new parameter sga_max_size. Anyway, I
tried to lower the size of db_cache_size dynamically and I did not
have a problem. So sgma_max_size does play its role in
db_cache_sizing.
I have not tested where I increase the size of sga_max_size using
init.ora and then try to increase the size of db_cache_size
dynamically by the same size as the increase in sga_max_size. That is
the test for coming weekend.
--- Kirtikumar Deshpande [EMAIL PROTECTED] wrote:
SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA
(Dynamic Sizing feature) components such as, shared pool, large
pool, buffer cache etc. In versions up to 8i, such changes
required bouncing the instance.
This parameter assumes the value of the SGA at instance startup.
Various components of the SGA can
then be increased/reduced as and when needed. The total SGA, thus,
can reach a maximum value set
by SGA_MAX_SIZE (if set in the init.ora file). That's the idea.
However, the implementation is
different on various platforms. With ISM, and DISM, on Solaris, there
are other issues when it
comes to using Dynamic SGA. You may want to search Metalink for
specific notes/articles for
Solaris.
On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in
init.ora, at the instanace startup,
and allocates the excess (difference in computed SGA value and set
SGA_MAX_SIZE) to 'variable
size'. Hence there is no room for any dynamic sizing (upward) of any
SGA component. I did not try
to downsize shared pool first, and 'upsize' buffer cache later. May
be that would work, but that
is not the intention of using this parameter.
PGA_AGRREGATE_TARGET is completely different from this parameter. It
sets an instance-wide upper limit for the memory used by sorting,
hashing processes.
Hope this helps..
- Kirti
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
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
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Why I have db_cache_size set to zero in my database? SQL show parameter db_cache_size NAME TYPEVALUE --- -- db_cache_sizebig integer 0 SQL -Original Message- Sent: Tuesday, August 05, 2003 5:34 PM To: Multiple recipients of list ORACLE-L don't know if it's still true, i played with sga_max_size and db_cache_size a year ago on 9.0 Solaris8 F15k w/o much luck. when sga_max_size = 64G and db_cache_size = 32G, db started impressingly fast. but db server cpu sys% jumped up to 90+ during warming-up tests, leaving usr% close to 0. tests never finished. reduced sga_max_size = 40G and db_cache_size = 32G, db cpu still around 50% sys. sga_max_size = 18G and db_cache_size = 16G, db performing ok. after that gave up sga_max_size, increased db_cache_size to 24G (sga_max_size was then auto set to 24G). db performing ok. db_cache_size = 32G (= sga_max_size). took 32 min to start db. db performing ok. db_cache_size = 40G (just curious), took 40min to start db. db performing ok. -Jessica --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
U R Welcome. Although, reducing SGA size is technically possible, it can be a very expensive operation in a busy environemt and can take a long time to complete. I have not run any specific tests to see how long it takes to reduce SGA by a certain amount, but you can imagine what must be done (find least used blocks/age out blocks, keep them from re-use, and finally 'detach' them etc..) - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Mladen, But then again, shmget and shmat use malloc/calloc internally, it just differs in that shmget/shmat create the memory space and then make a public handle so that multiple processes can access the memory allocated. (Very simplisticly put) So in a certain sense it is more than just a similar mechanism. What was this discussion about anyway ;) -Mensaje original- De: Mladen Gogala [mailto:[EMAIL PROTECTED] Enviado el: viernes, 08 de agosto de 2003 16:09 Para: Multiple recipients of list ORACLE-L Asunto: RE: 9i - Dynamic SGA - SGA_MAX_SIZE BTW, my debate of this issue was completely incorrect. As a colleague from This list has noted, SGA is created using shmget and shmat, not malloc and calloc. The only explanation might be that the mechanism is similar to the one of malloc. -- Mladen Gogala Oracle DBA -Original Message- Jessica Mao Sent: Tuesday, August 05, 2003 6:34 PM To: Multiple recipients of list ORACLE-L don't know if it's still true, i played with sga_max_size and db_cache_size a year ago on 9.0 Solaris8 F15k w/o much luck. when sga_max_size = 64G and db_cache_size = 32G, db started impressingly fast. but db server cpu sys% jumped up to 90+ during warming-up tests, leaving usr% close to 0. tests never finished. reduced sga_max_size = 40G and db_cache_size = 32G, db cpu still around 50% sys. sga_max_size = 18G and db_cache_size = 16G, db performing ok. after that gave up sga_max_size, increased db_cache_size to 24G (sga_max_size was then auto set to 24G). db performing ok. db_cache_size = 32G (= sga_max_size). took 32 min to start db. db performing ok. db_cache_size = 40G (just curious), took 40min to start db. db performing ok. -Jessica --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
To continue down the Stephen's path, shutdown abort will complete very quickly and will drastically decrease the size of SGA. What is more, the very busy environment Will not be very busy any more. Perhaps very loud, but not very busy. -- Mladen Gogala Oracle DBA -Original Message- Kirtikumar Deshpande Sent: Tuesday, August 05, 2003 12:35 PM To: Multiple recipients of list ORACLE-L U R Welcome. Although, reducing SGA size is technically possible, it can be a very expensive operation in a busy environemt and can take a long time to complete. I have not run any specific tests to see how long it takes to reduce SGA by a certain amount, but you can imagine what must be done (find least used blocks/age out blocks, keep them from re-use, and finally 'detach' them etc..) - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Because you are still using db_block_buffers in init.ora file.. - Kirti --- Roger Xu [EMAIL PROTECTED] wrote: Why I have db_cache_size set to zero in my database? SQL show parameter db_cache_size NAME TYPEVALUE --- -- db_cache_sizebig integer 0 SQL -Original Message- Sent: Tuesday, August 05, 2003 5:34 PM To: Multiple recipients of list ORACLE-L snip __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Oracle have changed there response when I pointed out that ask Tom indicated that different operating systems appear to perform differently Oracle's response is below and from all your replies this would appear to be the case. Thanks, for your help. stephen ** *** Yes, on most platforms the shared memory would be pre-allocated for the SGA. equivalent to SGFA_MAX_SIZE. Solaris with Dynamic ISM enabled is one exception. There may be others. It's not 100% obvious if this is really wasting resources. If the memory is never being referenced it may not have to be resident in physical memory (though some platforms do try to keep the SGA pinned in memory). ** *** Phone: 01737 27 5564 [EMAIL PROTECTED] Mladen Gogala [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hia.net cc: Sent by: Subject: Re: 9i - Dynamic SGA - SGA_MAX_SIZE [EMAIL PROTECTED] ity.com 03/08/03 05:19 Please respond to ORACLE-L Kirti, problem is in the malloc mechanism. The subroutine to free the memory doesn't return it to the OS, it returns it to the allocation pool instead. As a consequence, when oracle does free, its address space is not going to go down. You realize, of course, that malloc, calloc, realloc and free are the only options, because of their portability. There are low level allocation routines like brk() to increase the address space, but unaware of any routine to decrease the address space. Here is the manual page for the library subroutine called mallopt, which is mostly obsolete but still exists on HP-UX. The mechanism remained the same, but the control was taken away from the users. Manual page does illustrate the mechanism, though. malloc(3C) malloc(3C) NAME malloc(), free(), realloc(), calloc(), mallopt(), mallinfo(), memorymap() - main memory allocator SYNOPSIS #include stdlib.h void *malloc(size_t size); void *calloc(size_t nelem, size_t elsize); void *realloc(void *ptr, size_t size); void
Re: 9i - Dynamic SGA - SGA_MAX_SIZE
We covered a similar issue with sort_area_size in Tim Gorman's seminar at IOUG in April. Unless brk() is called, the memory is not returned to the OS. Jared Kirtikumar Deshpande [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/03/2003 12:29 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: 9i - Dynamic SGA - SGA_MAX_SIZE Thanks, Mladen. That makes sense. One just needs to make sure (by testing) that the OS supports this feature. - Kirti --- Mladen Gogala [EMAIL PROTECTED] wrote: Kirti, problem is in the malloc mechanism. The subroutine to free the memory doesn't return it to the OS, it returns it to the allocation pool instead. As a consequence, when oracle does free, its address space is not going to go down. You realize, of course, that malloc, calloc, realloc and free are the only options, because of their portability. There are low level allocation routines like brk() to increase the address space, but unaware of any routine to decrease the address space. Here is the manual page for the library subroutine called mallopt, which is mostly obsolete but still exists on HP-UX. The mechanism remained the same, but the control was taken away from the users. Manual page does illustrate the mechanism, though. malloc(3C) malloc(3C) snip __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Kirti Thanks for the info. I could not raise dynamically the size of db_cache_size and did not know why, until I noticed the new parameter sga_max_size. Anyway, I tried to lower the size of db_cache_size dynamically and I did not have a problem. So sgma_max_size does play its role in db_cache_sizing. I have not tested where I increase the size of sga_max_size using init.ora and then try to increase the size of db_cache_size dynamically by the same size as the increase in sga_max_size. That is the test for coming weekend. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: I am little confused about this issue to. I am working on Oracle 9.2.0.3 on Solaris 9 (64 bits) platform. I did not set sga_max_size parameter and I see it set. I am not sure what it means and what kind of problem it will cause me. I have pga_aggregate_target is set for 512MB and it seems it is not counted in this count. I know it is a separate space in the memory. Since this is a new system for me, I am little concerned that Oracle does not chock on me. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Hi! I think sbrk() (which calls brk()) can be executed with negative value to reduce total heap size for a process? Anyway, on windows, SGA_MAX_SIZE didn't affect allocation of virtual memory, my SGA was 200M and max_size was set to 512M, still only 200M was allocated at startup. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, August 03, 2003 7:19 AM Kirti, problem is in the malloc mechanism. The subroutine to free the memory doesn't return it to the OS, it returns it to the allocation pool instead. As a consequence, when oracle does free, its address space is not going to go down. You realize, of course, that malloc, calloc, realloc and free are the only options, because of their portability. There are low level allocation routines like brk() to increase the address space, but unaware of any routine to decrease the address space. Here is the manual page for the library subroutine called mallopt, which is mostly obsolete but still exists on HP-UX. The mechanism remained the same, but the control was taken away from the users. Manual page does illustrate the mechanism, though. malloc(3C) malloc(3C) NAME malloc(), free(), realloc(), calloc(), mallopt(), mallinfo(), memorymap() - main memory allocator SYNOPSIS #include stdlib.h void *malloc(size_t size); void *calloc(size_t nelem, size_t elsize); void *realloc(void *ptr, size_t size); void free(void *ptr); void memorymap(int show_stats); SYSTEM V SYNOPSIS #include malloc.h char *malloc(unsigned size); void free(char *ptr); char *realloc(char *ptr, unsigned size); char *calloc(unsigned nelem, unsigned elsize); int mallopt(int cmd, int value); struct mallinfo mallinfo(void); Remarks The functionality in the old malloc(3X) package has been incorporated into malloc(3C). The library (/usr/lib/libmalloc.a) corresponding to the -lmalloc linker option is now an empty library. Makefiles that reference this library will continue to work. Applications that used the malloc(3X) package should still work properly with the new malloc(3C) package. If the old versions must be used, they are provided in files /usr/old/libmalloc3x.a and /usr/old/libmalloc3c.o for Release 8.07 only. DESCRIPTION The functions described in this manual entry provide a simple, general-purpose memory allocation package: malloc() allocates space for a block of at least size bytes, but does not initialize the space. calloc() allocates space for an array of nelem elements, each of size elsize bytes, and initializes the Hewlett-Packard Company- 1 - HP-UX Release 9.0: August 1992 malloc(3C) malloc(3C) space to zeros. realloc() changes the size of the block pointed to by ptr to size bytes and returns a pointer to the (possibly moved) block. Existing contents are unchanged up to the lesser of the new and old sizes. If ptr is a NULL pointer, realloc() behaves like malloc() for the specified size. If size is zero and ptr is not a NULL pointer, the object it points to is freed and NULL is returned. free() deallocates the space pointed to by ptr (a pointer to a block previously allocated by malloc(), realloc(), or calloc()) and makes the space available for further allocation. If ptr is a NULL pointer, no action occurs. mallopt() provides for control over the allocation algorithm and other options in the malloc(3C) package. The available values for cmd are: M_MXFAST Set maxfast to value. The algorithm allocates all blocks below the size of maxfast in large groups, then doles them out very quickly. The default value for maxfast is zero (0). M_NLBLKS Set numlblks to value. The above mentioned ``large groups'' each contain numlblks blocks. numlblks must be
Re: 9i - Dynamic SGA - SGA_MAX_SIZE
Thanks, Mladen. That makes sense. One just needs to make sure (by testing) that the OS supports this feature. - Kirti --- Mladen Gogala [EMAIL PROTECTED] wrote: Kirti, problem is in the malloc mechanism. The subroutine to free the memory doesn't return it to the OS, it returns it to the allocation pool instead. As a consequence, when oracle does free, its address space is not going to go down. You realize, of course, that malloc, calloc, realloc and free are the only options, because of their portability. There are low level allocation routines like brk() to increase the address space, but unaware of any routine to decrease the address space. Here is the manual page for the library subroutine called mallopt, which is mostly obsolete but still exists on HP-UX. The mechanism remained the same, but the control was taken away from the users. Manual page does illustrate the mechanism, though. malloc(3C) malloc(3C) snip __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: 9i - Dynamic SGA - SGA_MAX_SIZE
I am little confused about this issue to. I am working on Oracle 9.2.0.3 on Solaris 9 (64 bits) platform. I did not set sga_max_size parameter and I see it set. I am not sure what it means and what kind of problem it will cause me. I have pga_aggregate_target is set for 512MB and it seems it is not counted in this count. I know it is a separate space in the memory. Since this is a new system for me, I am little concerned that Oracle does not chock on me. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti --- Hately, Mike (LogicaCMG) [EMAIL PROTECTED] wrote: Stephen, The documentation is pretty wooly regarding this issue but the way it seems to be intended to work is this: At startup Oracle will allocate an SGA sized as specified in the sga_max_size parameter. This is to ensure that the system has enough memory accomodate what you see as a maximum requirement for the SGA. After it's allocated this and started the database it should deallocate any memory it holds over and above that required to store the components of the SGA. In some platforms/versions this deallocation doesn't occur. Solaris for example behaves like this unless you move to version 8. It's possible that your version of Tru64 has a similar limitation or that you're seeing a bug. To my mind though, Oracle Support's claim that this is expected behaviour is a bit of a cop out. This is certainly not the way it was supposed to work. The concept guide states the following: The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification. and Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE Both of these statements imply that the unused memory is supposed to be released back to the operating system. The way that this feature operates on your system it allows you to juggle storage backwards and forwards between caches which is still useful but not 'what it says on the box'. I'd ask Oracle under what cirtcumstances this is normal behaviour. It's not the way the software is intended to work so maybe it's a platform limitation. In order to give you a better idea of what Oracle thinks it's SGA is using you can query the following views : - V$SGA_CURRENT_RESIZE_OPS: Information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component. - V$SGA_RESIZE_OPS: Information about the last 100 completed SGA resize operations. This does not include any operations currently in progress. - V$SGA_DYNAMIC_COMPONENTS: Information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations since startup. - V$SGA_DYNAMIC_FREE_MEMORY: Information about the amount of SGA memory available for future dynamic SGA resize operations. Hope this helps, Mike Hately __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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). = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mohammed Shakir 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: 9i - Dynamic SGA - SGA_MAX_SIZE
SGA_MAX_SIZE was introduced in 9i to allow dynamic sizing of SGA (Dynamic Sizing feature) components such as, shared pool, large pool, buffer cache etc. In versions up to 8i, such changes required bouncing the instance. This parameter assumes the value of the SGA at instance startup. Various components of the SGA can then be increased/reduced as and when needed. The total SGA, thus, can reach a maximum value set by SGA_MAX_SIZE (if set in the init.ora file). That's the idea. However, the implementation is different on various platforms. With ISM, and DISM, on Solaris, there are other issues when it comes to using Dynamic SGA. You may want to search Metalink for specific notes/articles for Solaris. On AIX 5L as I found out, Oracle uses SGA_MAX_SIZE, if set in init.ora, at the instanace startup, and allocates the excess (difference in computed SGA value and set SGA_MAX_SIZE) to 'variable size'. Hence there is no room for any dynamic sizing (upward) of any SGA component. I did not try to downsize shared pool first, and 'upsize' buffer cache later. May be that would work, but that is not the intention of using this parameter. PGA_AGRREGATE_TARGET is completely different from this parameter. It sets an instance-wide upper limit for the memory used by sorting, hashing processes. Hope this helps.. - Kirti --- Mohammed Shakir [EMAIL PROTECTED] wrote: I am little confused about this issue to. I am working on Oracle 9.2.0.3 on Solaris 9 (64 bits) platform. I did not set sga_max_size parameter and I see it set. I am not sure what it means and what kind of problem it will cause me. I have pga_aggregate_target is set for 512MB and it seems it is not counted in this count. I know it is a separate space in the memory. Since this is a new system for me, I am little concerned that Oracle does not chock on me. --- Kirtikumar Deshpande [EMAIL PROTECTED] wrote: It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Kirti, problem is in the malloc mechanism. The subroutine to free the memory doesn't return it to the OS, it returns it to the allocation pool instead. As a consequence, when oracle does free, its address space is not going to go down. You realize, of course, that malloc, calloc, realloc and free are the only options, because of their portability. There are low level allocation routines like brk() to increase the address space, but unaware of any routine to decrease the address space. Here is the manual page for the library subroutine called mallopt, which is mostly obsolete but still exists on HP-UX. The mechanism remained the same, but the control was taken away from the users. Manual page does illustrate the mechanism, though. malloc(3C) malloc(3C) NAME malloc(), free(), realloc(), calloc(), mallopt(), mallinfo(), memorymap() - main memory allocator SYNOPSIS #include stdlib.h void *malloc(size_t size); void *calloc(size_t nelem, size_t elsize); void *realloc(void *ptr, size_t size); void free(void *ptr); void memorymap(int show_stats); SYSTEM V SYNOPSIS #include malloc.h char *malloc(unsigned size); void free(char *ptr); char *realloc(char *ptr, unsigned size); char *calloc(unsigned nelem, unsigned elsize); int mallopt(int cmd, int value); struct mallinfo mallinfo(void); Remarks The functionality in the old malloc(3X) package has been incorporated into malloc(3C). The library (/usr/lib/libmalloc.a) corresponding to the -lmalloc linker option is now an empty library. Makefiles that reference this library will continue to work. Applications that used the malloc(3X) package should still work properly with the new malloc(3C) package. If the old versions must be used, they are provided in files /usr/old/libmalloc3x.a and /usr/old/libmalloc3c.o for Release 8.07 only. DESCRIPTION The functions described in this manual entry provide a simple, general-purpose memory allocation package: malloc() allocates space for a block of at least size bytes, but does not initialize the space. calloc() allocates space for an array of nelem elements, each of size elsize bytes, and initializes the Hewlett-Packard Company- 1 - HP-UX Release 9.0: August 1992 malloc(3C) malloc(3C) space to zeros. realloc() changes the size of the block pointed to by ptr to size bytes and returns a pointer to the (possibly moved) block. Existing contents are unchanged up to the lesser of the new and old sizes. If ptr is a NULL pointer, realloc() behaves like malloc() for the specified size. If size is zero and ptr is not a NULL pointer, the object it points to is freed and NULL is returned. free() deallocates the space pointed to by ptr (a pointer to a block previously allocated by malloc(), realloc(), or calloc()) and makes the space available for further allocation. If ptr is a NULL pointer, no action occurs. mallopt() provides for control over the allocation algorithm and other options in the malloc(3C) package. The available values for cmd are: M_MXFAST Set maxfast to value. The algorithm allocates all blocks below the size of maxfast in large groups, then doles them out very quickly. The default value for maxfast is zero (0). M_NLBLKS Set numlblks to value. The above mentioned ``large groups'' each contain numlblks blocks. numlblks must be greater than 1. The default value for numlblks is 100. M_GRAINSet grain to value. The sizes of all blocks smaller than maxfast are considered to be rounded up to the nearest
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Stephen, That's the way it's meant to work. This is how Oracle guarantees that you can grow upto the value of SGA_MAX_SIZE. Look at the case below: DAY 1: you have your sga_max set to 100m but you are currently using only 50m of it. DAY 2: you want to use all 100m and you try to increase the sga size, but some other app has used rest of the physical memory and there is no sufficient memory available, at this stage oracle has to error out saying 'insufficient memory', but in reality it does not!! In reality: On DAY 1, Oracle tries to occupy all 100m in the memory so that it doesn't have any issues in future... this way you have 100m reserved for oracle, use it whenever you want to use it. Hope this helps you... ~Venu -Original Message- [EMAIL PROTECTED] Sent: Friday, August 01, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit. Example: You could configure your SGA to be 80M Set the SGA_MAX_SIZE to be 250M. I would have expected oracle to acquire 80M of memory from the UNIX machine. In fact using ipcs you can see that oracle will always acquire the value of SGA_MAX_SIZE. It acquires the extra space in the Variable Size of the SGA Sga_max_size=75m. Show sga: Total System Global Area 77041728 bytes Fixed Size 733248 bytes Variable Size 41943040 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes sga_max_size=110M and keep everything else the same. show sga: Total System Global Area 114790680 bytes Fixed Size 733464 bytes Variable Size 79691776 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes Database mounted. Database opened. I have raised a lengthy call on Metalink and the consultants are convinced this is normal behaviour and what you would expect. Do people agree with the metalink consultants? Maybe my expectations were to high but I thought a dynamic sga would mean I could change the amount of memory acquired by the UNIX box. All opinions welcome. I am on tru64 platform - 9.2.0.3.0 Thanks, Stephen ** This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited. This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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). **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Venu Gopal 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Hi! You should check mem usage from OS instead. Using ipcs -m for example. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 01, 2003 12:54 PM Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit. Example: You could configure your SGA to be 80M Set the SGA_MAX_SIZE to be 250M. I would have expected oracle to acquire 80M of memory from the UNIX machine. In fact using ipcs you can see that oracle will always acquire the value of SGA_MAX_SIZE. It acquires the extra space in the Variable Size of the SGA Sga_max_size=75m. Show sga: Total System Global Area 77041728 bytes Fixed Size 733248 bytes Variable Size 41943040 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes sga_max_size=110M and keep everything else the same. show sga: Total System Global Area 114790680 bytes Fixed Size 733464 bytes Variable Size 79691776 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes Database mounted. Database opened. I have raised a lengthy call on Metalink and the consultants are convinced this is normal behaviour and what you would expect. Do people agree with the metalink consultants? Maybe my expectations were to high but I thought a dynamic sga would mean I could change the amount of memory acquired by the UNIX box. All opinions welcome. I am on tru64 platform - 9.2.0.3.0 Thanks, Stephen ** This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited. This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Tanel Poder 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Tanel, I have done this. ipcs -m would not give me the size of the memory acquired on my system so I used ipcs -a I have also looked into the ps vax Which gives more detail: Currently this shows 199M reserved to the SGA EM9I /oracle/app/oracle/product/9.2.0/dbs ps avx|head -1 PID TTY S TIME SL PAGEIN VSZ RSS %CPU %MEM COMMAND OEM9I /oracle/app/oracle/product/9.2.0/dbs ps avx|grep OEM9I 472016 ?? S0:00.252 3 199M 6.2M 0.0 0.4 ora_dbw0_OEM9I 481099 ?? I0:00.20 32 2 198M 5.2M 0.0 0.3 ora_d000_OEM9I 481163 ?? S0:00.292 33 196M 3.8M 0.0 0.2 ora_pmon_OEM9I 481141 ?? S0:00.182 8 196M 3.6M 0.0 0.2 ora_s000_OEM9I 480555 ?? S0:01.49 19125 195M 2.9M 0.0 0.2 ora_qmn0_OEM9I 481126 ?? I0:00.66 184 41 195M 2.9M 0.0 0.2 ora_smon_OEM9I 481159 ?? S0:00.253 34 199M 2.8M 0.0 0.2 ora_lgwr_OEM9I 480962 ?? S0:00.601 5 195M 2.7M 0.0 0.2 ora_cjq0_OEM9I 481151 ?? I0:00.20 748 2 195M 2.7M 0.0 0.2 ora_reco_OEM9I 481148 ?? S0:00.432 2 195M 2.5M 0.0 0.2 ora_ckpt_OEM9I 481909 pts/22 S + 0:00.010 0 2.28M 232K 0.0 0.0 grep OEM9I I reduce the SGA_MAX_SIZE by 30M (keep all other parameters the same ) and the memory acquired on the OS drops by 30M 481979 ?? S0:00.252 0 167M 6.4M 0.0 0.4 ora_dbw0_OEM9I 481988 ?? S0:00.19 19 0 166M 5.2M 0.0 0.3 ora_d000_OEM9I 482000 ?? S0:00.191 0 164M 3.8M 0.0 0.2 ora_pmon_OEM9I 482040 ?? S0:00.17 19 0 164M 3.6M 0.0 0.2 ora_s000_OEM9I 481976 ?? S0:00.667 0 163M 2.9M 0.3 0.2 ora_smon_OEM9I 482004 ?? S0:00.222 1 167M 2.8M 0.0 0.2 ora_lgwr_OEM9I 481946 ?? S0:00.202 0 163M 2.7M 0.1 0.2 ora_cjq0_OEM9I 482024 ?? S0:00.172 0 163M 2.5M 0.0 0.2 ora_ckpt_OEM9I 481998 ?? S0:00.16 17 0 163M 2.4M 0.0 0.2 ora_reco_OEM9I 482030 ?? S0:00.16 19 0 163M 2.4M 0.0 0.2 ora_qmn0_OEM9I Oracle says this is what you would expect. It will grab the entire value of SGA_MAX_SIZE. This certainly seems to be correct I just think it is stupid. Why grab the memory from the os but never use it. stephen. [EMAIL PROTECTED] Tanel Poder tanel.poder.00 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Sent by: Subject: Re: 9i - Dynamic SGA - SGA_MAX_SIZE [EMAIL PROTECTED] ty.com 01/08/03 12:19 Please respond to ORACLE-L Hi! You should check mem usage from OS instead. Using ipcs -m for example. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 01, 2003 12:54 PM Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Venu, this does help and this is what the guys on metalink said. What I do not understand - what a waste. If I have to grab 100M initially on the OS side I may aswell use it. In my metalink tar I quoted many references in the documentation: E.G. In the Database Administration guide: Chapter 2: 'The size of the SGA is dynamic, and can grow or shrink by dynamically altering these parameters.' Now I would say grabing the SGA_MAX_SIZE for the entire uptime of the database is not what I would call dynamic. I can't see any growing or shrinking in SGA size. Stephen Hodgkinson Oracle DBA TotalFina Elf Gas Power Ltd Phone: 01737 27 5564 [EMAIL PROTECTED] Venu Gopal [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] pro.com cc: Sent by: Subject: RE: 9i - Dynamic SGA - SGA_MAX_SIZE [EMAIL PROTECTED] ity.com 01/08/03 12:14 Please respond to ORACLE-L Stephen, That's the way it's meant to work. This is how Oracle guarantees that you can grow upto the value of SGA_MAX_SIZE. Look at the case below: DAY 1: you have your sga_max set to 100m but you are currently using only 50m of it. DAY 2: you want to use all 100m and you try to increase the sga size, but some other app has used rest of the physical memory and there is no sufficient memory available, at this stage oracle has to error out saying 'insufficient memory', but in reality it does not!! In reality: On DAY 1, Oracle tries to occupy all 100m in the memory so that it doesn't have any issues in future... this way you have 100m reserved for oracle, use it whenever you want to use it. Hope this helps you... ~Venu -Original Message- [EMAIL PROTECTED] Sent: Friday, August 01, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit. Example: You could configure your SGA to be 80M Set the SGA_MAX_SIZE to be 250M. I would have expected oracle to acquire 80M of memory from the UNIX machine. In fact using ipcs you can see that oracle will always acquire the value of SGA_MAX_SIZE. It acquires the extra space in the Variable Size of the SGA Sga_max_size=75m. Show sga: Total System Global Area 77041728 bytes Fixed Size 733248 bytes
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Stephen, The documentation is pretty wooly regarding this issue but the way it seems to be intended to work is this: At startup Oracle will allocate an SGA sized as specified in the sga_max_size parameter. This is to ensure that the system has enough memory accomodate what you see as a maximum requirement for the SGA. After it's allocated this and started the database it should deallocate any memory it holds over and above that required to store the components of the SGA. In some platforms/versions this deallocation doesn't occur. Solaris for example behaves like this unless you move to version 8. It's possible that your version of Tru64 has a similar limitation or that you're seeing a bug. To my mind though, Oracle Support's claim that this is expected behaviour is a bit of a cop out. This is certainly not the way it was supposed to work. The concept guide states the following: The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification. and Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE Both of these statements imply that the unused memory is supposed to be released back to the operating system. The way that this feature operates on your system it allows you to juggle storage backwards and forwards between caches which is still useful but not 'what it says on the box'. I'd ask Oracle under what cirtcumstances this is normal behaviour. It's not the way the software is intended to work so maybe it's a platform limitation. In order to give you a better idea of what Oracle thinks it's SGA is using you can query the following views : - V$SGA_CURRENT_RESIZE_OPS: Information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component. - V$SGA_RESIZE_OPS: Information about the last 100 completed SGA resize operations. This does not include any operations currently in progress. - V$SGA_DYNAMIC_COMPONENTS: Information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations since startup. - V$SGA_DYNAMIC_FREE_MEMORY: Information about the amount of SGA memory available for future dynamic SGA resize operations. Hope this helps, Mike Hately -Original Message- Sent: 01 August 2003 11:59 To: Multiple recipients of list ORACLE-L Tanel, I have done this. ipcs -m would not give me the size of the memory acquired on my system so I used ipcs -a I have also looked into the ps vax Which gives more detail: Currently this shows 199M reserved to the SGA EM9I /oracle/app/oracle/product/9.2.0/dbs ps avx|head -1 PID TTY S TIME SL PAGEIN VSZ RSS %CPU %MEM COMMAND OEM9I /oracle/app/oracle/product/9.2.0/dbs ps avx|grep OEM9I 472016 ?? S0:00.252 3 199M 6.2M 0.0 0.4 ora_dbw0_OEM9I 481099 ?? I0:00.20 32 2 198M 5.2M 0.0 0.3 ora_d000_OEM9I 481163 ?? S0:00.292 33 196M 3.8M 0.0 0.2 ora_pmon_OEM9I 481141 ?? S0:00.182 8 196M 3.6M 0.0 0.2 ora_s000_OEM9I 480555 ?? S0:01.49 19125 195M 2.9M 0.0 0.2 ora_qmn0_OEM9I 481126 ?? I0:00.66 184 41 195M 2.9M 0.0 0.2 ora_smon_OEM9I 481159 ?? S0:00.253 34 199M 2.8M 0.0 0.2 ora_lgwr_OEM9I 480962 ?? S0:00.601 5 195M 2.7M 0.0 0.2 ora_cjq0_OEM9I 481151 ?? I0:00.20 748 2 195M 2.7M 0.0 0.2 ora_reco_OEM9I 481148 ?? S0:00.432 2 195M 2.5M 0.0 0.2 ora_ckpt_OEM9I 481909 pts/22 S + 0:00.010 0 2.28M 232K 0.0 0.0 grep OEM9I I reduce the SGA_MAX_SIZE by 30M (keep all other parameters the same ) and the memory acquired on the OS drops by 30M 481979 ?? S0:00.252 0 167M 6.4M 0.0 0.4 ora_dbw0_OEM9I 481988 ?? S0:00.19 19 0 166M 5.2M 0.0 0.3 ora_d000_OEM9I 482000 ?? S0:00.191 0 164M 3.8M 0.0 0.2 ora_pmon_OEM9I 482040 ?? S0:00.17 19 0 164M 3.6M 0.0 0.2 ora_s000_OEM9I 481976 ?? S0:00.667 0 163M 2.9M 0.3 0.2 ora_smon_OEM9I 482004 ?? S0:00.222 1 167M 2.8M 0.0 0.2 ora_lgwr_OEM9I 481946 ?? S0:00.202 0 163M 2.7M 0.1 0.2 ora_cjq0_OEM9I 482024 ?? S0:00.172 0 163M 2.5M 0.0 0.2 ora_ckpt_OEM9I 481998 ?? S0:00.16 17 0 163M 2.4M 0.0 0.2 ora_reco_OEM9I 482030 ?? S0:00.16 19 0 163M 2.4M 0.0 0.2 ora_qmn0_OEM9I Oracle says this is what you would expect. It will grab the entire value of SGA_MAX_SIZE. This certainly seems to be correct I just think it
Re: 9i - Dynamic SGA - SGA_MAX_SIZE
Hi! Oracle says this is what you would expect. It will grab the entire value of SGA_MAX_SIZE. This certainly seems to be correct I just think it is stupid. Why grab the memory from the os but never use it. Check from man pages of ps, top or other utility, how to check how much of the *physical* memory is actually used by Oracle. I'm no Unix memory management expert, but I believe that if you use malloc() and for allocating memory, no physical memory pages are grabbed before you actually access the memory (note that calloc() does 'touch' every page, I believe) Only mapping with virtual memory is done at first, that probably means only some slight changes to kernels/CPUs paging or whatever memory mapping and translation tables. So, Oracle probably 'allocates' the memory set with sga_max_size, but uses only as much as needed or allowed. So, If you set sga_max_size to 2G but have defined SGA to be only 1G, then no more than 1G of physical memory can ever be used by Oracle SGA. Only virtual memory (which means swap space too) is 'wasted'. You can try to experiment - write a small C program, which allocates 100M of memory without 'touching' it, the amount of available physical memory should remain (almost) the same and no paging should occur either. With sga_max_size Oracle makes sure, that in some point of time it is possible to allocate that much of virtual memory to Oracle and nothing is really wasted (ok, a bit of swap space is wasted, but who cares about few gigabytes novadays)) Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: 9i - Dynamic SGA - SGA_MAX_SIZE
It does not work as advertised, in AIX either... I played with this in AIX 5L. - Kirti --- Hately, Mike (LogicaCMG) [EMAIL PROTECTED] wrote: Stephen, The documentation is pretty wooly regarding this issue but the way it seems to be intended to work is this: At startup Oracle will allocate an SGA sized as specified in the sga_max_size parameter. This is to ensure that the system has enough memory accomodate what you see as a maximum requirement for the SGA. After it's allocated this and started the database it should deallocate any memory it holds over and above that required to store the components of the SGA. In some platforms/versions this deallocation doesn't occur. Solaris for example behaves like this unless you move to version 8. It's possible that your version of Tru64 has a similar limitation or that you're seeing a bug. To my mind though, Oracle Support's claim that this is expected behaviour is a bit of a cop out. This is certainly not the way it was supposed to work. The concept guide states the following: The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification. and Oracle can start instances underconfigured and allow the instance to use more memory by growing the SGA components, up to a maximum of SGA_MAX_SIZE Both of these statements imply that the unused memory is supposed to be released back to the operating system. The way that this feature operates on your system it allows you to juggle storage backwards and forwards between caches which is still useful but not 'what it says on the box'. I'd ask Oracle under what cirtcumstances this is normal behaviour. It's not the way the software is intended to work so maybe it's a platform limitation. In order to give you a better idea of what Oracle thinks it's SGA is using you can query the following views : - V$SGA_CURRENT_RESIZE_OPS: Information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component. - V$SGA_RESIZE_OPS: Information about the last 100 completed SGA resize operations. This does not include any operations currently in progress. - V$SGA_DYNAMIC_COMPONENTS: Information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations since startup. - V$SGA_DYNAMIC_FREE_MEMORY: Information about the amount of SGA memory available for future dynamic SGA resize operations. Hope this helps, Mike Hately __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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).
