Increasing db_cache_size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Increasing db_cache_size

Hybrid View

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    Increasing db_cache_size

    I want to increase the size of the parameter db_cache_size from 1008M to
    2048M in order to get better hit ratio.

    SQL> show parameter db_cache_size;

    NAME TYPE VALUE
    db_cache_size big integer 1008M


    SQL> show sga

    Total System Global Area 2147483648 bytes
    Fixed Size 2085424 bytes
    Variable Size 1073745360 bytes
    Database Buffers 1056964608 bytes
    Redo Buffers 14688256 bytes

    SQL> show parameter sga

    NAME TYPE VALUE

    lock_sga boolean FALSE
    pre_page_sga boolean FALSE
    sga_max_size big integer 2G
    sga_target big integer 0


    My question is, if sga_max_size is set at 2G, would I be able to increase my db_cache_size to 2GB or do I need to first increase my sga_max_size first. Or to re-phrase, how is sga_max_size set, is
    it calculated or does it need to be manually set. I'm thinking its calculated because I dont see this in my spfile.


    I am running on a 64 bit AIX 5.3 machine on version 10.2.0.4 with 50GB
    of memory on this machine. So raising this value 1GB would not cause my performance to degrade.

    Thanks to all who answer
    Last edited by BeefStu; 01-12-2011 at 01:26 PM. Reason: fixed typo

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    SGA is more than just the buffer cache.
    Are you licensed for AWR? What does the SGA target advisory show?
    SGA_MAX_SIZE is set by you.
    http://download.oracle.com/docs/cd/B...htm#sthref1244

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    Here is my advisory report

    Estd Phys Estd Phys
    Cache Size (m) Buffers Read Factor Reads
    ---------------- - ----------- ----------- ----------------
    96 11,874 1.27 4,490,645,591 << 10% of Current Size
    192 23,748 1.18 4,174,301,538
    288 35,622 1.13 4,008,719,045
    384 47,496 1.10 3,892,860,349
    480 59,370 1.08 3,808,793,078
    576 71,244 1.06 3,742,975,772
    672 83,118 1.04 3,688,397,488
    768 94,992 1.03 3,641,786,016
    864 106,866 1.02 3,599,416,785
    960 118,740 1.01 3,557,367,731
    1,008 124,677 1.00 3,539,057,565
    1,056 130,614 1.00 3,522,122,142 << Current Size
    1,152 142,488 .99 3,492,253,740
    1,248 154,362 .98 3,465,373,356
    1,344 166,236 .97 3,439,718,306
    1,440 178,110 .97 3,416,458,246
    1,536 189,984 .96 3,395,725,154
    1,632 201,858 .95 3,376,433,866
    1,728 213,732 .95 3,358,980,730
    1,824 225,606 .94 3,342,605,606
    1,920 237,480 .76 2,676,266,371 << 200% of Current Size

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    What does the SGA advisor show? For the buffer cache, you could increase its size and see some improvement (per what Oracle estimates).

    http://download.oracle.com/docs/cd/B...ory.htm#i45097

  5. #5
    Join Date
    Jul 2006
    Posts
    195
    That is my next step but I want to put a load on my system first. I will
    update the numbers by Friday for those who are curious.

    As per my question, I did not set the sga_max_size the only thing I increased
    was the db_buffer_cache from 1G -> 2GB but the sga_max_size did grow 1GB
    along with the total SGA, which I expected.

    So it looks like it is calculated since I did not implicitly set it. Does that
    seem correct? Does the sga_max_size only apply when using ASM? I mean
    does it have to be set when using sga_target_size?

    Thanks for your help Stecal, its greatly appreciated.

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    How was is set initially? In 11g, for example, using DBCA, Oracle will target 40% of RAM for the memory_max_target.

    SGA_TARGET (and potentially SGA_MAX_TARGET) can change based on administrator statements.

    Dynamic SGA provides external controls for increasing and decreasing Oracle's use of physical memory. Together with the dynamic buffer cache, shared pool, and large pool, dynamic SGA allows the following:

    The SGA can grow in response to a database administrator statement, up to an operating system specified maximum and the SGA_MAX_SIZE specification.

    The SGA can shrink in response to a database administrator statement, to an Oracle prescribed minimum, usually an operating system preferred limit.

    Both the buffer cache and the SGA pools can grow and shrink at runtime according to some internal, Oracle-managed policy.

  7. #7
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    thou this is not the answer to your question, I would like to suggest to use ASMM by setting the value for SGA_TARGET parameter instead of manually setting the values for different pools. You can see more information on ASMM in one of the links provided by Stecal.

    for ex you can set 40% of the physical memory to SGA_MAX_SIZE (20 GB in your case) and based on DB size and requirement you can set SGA_TARGET value (you can start with 8GB and monitor). If the statistics shows the need of more memory then you can dynamically increase SGA_TARGET parameter value.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  8. #8
    Join Date
    Jul 2006
    Posts
    195
    I agree using ASSM is the right thing to do and when I upgrade my DB to
    11gr2 that's the approach I will be taking.

    Right now, since we are running on 10.2.0.4 I am holding off becuase of some
    negative articles I saw with ASSM and Oracle 10

  9. #9
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    as far as I know, there is no negative impact using ASMM in 10g. We have more than 50 big size databases in my shop and we are happy with ASMM.

    Please note - ASSM is not related to memory management. It's for segment space management.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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