This discussion is archived
1 2 3 Previous Next 41 Replies Latest reply: Jan 23, 2013 3:20 AM by Rob_J Go to original post RSS
  • 30. Re: Database Memory Usage
    Rob_J Journeyer
    Currently Being Moderated
    I just checked the docs and you are right, SGA_TARGET is from 10g onwards.

    So, your SGA_MAX_SIZE just says how much total you have to play with for the db cache, shared pool, large pool, etc.

    What values do you have for these:

    DB_CACHE_SIZE
    SHARED_POOL
    PARALLEL_AUTOMATIC_TUNING
    JAVA_POOL_SIZE
    LARGE_POOL_SIZE

    Also, remind me again how much memory you can use on the server for this database?
  • 31. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Thanks sb,

    Can this be adjusted in the init.ora? or is it okay to kill these seem to be zoombie processes?
  • 32. Re: Database Memory Usage
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    Thanks sb,

    Can this be adjusted in the init.ora? or is it okay to kill these seem to be zoombie processes?
    which OS resource, CPU, RAM, or I/O, that causes the slowdown?
    post COPY & PASTE proof that answers the question above?
  • 33. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Hi Rob,
    db_cache_size                   = 300000000
    shared_pool_size                = 300000000
    PARALLEL_AUTOMATIC_TUNING ***not found
    java_pool_size                 = 52428800
    large_pool **** not found
    SQL> show parameter large_pool
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    large_pool_size                      big integer 0
    
    SQL> show parameter java_pool
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    java_pool_size                       big integer 67108864
    
    
    SQL> show parameter  parallel_auto
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    parallel_automatic_tuning            boolean     FALSE
    
    SQL> show parameter shared_pool
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    _shared_pool_reserved_min_alloc      big integer 4100
    shared_pool_reserved_size            big integer 30000000
    shared_pool_size                     big integer 301989888
    
    SQL> show parameter db_cache
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_cache_advice                      string      ON
    db_cache_size                        big integer 301989888
    I am still encountering ORA-01555,
    ORA-01555 caused by SQL statement below (Query Duration=1713 sec, SCN: 0x056e.777b46ce):
    Mon Jan 21 18:21:15 2013
    
    Edited by: yxes2013 on 21.1.2013 7:51                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 34. Re: Database Memory Usage
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    And what is the output of these:
    select name, round(bytes/1024/1024,2) MB
     from v$sgastat
     where pool is null;
     select pool, round(sum(bytes/1024/1024),2)
     from v$sgastat
     group by pool;
  • 35. Re: Database Memory Usage
    jgarry Guru
    Currently Being Moderated
    sb92075 wrote:
    yxes2013 wrote:

    139 process. will these contribute to performace problem?
    yes.

    based upon OS PID it appears that some batch job just started 100+ processes which access the DB;
    OS PIDs are within one of the previous process.
    Could that also be evidence of a "login storm" that Jonathan sometimes mentions? Performance gets bad, app goes nuts trying to connect at 10:47 to 10:49.
  • 36. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Hi Rob,
    SQL> select name, round(bytes/1024/1024,2) MB
     from v$sgastat
     where pool is null;  2    3
    
    NAME                               MB
    -------------------------- ----------
    fixed_sga                         .43
    buffer_cache                      288
    log_buffer                      10.13
    
    
    SQL> select pool, round(sum(bytes/1024/1024),2)
     from v$sgastat
     group by pool;  2    3
    
    POOL        ROUND(SUM(BYTES/1024/1024),2)
    ----------- -----------------------------
    java pool                              64
    shared pool                           336
                                       298.56
  • 37. Re: Database Memory Usage
    yxes2013 Newbie
    Currently Being Moderated
    Hi all,

    I want to select the programs that are currently running in our database and is consuming lots of memory, cpu, i/o. It this program correct?
    SELECT 
    to_char(sysdate,'DD-MON-YYYY HH:MI'),  
    substr(sql_text,1,100), 
    cpu_time,
    executions,
    address 
    FROM V$SQLAREA WHERE cpu_time > 100000  and to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss')>= sysdate-1/24 ORDER BY cpu_time DESC
    
    
    SELECT 
    to_char(sysdate,'DD-MON-YYYY HH:MI'),  
    substr(sql_text,1,100), 
    disk_reads,
    executions,
    address 
    FROM V$SQLAREA WHERE disk_reads > 1000  and to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss')>= sysdate-1/24 ORDER BY disk_reads DESC
    
    
    SELECT 
    to_char(sysdate,'DD-MON-YYYY HH:MI'),  
    substr(sql_text,1,100), 
    buffer_gets,
    executions,
    address 
    FROM V$SQLAREA WHERE buffer_gets > 1000  and to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss')>= sysdate-1/24 ORDER BY buffer_gets DESC
    Are the above programs the ones also by statspack?


    Thanks
  • 38. Re: Database Memory Usage
    Rob_J Journeyer
    Currently Being Moderated
    Unless I'm mistaken it looks like you are only using 698MB of SGA in that case out of a possible 1600MB. So you should decide where you want to allocate that extra ~900MB of RAM. I would suggest the db_cache_size parameter so that there is more room in there for shared data blocks.

    Also, why is your shared_pool so large in comparison to your db_cache_size? Seems wrong to me but I don't know your application. Also, your PGA is 4GB if I remember correctly so that is way larger than the SGA. Usually you would configure it this way if you were running lots of large sorting queries like on a data warehouse.

    From everything I have read from your posts so far I would suggest that you get a DBA to look at your system, especially when you say that your company are losing lots of money each day from it and aren't happy with performance. It was mentioned many days, if not more than a week, ago about the size of your db_cache_size in comparison to your SGA_MAX_SIZE and you didn't even check what the values for your SGA memory areas were. This is such a fundamental part of configuration. Get this right before you do anything else.

    You didn't answer my previous question, either:
    Also, remind me again how much memory you can use on the server for this database?
    I'd suggest you take a look at the memory settings first. People here are happy to offer advice but if you don't take action there is not much more help we can give you.

    Hope that helps,
    Rob
  • 39. Re: Database Memory Usage
    jgarry Guru
    Currently Being Moderated
    I can't help but wonder if small buffer cache with large shared pool is a sign of no bind variables in app. This could be quite off for times when DSS queries are incoming. Some more modern versions with adaptable memory could make that worse, adjusting for one case after it is done and the other case is in force.

    Yes, an experienced dba is called for. Yes, some of this can likely be fixed by careful observation and adjustment.
  • 40. Re: Database Memory Usage
    Aman.... Oracle ACE
    Currently Being Moderated
    What I believe is that you are struck on the point that memory is the issue and to solve it, a big sized SGA and PGA would be helpful. It definitely can be the case that the SGA is not having enough memory but the question is, where is the memory going in the first place ? I would also suggest what Rob and Joel has mentioned-get a DBA to look at your system. In the meantime, get a Statspack or AWR report from the time period when you think the db is slow and post it here.

    Aman....
  • 41. Re: Database Memory Usage
    Rob_J Journeyer
    Currently Being Moderated
    I totally agree with what you are saying, Aman. The reason I was asking about this is because yxes2013 seemed to think that the SGA was sized to use 1600MB, but it doesn't look like it is to me. Also, although throwing memory at the problem is fixing the symptom rather than the cause in a lot of cases, 288MB for the db cache does seem tiny and if they are really in a pickle then increasing the memory to alleviate the problem while they look at getting a proper fix would help them out and it's quick and easy to do.

    It would also be good to hear what the rationale behind the sizing of the various SGA and PGA components was, yxes2013?
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points