Memory usage on HP UX(Oracle 8.1.7 & Siebel)

My production database 8.1.7 is running on HPUx 11.11. The total physical memory on the box is 16 Gig. Around the peak time I'm always noticing about less than 100 meg free memory. My SGA is 7.4 Gig(Fixed 100kb, variable 1.0G, DB Buffer=6.4G, Redo=2M). Each process is using up approx 5-7 meg memory. SO during the peak time if there are approx 700 process my total memory usage is about 15G + and so I am left with about 100 meg. I also have 4 swap devices with 4 G each contributing 16G.

My question is ? Is there a way I can redistribute the memory usage or is it inevitable that we need more memory on the box ?? Can anyone give me a way to recalculate the memory usage for oracle and user processes ?

In terms of the shared pool, you could see if this can be reduced by taking a statspack report during peak uasage. Check the Memory Usage% bit of the Shared Pool Statistics section and see if this is significantly under used. Alternatively execute this:

SELECT pool, name, bytes / (1024*1024) FROM V$SGASTAT WHERE name = 'free memory'

Quote:

---

Originally posted by hacketta1

SELECT pool, name, bytes / (1024*1024) FROM V$SGASTAT WHERE name = 'free memory'

---

I have check for the shared pool and it show about 50 - 100 meg free space which is OK. I am trying to find the right buffer cache size. Currently it is set to 6.4 G.

Also I am seeing a lot of inactive oracle processes but each one of them is using up around 7 meg memory. Guess that's how siebel is set up. Since there are about 1000 process(may be 200 of them inactive) uses a total of 7 G memory.

6.4G of buffer cache? how big is your database?

Quote:

---

Originally posted by marist89
6.4G of buffer cache? how big is your database?

---

700GB

check your hit ratio and try to reduce the db buffer cache.

How did you find the memory usage of each oracle process? Using top? Top mis guides..

Quote:

---

Originally posted by nagarjuna
check your hit ratio ...

---

Time for the asbetos underpants.

* ahem *

Hit ratio is a waste of times. Don't even think about it.

Quote:

---

Originally posted by slimdave
Hit ratio is a waste of times.

---

In this case I don't agree - if the hit-ratio comes from a representative sample of activity and you monitor it as you reduce the buffer size, it should give an indication of when you've gone too far. End-user response time is of course a better statistic, but harder to measure.

(Hit-ratio is also a good indication of your success on "International Smack a Developer Day").

Quote:

---

Originally posted by nagarjuna
check your hit ratio and try to reduce the db buffer cache.

How did you find the memory usage of each oracle process? Using top? Top mis guides..

---

I check the RSS(Resident Set Size) on glance for each process. It is suppose to give the total memory usage for each process. My Free Memory is going down to less than 60 meg during the peak hour(ususaly during 2:00pm - 3:00pm). I kill some of the most memory consuming porcesses to free up the memory. ANy advice on how to resolve the issue and not just ask for more memory. The DB buffer Cache was increased gradually to imporve performance. I guess I p'bly can decrese to some extent but not a whole lot.

The other thing is any connection from siebel evenif not active on oracle still uses the memory on the Box.

Quote:

---

Originally posted by DaPi
In this case I don't agree - if the hit-ratio comes from a representative sample of activity and you monitor it as you reduce the buffer size, it should give an indication of when you've gone too far. End-user response time is of course a better statistic, but harder to measure.

---

I think that you missed out "... and if all of your SQL is perfectly tuned ..."

Quote:

---

Originally posted by slimdave
I think that you missed out "... and if all of your SQL is perfectly tuned ..."

---

No. It can answer (approximately) the question "how small can I make the buffer given the current state of the SQL". Improving the SQL might allow it to be reduced further - but as this is 3rd party soft that might not be possible.

P.S. (added later) If not the hit-ratio, then what would you measure?

Quote:

---

Originally posted by dbasupuser
The DB buffer Cache was increased gradually to imporve performance. I guess I p'bly can decrese to some extent but not a whole lot.

---

A small percentage decrease in buffer size represents a big percentage increase in free memory. If I understand your 2nd post correctly, going from 6.4GB to 6.2GB would provide memory for 30 more users.

Quote:

---

Originally posted by DaPi
P.S. (added later) If not the hit-ratio, then what would you measure?

---

I would look for the most resource-intensive queries at the time when the system is running poorly, and try to address those first.

edit: You have to consider what action the hit ratio allows you to take also ... what if the hit ratio is 99%? Or what if it's 30%? I don't see what action can be taken from knowledge of the hit ratio alone.

Quote:

---

Originally posted by slimdave
I don't see what action can be taken from knowledge of the hit ratio alone.

---

Ah, but it's not just a question of it "alone". nagarjuna is proposing to monitor it as the buffer size is reduced, so as to be able to stop before the users complain. The results might be like:

Code:

---

Buffer      Hit Ratio
6.4GB        98%
6.2GB        96%
6.0GB        94%
5.8GB        82%

---

In fact this experiment has already been done:

Quote:

---

The DB buffer Cache was increased gradually to imporve performance

---

so dbasupuser already has an idea what a "good" hit ratio is for his application (95% or 30%) and how far he can deviate from that.

I'm certainly not saying that this is the only thing to do, but it might be a quick and easy way to free up a few 100's MB and should not be dismissed.

Jumping into the Long Long Post ....

It's question of overall Tuning of the Database, I can recommend check for Full Table Scans happning on Big Tables (I feel there will be quite a few Large tables, as size is 700 Gigs). So introduce Index to avoide Full table scans if happning on Large tables.

The real crux is finding out what are the costly queries running at peak time. What are the waits happning, How is the I/O distributed. Is it a Read-Intensive or Read-Write Intensive application ?

Lot's many question involved with it.

Good luck

Quote:

---

Originally posted by slimdave
Time for the asbetos underpants.

* ahem *

Hit ratio is a waste of times. Don't even think about it.

---

yo man, you are right. Even I gave up tuning the database on hit ratio basis. Hit ratios are good point to start with.

coming to what I said in this thread with respect to the problem posted..

The buffer cache is too big. I just wanted to know whether his hitratio is 99% or something like this.. There are situations when you may find that increasing the buffer cache beyond a limit does not improve the hit ratio. I just wanted to do the reverse here. I wanted to find out the buffer cache size at which the hit ratio is optimal. Take this example

buffer size hit ratio
6.5 G 98%
6.0 G 97%
5.5 G 96%
5.0 G 95%
4.5 G 90%
4.0 G 85%

In this case, we can say that 5G is the optimal size of buffer cache as the hit ratio growth is steep till that point. Isnt it? This is what I wanted to find out.

For others,

yes, we have to check our top queries also. v$sqlarea is the view.

dbasupuser,
check out your "sort_area_retained_size"

Check out for
cursor_space_for_time,
open_cursors,
session_cached_cursors,
max log members

and few more parameters that affect the size of your PGA.