Memory / SGA question

[dgibbs_dba]

We currently run VMS 7.3, Oracle 8.1.7.4 OPS; box has 12gig of memory; SGA around 4gig; new server has 32gig of memory; Oracle is only thing running on servers. Should I increase SGA - Shared Pool and DB_BLOCK_BUFFERS - all my stats look good > 98%. Any suggestions will be appreciated. Is it true that having a too large SGA will cause performance issues?

Thanks.

[slimdave]

Maybe you have a 98% hit ratio because of bad SQL -- maybe because of a lot of hash joins. Whatever the reason, stop thinking about buffer cache hit ratio's, and start measuring wait events instead.

Large SGA used to be a potential cause of problems, but not anymore.

[abhaysk]

Originally posted by slimdave
Large SGA used to be a potential cause of problems, but not anymore.

Well can you please get a link where it says having large SGA is not a problem from 9.x.. Or rather give a logical analogy?

Well i have seen in some of the instances (9.x) where SGA was more than 3+GB were performing poor (DB Cache was 2.5+GB)... and after reducing it to 1GB DB was performing good.

Abhay.

[slimdave]

Originally posted by abhaysk
Well can you please get a link where it says having large SGA is not a problem from 9.x.. Or rather give a logical analogy?

Well i have seen in some of the instances (9.x) where SGA was more than 3+GB were performing poor (DB Cache was 2.5+GB)... and after reducing it to 1GB DB was performing good.

Abhay.

I recall that the problem was to do with the way that blocks in the sga were "indexed" internally -- if there were too many in memory the it took too long for them to be scanned.

[abhaysk]

didnt you face any problems with db cache more the 2+ GB? -- On 9x

[slimdave]

Originally posted by abhaysk
didnt you face any problems with db cache more the 2+ GB? -- On 9x

Who me? Nope

[jodie]

Originally posted by abhaysk
didnt you face any problems with db cache more the 2+ GB? -- On 9x

That was me, Abhay - but it was on 8.1.7.