help! hpux∨acle

[milaren]

hpux11i&oracle9i
I noticed that so many processes of oracle,each size about 40M!
how to decrease these processes ' size?

[Hein van den Heuvel]

Aypu, Oracle is big. Fortunately most of it is shared data and text (the 1340 M), but a good chunk is private. (Stack, 'PGA').

Mostly, if it takes it, it needs it.
But you have some control trhough init.ora setting.
In the old days most of us used like 'sort_area_retained_size' params (SQL> show parameter sort).
Nowadays you are supposed to tweak pga_aggregate_target.

Please be sure to check out the Oracle doc for details. ("Database Reference", Chapter 1, Initialization Parameters)

hth,
Hein.

[Steven E. Protter]

You can change the amount of resources oracle uses by changing the inti.ora file for the database instance.

You do need ot have some idea what you are doiing or you will end up with a useless database.

I would suggest some reading at:

http//technet.oracle.com

SEP

[T G Manikandan]

Look here,every oracle connection to the database grabs some amount of memory.

I am rarely working on Oracle nowa days and on 8i,even I noticed that each connection picks up 30Mb.

Look here,
right now you should be running your oracle database with "dedicated server connection".

If you are to reduce the consumption of memory for as you have more usage of RAM then you should convert that to "multithreaded model" .

The difference between dedicated and multithreaded is that
>>dedicated,each connection takes up a individual separate oracle server process.
>>Multithreaded,define a value server process running on the server which serves the client connections.

Let me know on more info.

[milaren]

Thank you for your help!

[milaren]

Today,i find DISK utility value is 100% in Glance.
So I collected data with sar and iostat,
and find that so many physic and logic reads on filesystem /tiger01(/vg01/lvol1).
All my oracle datafiles,controlfiles,logfiles are locate in /tiger01.
what caused this?
huage sql select ?
help me.

[Victor BERRIDGE]

Hi Milaren,
By the "top" you gave, you seem to be in cluster which could explain the way you see things (less disks is easier to cope with in clusterized environment...) but I may be wrong...
For performance it is wise to put on separate filesystems the logs the data the indexes the redologs etc... but this is true if you filesytems are on separate disks, if you have in your package only one volume group with one disk there you will not get better performances by creating loads of filesystems - the performance issue is having always the same disk working.
In the case you have in your vg01 more than one disk or have another vg you can use then reorganize your files so that they lay on mutiple disks that can be accessed simultaneouly - its a bit late now but just an example: You could if have to use one vg only for your package add a disk then configure the 2 disks using lvm striping - this way you would have a 50/50 load balancing between the 2 disks, but for this you should have dual access to your disk through separate scsi controllers or HBAs
Now if you are on a SAN with dynamic load balancing utilities installed then forget all I just said about stripe in this case the solution is more smaller disks and a good repartition of the filesystems on them to have to most disks accesses...

All the best
Victor

[Jean-Luc Oudart]

Hi,

You will have to spread the IO load across different disks.
For the size of these processes, you need to make difference between shared on non-shared (private). With Glance, select one of the processes and user the "m" option for the memory report.

I also attached a doc from Oracle on SAME method to spread oracle files.

Regards
Jean-Luc

[milaren]

Victor,you are right.It's a cluster.
Jean,your attachment is helpful for me.
thanks!