SGA versus sharedpool

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

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

2- Have you traced the query?

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,

Quote:

---

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... :p

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.

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,

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

Quote:

---

Originally Posted by matthew00

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.