Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    40

    Unanswered: statspack report analysis

    We have been getting a lot of ora-04030-out of process memory errors lately.
    I installed statspack on our database that is 29 terabytes. I noticed a lot of pct misses in the dictionary cache, library cache, and latch activity.
    I am of the assumption that this is pointing to SGA problems. My boss seems to the think that it is PGA problems. Here is a copy of my report:
    Attached Files Attached Files

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Almost withpout exception , "hit ratios" are Mythical Indicators of Performance.
    IMO, they can be safely ignored.
    Find the SQL statements that are running "too slow" & tune them.
    Your time will be much better spent that diddling with hit ratios.
    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.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I agree with anacedent... I see a few queries that have a large amount of
    buffer gets. I would be more concerned with getting those down and looking
    at the sorts (or groupby's) that are over 120m

  4. #4
    Join Date
    Aug 2005
    Posts
    40
    Thank you all so much for your replies.

    I will try to find those sql statements and tune them now and hopefully we will have no more errors.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What does an ORA-4030 mean?
    This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

    The ORA-4030 thus indicates the process needs more memory (stack UGA or PGA) to perform its job.

    What causes this error?
    Since you run into this error, you can't allocate memory from the operating system. This could be caused by your process itself, like your process is just requesting too much memory, or some other reasons cause the operating system memory to be depleted, like a too big SGA or too many processes to be accomadated for the systems virtual memory (physical memory + swap space). Many operating systems impose limits on the amout of memory a single process can acquire to protect itself.
    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.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Code:
    Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   97.11   96.82
    It looks as if your shared pool is too small.
    If you ever hit 100% then you will get the pool flushed out.
    I would increase the shared_pool to a size that would give you about 75-80% usage.

    Also look into code you have that is not using bind variables that might be
    filling up your shared pool for no good reason. This would be another area
    where you could lower your memory usage.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    With regards PGA tuning you may wish to consider manual workarea sizing for particular sessions (using alter session) if you know that particular sessions need to do very large sorts. There are a number of articles on this such as
    http://asktom.oracle.com/pls/ask/f?p...:8759826405304
    http://www.jlcomp.demon.co.uk/untested.html
    This may actually help you to reduce your PGA needs as AUTO workarea sizing limits the sort area size to a fraction of your PGA.

    One minor point but it looks like you havent setup the recycle/keep caches. This may help you a lot in terms of performance given the size of your database.

    Alan

Posting Permissions

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