This discussion is archived
10 Replies Latest reply: Mar 27, 2013 1:56 PM by MSandico RSS

Monitoring memory performance for oracle database on windows

MSandico Newbie
Currently Being Moderated
Hi All,

I'm new to Oracle database admin and am looking at a dedicated Oracle db server running on Windows with 12GB of RAM. I noticed that available mem is 10GB and when checking SGA size in EM, it says max size is 1000MB! We've looked at how to increase the size (we are going to increase to 8000MB) but what i'd like to know is how to monitor memory performance from an Oracle perspective (i am familiar with windows OS counters but am looking for Oracle db-specific counters). I see one about buffer cache hit ratio but is that the only one? In SQL server, there are several SQL-specific counters to look at in terms of SQL memory usage, hoping to get the same for ORacle..

thanks in advance
  • 1. Re: Monitoring memory performance for oracle database on windows
    TSharma-Oracle Guru
    Currently Being Moderated
    Why do you want to increase it at first place? Don't fix it if ain't broke. Secondly, this is Oracle not sqlserver, both are just different in memory consumption.

    Normally we do increase a memory so that we can reduce the physical disk reads by reading more blocks from buffer cache. In Oracle ,adding memory is not the only solution, if you are expeiencing a slow performance , you should find a root cause and fix it. If you have poorly written queries , it will consume all the memory doesn't matter how much memory you have.

    You can definetely run AWR reports which has several sections which tells you about the memory consumtion and other ratios.You can check SGA and PGA advisors(Memory advisors) in EM under Server section.

    Also 8GB out 12 GB just for SGA? What about PGA(depends upon your sessions) and system reserved memory. You could swapping heavily.

    Also Are you on 32-bit or 64-bit Windows?

    BUT remember , *"dont fix it if it ain't broke"*
  • 2. Re: Monitoring memory performance for oracle database on windows
    jsenior Newbie
    Currently Being Moderated
    Which version of windows are you running on as this may limit the amount of RAM that can be utilised?

    Windows doesn't control the memory for the database, you need to configure that in the database initialisation parameters such as SGA_TARGET.

    Some you will be able to change dynamically with the database up and others will require a restart of the database.

    Check this out and size it according to what you need : [ [http://docs.oracle.com/cd/B19306_01/server.102/b14211/memory.htm] ]
  • 3. Re: Monitoring memory performance for oracle database on windows
    jgarry Guru
    Currently Being Moderated
    When asking questions, always give the exact version and patch level you are using; select version from v$instance;

    Do not use the buffer hit ratio to make tuning adjustments, and do not listen to anyone who tells you to. The mentioned advisors are actually pretty good, though they may just keep asking for more more more.

    Show us the adjusted instance parameters (from the startup messages in your text alert log) and let us know how many users you have and what they are doing.
  • 4. Re: Monitoring memory performance for oracle database on windows
    MSandico Newbie
    Currently Being Moderated
    Thanks for the replies everyone and sorry for the late reply..

    We are using Windows 2003 Ent Ed. 32-bit (though we are going to upgrade to Win 2008 R2 64-bit very soon..)

    Version is 10.2.0.3.0

    non-default parameters are here:

    tarting up ORACLE RDBMS Version: 10.2.0.3.0.
    System parameters with non-default values:
    processes = 500
    sga_max_size = 1048576000
    shared_pool_size = 369098752
    large_pool_size = 8388608
    java_pool_size = 75497472
    nls_language = ENGLISH
    nls_territory = AMERICA
    sga_target = 1048576000
    control_files = E:\ORACLE\ORADATA\MYDATABASE\CTL\CONTROL01.CTL, F:\ORACLE\ORADATA\MYDATABASE\CTL\CONTROL02.CTL, E:\ORACLE\ORADATA\MYDATABASE\CTL\CONTROL03.CTL
    db_block_size = 8192
    db_cache_size = 524288000
    compatible = 10.2.0.2.0
    log_archive_dest_1 = LOCATION=use_db_recovery_file_dest
    log_archive_format = T%TS%S%R.ARC
    archive_lag_target = 1800
    db_file_multiblock_read_count= 16
    db_recovery_file_dest = L:\ORAFRA
    db_recovery_file_dest_size= 30064771072
    db_flashback_retention_target= 1440
    undo_management = AUTO
    undo_tablespace = UNDOTBS1
    undo_retention = 14400
    recyclebin = OFF
    O7_DICTIONARY_ACCESSIBILITY= TRUE
    remote_login_passwordfile= EXCLUSIVE
    db_domain =
    dispatchers = (PROTOCOL=TCP) (SERVICE=MMSPRDXDB)
    utl_file_dir = *
    job_queue_processes = 10
    audit_file_dest = E:\ORACLE\ORADATA\MYDATABASE\A_DUMP
    background_dump_dest = E:\ORACLE\ORADATA\MYDATABASE\B_DUMP
    user_dump_dest = E:\ORACLE\ORADATA\MYDATABASE\U_DUMP
    core_dump_dest = E:\ORACLE\ORADATA\MYDATABASE\C_DUMP
    db_name = MYDATABASE
    open_cursors = 5000
    pga_aggregate_target = 524288000
  • 5. Re: Monitoring memory performance for oracle database on windows
    sb92075 Guru
    Currently Being Moderated
    MSandico wrote:
    Thanks for the replies everyone and sorry for the late reply..

    We are using Windows 2003 Ent Ed. 32-bit (though we are going to upgrade to Win 2008 R2 64-bit very soon..)
    Do NOT make any parameter changes until after the DB resides on 64-bit OS & 64-bit Oracle software.
  • 6. Re: Monitoring memory performance for oracle database on windows
    TSharma-Oracle Guru
    Currently Being Moderated
    In 32-bit windows, you won't be able to take an advantage of all the memory. Maximum limit is 4 GB that also with some switches enable. I would ask again , What is the reason you want to increase the memory. Are you having any performance problem?
  • 7. Re: Monitoring memory performance for oracle database on windows
    MSandico Newbie
    Currently Being Moderated
    Yes we are having performance issues and from OS perfmon counters, there is nothing i can see (cpu, memory, etc..). So i was looking to see if there were any application-level specific perfmon counters i can look at (again im thinknig about how i can tell from SQL server perspective..).

    One of my first inclinations was to investigate the available memory on the server (10GB), and when i noticed SGA was set to maxsize of 1GB, i wondered if it should be configured to take advantage of the memory on that dedicated db server. A quick look in EM reports that Oracle says to increase SGA to 1250MB (still not the 8-9GB i was initially thinking). I always figure that the more the memory, the happier a db server can be..whether it needs it or not..it may want that extra juice ;)
  • 8. Re: Monitoring memory performance for oracle database on windows
    sb92075 Guru
    Currently Being Moderated
    Bigger is NOT always better.

    Will your car go faster with a larger gas tank?
  • 9. Re: Monitoring memory performance for oracle database on windows
    jgarry Guru
    Currently Being Moderated
    MSandico wrote:
    Yes we are having performance issues and from OS perfmon counters, there is nothing i can see (cpu, memory, etc..). So i was looking to see if there were any application-level specific perfmon counters i can look at (again im thinknig about how i can tell from SQL server perspective..).
    Don't think about SQL server. It works differently, has different performance limitations - locking and consistency methods, in particular.

    >
    One of my first inclinations was to investigate the available memory on the server (10GB), and when i noticed SGA was set to maxsize of 1GB, i wondered if it should be configured to take advantage of the memory on that dedicated db server. A quick look in EM reports that Oracle says to increase SGA to 1250MB (still not the 8-9GB i was initially thinking). I always figure that the more the memory, the happier a db server can be..whether it needs it or not..it may want that extra juice ;)
    Not true at all. There are a number of bottlenecks and internal tuning features that can give surprising results to the uninitiated. Adding more memory to a 32 bit server that is already using as much as it can is like adding a building to your office - doesn't help you much if you are stuck in the same cubicle. Adding more memory to a server that can use it can change the bottlenecks around to make performance worse, for example, you might have more data in memory, making your cpu overloaded trying to thrash through it all, when before it could have a breather while waiting on I/O. Like adding a new building to your office, moving you to a bigger cubicle in the new building, then you have to walk twice as far to all those dang meetings.

    64 bit servers are another story, in general they remove limitations. Though it is still possible for faster and bigger to be slower. In the end, it only matters what your users see.
  • 10. Re: Monitoring memory performance for oracle database on windows
    MSandico Newbie
    Currently Being Moderated
    Thanks for all the replies, i think pretty much bottom line is trust the memory advisors within EM and various AWR reports. THe perfmon counters, memory-wise, for buffer cache is useless (similar in SQL server ;)

Legend

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