This discussion is archived
5 Replies Latest reply: Feb 23, 2013 9:11 AM by SigCle RSS

Significant virtual memory paging was detected on the host operatingsystem

SigCle Newbie
Currently Being Moderated
Dear Experts,

one of my client Oracle DB Production ADDM said :
"Significant virtual memory paging was detected on the host operatingsystem"

OS : Sun Solaris Spark 64 bit
Oracle DB : 10.2.0.5
OEM always on 24 hours

I haven't get vmstat, glance but hope could get the information for next 6 hours since I writing this.

Based on two links below :
http://www.dba-oracle.com/t_tuning_cpu_usage_vmstat.htm
Virtual Memory PAging 11G

First I must analyze about vmstat 1 1000 and glance from the OS for gathering information about excessive of Oracle RAM usage which could suffer in swap.
and then after that decide whether should reduce : SGA (shared_pool_size and java_pool_size) and PGA and sort_area_size parameter
in order to giving breath for external OS services processes.


My Question :_
1. vmstat indicator to reduce sga and pga :
IF pi column compare with po column, if pi quite bigger than po and high wa so I should reduce SGA and PGA size ?
2. Is OEM could annoy Production System Process if OEM view performance data set to refresh every 15 seconds ?

Any answer is very appreciated.

Thanks and Regards,
Sigcle
  • 1. Re: Significant virtual memory paging was detected on the host operatingsystem
    jgarry Guru
    Currently Being Moderated
    I don't know about your OS, but on mine it can be a completely bogus message (and funnily enough, it spit out a bogus complaint just a couple of hours ago). If you are actually swapping, it could indeed be oversized SGA or PGA (since the pga_aggregate_target is just a target, I have seen where adding a new user, sql, or making a minor memory configuration tweak sends memory usage over the cliff.)

    Watch out for out-of-date and otherwise wrong or misleading advice on the intertubes. Even the definitions of paging and swapping are often screwy.

    What non-default parameter settings does the db have?
  • 2. Re: Significant virtual memory paging was detected on the host operatingsystem
    sb92075 Guru
    Currently Being Moderated
    SigCle wrote:
    Dear Experts,

    one of my client Oracle DB Production ADDM said :
    "Significant virtual memory paging was detected on the host operatingsystem"

    OS : Sun Solaris Spark 64 bit
    Oracle DB : 10.2.0.5
    OEM always on 24 hours

    I haven't get vmstat, glance but hope could get the information for next 6 hours since I writing this.

    Based on two links below :
    http://www.dba-oracle.com/t_tuning_cpu_usage_vmstat.htm
    Virtual Memory PAging 11G

    First I must analyze about vmstat 1 1000 and glance from the OS for gathering information about excessive of Oracle RAM usage which could suffer in swap.
    and then after that decide whether should reduce : SGA (shared_pool_size and java_pool_size) and PGA and sort_area_size parameter
    in order to giving breath for external OS services processes.


    My Question :_
    1. vmstat indicator to reduce sga and pga :
    IF pi column compare with po column, if pi quite bigger than po and high wa so I should reduce SGA and PGA size ?
    Explain with basic logic exactly how you could possibly Page In (pi) many more blocks than you Page Out (po).

    Don't pages have to be paged out of memory, before they can be paged in?

    Realize that Virtual Memory Operating System are actually designed to page blocks of of RAM as a basic operation; so you can have programs that have a memory address space larger than total RAM.


    Paging operations are NOT how you determine if RAM is a bottleneck or not!
    2. Is OEM could annoy Production System Process if OEM view performance data set to refresh every 15 seconds ?
    If you suspect that OEM is source of the problem, then shut it down since it is NOT required
    & see if performance immediately improves!

    Handle:     SigCle
    Status Level:     Newbie (30)
    Registered:     Jun 26, 2010
    Total Posts:     87
    Total Questions:     25 (20 unresolved)


    Why do you waste time here when your questions rarely get answered?
  • 3. Re: Significant virtual memory paging was detected on the host operatingsystem
    marksmithusa Journeyer
    Currently Being Moderated
    jgarry wrote:
    I don't know about your OS, but on mine it can be a completely bogus message (and funnily enough, it spit out a bogus complaint just a couple of hours ago).
    Indeed, I've noticed this a LOT since upgrading one of my 'streams' to 11.2.0.3 (from 10.2.0.5). We hit a known bug which is incorrectly reporting swapping statistics in a v$ view.

    Our UNIX admins confirm that we never seem to be actually swapping when we receive this message.
  • 4. Re: Significant virtual memory paging was detected on the host operatingsystem
    jgarry Guru
    Currently Being Moderated
    Sometimes it does seem to correlate with java for dbconsole. Sometimes I see it after users are done with some hefty processing, then not much is happening and some OMS thing decides to do lots of stuff. (10.2.0.4)
  • 5. Re: Significant virtual memory paging was detected on the host operatingsystem
    SigCle Newbie
    Currently Being Moderated
    Dear sb92075,

    I'm sorry for too long response. Yes, it's true that my question rarely answered. But clues especially that come from Frank Kulash, Jonathan Lewis help me.

    Sometimes ADDM showing bogus or confusing report but sometimes it's a clue.
    Mostly AWR, Tkprof, alert log, and .trc logs help me to analyze performance problems.

    I have another issue that explain plan doesn't quarantee real performance execution time.
    I have same query but access different tables (the two tables has exactly same data, same DDL, but different partitions, one is by hash, the other is by list), and explain plan with partition by hash is very small compare with partition by list, but execution time by list is faster than by hash. So confusing !!

    I even think that rebuild indexes, setting right pctfree, right Solaris/UNIX/RHEL or other OS kernels parameter(such /etc/sysctl.conf ,/etc/security/limits.conf), update table and index statistics could help decrease chain rows and migrated rows hence help Oracle CBO choose the best path (besides of course significant performance proportion up to 80% come from query tuning or maybe added some hints).

    But, as my experiences, if I want tune the database administration, SQL, PL/SQL, I must always watch over bugs whether on current Oracle DB version on Metalink.
    Like on this case that I currently reading : MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
    About pageout and pagein, I read from many articles like dba-oracle (burlesson consulting) although sometimes his theory is wrong.

    Regards,
    Sigcle

Legend

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