Impact on Excessive memoryer to SGA

All posts relating to Oracle database administration.

Moderator: Tim...

Impact on Excessive memoryer to SGA

Postby sivakumarocp » Mon Jul 22, 2013 2:01 pm

Hi Tim,

I would like to know, is there be any impact on the DB performance if we allocate more memory to SGA.

2. regarding cursor_sharing parameter
one of our prod db is running under 11.2.0.3, and CURSOR_SHARING parameter is set to EXACT, in that database they are using Bind variables and Literals
I would like to suggest them to use FORCE instead. (as this FORCE setting internally assign bind variable for literals). if i do so will there be any impact on the database.

on the same DB (point #2) we do get wait event related to "Concurrency" Issue, upon checking we found there was latch issue on Library cache.
1. I would like to know why the concurrency happens
2. Is there any query to get detailed info about Latches.
Regards
Sivakumar.A
www.sivakumardba.com
sivakumarocp
Advisor
 
Posts: 256
Joined: Sat Jun 27, 2009 3:53 am
Location: India

Re: Impact on Excessive memoryer to SGA

Postby Tim... » Mon Jul 22, 2013 3:50 pm

Hi.

1) Adding more memory to the SGA may improve performance as more blocks can be held in the buffer cache. This will allow future block reads to come from memory, thereby improving performance. This relies very much on similar blocks being requested repeatedly, like you get in OLTP system. If each query is pulling back unique rows, then adding memory to the SGA will not necessarily improve performance. If the extra memory is taken up in the shared pool, holding lots of statement with literals, then the extra SGA will be of no benefit.

2) Are they using literals because they are too lazy to use bind variables, or was it a concious decision? It is very common for data warehouses to use lots of literals because each statement is unique and they do not expect SQL reuse. Adding bind variables in this case would not help SQL reuse and may reduce performance because of the way the optimizer deals with bind variables.

If this is an OLTP system and they would like better SQL reuse, the cursor sharing can help, but you have to be careful about the affects of bind peeking. Some statements may perform worse, especially those with skewed data. You have to do a full system test to determine the affect of the change.

3) Sounds like the system is doing lots of hard parses of new SQL statements. Each hard parse requires a latch on the library cache, since the hard parse results in an amendment to the contents of the library cache, which gets reported as a concurrency wait.

The best place to get the latch information from is the AWR report, or statspack if you don't have the Diagnostics and Tuning Pack option.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 2 guests

cron