Assign memory to Oracle with W2K3 Std. Ed.

[MITELMX]

Hi all, i hope you can help me with this.

I have Oracle 9 installed on a Windows 2003 Server STD ED. with 8 GB of RAM Memory, my problems is that windows only get 4 Gb for the application and i have problems with overconnection with the database, i have been trying to increase the page file but did not work, i am still have the same problem.
I am wondering if exist some way to tune-up Oracle, i do not know if is possible to assign more memory or maybe it is wrong confgured.

Well thanks in advance for you help and i will waiting for your help.

My best regards.

 

[SantaMufasa]

MITELMX,

First, let's determine how much memory your instance is consuming. Please issue the following command that confirms your memory consumption:

col name format a16
col value format a10
select name, value
from v$parameter
where name in ('shared_pool_size','db_block_buffers',
'java_pool_size','log_buffer');

Also, what are the major types of work that your Oracle instance is doing (e.g., Reporting, Data Entry, Data Warehousing, et cetera)? What are the symptoms that tell you that your instance needs its memory structures tuned?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MITELMX]

Hi Mufasa,
I asked to Database administrator and this was the answer:

Total 8 Gig of RAM memory

Standar memory 4 GB
share_pool_size 1056964608 1008
java_pool_size 58720256 56
log_buffer 524288 0.5
Total 1064.5
Free 3031.5
SAE db_block_buffers 262144
db_block_size 16384
4294967296 4096
Total 4096
Free 0

The main problem is that after some number of connections the database don't allow more connection and start to drop them, send an error of memory but if you see the standard memory we have 3 GB Free, so don't make sense for me.

By the way, the windows O.S is Enterprise not Std.

Any idea?

Thanks.

 

[SantaMufasa]

...after some number of connections the database don't allow more connection and start to drop them, send an error of memory...

I have never heard of such a scenario. Can you please post a copy-and-paste of the exact error message you are receiving?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MITELMX]

This is the error that i get when i try to connect to the database:

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNSrotocol adapter error
TNS-00510: Internal limit restriction exceeded
32-bit Windows Error: 8: Exec format error


I assume that is a memory error, but i asked Oracle for this error and at this moment i don't get any solution :S

Any idea?

Thanks you very much for you time.

Regards,

 

[SantaMufasa]

MITELMX,

Your overall memory consumption for your instance is a whopping 5.4GB:

select to_char((262144 * 16384) + 1056964608 +
                58720256 + 524288,'99,999,999,999')memory
from dual;

MEMORY
---------------
  5,411,176,448

Our largest Oracle instances running on Unix rarely consume 1GB of memory and their performance is just fine.

To troubleshoot your issue and to determine if the instance size is the cause of your problems, I recommend your reducing the size of your instance's memory consumption (at least temporarily) to under 1GB.

To achieve this 1GB objective (most quickly and easily, with the least negative impact on your instance), I suggest your modifying your instance parameters to these values:

shared_pool_size = 400MB
db_block_buffers = 48000

These values bring your instance consumption down to about 1.25 GB.

Re-boot your instance with these values, re-try your connections, then post your results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MITELMX]

But right now i have the next values:

shared_pool_sized = 1000 MB
db_block_buffer = 1000

and i am still have the error.

Those values are ok or i am wrong.

I am trying to realize how this stuff work.

Thanks.

 

[SantaMufasa]

Those values are ok or i am wrong.

They should be okay. (They are heavy on shared_pool_size and light on db_block_buffers, but still workable.)


Next let's confirm how many processes you are allowing. Please run this code:

col name format a16
col value format a10
select name, value
from v$parameter
where name in ('processes','sessions');

How many sessions, maximum, attempt to connect to your application at one time?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MITELMX]

We have 345 sessions and 500 processes at one time.

 

[SantaMufasa]

We have 345 sessions and 500 processes at one time.

So, MX, are these the responses to my question, "How many sessions, maximum, attempt to connect to your application at one time?" or are these the results of the query I hoped you would run:

col name format a16
col value format a10
select name, value
from v$parameter
where name in ('processes','sessions');

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MITELMX]

After run the query i get the next numbers

500 processes
555 sessions

Thanks

 

[SantaMufasa]

So, are your actual connections exceeding the limitations ("500 processes, 555 sessions")? If you approaching those boundaries, then I would boost "processes" by 50% (since, by default, "sessions" is a computed derivative of "processes").

Once you boost your "processes" parameter, please re-try your previously failing behaviour to see where we are.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[MITELMX]

How can i boost my processes? i mean exist some command or i have to configure it in some file.

 

[SantaMufasa]

The definitive answer depends upon whether you provide parameter values via your init<SID>.ora file (external flat parameter file) or via an spfile (stored internally to the database).

I'll answer as though you rely exclusively upon the standard init<SID>.ora file (and if you use an spfile, then you can make appropriate adjustments to my suggestion).

Locate your init<SID>.ora file, which, on a Windows Oracle installation defaults to your path "%ORACLE_HOME%\database". There you should find the file init<SID>.ora (where <SID> is the name of your Oracle instance). In that file, you may find an entry that says:

processes = 500

You can then change the entry to read:

processes = <some new, higher value>

(If you did not find a "processes" parameter there, you can just create one, which should then take effect upon db bounce.)

After changing a standard init<SID>.ora file, for the new values to take effect, you "shutdown immediate", then "startup" your Oracle instance. If you check the parameter values (with my earlier query against v$parameter) and you do not see the new value that you set in init<SID>.ora, then that means that your instance relies upon "spfile" parameters.

If that is the case, then you can change the parameter value in both the init<SID>.ora file and the spfile file with the following SQL command (provided you have DBA privileges ):

Alter system set processes=<some new value> SCOPE=BOTH;

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

The "Alter system set processes=<some new value> SCOPE=BOTH;" command won't work, since processes is not a dynamic parameter. You will get an "ORA-02095: specified initialization parameter cannot be modified" error. Instead, you will have to use the command "Alter system set processes=<some new value> SCOPE=SPFILE;" and then bounce the instance.

However, I suspect that the value of the processes parameter is not the issue and that SantaMufasa was on the right track earlier when he was questioning your extremely large memory allocations. Metalink note 171636.1 discusses the exact set of TNS errors you are getting, and one of the things it points out is that 32 bit operating systems can't access more than 4 GB of memory (since 2**32 = 4294967296). With an SGA over 5 GB in size, it's not surprising that you would get this type of Windows error when you try to access memory above the 4 GB limit.

I would double check the size of your SGA. Are you certain that it really has been made smaller? Perhaps you only adjusted the parameters in the spfile and the running instance is still using the larger values.

 

[karluk]

By the way, the command

show sga

should give you the current allocated memory.