oracle swap_join_inputs,如何确定oracle 是否使用了swap？

I don't think we can unambiguously tell. You didn't say which OS. Here's an example on Linux. I'm checking my bash process. The output of "top -p $$" has:

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

14900 oracle    15   0 66056 1600 1180 S  0.0  0.0   0:00.02 bash

And its anonymous memory segments in its address space are:

$ pmap $$ | egrep 'anon|stack'

00000000006bc000     20K rw---    [ anon ]

000000000d2ba000    396K rw---    [ anon ]

0000003417152000     20K rw---    [ anon ]

00002b21b1bda000      4K rw---    [ anon ]

00002b21b1bef000     12K rw---    [ anon ]

00002b21b53d2000      8K rw---    [ anon ]

00007fff428c0000     84K rw---    [ stack ]

ffffffffff600000   8192K -----    [ anon ]

Note two facts:

1. virtual = resident + swapped-out memory;

2. swap space is for swapped out anonymous pages, not swapped out file pages (because memory pages backed by files simply don't need swap space; the files are their backing store).

We can say that 66056 - 1600 = 64456 KB memory is swapped out, some of which (the anonymous pages) are on swap disk, and some back to files. We can also say the process has about 8.5 MB anonymous pages in its address space. But the two numbers can't be mathematically correlated; we can't say all the 8.5 MB is on swap disk, because it's possible some of it is currently in RAM memory. "pmap -x" shows an additional column called RSS (resident set), but I don't think it's trustworthy.

So that bash process definitely has swapped out pages (in fact, 90% of its pages are out). But whether they're even partially on swap space is unknown.

Back to Oracle. I always configure HugePages and make sure they're used by the Oracle instance. See the section "Checking" at http://yong321.freeshell.org/oranotes/HugePages.txt

RHEL 5 top or ps has a problem with RSS for HugePages. But I know for sure my SGA is definitely in RAM memory.

However, PGA may be using swap. But there's really no definitive way to find out. You can run a big order-by query using huge amounts of PGA to sort (make sure your parameters are set to allow that), or make a huge PL/SQL array. Then watch /proc/memory Swap* stats and vmstat swpd values to change. Those system-wide stats don't tell you which process uses swap space. So you have to guess based on your Oracle's activity.

Yong Huang

[本帖最后由 Yong Huang 于 2009-11-21 16:38 编辑]