Bind variables and SGA_TARGET?

[ssmith]

Does SGA_TARGET take into account the use of BIND variables?

We have an 3rd party applications that doesn't use bind variables. It uses dynamic SQL. It never mattered how big you made the shared_pool_size as the SQL was never shared, therefore I always kept it low 150M, and db_cache_buffers=250m. This worked fine for the application and performance was ok.

Now we have migrated to 10g, I have set the SGA_TARGET to 400M, but we are now getting performance problems. On looking at the memory advice, it has set the DB Buffers to 130M and Shared pool to 250M. I am now assuming that the memory advice is not that clever, and doesn't take into account the application not using bind variables. Has anyone else experience this? Thanks.
Rgds. Sheryl

[hrishy]

Hi Sheryl

The correct way to solve that problem is to make use of bind variables.
However if you are no in a position to do that first find out how the mmeory is being taken from other areas of the SGA to maintain the sga_target.

In our case we had to go with a lower limit for the db_cache_size and still set the sga_target that way the db_cache_size was never resized dynamically below the lower threshold we wanted.

regards
Hrishy

[pando]

well I dont think the advisor take that sort of thing into account because the database does not know what type of application is yours, what if you really want to use literals and not binds? It's a piece of software which at the moment cannot determine your database type IMHO unless there are some magic parametr such as db_type =OLTP|DWH|MIXED

[PAVB]

Now we have migrated to 10g, I have set the SGA_TARGET to 400M, but we are now getting performance problems.

Have you migrated from single instance to RAC?

[ssmith]

Its not RAC its a single instance.

Should I set db_cache_buffers and shared_pool_size manually, instead of using SGA_TARGET? Rgds. Sheryl