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

Artis Gripemore wrote:

> Please ignore this if the questions of amatuers are not allowed here.
> I thought that perhaps the rare hardware configuration might be of
> interest, however.
>
> Thanks in advance.
>
> I have shmmax set to 8G, but I cannot get db-cache_size over 2G or
> oracle runs out of memory. What have I done wrong?

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.

> using--
>
> db_cache_size at just under 2G (and it cannot be increased without
> failure)
> pga_aggregate_target at 4G
> workarea_size_policy set to auto
> shared_pool_size is 24M

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.

> block size is 2048

Why? You're using Red Hat. Like any Linux, the thing is configurable, but I'll lay small odds that when you created your file systems you just accepted all the defaults. That means your block size should be 4K (because that's what ext3 uses by default as its own buffer size, and the Oracle block size must match the file system buffer size exactly).

> (the other settings are quite conservative, but feel free to ask)
>
> I am sure that I am doing something wrong, but I've tried a bunch of
> different sysctl.conf variations and I still get trouble increasing
> the buffers. If anyone would like to suggest a sysctl.conf
> appropriate to the hardware, or show me the error in this init.ora, I
> would appreciate it. Please ask if you need more info.
>
> Thanks again.
>
> S

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.

Third, there are parameters described in your post, which are set so bizarrely that I have to question just how much understanding of Oracle internals you have. You may want to read up on, for example, the basic concepts guide available at http://tahiti.oracle.com before you go much further.

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? Is it a warehouse or an OLTP? Are there users complaining? Or are you just experimenting? In short, there's not much context to your post, which makes meaningful answers hard to come by.

Regards
HJR Received on Fri Aug 27 2004 - 18:13:42 CDT