SGA Tuning
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: SGA Tuning

  1. #1
    Join Date
    Aug 2001
    Posts
    64

    Red face

    Can someone help me with SGA tuning. What is the standard for SGA values.
    I am running 8i (8.1.5) on Solaris 2.6, total allocation to SGA is 100MB of 2GB (total memory on the system). Out of this total:
    54 MB buffer cache with hit ratio of 96.8 %
    45 MB shared pool with hit ratio of 74.5 %
    171 KB redo buffer

    Drilling down furhter:
    data buffer cache: 15% on average and 60% max.

    In regards to the above information, is the data buffer cache considered to be part of the buffer cache? If they are one in the same, what is an acceptable value for it? What is the standard for shared pool hit ratio?

    on another note, we are only using 100 MB of SGA which left us almost 1.8GB of memory. However, when I ran top, only 45 MB of memory is free. when I sorted by RS memory, oracle processes comes right on top. What should I do next? Looking into turning off certain function in oracle which we are not using such as JAVA? How do I find out what is causing the memory consumption? I am tempted just to run the system without starting oracle, just to check out how much is the system using memory. This way I know for sure, that's it's oracle if the system is not consuming memory.

    The fool i used to get the above information is Tora. I am not sure of it's accuracy. But people who uses it, gives it good praises.

    Thanks in advance for your help.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Top lies. It doesn't handle shared memory very well. The better way to tell your shared memory is by ipcs or simply doing a /usr/ucb/ps -aux for the smon process and looking at the %MEM value. With all that memory, I would at least DOUBLE your shared_pool_size and db_block_buffers.

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    It is a bit strange that with 2G RAM you have only 54 MB buffer cache and a 45 MB shared pool. Do you have other instances running on that machine? Increase the values as Jeff suggested.

  4. #4
    Join Date
    Aug 2001
    Posts
    64

    Unhappy

    Juilian, there is only one instance running and Yes, I agreed totally that the size of the SGA should be larger considering that we have 2GB of memory available. But I am not the senior DBA at this place, every time I open my mouth about tunning it, the Senior guy get very offended. I believe the database is not fully optimized, but he seems to have a very diffrent opinion than mine. I am hoping to find some justification that the SGA needs tunning and that is why I am looking into the it. I hope you guys can clarify that the SGA is not size correctly considering the physical memory we have.

    what's more, the database is running in dedicated server mode, for christ sake shouldn't it be in MTS; I think solaris 2.6 running on an E450 with 2 cpu can handle it. On top of this, he has connection pooling and multthreading on. There's two things wrong here. One, connection pooling and multiplexing is mutually exclusive. Two, I don't think dedicated server can support pooling and multiplexing.

    Sorry for renting at this forum like this. Thanks for your input, I will look into the memory issue with ipcs and ps command. I will let you know of the results.


  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by tekion

    what's more, the database is running in dedicated server mode, for christ sake shouldn't it be in MTS; I think solaris 2.6 running on an E450 with 2 cpu can handle it. On top of this, he has connection pooling and multthreading on. There's two things wrong here. One, connection pooling and multiplexing is mutually exclusive. Two, I don't think dedicated server can support pooling and multiplexing.
    No, multiplexing and pooling won't support dedicated servers. You have to be in MTS mode.

    Multiplexing and Pooling are mutually exclusive. In fact, Oracle recommends you don't run the two together on the same dispatchers.

    MTS carries a lot of headaches with it. Unless you are running with a large number of users (> 200+), I wouldn't even consider MTS. Personally, I wouldn't consider using multiplexing or pooling unless my users were approaching 1000.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Well, usually the Sen. has the final word. I have been in your situation and I understand how it feels. Try to get a job where you will be the Sen.

    Jeff gave you a very good follow up. I am also against Shared Server if not absolutely needed.

    When you ask the Sen. "How do you expect to have connection pooling and multiplexing (which are BTW mutually exclusive) in a dedicated server?" what is his answer :-)





  7. #7
    Join Date
    Aug 2001
    Posts
    64

    Question

    morist89,
    here's a pre-formatted output from /usr/ucb/ps -aux:
    USER %MEM COMMAND
    oracle8i 5.6 ora_i201_PROD
    oracle8i 5.6 ora_pmon_PROD
    oracle8i 5.6 ora_dbw0_PROD
    oracle8i 5.6 ora_lgwr_PROD
    oracle8i 5.7 ora_ckpt_PROD
    oracle8i 5.8 ora_smon_PROD
    oracle8i 5.7 ora_reco_PROD
    oracle8i 5.7 ora_snp0_PROD
    oracle8i 5.7 ora_i101_PROD
    oracle8i 5.7 ora_i102_PROD

    Total percentage 56.7 which is 1.61216 GB out of 2GB

    Is this normatl memory consumption? Note, I only include background processes. With a total of 15 connection ( bec. we're running in dedicated server mode) taking up a total of 87.4% of memory(1.790GB of 2GB), see below:
    oracle8i 5.9 (LOCAL=
    oracle8i 6.0 (LOCAL=
    oracle8i 5.9 (LOCAL=
    oracle8i 6.0 (LOCAL=
    oracle8i 5.8 (LOCAL=
    oracle8i 5.9 (LOCAL=
    oracle8i 5.9 (LOCAL=
    oracle8i 6.1 (LOCAL=
    oracle8i 5.7 (LOCAL=
    oracle8i 5.7 (LOCAL=
    oracle8i 5.7 (LOCAL=
    oracle8i 5.7 (LOCAL=
    oracle8i 5.7 (LOCAL=
    oracle8i 5.7 (LOCAL=
    oracle8i 5.7 (LOCAL=

    With the above calculation, this gives me about a total of 3.4 Gb. So, the number does not add to 2GB. I take it this is because of share mem, each one of those processes is digging into share mem. How do I find out how much is each proccess using the share memory. In other words how do I take into account my calcualtion of share mem for each processes.

    I think this may be the cause of my memory heist. Any input is appreciated.
    Thanks.

    On another note, Does anyone know what "ora_i" is? Thanks.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by tekion

    Total percentage 56.7 which is 1.61216 GB out of 2GB


    Nope. Shared memory at work. All your background processes are using shared memory, probably about 5.6% of your physical memory.


    Is this normatl memory consumption? Note, I only include background processes. With a total of 15 connection ( bec. we're running in dedicated server mode) taking up a total of 87.4% of memory(1.790GB of 2GB)


    Nope again. Each connection is using about .3 -.4 % distinct memory. The rest is shared memory.


    How do I find out how much is each proccess using the share memory.

    ipcs and pmap are the keys to figuring out your shared memory segments.



    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Aug 2001
    Posts
    64

    Talking

    Morist89,
    Why would you say that the background processes is only consuming 5.6% of memory? and the Local connection between .3 and .4 of memory? It doesn't make since when I check how much each processes is tapping into share mem. via pmap. Here's a sample output of it via "
    ./pmap -x 29709 | egrep "Address|total":
    Address Kbytes Resident Shared Private Permissions Mapped File
    total Kb 172768 140896 12024 128872

    according to this, each procesesses consume about 12 Mb of share mem.

    Could you explain why? Thanks.

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Lets try it this way:

    On one of my systems, I have various user sessions:
    Code:
    oracle8@oradev1 ibdev1$ ps -ef | grep oracleibdev1
     oracle8  4618  4610  0 15:00:01 ?        0:01 oracleibdev1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
     oracle8  3649     1  0 13:48:21 ?        0:01 oracleibdev1 (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    If I use pmap on process 4618 and look for the shared memory:
    Code:
    oracle8@oradev1 ibdev1$ pmap -x 4618 | grep shared
    
    80000000  264312  264312       -  264312 read/write/exec/shared  [ ism shmid=0x79c00 ]
    FF1D0000       8       8       8       - read/write/exec/shared   [ anon ]
    This tells me that my process is attached to a shared memory segment at location x79c00 ( or 498688). The size of this shared memory segment is 264312K or about 258MB.

    If I then look at my shared memory segments using ipcs:
    Code:
    oracle8@oradev1 ibdev1$ ipcs -a
    IPC status from  as of Fri Jan 18 15:11:09 EST 2002
    T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP CBYTES  QNUM QBYTES LSPID LRPID   STIME    RTIME    CTIME 
    Message Queues:
    q       1400   0x5a005301 -Rrw-rw-rw- alexbkup alexbkup     root     root      0     0  65536 15567 15571 15:11:00 15:11:00 14:15:36
    q       3301   0x5a005bc5 -Rrw-rw-rw-     root     root     root     root      0     0  65536 15597 15632 14:16:13 14:16:13 14:16:03
    T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NATTCH      SEGSZ  CPID  LPID   ATIME    DTIME    CTIME 
    
    Shared Memory:
    m     498688   0x466822b8 --rw-r-----  oracle8      dba  oracle8      dba     34  270655488  4984  4618 15:00:01 15:00:01  9:28:10
    m     179201   0xb2da34f4 --rw-r-----  oracle8      dba  oracle8      dba     13   97787904 16914  4620 15:00:01 15:00:01 10:25:43
    m      31746   0x615110ec --rw-r-----   oracle      dba   oracle      dba     11  252207104 23409  4509 14:57:24 14:57:24 13:23:00
    m    1918980   0x76ab35c0 --rw-r-----   oracle      dba   oracle      dba     10  252207104  3383 25198 14:35:33 14:42:24 15:31:09
    
    T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NSEMS   OTIME    CTIME 
    
    Semaphores:
    s   38404096   0x9e423e14 --ra-r-----  oracle8      dba  oracle8      dba   204 15:10:52  9:28:10
    s   20316161   0x9e417c74 --ra-r-----  oracle8      dba  oracle8      dba    54 15:11:07 10:25:43
    s    2555906   0x54c69ac  --ra-r-----   oracle      dba   oracle      dba   154 15:07:22 13:23:01
    s  124911620   0xb0ce325c --ra-r-----   oracle      dba   oracle      dba   154 15:06:56 15:31:12
    I can see that I am attached to shared memory segment where ID=498688.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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