Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004

    Unanswered: How much memory does oracle need?

    Here's my situation:

    I have Oracle 8.1.6 on Widnows 2000 Srv with 2GB memory.

    My database is not very big, overall exported file is 7GB.

    My oracle is using up all 2GB memory right now. My question is if this is normal? I know it has a lot to do with init.ora file setting, but still the question remains.

  2. #2
    Join Date
    Apr 2003
    Greenville, SC (USA)
    Here's a liitle calculation that I use as a guideline .... AGAIN Guideline ... I make adjustments as needed once the system is up and running...

    Shared Pool
    Since shared pool usage is highly application dependent, it is necessary
    to examine each database application individually in order to project a
    recommended shared pool value.

    While analyzing shared pool sizing, it is helpful to first increase the
    shared pool to a very large value, so that the dynamically allocated SGA
    structures may be allowed to expand to a desirable size. Once this sizing
    exercise has been completed, the shared pool may be downsized to the
    appropriate value.

    Shared pool calculation is especially critical when the multi-threaded
    server is in use because the PGA for each multi-threaded server database
    user will be allocated from shared pool.


    Max Session Memory * No. of concurrent users

    + Total Shared SQL Area Usage

    + PLSQL Sharable Memory

    + Minimum 30% Free Space
    = Minimum Allowable Shared Pool


    Find the SID for an example user session:

    SQLDBA> select sid from v$process p, v$session s
    2> where p.addr=s.paddr and s.username='OPS$JSMITH';
    1 rows selected.

    Get the maximum session memory for this session:

    SQLDBA> select value from v$sesstat s, v$statname n
    2> where s.statistic# = n.statistic#
    3> and = 'session uga memory max'
    4> and sid=29;
    1 rows selected.

    Get the total shared SQL area:

    SQLDBA> select sum(sharable_mem) from v$sqlarea;
    1 row selected.

    Get the PLSQL sharable memory area:

    SQLDBA> select sum(sharable_mem) from v$db_object_cache;
    1 row selected.

    Example shared pool calculation:

    274K shared memory * 400 users

    + 9M Shared SQL Area

    + 5M PLSQL Sharable Memory

    + 60M Free Space (30%)
    = 184M Shared Pool

    In this example, the recommended shared pool value is 184M.


  3. #3
    Join Date
    Dec 2003
    Kekaha, Kauai, Hawaii
    Oracle uses the System Global Area (SGA) for caching data, properly sizing this is important for getting good performance from an Oracle database. The size of the SGA is determined by the settings in your INIT.ORA file. On NT this is probably C:\ORANT\DATABASE\INIT<sid name>.ORA. Please post this file and someone can probably point out the parameter that is using up all your memory.

    The parameters that chew up the most memory are DB_BLOCK_BUFFERS (Oracle's data cache), and SHARED_POOL_SIZE (cache for data dictionary). These are important for performance but they can probably be decreased based on the size of your database.

    Oracle needs memory for the SGA, for the system processes, and for user processes (by default there is one user process created for each user attached to the database). You can also use the MTS (Multi-Threaded Server) to allow multiple users to connect to each user process. Normally, you take the total amount of memory, subtract out what is needed for other applications, subtract off the number of users times the size of each user process, and dedicate the rest to the SGA. In your case (because you have a pretty small database so your cache can be smaller too) you should have a lot of room for decreasing the size of the SGA without making much impact on performance.

  4. #4
    Join Date
    Nov 2002
    Desk, slightly south of keyboard

    You can calculate and measure until you're blue in the face, but on NT Oracle doesn't actually allocate the memory until it's needed. You can have an Oracle instance configured with a 10Gb SGA, shown as only using 100Mb under NT.

    (I have no idea how it deals with this under *nix).

    When sizing, comparing etc, add the line PRE_PAGE_SGA=YES into your Init.Ora file. This will force Oracle to pre-allocate all needed memory on startup.

    You will get a much better idea of exactly what Oracle is using, but beware - it can still increase :-) Check the docs for further info.

    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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