1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle 11G Memory Issue

Discussion in 'General' started by mary, Mar 7, 2014.

  1. mary

    mary Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Oracle -- 11.2.0.3
    OS = Windows 2008
    Memory_Max_target = 5G
    Memory_target = 4G
    Total RAM = 8G
    SQLNET.EXPIRE_TIME=10

    The Memory Usage keeps increasing up to a point where the database has to be rebooted.

    Worked with Oracle Support--Before Upgrading to 11g, we were on 10.2.0.4 on Windows 2000 32 Bit. Oracle Support suggested to upgrade to 64 Bit and we did.
    But the memory issue still persists.

    There are 2 databases on the same server. The smaller database is less than a gig and is used only in a few queries. Memory on this service is stable.

    Why does the memory usage keep increasing? Is it a Windows thing? Any inputs would be appreciated.

    Thank You
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  3. mary

    mary Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Win server 2008 R2 Enterprise 64-Bit OS
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    you can show output of sql-queries:

    select * from v$sgainfo

    select * from v$sga_resize_ops ro where ro.status != 'COMPLETE'

    select * from v$sga_target_advice order by sga_size;


    P.S. The best decision would be to unused AMM and to set up manually according to recommendations
     
  5. mary

    mary Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    select * from v$sgainfo;

    NAME BYTES RESIZEABLE
    Fixed SGA Size 2276208 No
    Redo Buffers 7278592 No
    Buffer Cache Size 1019215872 Yes
    Shared Pool Size 1518338048 Yes
    Large Pool Size 8388608 Yes
    Java Pool Size 4194304 Yes
    Streams Pool Size 4194304 Yes
    Shared IO Pool Size 0 Yes
    Granule Size 4194304 No
    Maximum SGA Size 2563887104 No
    Startup overhead in 213716256 No
    Shared Pool

    Free SGA Memory Available 0

    ------------------------------------------------------------------------------------------------------------------
    select * from v$sga_target_advice order by sga_size;

    SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

    1228 0.5 82525073 187.7127 22457490
    1842 0.75 474058 1.0783 20187477
    2456 1 439635 1 17973181
    3070 1.25 434008 0.9872 17608325
    3684 1.5 432249 0.9832 17523851
    4298 1.75 430271 0.9787 17579568
    4912 2 428952 0.9757 17523851
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------

    select * from v$sga_resize_ops ro where ro.status != 'COMPLETE';

    No Output
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    can show output of sql-queries:


    Code (SQL):


    SELECT * FROM v$sga_resize_ops

    SELECT COUNT(*) FROM v$sql WHERE executions=1;


    SET linesize 132
    ALTER SESSION SET nls_date_format='DD-MM-YYYY HH24:MI:SS';
    SELECT sysdate, name, VALUE FROM v$sysstat WHERE name LIKE 'parse%';
    EXEC dbms_lock.sleep(10);
    SELECT sysdate, name, VALUE FROM v$sysstat WHERE name LIKE 'parse%';

     

    tell, execution of resets of buffer_cache and shared_pool doesn't solve problems with memory ?