Memory usage on HP UX(Oracle 8.1.7 & Siebel)

[DaPi]

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?

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.

[slimdave]

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.

[DaPi]

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:

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

In fact this experiment has already been done:

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.

[Dilippatel]

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

[nagarjuna]

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"

[nagarjuna]

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.