Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am receiving this error in a development database. It happens on some specific queries (simple inserts and deletes via .NET application). The database is used by one user, and error happens regardless for how long the database was running.

The only memory related parameter which is set is: MEMORY_TARGET=1.2G

Oracle 11.2 x64 Standard Edition One Windows Server 2008 R2

share|improve this question

Your application is probably not using bind variables, which has caused the SGA to become fragmented (it fills up with multiple copies of SQL that use literals instead of binds).

You can temporarily fix the problem by flushing the shared pool with:

alter system flush shared_pool;

There's a workaround you can try to stop it happening in the future.

Turn on cursor sharing, then bounce the database:

ALTER SYSTEM SET cursor_sharing='SIMILAR' SCOPE=BOTH;

The only real way of fixing this is to rewrite your application so that it uses bind variables.

share|improve this answer
    
"then bounce the database" ==? "shutdown - startup" ? – Atilla Ozgur Feb 1 '12 at 13:59
    
Yes, shut the database down & start it up again – Phil Feb 1 '12 at 14:00
    
problem "solved" in a strange way – spm Feb 1 '12 at 14:02
    
As I said, if the application not using bind variables is the cause of the problem, the only way to "solve" it is to rewrite the application to use them. – Phil Feb 1 '12 at 14:05
    
I will try both solutions if problem occurs again. But i don't think it applies to this case, because it happens even if it is first query after restart. – spm Feb 1 '12 at 14:07

As an addition to previous answer, it turns out that ALTER SESSION SET NLS_COMP=LINGUISTIC;ALTER SESSION SET NLS_SORT=GENERIC_M_CI; for some reason quickly exhausts memory. Problem dissapears when using another NLS_SORT.

share|improve this answer

The answer above is a little old.

alter system flush shared_pool; 

May alleviate the problem right away but not deal with the root cause. the cursor_sharing parameter now takes 2 values:

FORCE or EXACT. With EXACT, your query will have to match exactly for a cursor (sql with execution plan ) to be reused, with "FORCE", all values in your queries are changed to bind variables. This is really handy when your application doesn't use bind variables. The database will do that for you.

If you are already using cursor_sharing=FORCE. Then you need to start considering adjusting your memory allocationand depending on the memory parameters your system use, you can obtain an idea of where you should set that value with the following queries:

When SGA_TARGET and SGA_MAX__SIZE /PGA_TARGET and PGA_MAX_SIZE are used (they should match): select * from v$sga_target_advice; select * from v$pga_target_advice;

It would look like this: In my case this is a pre prod system, it won't look this good for long:

This is the SGA_TARGET_ADVICE:

The row where SGA_SIZE_FACTOR is 1, is the current setting. In a system where memory is undersized, the increase in SGA_SIZE and SGA_SIZE_FACTOR should show a dramatic decrease in ESTD_DB_TIME and ESTD_DB_TIME_FACTOR. Increase the memory to a point where increasing again doesn't make such a big change to the estd_db_time anymore.

In my case here, with my current load, I could easily reduce the size of SGA_TARGET to 80G without suffering a big hit. However reducing it to 57G and less would produce more and more dramatic performance issues.

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE     CON_ID
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- ----------
      5760           .0625       482104               .5257            50950730                  76032                 18176          0
     11520            .125       482104               .5257            50950730                  76032                 18176          0
     17280           .1875       482104               .5257            50950730                  76032                 18176          0
     23040             .25       482104               .5257            50950730                  76032                 18176          0
     28800           .3125      1560028              1.7011           174592866                   6912                 19456          0
     34560            .375      1374046              1.4983           138703172                  13824                 18176          0
     40320           .4375      1105895              1.2059            87207269                  20736                 18176          0
     46080              .5      1028769              1.1218            72319466                  27648                 17664          0
     51840           .5625      1000157              1.0906            66607889                  34560                 16896          0
     57600            .625       980623              1.0693            62628637                  41472                 16128          0
     63360           .6875       976129              1.0644            62628637                  41472                 21248          0
     69120             .75       961456              1.0484            59805967                  48384                 21248          0
     74880           .8125       945683              1.0312            56626641                  55296                 19456          0
     80640            .875       933852              1.0183            54359334                  62208                 19456          0
     86400           .9375       923765              1.0073            51867843                  71424                 16640          0
     92160               1       917070                   1            50950730                  76032                 18176          0
     97920          1.0625       910467               .9928            49534300                  82944                 17408          0
    103680           1.125       903131               .9848            47914066                  89856                 16640          0
    109440          1.1875       896528               .9776            46385545                  96768                 15872          0
    115200            1.25       891575               .9722            46385545                  96768                 21248          0
    120960          1.3125       886990               .9672            45361435                 103680                 19456          0
    126720           1.375       884331               .9643            44851928                 110592                 19456          0
    132480          1.4375       880937               .9606            44194663                 117504                 19456          0
    138240             1.5       875252               .9544            42915800                 124416                 18176          0
    144000          1.5625       870116               .9488            41901880                 131328                 17920          0
    149760           1.625       867915               .9464            41901880                 131328                 23040          0
    155520          1.6875       867181               .9456            41769408                 138240                 23040          0
    161280            1.75       866723               .9451            41769408                 138240                 28416          0
    167040          1.8125       866631                .945            41769408                 138240                 32000          0
    172800           1.875       866631                .945            41769408                 138240                 32000          0
    178560          1.9375       866631                .945            41769408                 138240                 32000          0
    184320               2       866631                .945            41769408                 145152                 32000          0

32 rows selected.

When MEMORY_TARGET is used:

select * from v$memory_target_advice;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.