This content has been marked as final. Show 5 replies
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?
SigCle wrote:Explain with basic logic exactly how you could possibly Page In (pi) many more blocks than you Page Out (po).
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 :
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 ?
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!
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?
jgarry wrote:Indeed, I've noticed this a LOT since upgrading one of my 'streams' to 18.104.22.168 (from 10.2.0.5). We hit a known bug which is incorrectly reporting swapping statistics in a v$ view.
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).
Our UNIX admins confirm that we never seem to be actually swapping when we receive this message.
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.