SGA versus sharedpool

[matthew00]

Hi All,

I am on 10.2.0.4 and I have a quick question to clear out my doubt. when we use SGA_TARGET, we would let Oracle to manage its own memory so we don't need to specify the size for shared_pool, is it correct?

1. why from time to time, I have been reading and noticed that sga_target is used and shared_pool is also used and I bit confused.
2. there is a sql statement that was executed over 5 million times over the period of the batch job was ran, it is used bind variable, I wonder if there is anything esle that we can do to make it run faster.

Thanks all

[PAVB]

1- If SGA_TARGET is specified then SHARED_POOL_SIZE (among others) will be automatically sized.

2- Have you traced the query?

[matthew00]

thanks and regarding to number 2. I have trace the sql and the plan is very sufficient as I mentioned I used bind variable. the only thing I just want to make sure that it will do soft parse (memory) rather than hard parse. are there any parameters that I can tune?

Thanks,

[gandolf989]

there is a sql statement that was executed over 5 million times over the period of the batch job was ran, it is used bind variable, I wonder if there is anything esle that we can do to make it run faster.

If it were me, I would just run the query less often...

If this query really runs 5 million times, then it needs to be really efficient. You can also create a temporary table with the data you want in a demormalized format, then just loop through the query.

If you can do something with a single SQL statement then do so, otherwise look at PL/SQL collections and or temporary tables.

[matthew00]

PVAB,

so SGA_TARGET is specified, then those number that we set for SHARED_POOL_SIZE (AMONG OTHERS) are irrelevant even it was set. In other word, Oracle will ignore those number and manage by itself?

thanks,

[PAVB]

Yes. If SGA_TARGET is set to anything other than Zero then Oracle will automatically manage SGA memory allocation.

[ebrian]

so SGA_TARGET is specified, then those number that we set for SHARED_POOL_SIZE (AMONG OTHERS) are irrelevant even it was set. In other word, Oracle will ignore those number and manage by itself?

If you have any of the automatically tuned SGA components set to a non-zero value, then this will set a minimum value that Oracle will use for that particular component.