Re: 9.0.2 64-bit on redhat 3.0r2 AMD64 (4-way) 17G RAM

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<412fc044$0$11963$afc38c87_at_news.optusnet.com.au>...

> What you've done wrong is to want to use an 8GB SGA in the first place. Pace
> other threads in this group, it is not obvious that extra memory must
> result in better performance. Quite the opposite.

Thanks. The general advice on the 'net is to get half the RAM for the db, and I was trying to do this. Now I find that this wisdom is wrong, which is OK with me as long as I get it right.

> 24 MEG? You jest, surely. The shared pool generally ought to be much larger
> than that. Only by tuning can one say how large, though.
>
> > shared_pool_reserved_size is 2M
>
> Do you ever get ORA-04031 errors? I'd expect you to with this sort of
> setting.

I didn't like these settings either.

When they moved to 9, 64-bit, they kept the oraSIDinit.ora from their 8.1 setup. The problem was that this setup did not perform well under 8.1, much less 9. I am trying to tune it, and all the advice I am getting elsewhere is "crank the buffers," "use as much RAM as you can," etc. So I am trying.

For instance, the formula for using 50% of RAM is right off Oracle's site. Furthermore, they say that PGA = RAM - (RAM for SGA + RAM for OS), right in the docs for pga_aggregate_target, which is rather misleading if the info here is correct.

> > block size is 2048
>
> Why? You're using Red Hat.

True, but the db was created 2048 and there is nothing I can do about it. Not that I have not tried.

> First, tune the parameters which are obviously wrong. Second, establish the
> real need for a humungously-large buffer cache before trying to achieve
> one. Whilst it is true that a certain well-known author will advise you to
> throw memory at your database till the cows come home, like much else he
> writes, that advice is bunkum.

This is what I came here for, and thanks. We tyros are stuck with well-known authors for advice; I am not going to be a DBA, nor can our organization afford one. I started doing this when I realized that the vendor wasn't going to change anything--the performance was abysmal.

> You may want to read up on, for example, the basic
> concepts guide available at http://tahiti.oracle.com before you go much
> further.

Great! Thanks again.  

> Finally... despite all the information you included in your post, it's
> actually not telling us much. For example, is this a production system, or
> a test system?

It's a test machine--I can do what I want with it. When I get it right, it will go into production. OLTP. It is maybe 6G of data, and transaction-heavy. It was a dog in 4G of RAM.

I will post the entire init.ora, if that will help. To eliminate weirdness, I will go back to the original one and submit it for critique. I can then make changes (if any) from there.

Thanks,

Steve Received on Sat Aug 28 2004 - 04:39:40 CDT