Where should the Memory be?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Where should the Memory be?

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    Where should the Memory be?

    Hi,

    We know that Oracle performs better if you have more memory available. But the question is where exactly should the memory be- Is that just the physical RAM or should that be given to the SGA?

    Badrinath
    There is always a better way to do the things.

  2. #2
    Join Date
    Aug 2001
    Posts
    267
    Def. SGA . But some of the secondary services use Physical RAM . So may be both.
    Raghu

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Does it mean the oracle.exe keeps growing till 2GB with the increase in the # of users and complains thereafter on a NT box?

    Means start with SGA+initial user session and go on till 2GB.

    What bothers me is I have 800MB SGA and my oracle.exe is showing 570M when the user population is very low. How'z that?

    Badrinath
    There is always a better way to do the things.

  4. #4
    Join Date
    Aug 2001
    Posts
    267
    Even in my case my total SGA is 5 times than the oracle.exe is now using . We can't compare like that .

    Your Current RAM = total RAM - using by Oracle.exe - using by listener - seconday processes such as Distributed services.
    Raghu

  5. #5
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    More memory doesnot always mean to get higher performance. Just give it as it need. In oracle 9i, you can get advices on buffer size, shared pool size, and large pool size, you can also resize them dynamically.

    Over confgiured memory cause more larger process page table, which is expensive with context switch. And it also mean large buffer chain in SGA.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  6. #6
    Join Date
    Jan 2001
    Posts
    642
    Thanks Calvin,

    Here is what the memory usage when my system is running on the peak load ( a batch job - which used to fail with 04030 till I reduced the sort_area).

    11 session uga memory max 23730132
    11 session pga memory 1130729720
    11 session pga memory max 1130729720
    Currently my MEM usage is 1.6gb and VM is showing 1.9

    Currently I am running it in the dedicated server mode, Does it mean that I have to Increase my SGA Multiple times if I have to run the same using MTS.

    (PGA Memory:- Is the value above the max value used for sorting?)

    Badrinath
    There is always a better way to do the things.

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    BTW, The oracle support said that the oracle.exe should be atleast as big as SGA. As it isn't, so it's a bug and asked me to upgrade to the higher version..from 8.1.6 to....higher.

    How far is that true?

    Badrinath
    There is always a better way to do the things.

  8. #8
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    Oracle is a hog. Give it as much RAM(Physical Memory) as you can without causing it to be swapped out (pagefile, swap file, virtual memory file, whatever). It used to be that this was up to about 30% of the physical memory on the server, but I am seeing more and more figures in the 50%-75% range, depending on the applications. A lot depends on the size of the database and the applications.

    The physical memory is 'assigned' to the SGA. Most of it usually goes into the db_block_buffer pool, with a significant amount to the shared pool.

    Be sure to leave enough for the user PGAs, and maybe the OS.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  9. #9
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Originally posted by badrinathn
    Here is what the memory usage when my system is running on the peak load ( a batch job - which used to fail with 04030 till I reduced the sort_area).

    11 session uga memory max 23730132
    11 session pga memory 1130729720
    11 session pga memory max 1130729720
    Currently my MEM usage is 1.6gb and VM is showing 1.9

    What are the SORT_AREA_SIZE and SORT_AREA_REMAINED_SIZE? What is the application type, batch job?

    Try to reduce SORT_AREA_REMAINED_SIZE;
    If most of jobs are batch and long time job, MTS is useless for our case.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  10. #10
    Join Date
    Jan 2001
    Posts
    642
    Hi Calvin,

    My sort_area_size was 100M, which I reduced to 50M and my s_A_R_S is 500K.
    The application is a batch job, which tries to remap few product codes from a global Inventory, billing and bookings. It happens every now and then (~once in 8-10 days).
    Given this, if I have go to MTS, Will there be a change in the sort and s_a_r_s.

    This is only to understand for theory purpose. The current problem was solved after reducing the S_A_S to 50MB..

    Your inputs will be helpful.

    Badrinath
    There is always a better way to do the things.

Posting Permissions

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


Click Here to Expand Forum to Full Width