Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Location
    bamberg
    Posts
    1

    Unanswered: RAM usage of user processes

    Hi,

    I have a large database (lots of large pics in BLOBs, large Tables etc) on a solaris machine. Due to bad performance I raised the SGA up to 2.2Gbyte by increasing DB_BLOCK_BUFFERS. The result was as much more better performance for SOME operations. But some users still complain about a bad performance. The Top Command of the OS tells me, that only 61M of 4096 MB pysical RAM is free and around 7000M (!)Swap is in use. Due to that value the machine does a lot of disc IO. How can I find out what is wrong. Nothing else is running on that machine except one ORACLE instance. The max. number of user-sessions is about 90. We do not use MTS. Does a session take RAM from the OS additionally to the SGA? And how can I find out how much?

    regards

    Tspaul

  2. #2
    Join Date
    Oct 2003
    Posts
    706
    This will probably take more than a casual amount of investigation, because ... all RAM is virtual. At least from the point-of-view of any user process. It is desirable for all physical-RAM to be utilized; it is typical for about twice that amount of backing-storage (swap space) to be used.

    The amount of memory used by a DBMS "per user" is highly variable because it depends, entirely, upon what those users are doing. Plus many of the operations done by the DBMS are not tightly coupled to "a user" anyway.

    First and foremost, before you do anything else, my Rx would be for you to buy more RAM for that machine. "Chips are cheap," and there's no other prescription that is more likely to do some good, quickly.

    Also note: if you "raised a limit to 2GB" without the physical resources to actually support it, you can create a thrashing situation that makes things worse, not better. Unlike the Federal Government, you can't make resources out of thin air...
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use SQL_TRACE & TKPROF
    use SQL_TRACE & TKPROF
    use SQL_TRACE & TKPROF
    use SQL_TRACE & TKPROF
    The output from TKPROF will tell you EXACTLY where the time is being spent.
    Stop guessing.
    Collect facts and act on the bottleneck(s)
    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.

Posting Permissions

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