Configuring SGA

[sureshot]

I have a OEE 9.2 on W2K server with 2Gig memory out which 1.2 Gig will be allotted for SGA.

I have a hybrid database, 16K block size and db file multiblock read count=16. Off the top of my head I allocated memory like this:

Shared pool: 512000000
Db cache: 255000000
larged pool: 128000000
java: 128000000
PGA aggregate target: 128000000.

Are there any gotcha's or something fundamentally wrong in this configuration?

[pando]

shard pool too big

large pool used for?

java pool used for?

[julian]

Originally posted by sureshot
I have a OEE 9.2 on W2K server with 2Gig memory out which 1.2 Gig will be allotted for SGA.

I have a hybrid database, 16K block size and db file multiblock read count=16. Off the top of my head I allocated memory like this:

Shared pool: 512000000
Db cache: 255000000
larged pool: 128000000
java: 128000000
PGA aggregate target: 128000000.

Are there any gotcha's or something fundamentally wrong in this configuration?

You have too high values, except for PGA aggregate target which is low.

Large pool is needed 0nly if you use Shared Server configuration or you take backups with RMAN to tape and you have enables tape slaves.

The Java Pool needs to be around this value only if you patch a 9.2 database. After applying the patch you may reduce the value to 40-50M, even less.

[a128]

Hi,

Can you elaborate on why you think the shared pool is too big? Any general guidelines that can be followed?

Thanks,

[julian]

Originally posted by a128
Hi,

Can you elaborate on why you think the shared pool is too big? Any general guidelines that can be followed?

Thanks,

512M is too much in general. If you really have a lot of PL/SQL in the database, probably it is ok but I have never seen a database that needs to have half a gee of shared pool.

You know that oversizing the shared pool size is bad for performance?

[sureshot]

Oracle is rounding off the memory to another number. Is this because of 16K block size? 32M x 1024 = 32768000 not 33554432.

java_pool_size big integer 134217728 (128 MB?)
large_pool_size big integer 134217728 (128 MB?)
olap_page_pool_size integer 33554432 (32 MB?)
shared_pool_reserved_size big integer 26004684
shared_pool_size big integer 520093696 (512 MB?)

We are determined to use a Gig for oracle 9.2. Can someone suggest a better configuration? More database cache less shared pool? We do have some BOLBs of 30K a few tables.

[marist89]

Originally posted by sureshot
32M x 1024 = 32768000 not 33554432.

since when? 32*1024*1024=33554432

[marist89]

Originally posted by a128
Can you elaborate on why you think the shared pool is too big? Any general guidelines that can be followed?

I would love to know why as well since there are no concrete numbers to suggest otherwise...

[sureshot]

Ah..jeff reduced it to simple math.. thanks.

I have a question:

Is there any relationship between redolog file size and what a log_buffer in SGA should be? More specific, what is the size of the log buffer if the redo log file is 250MB?

Also any relation between database cache size and log buffer size?