Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: SGA memory recommendations - Oracle 10g

    Hi,

    I am running 3 instances on a windows 2003 64bit server. The server has 8gb ram. Each of the 3 instances had the sga target set to 2430M each. Would I be right in saying that Oracle allocated 3*2432M=7296M memory therefore leaving the remainder memory (which is hardly anything) for the OS. I am now looking to reduce the amount of SGA allocated to around 1gb per instance. Is there a formula to calculate the amount of SGA required for each instance? The reason I am wanting to change the SGA is that the listener logs show TNS-12518: TNS:listener could not hand off client connection
    TNS-12560: TNSroto adapter error
    I have researched this error and I think it is related to memory allowing connections as the instances have processes set to 200, the server is running as dedicated.

    The database stopped allowing connections previously and I found the above error, the alert logs showed:
    Process startup failed, error stack:
    Tue Jul 03 10:00:09 2007
    Errors in file
    g:\oracle\product\10.2.0\admin\procserve\bdump\pro cserve_psp0_2496.trc:
    ORA-27300: Message 27300 not found; No message file for product=RDBMS, facility=ORA;
    arguments: [spcdr:9261:4200] [997]
    ORA-27301: Message 27301 not found; No message file for product=RDBMS, facility=ORA; arguments: [Overlapped I/O
    operation is in progress.]
    ORA-27302: Message 27302 not found; No message file for product=RDBMS, facility=ORA; arguments: [skgpspawn]

    I changed the page filing size to 1 and 1.5 times the physical ram and since I the database allows connections but I can still see the TNS errors in the listener log intermittingy.

    If you have any information on how best to resolve this, I will be most grateful.

    Regards,

    Dal

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Dont forget PGA, I would ensure your databases come to about 6-7GB max for SGA+PGA. Use Task Manager to ensure your total memory usage (inc OS) doesnt go into swap at all.

    Alan

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Do/did you have Pro*C or PSP application accessing Oracle DB?
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    London
    Posts
    76
    The SGA is 1.2GB*3 = 2.6GB
    The PGA is 0.8GB*3 = 2.4GB
    TOTAL = 6GB out of a total of 8GB ram
    Is this too high? 2gb ram for OS should be sufficient? Task manager shows PHYSICAL MEMORY (K) Total = 8386176, Available = 1538300, System Cache = 908916. Therefore, with approx 1538300 memory available the system is not going into swap. I have set counters on the server to check if it goes into SWAP at any time. PF usage is showing 8.13GB, not sure why this is when there is available memory? There is no Proc*C or PSP application being used. Since I have decreased the SGA I have not seen the TNS errors in the listener log, however I would like to know what the general recommendations are for SGA and PGA. I have set the pagefiling to 12000 MB (1.5 times the ram as generally recommended).

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This article should help you understand the vagaries of the windows 64bit paging.

    http://support.microsoft.com/kb/889654

    Alan

Posting Permissions

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