MEMORY I/O CONTENTION ON ORACLE 9I

Hi all,

I have Oracle 9i running on Linux 7.3. The total ram on the box is 640M. I have the parameter copied as below and with these value for some reason, Oracle consume memory more than what I have specified on the parameter and I try to understand why. Every time , when I start Oracle, it take away 630M of ram and I have nothing running on this box, I only have oe database. please see below

db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16

open_cursors=300

db_domain=""
db_name=TEST

background_dump_dest=/home/oracle/admin/TEST/bdump
core_dump_dest=/home/oracle/admin/TEST/cdump
timed_statistics=TRUE
user_dump_dest=/home/oracle/admin/TEST/udump

control_files=("/u01/oradata/TEST/control01.ctl", "/u02/oradata/TEST/control02.ctl", "/u01/oradata/TEST/control03.ctl")

instance_name=TEST

compatible=9.2.0.0.0

hash_join_enabled=TRUE
query_rewrite_enabled=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
star_transformation_enabled=FALSE
max_enabled_roles=100

java_pool_size=33554432
large_pool_size=4194304
shared_pool_size=33554432

processes=150

fast_start_mttr_target=300

remote_login_passwordfile=EXCLUSIVE

pga_aggregate_target=25165824
sort_area_size=524288

=undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1


Could someone please tell me how to fix this problem???

Thanks so much

What does show sga say?

Total System Global Area 114364796 bytes
Fixed Size 450940 bytes
Variable Size 88080384 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

I noticed that the variable size is huge, could you please tell me why ??? and what else I need to do to fix this problem???

thanks as always JEFF

Your SGA size is only 114M. The Variable Size is mainly made up from your large pool, java pool, and shared pool. You think you are allocating about 68M, but you must remember that oracle will allocate in granules. Also, there are other parameters that contribute to the SGA size. I think this looks about right for what you have specified.

If Oracle really is taking 630M at startup, I would look at your kernal parameters. You may have the minimum shared memory segment at 640M or something.

If Oracle really is taking 630M at startup, I would look at your kernal parameters. You may have the minimum shared memory segment at 640M or something.
[/B][/QUOTE]

Thanks for your reply, this is the first time I install Oracle on Linux environement so I don't know much about the Unix side. per your advise, I might have the minimum shared memory segment at 640M or something, how to I check that??? What do I need to do ???

Please help me b/c I want to learn more about Unix.

Thanks so much Jeff

I don't know about linux, but on solaris the kernal parameters are in the /etc/system file.

See http://metalink.oracle.com/metalink/...&p_id=144638.1 for kernal paramter definitions.

Quote:

---

Originally posted by ashley75
Oracle consume memory more than what I have specified

---

Hw do you determine it's using more than you specified? With what command?

by looking at the paramter I specified above, plus my SGA.

Do you have any solutions or advises????

Thanks

The parameters you have specified above say your SGA is only 114 MB. How do you get 630 MB?

Quote:

---

Originally posted by marist89
The parameters you have specified above say your SGA is only 114 MB. How do you get 630 MB?

---

That's why I wonder

Jeff and Pando,

That's what I don't understand either. All I know is that when Linux doesn't start it take only 110M of RAM, after about one day, it goes up to 630M of ram and this is a new database, it has very few processes to it. and the memory never go down so that my database run really slow.
that's what I am confused and don't know what's going on.



[Edited by ashley75 on 07-29-2002 at 06:02 PM]

and that's why I asked with what command are you using to determine that?

Pando,

I used the TOP command

well that's SHARED segment, it's not what Oracle is using, it is shared memory segments

Pando,

so please tell me what I need to do???

the box does not have anything running on there beside Oracle

Below is the snapshot of what I am tlaking about


48 processes: 46 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 0.0% user, 0.1% system, 0.0% nice, 99.8% idle
Mem: 643708K av, 253288K used, 390420K free, 0K shrd, 44100K buff
Swap: 1518100K av, 0K used, 1518100K free 165240K cached

This is what happened after I bounce the databse an hour ago. and the amount of memory will increase from now till tomorow.

I am confused. please help me






[Edited by ashley75 on 07-29-2002 at 06:12 PM]

nothing there is nothing needed to do, it's how it works and it does NOT mean Oracle is using that memory
It's how Linux works, it tries to cache everything in memory when it can

Pando,

thanks again for all of the advise, but the database is not running as fast as I expect and the performance get worst if someone update something in the database. Below is the memory stats I got this morning:

CPU states: 0.0% user, 0.3% system, 0.0% nice, 99.6% idle
Mem: 643708K av, 564428K used, 79280K free, 0K shrd, 174416K buff
Swap: 1518100K av, 0K used, 1518100K free 223064K cached


Today, it goes up to 560M and noone have done anything to the database yet.

I am sorry to be annoyed but I like to understand what's going on with this box.

Thanks

Quote:

---

Originally posted by ashley75

thanks again for all of the advise, but the database is not running as fast as I expect and the performance get worst if someone update something in the database. Below is the memory stats I got this morning:

---

Ah, now we come to the real problem. The database is slow, how do I tune it?

Find out where your bottlenecks are. Look at your disks, are they at 100%. What does your CPU say? What does vmstat say? Are only some queries slow? Which ones?

Have you checked the alert.log file? Do you see any ORA- message? There is a bug related to memory and process about Oracle 9i R2. You might need to apply patch if that is the causation of the server problem.

Susan

Jeff,

Below is the stat of my system bottleneck:

null event 1 0 1 1
pmon timer 0 1 1
smon timer 0 1 1
rdbms ipc message 0 4 4

The CPU is idle or only being used a little not much.

I would say, not only certain query, seem like for all of query the users run.


srlhome00
Could you please refer me to the bug number as you mentioned???

thanks

If you didn't get any error message, the following info might not apply to your case.

*******************************************
Patch::2391227

ORA-04030: OUT OF PROCESS MEMORY, SUBHEAP, CONCURRENT INSERT
*******************************************

Susan

this is the only warning I got when I startup my database other than that I don't have any errors:

Mon Jul 29 16:10:15 2002
Starting ORACLE instance (normal)
Mon Jul 29 16:10:15 2002
WARNING: EINVAL creating segment of size 0x0000000007400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE

fix shm parameters in /etc/system or equivalent
*******************************************
This is the answer to your case. You need to modify your /etc/system file. This file contains information about the OS kernal. On the installation menu of 9i, you will find the related info on p2-20.

Susan

echo 671088640 > /proc/sys/kernel/shmmax

bounce the database

Hmm, imagine that, a kernal parameter problem.

Thanks Pando, Jeff and srlhome00 so much and I have the feeling that something to do with the Kernal but I am not sure b/c I am new with Linux

I run what Pando advise me to run : Log in as root and I run:

echo 671088640 > /proc/sys/kernel/shmmax

and I did bounce the database


CPU states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
Mem: 643708K av, 57416K used, 586292K free, 0K shrd, 4764K buff
Swap: 1518100K av, 0K used, 1518100K free 22920K cached



and this is after I just run analyze statistic on one schema which had about 150 tables:


CPU states: 0.1% user, 0.1% system, 0.0% nice, 99.6% idle
Mem: 643708K av, 634884K used, 8824K free, 0K shrd, 25296K buff
Swap: 1518100K av, 0K used, 1518100K free 563068K cached


The question is I could understand the memory will peak up b/c of the process I ran but how come it doesn't go down when this process finish ???

please don't be upset if I keep coming back with this issue but it does bother me.

thanks so much for all of help.

I have already told you Linux tries to cache everything in memory even your process finishes linux allocates free memory to other processes
No matter what you do you will see 640MB being used, what's the point having 640MB if you only use 100MB? Linux tries to make good use of all of them

Jeff and Pando,

I am sorry I hate to comeback this thread again, but I have a question. I still had the warning on the alert log file. please help me with this even I already update the kernel per your advise

below is the warning on my log:





Starting ORACLE instance (normal)
Thu Aug 1 09:44:14 2002
WARNING: EINVAL creating segment of size 0x0000000007400000
fix shm parameters in /etc/system or equivalent
Thu Aug 1 09:44:32 2002
Starting ORACLE instance (normal)
Thu Aug 1 09:44:32 2002
WARNING: EINVAL creating segment of size 0x0000000007400000
fix shm parameters in /etc/system or equivalent
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:


Could you please let me know what I need to do ???

Thanks

echo value /proc/sys/kernel/shmmax stops working after the server is rebooted

you have to create a script in /etc/init.d to make this change everytime your server is rebooted

Pando,

you could add the change in here

/etc/rc.local

and it will change

Any way, thanks so much for your help

you can but it is not the proper way