In sun Solaris 8 , with Oracle 9.2.0.2

I set like this in init.ora
sga_max_Size=104857600
db_cache_size = 25165824
shared_pool_size = 12000000
java_pool_size = 0
large_pool_size = 0
log_buffer = 163840
after starting the database 
15:47:33 SQL> show sga 
Total System Global Area 105907536 bytes
Fixed Size 730448 bytes
Variable Size 79691776 bytes
Database Buffers 25165824 bytes
Redo Buffers 319488 bytes
15:51:20 SQL>
15:51:37 SQL> select sum(bytes) from v$sgastat where pool like 'shared%';
SUM(BYTES)
----------
29360128
Elapsed: 00:00:00.01
15:53:37 SQL> 
now sga is showing 100M where as my total sga is not ( after addition of 
shared+log+buffer)
database buffers is showing as correct value but where as sum of bytes in 
v$sgastat for shared pool is showing 28M It should be 12M why it is showing 
as 28M why addition of 16M.. ( granule ?? if granule it should be of 4m)
but variable size showing 76M .. Then what is the meaning of this 76M?

I think it is preallocating 

When I check with  ipcs -a | grep <MemID> it is showing
appp02:CRFO4DEM:/usr/home/oracle> ipcs -a | grep 216650
m 216650 0x733e394 --rw-r----- oracle dba oracle dba 
7 113246208 25061 1784 10:06:17 10:06:17 16:17:46

in segsz column it is 113246208 -> 100+ M??

Please give your expert opinion
 

While performing the following results I got some unexpected results. As it is illegal to set as non-standard cache size = to standard block size. I have db_block_size = 4K so I was expecting that I can't set a cache size of 4k but I am able to do so and then I have created a ts as well. I have oracle 9i Rel 1. After doing all this I can't shutdown the db or it give message that you can't set cache of 4k which is right but it is not giving any message while I am setting it.

SYS > show parameter block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 4096
SYS > show parameter cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_size big integer 33554432
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SYS > alter system set db_cache_size = 28M;

System altered.

SYS > alter system set db_4k_cache_size = 2M;

System altered.

SYS > show parameter cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 4194304
db_8k_cache_size big integer 0
db_cache_size big integer 29360128
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
SYS > create tablespace ts_4k datafile c:\temp\ts_4k01.dbf.dbf' size 2m blocksize 8k;
create tablespace ts_4k datafile c:\temp\ts_4k01.dbf.dbf' size 2m blocksize 8k
*
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE


SYS > ed
Wrote file afiedt.buf

1* create tablespace ts_4k datafile 'c:\temp\ts_4k01.dbf.dbf' size 2m blocksize 8k
SYS > /
create tablespace ts_4k datafile 'c:\temp\ts_4k01.dbf.dbf' size 2m blocksize 8k
*
ERROR at line 1:
ORA-29339: tablespace block size 8192 does not match configured block sizes


SYS > ed
Wrote file afiedt.buf

1* create tablespace ts_4k datafile 'c:\temp\ts_4k01.dbf.dbf' size 2m blocksize 4k
SYS > /
create tablespace ts_4k datafile 'c:\temp\ts_4k01.dbf.dbf' size 2m blocksize 4k
*
ERROR at line 1:
ORA-01543: tablespace 'TS_4K' already exists


SYS > drop tablespace ts_4k;

Tablespace dropped.

SYS > create tablespace ts_4k datafile 'c:\temp\ts_4k01.dbf.dbf' size 2m blocksize 4k

Tablespace created.

SYS > select * from v$tablespace;

TS# NAME INC
------------ ------------------------------ ---
2 CWMLITE YES
3 DRSYS YES
4 EXAMPLE YES
5 INDX YES
0 SYSTEM YES
7 TOOLS YES
1 UNDOTBS YES
8 USERS YES
6 TEMP YES
12 TS_4K YES

10 rows selected.

SYS > select * from dba_tablespaces;

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ------------ -------------- ------------ ------------
SYSTEM 4096 12288 12288 1
UNDOTBS 4096 65536 1
CWMLITE 4096 65536 1
DRSYS 4096 65536 1
EXAMPLE 4096 65536 1
INDX 4096 65536 1
TEMP 4096 1048576 1048576 1
TOOLS 4096 65536 1
USERS 4096 65536 1
TS_4K 4096 65536 1

10 rows selected.

SYS > shtudown immediate
SQL Plus Hangs here................

Thanks for your great answers.

I have 4K of DB and for this I have specified cache for this with db_cache_size. Now I am defining non-standard block size and it is allowing me of 4K which should not as 4K is the db block size. I can define 2,8,16,32.
Is it possible,

db_block_size=4k
db_cache_size=12M
db_4k_cache_size=2M

it won't let me on my 9203 box

ops$tkyte@ORA920> alter system set db_8k_cache_size = 4m;
alter system set db_8k_cache_size = 4m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size



In 9.0.1 it let me however.  and after that, connections failed with:

> plus

SQL*Plus: Release 9.0.1.0.0 - Production on Mon May 12 13:20:24 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

ERROR:
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size



so, what you need to do is

$ sqlplus "/ as sysdba"

SQL> create pfile from spfile;

<b>edit the generated $ORACLE_HOME/dbs/init$ORACLE_SID.ora file and get rid of the 4k cache setting you have and then</b>


SQL> create spfile from pfile;
SQL> startup force


sorry about that, didn't try in 9iR1. 

Thanks TOM I think I should sorry as I am still using R1. So I may say a bug in R1 which is removed in R2. I will try to get R2 soon. Thanks for your patience.

I've a peculiar problem. Im working Oracle 9 Rel2 on Windows. In init.ora i've set db_block_size = 8192.
(1) alter system set db_4k_cache_size=2m

show parameter cache_size shows values
db_4k_cache_size=4194304
db_cache_size=20971520

All other nk_cache_size = 0 as i've not set.

Then I give Create Tablespace t1 datafile 'c:\db4k.dbf' size 2m blocksize 8k;
tablespace was created without any error.
As per ur response it shud not create. Then why on my db.
(2)Im also able to set db_4k_cache_size=2m . Oops it works !!!. Also I can set db_16k_cache_size=2m. But as per ur previous response to one of the question posted it shud not work. What's the reason ? Even for that matter db_2k_cache_size=4m works. Even for other nk_cache_size is working without any problem.
Am I doing something wrong ?

I'm confuse while reading the structuer of Oracle 9i's SGA from DB Concept guid. The terms are:
Buffer, Cache, Pool
Free buffers, Pinned buffers, and Dirty buffers;
KEEP buffer pool, RECYCLE buffer pool, DEFAULT buffer pool
What's the relation between them?
How they reside in the memory?

Hi tom
Database  could not startup (9.2.0.1.0),Afetr set the multiple block sizes .While restarting getting the below ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
 
I have done the following tasks 

SQL> alter system set db_2k_cache_size=4M SCOPE=SPFILE;

System altered.

SQL> alter system set db_8k_cache_size=20M SCOPE=SPFILE;

System altered.

SQL> alter system set db_16k_cache_size=40M SCOPE=SPFILE;

System altered.

SQL> alter system set db_32k_cache_size=140M SCOPE=SPFILE;

System altered.

SQL> alter system set db_16k_cache_size=80M SCOPE=SPFILE;

System altered.

SQL>shtdown immediate

Then i try to restart it  i got the below error .

 
SQL> startup
ORA-00380: cannot specify db_8k_cache_size since 8K is the standard block size
SQL> create pfile from spfile;

edit the generated $ORACLE_HOME/database/init$ORACLE_SID.ora file and get rid of 
the 2k,8k,16,32k cache (*.db_2k_cache_size=0
*.db_32k_cache_size=0
*.db_8k_cache_size=0)


Please let help how to reststart my database and if possible send spfile file because i modified  spfile


This is my SPFILEDEMO file 

   S  C *.aq_tm_processes=1
*.background_dump_dest='d:\oracle\admin\demo\bdump'
*.compatible='9.2.0.0.0'
*.control_files='d:\oracle\oradata\demo\CONTROL01.CTL','d:\oracle\oradata\demo\CONTROL02.CTL','d:\oracle\oradata\demo\CONTROL03.CTL'
*.core_dump_dest='d:\oracle\admin\demo\cdump'
*.db_16k_cache_size=0
*.db_2k_cache_size=0
*.db_32k_cache_size=0
*.db_8k_cache_size=0
*.db_block_size=8192
*.db_cache_size=16777216
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='demo'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=demoXDB)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=TRUE
*.instance_name='demo'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.open_cursors=300
*.pga_aggregate_target=33554432
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=62914560
*.sort_area_size=1048576
*.star_transformation_enabled='TRUE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='d:\oracle\admin\demo\udump'
Regards
Mohan 
 

Tom,
Recently we set the SGA_MAX_SIZE = 2G on a new production instance. This instance is on a server which has other producton instances running. The actual SGA allocated to the new instance was 1.17 GB. After running for few hours today, suddenly the initial connection to all the database instances running on this server became slow. From SQL*plus on the server, it took more than 3 minutes to get to SQL> prompt. All the production applications halted. We realized that if we shutdown the new instance, the connections to other instances was in sub-second.

We just removed the SGA_MAX_SIZE parameter from the spfile of this new instance and restarted the instance. Everything is running smooth now. Is there any issue that you know of in using dynamic SGA feature on Sun Solaris 5.8 64 bit? I have searched Metalink but cannot find anything. We are on 9ir2. 

Thanks... 

Arun,

I could be wrong, you may be hitting a Solaris bug that could cause performance degradation.

The bug# used to be 4675878.

I see a different one now:

</code> http://sunsolve6.sun.com/search/document.do?assetkey=1-9-72952-1&searchclause=Patch%20Club%20Report <code>

-Arul

That is a recent bug fix update.

On Solaris 8/9, Oracle9i/10g has problems allocating memory dynamically. It is a bug in Solaris' dynamic memory menagement module (DISM), which Oracle uses to allocate/free SGA granules.

In my case, runing 9iR2 on Solaris 8, the same occurred. Oracle pre-allocated all SGM_MAX_SIZE and I had problems resizing SGA and getting info about real SGA size.

When Solaris kernel patch 117000-05 is applied, SGA manipulation works just fine.

On Sun's site, it's very clear. I have searched Metalink for this issue and have found no references, though. I have opened a tar informing them of this bug fix.

We also found the same article from Sun and quickly disabled the sga_max_size. There is no reference on Metalink about this issue. I have also opened a TAR with Oracle. Thanks to everyone...

My server has 4 GB RAM.
My system - OLTP, transaction and reports in excessive manner.
No Java procedure
PL sql procedures/trigger are in good nos.
max concurrent users - 300

I want to allocate SGA as 2 GB.
Please guide me how to set the parameters.
Large pool
Jave pool
buffer cache
shared pool
sort area size

Thanks for your answer.

We dont have inhouse DBA. We are getting services on per call basis. Junior DBA (so called ?) is available, but only for back up and for basic things.

And whenever we report a problem of slowness / error, he is simple increasing something.
Further details in our system:
1. not an MTS
2. it is an OLTP
3. 4 GB RAM
4. oracle 9.0.1.1 (changed to 9i recently)
5. 2 Mini ERP application is running (GUI) using BIND
6. NO Java
7. Importatnt processes are through PL SQL procedure/triggers

some inputs:
currently it has about 240 Large pool and 32 MB Java pool. So shared pool automatically calculated as 160 MB. Buffer cache is 128 MB. concurrent users - 100.

Please guide, how much to be allocated in the begining or atleast give me the % of each. (combination)

Thanks in advance.

In 9i, which process does the resizing of the SGA components such as shared pool when I issue the command ALTER SYSTEM SET SHARED_POOL_SIZE=48M for example? I am just curious to know whether any of the background process does it or the server process? Thanks. You are the best!

I have a 64-bit Solaris system with 8GB RAM. I am using Oracle 9iR2 (9.2.0.4).

I decided to allocate 3GB to SGA and 3GB to PGA_AGGREGATE_TARGET to start off with. I want to use the dynamic SGA sizing feature and so I set my init.ora parameters as follows

sga_max_size = 5G # See Note 151222.1 and Bug 2175916
db_block_size = 8192
db_cache_size = 2000M
db_32k_cache_size = 200M
db_keep_cache_size = 200M
db_recycle_cache_size = 100M

# Shared pool
shared_pool_size = 100M
shared_pool_reserved_size = 10M

# Log buffer
log_buffer = 5242880

# PGA
pga_aggregate_target = 3G
workarea_size_policy = AUTO

This way, I can go all the way up to 5GB for my SGA and when I do, I can bump down the PGA appropriately.

When I started up the database, I got ORADISM warnings in the alert log and saw that the Total SGA allocated was 5GB. So things are not working as documented, Oracle is only supposed to allocate the actual SGA component used (<3GB).

Also, I started getting 'Unable to grow stack space for process ...' messages in my /var/adm/messages file. I have 4GB of swap allocated, even if Oracle pre-allocated 5GB, why would the machine run out of memory? There is nothing else on the machine except this Oracle instance.

I went to Metalink and saw lots of confusion and frustration regarding this. See the Note and Bug mentioned above.

Not once did Oracle support even hint that this might be a Solaris issue. No mention of the Sunsolve link, Solaris kernel patch, nothing. Very dissapointing.

Did the Solaris kernel patch work for everyone out there? After applying the patch, do we still need to do the stuff with /etc/security_attr, chmod 510 $ORACLE_HOME/bin/oradism, chown root:dba $ORACLE_HOME/bin/oradism?

Thanks

Search metalink for oradism solaris and browse the Forum discussions, you will get a feel for what I am talking about. Support keeps referring customers to docs, the actual behaviour observed is different, and there is a lot of confusion.

As far as Oracle error is concerned, I get this in my alert log

WARNING: -------------------------------
WARNING: oradism not set up correctly.
Dynamic ISM can not be locked. Please
setup oradism, or unset sga_max_size.
[diagnostic 0, 16, 600]

One of the Oracle techs refered to Note 417366.999 but this doesnt seem to be open to public.

"sga_max_size does some actual checking (and depending on OS actually does RESERVE the space -- as documented) to ensure we can grow to 5 gig -- eg: windows).  so perhaps this was perfectly acceptable and reasonable"

That might be so, but Solaris 8 and up is special, it is treated differently. Read

http://sunsolve6.sun.com/search/document.do?assetkey=1-9-72952-1&searchclause=Patch%20Club%20Report

Also, from the referenced note,

"In Solaris 8, thanks to DISM support (Dynamic Intimate Shared Memory), it is possible to avoid the above limitation and allocate the shared memory segment such that only the active granules in the SGA are located in physical memory. All unused granules will be stored in swap until needed"

It clearly says that DISM is a new feature in Solaris 8 and Oracle 9i is the first major user of that feature. Basically, yes, at instance startup, Oracle should indeed check if it can allocate sga_max_size, but it should really only use up the sum of the components (~3G in my example above). 

Clearly, it doesnt do that. 'top' and 'ipcs' show a SEGSZ of 5.1G in my example. 

Whether the reason for this is a Solaris bug (mentioned the kernel patch) or Oracle bug is unknown, but Metalink's silence on this is troubling.

"stack is very different from pga, sounds like your solaris system itself (stack are local variables -- oracle does nothing to allocate stack, we just call functions) might be "not properly configured"?"

Well, my system has 8GB physical RAM (prtconf -v|head), 'swap -l' tells me that I have one swap partition of 4GB. That still doesnt explain why after Oracle did whatever it did, simple commands like

SQL> !date
/bin/ksh: no space -- something like this, dont have verbatim details at hand

What Solaris configuration are you referring to? 

"ok, so i refer to the note that starts with: "Please Note: This does *NOT* apply to Oracle9i Release 2. (9.2)" and I'm left wondering why i'm reading it since you said "9204""

Yes, I am on 9.2.0.4

Its a little misleading. The note says " As documented in the 9.2 Unix Admin Guide, the Installer in 9.2 automatically sets it up in $ORACLE_HOME/bin/. Hence, explicit action should not be performed to move this utility for 9.2"

Yet, the instructions do stuff like

mkdir -p /opt/Oracle/sbin/
mv $ORACLE_HOME/bin/oradism /opt/Oracle/sbin/oradism
chown -R root /opt/Oracle
chgrp -R dba /opt/Oracle
chmod 550 /opt/Oracle /opt/Oracle/sbin
chmod 510 /opt/Oracle/sbin/oradism
Add a line to the following files:
/etc/user_attr:
oracle::::type=normal;profiles=Oracle DISM mgmt
/etc/security/exec_attr:
Oracle DISM mgmt:suser:cmd:::/opt/Oracle/sbin/oradism:euid=0
[8] ln -s /opt/Oracle/sbin/oradism $ORACLE_HOME/bin/oradism 

The 2 things dont really have anything to do with each other. Even if the Oracle Installer puts the oradism binary in $ORACLE_HOME/bin, are all the steps necessary in 9.2.0.4 or not? Thats not clear.

Anyway, even after I did all the steps mentioned above, it still allocated 5GB at instance startup.

I will apply the Solaris kernel patch mentioned in the SunSolve note and see if that changes the behaviour.

One more question, if you are still with me so far. I know we discussed the pga_aggregate_target in another thread, but in my init.ora example above, I set PAT to 3GB. This 3GB is NOT allocated at instance startup, right? I think not, but if it were that might explain why I was running out of stack space (since sga_max_size + pga_agg_target = physical RAM)? Or am I missing something?

Thanks 

<quote>
such that only the active granules in the SGA are located in physical memory. All unused granules will be stored in swap until needed
</quote>

Right, the Sun doc also says Finally, since DISM memory is not automatically locked, swap space must be allocated for the whole segment. This remaining difference between ISM and DISM is unlikely to be a major issue, though, given the capacity of modern disk drives

"only the active granules (of what you have about 3gig) are in real memory, the rest is backed up by swap"

Right, that matches up with what the Sun docs say above.

"so, is it still "clearly not doing that". how did you tell from top and ipcs that it was real or swap?"

Hm, you are right, I didnt. Is there a way to tell using Unix utils how much is real and how much is swap backed?

Also, as I said, my 'swap -l' is 4GB and sga_max_size was 5.1G, that must have caused some trouble for the system since the swap was all taken up by the Oracle instance. But if the real memory usage was only 3G leaving 5GB of real memory for the OS, I dont understand why any other process would need swap?

Also, as per the Sun doc, that WARNING I posted earlier is due to the oradism binary not getting euid of root. I did the RBAC stuff (role based access control) by modifying the /etc/security/... files so I dont understand this.

But it also says that later Oracle releases dont use the RBAC stuff and simply make the oradism binary suid-root. Let me try to do this and see what happens.

Anyway, things are looking bleak in this regard for Solaris 8/Oracle 9iR2. The Sun doc clearly says that

On Solaris 8, DISM should only be used if Solaris Patch 117000-05 is installed. Even once the patch is installed, DISM should be avoided where performance is critical. Use of DISM can cost up to 10% in performance compared to ISM for SGAs up to 8 Gbytes in size (although your mileage may vary depending on your circumstances!). Sun recommends avoiding DISM on Solaris 8 where the SGA is larger than 8 Gbytes, or where CPU utilization is typically greater than 70%

Not sure what exactly that 117000-5 patch is supposed to address in all this but guess it is worth a shot.

Thanks

From oracle online doc from the link you provided above in this thread,

<quote>Dynamic SGA allows Oracle to set, at run time, limits on how much virtual memory Oracle uses for the SGA<quote>

I have a basic question.
(1) What does virtual memory mean? Is sga_max_size going to be allocated in the RAM that I got? Say, I have 1G RAM. I set sga_max_size=700 MB, assuming My current sga size is 500M, is sga_max_size of 700M is preallocated in my RAM?
(2) Does Oracle check if I specified a value for sga_max_size that is larger than the RAM that I got?
Thanks.

Tom,

we have recently created a new web-based application that runs on Solaris/Oracle 9i.

Our temp table space is showing to be 99.83%used. Does Oracle automatically allocate all available memory at startup and therefore this is not an issue? or is this related to the solaris bug that needs to be fixed.

thank you

Thank you, Tom.

This was very helpful.

Hi tom,

Here is my situation. I am running mts with RAC and
my configuration are as follow:

session 665
sga_max_size 1209083916
share_pool_reserve_size 1216381
share_pool_size 243269632


Do I need to increase my memory? Does it
seem reasonably to you?

large_pool_size 142,606,336

I am asking these questions since NT non_page pool is
getting depleted.

ORA-27300: OS system dependent operation:IPCSOCK_Send failed with status: 10055
ORA-27301: OS failure message: An operation on a socket could not be performed because the system lacked suffi
ORA-27302: failure occurred at: send_3
ORA-27508: IPC error sending a message

We got the big database machine soalris 8.0 sparc 4.1, 16 cpus and 64 GB of memory for production. We need only one database on that. How we use this big memory, as if do not allocate all to oracle it might be waste and shame for DBA. What is best place to give memory? System is going to be OLTP Java based and not going to be that resource intensive. When we looked to system test enevironment we can see that developer very well used the bind variables and PL/SQL code most of the time. What special oracle parmeter (other than the bsaic one) will be worth to test? Please guide.

Tom,

Due to the insufficient memory i modified the Semaphores value according to metalink info. Do i need to restart the machine? since its sun...

Cheers,
sikkandar.

Tom,

Due to the insufficient memory i modified the Semaphores value according to metalink info. for creating new database. Do i need to restart the machine? since its sun with other 2 more DB running!

cheers,
sikanthar.

1) Shared memory means physical memory or virtual memory ?
2) It is documented that SGA should be in physical ram. Suppose if physical ram is not sufficient then Oracle instance will
be up if I use ISM , not DISM. Lets say I have 1g ram and I have 3g swap space in solaris box and I did not use
sga_max_size. So it will use ISM , not DISM. here size of my SGA is 1.5g . So in this case instance will be up ?
3) If the instance is up then , then it means that it uses virtual memory and shared memory refers to virtual memory. If that
is the case then what is the basic difference between ISM and DISM ? Becuase i know that DISM uses swap space where ISM
does not use swap space.

Please help me in clearing this doubt.

Thanks
Deba

It appears as though the majority of the information here relates to Oracle 9i and the oradism process. I have just created an Oracle 10g instance on Solaris 10-x86 and already my alert log is full of messages like:

DISM started, OS id=11536
WARNING: Detected dead oradism process.

I have checked metalink and can find nothing related to this issue on solaris 10 and have googled the error message with no success. Humbly I ask for whatever assistance you can provide. On metalink I did find a reference to a bug 2175916, but the link points to a page "which cannot be displayed".

What do I do now? It's not generating trace files that seem to relate to the issue and I am not sure where to investigate further.

Any assistance is greatly appreciated.

sun states on its sunsolve website that since the oradism process restarts, this is not an issue. unfortunately i receive this message in the alert.log:

WARNING: Detected too many memory locking problems.
WARNING: Performance degradation may occur.

help?

i did enter an SR on this issue. unfortunately oracle's response was to open an issue with sun. this used to happen quite frequently in the early 90s but then came VOS. is the veritas/oracle/sun initiative no longer together?

so i am back to oracle says it's sun and sun saying it is oracle.

if you do hear anything or come across anything that could remedy my problem, please let me know.

thanks -- and am enjoying your new book immensely and waiting (im)patiently for the sequel.

Tom,

What is the save way to set sga_max_size and pga_aggregate_area on Windows?

Windows can only address max of 2GB memory. I don't want to waste any of this 2GB so I try to set my max_sga_size + pga_aggregate_area so close as possible to this 2 GByte limit. I had the parameters set to 1,7 GBYTE to max_sga_size and 0,25 GB to pga_aggregate_target. Some hours after system start the application got ORA-04030 error. So I was unable to connect to Oracle - listener could not start a dedicated process. After reboot I set the parameters to 1,6 and 0,25 GB. This time I have the same problem after two days.

What is the save way to set this both parameters on Windows.
Again I will not waste much memory. How much memory I should subtract from max of 2 GB.

Another question is sga_max_memory exactly the sum of large, java, shared pool, db_buffer_cache, fixed sized and redo log buffer in dedicated server or should I substract some memory for Oracle overhead. If yes how much?

If we don't use java procedures stored in the database as well as shared_servers and paralell execution - is it okay to set java_pool_size to zero and large_pool to something about 40 MB (we use rman).

Thanks in advance

Hi Tom,

Please help me in understanding the following....

I have 8 GB of RAM for Oracle on Solaris.

At instance level the following parameters are set:
SGA_MAX_SIZE=4GB
PGA_AGGREGATE_TARGET=2GB
WORK_AREA_SIZE_POLICY=AUTO

At session level I have set the follwing:
WORK_AREA_SIZE_POLICY=MANUAL
HASH_AREA_SIZE=2GB

Now my questions are:

1> What is the Maximum memory that can be used by Oracle at any point in time? Is it 6GB (4+2)?

2> If the answer to the above question is yes, then will the HASH_AREA_SIZE mentioned at the session level be allocated from PGA_AGGREGATE_TARGET memory?

3> What will be the SORT_AREA_SIZE value for the said session?

4> If parallel queries are run from that session, will each PQ process have 2GB of RAM for HASH_AREA_SIZE?

Hi Tom,

Thanks for your time. But I could not understand few points from your answer...

1) infinite - you need to understand the number of sessions you have, what they do.

-- Are you talking about PGA overallocation memory only? If not, what condition will cause that inifinite memory allocation?

If you left everything AUTO, it would try to stay (try, TRY) under 6gb. If you had lots of sessions that did things (like fill up really big plsql tables in memory for example), it might not be able to do that.

Just curious to know, where does Oracle store the PL/SQL tables and other variables? Inside PGA/SGA?

If you go auto - every hash operation could take up to 2gb in your example, have 100 hash operations and you might try to allocate 200gb of memory.

In case of AUTO PGA is n't there a limit to HASH_AREA_SIZE for a session, like SORT_AREA_SIZE, which is 5% of PGA_AGGREGATE_TARGET?


My other question is :

If parallel queries are run from that session with manual PGA set (HASH_AREA_SIZE=2GB) then will each PQ process have 2GB of RAM for HASH_AREA_SIZE?

Thanks tom for nice explanations. With your explanations/answers many DBAs have been benefited tremendously. Hope you will continue to help the same way in future as well...

Have one query on the last part of your answer...

PQ execution servers do not inherit all session settings like that - they are processes that have their own sessions, they would use auto (the global, init.ora) settings.

Let's assume that I have a query with does hash join of very huge tables. Because of that I chose to set the HASH_AREA_SIZE for that session only(Instance level PGA is auto). Now if the tables are partitioned ones and I have multiple CPUs on the server, then in order to make the join faster I use PARALLEL hint to enable parallel execution.

Now the issue is if the PQ processes use the global setting for PGA(which is Auto in this case). Then my query may not take advantage of the huge HASH AREA that I set for that session.

Does this mean we should not use this combination?

Hi Tom,
When I query v$sga_dynamic_components, I could see shared pool, large pool and buffer cache. But not java pool and log buffer. Can you explain the reason behind this? I have JAVA_POOL_SIZE specified in the init parameter.
I am using Oracle 9.2.0.6.

Thanks
Sandi

ops$tkyte%ORA9IR2> select component from v$sga_dynamic_components;

COMPONENT
----------------------------------------------------------------
shared pool
large pool
buffer cache




ops$tkyte%ORA10GR2> select component from v$sga_dynamic_components;

COMPONENT
----------------------------------------------------------------
shared pool
large pool
java pool
streams pool
DEFAULT buffer cache
KEEP buffer cache
RECYCLE buffer cache
DEFAULT 2K buffer cache
DEFAULT 4K buffer cache
DEFAULT 8K buffer cache
DEFAULT 16K buffer cache
DEFAULT 32K buffer cache
ASM Buffer Cache

13 rows selected.

Hi Tom. I hope this is a relevant followup.

On Windows each process has a 2Gb limit for the virtual memory it can use (unless you use /3Gb on boot). I have a database with SGA_TARGET=800M, PGA_AGGREGATE_TARGET=200M running on Windows.

Currently using "pslist -m oracle" (sysinternals) I get a working set of 1Gb ish (as expected from SGA+PGA), but my virtual memory is showing 1.5Gb and increases day on day.

Questions
---------
1. What does it mean if virtual memory > working set?
2. Since this is increasing, will I not get ORA-4030 once
I hit 2Gb-ish?
3. Does the fact that the VM is much bigger than SGA+PGA mean there is a memory leak, either in oracle or my app?

Thanks

Hi Tom,

Sorry. I obviously wasn't clear in my description.

I know that the amount of memory used by oracle.exe on windows will always exceed SGA + PGA since it also contains the shared dll code etc.

I also know that the memory used by oracle.exe can and will exceed SGA + PGA target, since the PGA target is a target and can be "blown out" by allocating lots of memory in PLSQL code etc.

However in my system, SGA+PGA matches up pretty well with the "working set" memory for oracle.exe, but the "virtual memory" for oracle.exe is 1.8Gb, more than 600Mb larger than sga + pga. Not only that, but this value is increasing daily, whereas the working set is stationary.

See below for a dump from pslist

pslist v1.28 - Sysinternals PsList
Copyright ® 2000-2004 Mark Russinovich
Sysinternals

Process memory detail for *********:

Name VM WS Priv Priv Pk
oracle 1848972 1061704 1059584 2960812

Is this normal and if so, what does it mean when virtual memory far exceeds working set?

I don't think it can be reserved memory since SGA_MAX is also 800Mb. Equally when I restart the instance, virtual memory is approximately working set. It's only once the database is serving requests that the virtual memory increases.

It appears that every page access by mod_plsql causes the virtual memory to increase slightly, and since we get over 10k pages a day, that small increase adds up and once the virtual memory hits 1.8 / 1.9Gb we get 4030's.

As ever, I guess the solution is a "small, reproducible test case"...

Thanks for the input

All comments to date are "bs" !!! Here's how to fix the problem based on Metalink note 374367.1 (1) cd $ORACLE_HOME/bin (2) chmod 4550 oradism (3) chmod g+s oradism (4) chown root:dba oradism (5) bounce the database I've applied this to all our servers (9i, 10g, and 11g) and the problem is fixed.

Hi Tom,

I'm very confused about virtual memory usage in Oracle on Windows (2003). Basically, the virtual memory I see after the database has been in use doesn't come even close to the sum of sga / pga / oracle binaries and keeps going up (forcing us to restart the database once it reaches 2.7Gb). As a simple example

Oracle Database 10g Release 10.1.0.5.0 - Production

SQL> show sga

Total System Global Area 1048576000 bytes
Fixed Size 792728 bytes
Variable Size 560195432 bytes
Database Buffers 486539264 bytes
Redo Buffers 1048576 bytes

SQL> select sum(pga_max_mem) from v$process;

SUM(PGA_MAX_MEM)
----------------
393896670

So we have a 1Gb SGA and a 400Mb PGA at maximum. Yet, Windows reports

Process memory detail for *****:

Name Pid VM WS Priv Priv Pk
oracle 8512 1906332 1292932 1293872 1318900

and the VM value keeps increasing.

Can you explain how virtual memory gets used? Would this increase due to applications just reading data?

Thanks

Hi Tom,

I'm posting this as a followup to my previous comment, in the hope that it saves someone else a lot of head scratching in the future.

It turns out there is a known bug in Oracle 10gR2 (and we have now confirmed in in 10gR1 as well) on x64 chips whereby threads are not cleaned up correctly in the Oracle JVM after the call completes leading to "zombie" threads which take up memory but never get cleaned out. This means that on an x64 windows platform the Virtual Memory of the Oracle.exe process increases continuously if you use the JVM.

What do you need to hit this bug
--------------------------------
1. 64 bit chip (note: not 64 bit OS - we hit this on 32 bit)
2. 10gR1 or 10gR2 (note: supposed to be fixed in 11gr1)
3. Windows OS (2003 in our case)
4. Run Java in the database

Symptoms
--------
1. Virtual memory of Oracle.exe increases continuously
2. If you run VMMap (sysinternals) you will see a massive allocation for Thread Stack space
3. The thread stack breakdown will show lots of threads without an associated thread id

Solution
--------
1. Go onto metalink and read 1062406.1

The solution is an undocumented parameter and I am therefore not about to publish it in case somebody decides to set it without being advised to by Oracle Support.

Hope this is OK to publish here Tom.

Hi Tom,

I just found this topic (interesting topic) and would like to add some precisions (and questions...) because I'm actually dealing with the same kind of memory leak.
When I use PSLIST -M to see the virtual memory of the Oracle Process, it's always growing
and growing till it reaches the limits Windows authorizes... And the instance crashes, of course.
On the last message of this topic, we are advised to do what the Metalink note 1062406.1 says.
But in my case, it did not work.
We don't have Java applications, but Php or perl ones.
The database (whose memory is flying away) is on a windows 2003 server (32 bits), it has a sga_max_size of 1400 M (sga_target=1200 M), and a pga_aggregate_target of 102 M. The database release is Oracle 10.2.0.4.
I have put the /3gb parameter on the server, and it allows me to use about 2800 M of memory for the oracle process. So when the oracle process virtual memory reaches 2800 M, it crashes without a word.
So my question is ... what can I do to prevent my database from crashing (without upgrading my database ... my customer is not quite ready ...)
Thanks alot for all you are doing for us, poor "young" dba's ;-)