Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

    Unanswered: Cannot increase max_memory

    Hi,

    I'm trying to increase the value for the parameter memory_target target, but Oracle complains that it cannot shrink it.

    Code:
    c:\>sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 1 10:49:46 2014
    
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> select banner from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE    11.2.0.4.0      Production
    TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    
    SQL> show parameter memory_target
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    memory_target                        big integer 40G
    SQL> show parameter memory_max_target
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    memory_max_target                    big integer 58G
    
    SQL> alter system set memory_target=45G scope=both;
    alter system set memory_target=45G scope=both
    *
    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified value is invali
    ORA-00846: could not shrink MEMORY_TARGET to specified value
    
    SQL>
    Why does Oracle complain it cannot shrink the memory while I clearly want to grow it?

    I know I can "fix" this by using scope=spfile and then bounce the instance, but I'm more interested on why this error occurs.

    The operating system is Windows 2008 R2 Server
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    What does this produce?:
    select min_size/1024/1024 from v$memory_dynamic_components where component in ('PGA Target', 'SGA Target');
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by cis_groupie View Post
    What does this produce?:
    select min_size/1024/1024 from v$memory_dynamic_components where component in ('PGA Target', 'SGA Target');
    Code:
    select component, min_size/1024/1024 
    from v$memory_dynamic_components 
    where component in ('PGA Target', 'SGA Target');
    returns
    Code:
    COMPONENT  | MIN_SIZE/1024/1024
    -----------+-------------------
    SGA Target |              22400
    PGA Target |               6272
    (Although I have set sga_target to 0 in order to enable automatic memory management)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    MEMORY_TARGET should be set higher than or equal to the sum of the current sizes of the SGA and PGA.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent View Post
    MEMORY_TARGET should be set higher than or equal to the sum of the current sizes of the SGA and PGA.
    Did you actually read my initial question?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    The error message is usually given when your allocated memory exceeds what you're attempting to change it to (hence the complaint about shrinking). The culprits are usually PGA & SGA, although this does not seem to be so in your case. Which suggests that the culprits are one or more other components. You'll need to query v$memory_dynamic_components and see what else is using the memory - MEMORY_TARGET cannot be a value lower than the total of the values in this view.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •