Pardon my ignorance. Could you pls explain what is the differece between paging and swapping.

Great info as always. I think our MTS setup is ok. We had about 200 dedicated connections which are about 5-7 MB each. We are expecting up to 600 within 2 months. I have 2 dispatchers and 20 server processes and a 300 MB large pool. No complaints so far. 200*6=1.2GB so with 300MB large pool and 20 server processes*7MB=140MB it seems a pretty good memory savings. of 600MB. Seems like gobs to me on a 4GB box.

Hello,

just a small question:

According to the Server Concepts manual:
Oracle 8 i
Concepts
Release 2 (8.1.6)
December 1999
Part No. A76965-01
<Quote>
Suggestion:
Usually, it is best to keep the entire SGA in real memory. On many platforms, you can lock the SGA into real memory with the LOCK_SGA parameter.
</Quote>

LOCK_SGA parameter locks the SGA into physical memory.

however, (ref. </code> http://www.quest.com/whitepapers/orcl_db_mgmt.pdf <code>
<Quote>
MLOCK_SGA/LOCK_SGA/_LOCK_SGA etc. are all parameters that facilitate locking of the SGA in memory and preventing it from being paged/swapped. Proper use of this parameter will prevent the paging algorithm from considering the memory pages used by the SGA for page-outs. This provides a significant performance benefit especially during periods of heavy activity. This parameter should not be used if the system is not configured with an optimal amount of memory to begin with.
</Quote>

If my system (HP-UX) supports locking of SGA, how will i know if i have enough PHYSICAL memory available and other applications running on my server will not be affected?

Also, when i "lock" the SGA, is it exactly the amount of physical memory comprising SGA (show sga) being dedicated?

Thank you for a reply.

Best Regards
Yogeeraj

thank you for the reply.

make things clearer.

there definitely should be a compromise...
need to carry out some benchmarks

Regards
Yogeeraj

Hi Tom,

Just a question on swapping. Here are the informations.

Database Version Oracle 8.1.7.0.0
Operating System IBM AIX 4.3
Total System Global Area 1968998524 bytes
Fixed Size 116860 bytes
Variable Size 944349184 bytes
Database Buffers 1024000000 bytes
Redo Buffers 532480 bytes
sort_area_size 8388608
sort_area_retained_size 0
shared_pool_size 800000000

lsps -a output
Page Space Physical Volume Volume Group Size %Used Active Auto Type
paging00 hdisk2 swapvg 6288MB 12 yes yes lv
hd6 hdisk1 rootvg 1024MB 73 yes yes lv

vmstat output

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
0 0 426831 124 0 1 1 37 126 0 183 498 242 5 1 89 5

lsattr -E| mem0
size 3072 Total amount of physical memory in Mbytes False
goodsize 3072 Amount of usable physical memory in Mbytes False

We are having some discussions on the swap of 12% as shown in the lsps command. A sysadmin tells us DBA's that it is high and there should only be 5% ideally on unix machines. Though there really isn't any problem on the database. Our sort_area_size is big enough that there isn't much sorting done on disks anymore. Please give me your opinion on these.
1. Is the 12% swap really too high? If yes, how could we lower it.
2. In connection to the above question, for an OS memory of 3gig, is an SGA of 1.9G acceptable?

Thank you Tom. Good day and God bless you!

Tom, I know this is not asktom.solaris.com, but do you know how to see exactly how much swap is being used on Solaris, and by which process?

Thanks,
Juan

Thanks for the prompt response Tom.
Your response was helpful.
c",)

lock_sga doesnt works in all platforms,

In documentation there is not too much about PRE_PAGE_SGA
parameter, could you please give more your personal experience about its use. And when you advise to use it.

Thanks

thanks

Hello, Tom
I have a problem with performance on the bigger machine
(2 CPU and 3G RAM , WinNT2000) with oracle (SGA ~1,5G)
than on the old one (1 CPU and 1G RAM,WinNT2000) with oracle (SGA~ 550M).
One the more power machine it runs slower.
Can it be swap. If yes? then why and what to do with it.

---------------------------------1 server (big) -----------------------------
select count(*)
from
hist_blanks


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.60 0.60 106 19646 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.60 0.60 106 19646 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (DEVELOPER)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2073310 TABLE ACCESS FULL HIST_BLANKS

----------------------------------2 server (old) ----------------------------
select count(*)
from
hist_blanks


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.46 1.95 11242 19646 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.46 1.95 11242 19646 0 1

I see that cpu on the 1 pc = 0.60 and on the 2 = 0.46.
Could you comment it , please (indexes are absend).
Thanks in advance.

Hi Tom,

I have a few questions of tuning Oracle on Solaris.

(1) It seems that Solaris thinks it is user cpu time when I run a long stored procedure. Does any Oracle background
process use system CPU time? Or we can assume that if systemp CPU time is high, it is non Oracle process.

Here is the procedure and vmstat -S result
---------------------------
begin
2 for i in 1 .. 100000
3 loop
4 insert /* STATIC SQL */ into t values ( i );
5 end loop;
6 end;
7 /
--------------------------

-----------------------------

vmstat -S 5 30
procs memory page disk faults cpu
r b w swap free si so pi po fr de sr dd f0 s0 -- in sy cs us sy id
0 0 0 791904 509392 0 0 0 0 0 0 0 1 0 0 0 312 50 65 0 0 100 -- Start to run procedure.
0 0 0 791904 509320 0 0 12 0 0 0 0 24 0 0 0 361 3647 132 50 1 49
0 0 0 791904 508632 0 0 24 0 0 0 0 46 0 0 0 394 7489 176 98 2 0
1 0 0 791904 507728 0 0 27 0 0 0 0 51 0 0 0 421 6878 199 96 4 0
------------------------------

(2) By locking SGA, is it true hat Unix could swap out whole background process such as DBWriter?
It seems that it is a much worse situation if this happens.


Thanks so much for your help.

Hi Tom,

In terms of CPU time, I don't have example to show that big system CPU time is due to Oracle process. Do you have such sql code which would increase system CPU time dramatically?

The reason that I ask this question is that in case I see big system CPU time, I want to make sure whether it is because of Oracle process or other reasons.

Thanks so much for your help.

Hi Tom,

My goal: I just want to learn how to use vmstat to turn Oracle on Solaris.

As you indicated, if there is large percent of system CPU time, it is hard to say this is because of Oracle or this is not because of Oracle.

Thanks so much for your help.

Hi Tom,

You are absolutely right.  But I am just curious about how each oracle process uses CPU.

I used prstat to track CPU time of each Oracle process.  During the execution procedure, process id 374 used most of CPU.  By querying v$process view, the name of the process is TNS V1-V3.  

What is TNS V1-V3? Is this server process?  

Here is my result.

Thanks so much for your help.

---------------------------------
$ prstat

-- Before running the procedure.
PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
353 oracle    287M  256M sleep   51    0   0:00.01 0.2% oracle/1
320 oracle   1520K 1272K cpu0    58    0   0:00.00 0.1% prstat/1
338 oracle    288M  254M sleep   58    0   0:00.01 0.0% oracle/11
348 oracle    287M  249M sleep   58    0   0:00.00 0.0% oracle/1
346 oracle    287M  249M sleep   58    0   0:00.00 0.0% oracle/1
344 oracle    288M  249M sleep   58    0   0:00.00 0.0% oracle/1
342 oracle    287M  252M sleep   58    0   0:00.00 0.0% oracle/1
340 oracle    287M  253M sleep   35    0   0:00.00 0.0% oracle/1
336 oracle    293M  251M sleep   59    0   0:00.00 0.0% oracle/11
334 oracle    290M  252M sleep   58    0   0:00.00 0.0% oracle/11
332 oracle    288M  250M sleep   58    0   0:00.00 0.0% oracle/1
329 oracle     12M 7016K sleep   58    0   0:00.00 0.0% sqlplus/4
323 oracle   1808K 1152K sleep   43    0   0:00.00 0.0% ksh/1

-- During running the procedure.
PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
374 oracle    287M  256M run      0    0   0:00.59  93% oracle/1   -- This one used most of CPU.
336 oracle    293M  251M sleep   59    0   0:00.00 0.5% oracle/11
334 oracle    290M  252M sleep   59    0   0:00.00 0.3% oracle/23
338 oracle    288M  254M sleep   49    0   0:00.01 0.2% oracle/11
340 oracle    287M  253M sleep   49    0   0:00.00 0.1% oracle/1
320 oracle   1520K 1272K cpu0    58    0   0:00.04 0.0% prstat/1
350 oracle    287M  249M sleep   59    0   0:00.00 0.0% oracle/1
348 oracle    287M  249M sleep   59    0   0:00.00 0.0% oracle/1
346 oracle    287M  249M sleep   59    0   0:00.00 0.0% oracle/1
344 oracle    288M  249M sleep   59    0   0:00.00 0.0% oracle/1
342 oracle    287M  252M sleep   58    0   0:00.00 0.0% oracle/1
332 oracle    288M  251M sleep   59    0   0:00.00 0.0% oracle/1
329 oracle     12M 7040K sleep   58    0   0:00.00 0.0% sqlplus/4
323 oracle   1808K 1152K sleep   43    0   0:00.00 0.0% ksh/1


SQL> select spid, program from v$process;

SPID      PROGRAM
--------- ----------------------------------
          PSEUDO
332       oracle@bond6 (PMON)
334       oracle@bond6 (DBW0)
336       oracle@bond6 (LGWR)
338       oracle@bond6 (CKPT)
340       oracle@bond6 (SMON)
342       oracle@bond6 (RECO)
344       oracle@bond6 (S000)
346       oracle@bond6 (D000)
348       oracle@bond6 (D001)
350       oracle@bond6 (D002)
374       oracle@bond6 (TNS V1-V3)  -- Here is the one
373       oracle@bond6 (TNS V1-V3)
391       oracle@bond6 (TNS V1-V3)
 

Sean,
System time is just time spent by a process in a system call, like read(), send(), etc. ANY process needs to spend some time in System/privileged mode. Other things that happen in System mode are paging, swapping, and context switching - which are performed by the responsible OS processes. That part represents maintenance overhead, so if you have e.g. insufficient physical memory causing the OS to page excessively or even swap, you'd see an increase in System time. The same will happen if your processes voluntarily yield the CPU too often - such as when you have an application that doesn't use bulk binds and/or uses the database as a data dump. The high amount of System time would then be just another symptom of the real problem which, as Tom said, lies in the application logic 99.99% of the time.
On the other hand, high System time is *not always* a symptom for a problem. For example, I've seen an AIX box that used Kernelized Asynchronous I/O (KAIO, a very good thing) and sar consistently reported >40% System time. It was just an accounting issue in this case - since the AIO servers were doing the actual I/O waits, all the I/O wait time was counted as System time.
Lastly, if you want to know how much System time was spent by an Oracle instance or a single session, you can switch on the "timed_os_statistics" instance parameter. Thereafter, v$sysstat and v$sesstat will contain rows for CPU time in User and System mode, which should approximately sum to the "CPU used by this session" statistic. You will also get other interesting statistics like the number of voluntary and involuntary context switches, etc.

Hope this helps.

Flado

Thanks so much Vladimir,

By the way, do you have recommendation of good web sites for Solaris discussion?

Sean,

I've never been much into Solaris myself, so no, I can't recommend Solaris-related sites beyond docs.sun.com.
Cary Millsap goes into much detail on CPU time accounting in general, and its effects on Oracle in particular in his book and, I presume, on his site (hotsos.com). There's also a very detailed paper on Microstate Accounting on Craig Shallahamer's orapub.com, and of course a lot of very useful stuff for all things Unix+Oracle on Steve Adams' www.ixora.com.au.

HTH.

Flado

Vladimir,

Thanks so much for the useful infomation.

Hi Tom / All,

We are running Oracle 9.2.0.1.0 on Solaris 8 for HPOV management Server. I hv small confusion on memory consumptions.

Executed:
----------
$top
$prstat -a
metalink memory.sql script id:-239846.1

Observed:
-----------
prstat -a is showing 92%
top is showing 350M
memory.sql is showing both sum of PGA and UGA is 30M and my total sga
is 271.09MB.

My Doubts :
-------------

1) Why prstat -a is showing 92% of RAM and top is 350M of RAM @ same time wt is
the diff.
2) When i execute memory.sql
Total PGA Sessions : 24MB
Total UGA Sessions : 3MB
Total show SGA is : 271.09 MB
-------------
298 MB
--------------
As per my understanding Oracle is taking 298MB out of 2GB of RAM as per the above collections. Is it was correct.

And also why is diff between top and prstat -a output what i need to consider.


Thanks & Regards
Prem

Thanks Tom,

"But I can say I cannot compare a pct like 92% to a number, like 350m, it does not make sense to me."

Sorry to ask u the compare of % and MB.

"If you want to understand how much ram oracle is using, the v$ tables are helpful."

As you said these are the tables?
v$sesstat
v$session
v$bgprocess
v$process
v$instance

When i execute memory.sql from metalink 239846.1

Total PGA Sessions : 24MB
Total UGA Sessions : 3MB
Total show SGA is : 271.09 MB
-------------
298 MB
--------------

So Oracle is taking 298 MB of RAM as per above calculations is it correct?

Are still any other method to find how much memory oracle is using?

Thanks $ Regards
Prem

Tom,

after altering the following init.ora parameters and restarting we have continuelly (since two weeks) 100 % CPU usage and a lot of content switching on our Linux system.
I can't explain this only with overhead of auditing.
We actually didn't start any auditing like audit table or so, we only change init.ora parameters below.
I can't explain this with cursor_sharing parameter as well. As the parameter is a dynamic one I set it back to EXACT using alter system some days ago, but we still have 100 CPU load.
Could you explain this? How much is the overhead for auditing?
If this should be a problem with cursor sharing, what happens with the running session if I change the parameter with alter system cursor_sharing=EXACT on fly?


o7_dictionary_accessibility = FALSE
audit_trail = DB
audit_sys_operations = TRUE
remote_os_authent = FALSE
cursor_sharing = FORCE
audit_file_dest = /oracle/product/9i/rdbms/audit/PRODDB

vmstat 30
procs memory swap io system cpu
r b w swpd free buff cache si so bi bo in cs us sy id
5 0 0 259852 85140 28328 2433720 1 1 22 56 90 45 31 67 2
2 0 0 259852 92224 28424 2434312 0 0 2 90 845 1454 30 69 1
3 0 1 259852 91628 28520 2434888 0 0 1 82 843 1451 30 69 1
2 0 0 259852 91244 28616 2435736 0 0 2 93 866 1500 25 74 2
3 0 0 259852 89848 28712 2436296 0 0 1 96 861 1474 25 74 1
3 0 0 259852 89856 28792 2436848 0 0 2 104 841 1447 26 73 1
2 0 0 259852 89148 28880 2437508 0 0 1 125 861 1481 23 76 1
2 0 0 259852 87884 28976 2438396 0 0 9 117 853 1466 29 70 1
2 0 1 259852 86420 29072 2439140 0 0 3 87 848 1461 21 78 1
2 0 0 259852 85636 29160 2439908 0 0 1 100 827 1417 33 67 1
3 0 0 259852 85320 29260 2439804 0 0 23 88 851 1452 24 74 1
3 0 1 259852 84752 29372 2439240 0 0 7 90 843 1437 32 67 1
3 0 1 259852 85732 29468 2439680 0 0 4 81 835 1440 35 65 0
5 0 1 259852 85452 29560 2438992 0 0 7 93 823 1432 41 59 0
5 0 1 259848 83600 29868 2433552 1 0 401 68 831 1399 36 64 0
5 0 1 259728 84596 30176 2431444 6 0 6939 502 1095 1560 27 73 0
6 0 1 264300 84556 30148 2429704 0 1077 4968 1165 1021 1481 24 76 0
4 0 1 267588 84208 22428 2436132 10 2867 9134 2963 1039 1399 26 74 0
4 0 0 267544 84760 20012 2446868 0 1 5757 146 888 1261 23 77 0
5 0 1 267520 83944 13944 2453208 0 0 2778 115 791 1163 26 74 0
3 1 0 267476 85756 14024 2452072 2 0 1539 562 777 1140 24 76 0
7 0 1 267508 84616 13252 2546936 0 269 5866 389 810 1114 24 76 0
4 0 0 267516 85588 12992 2571480 0 1 2590 113 886 1336 29 71 0
5 0 1 267512 85512 12452 2571884 0 0 2354 116 841 1258 31 69 0
5 0 0 267500 90844 12272 2573008 2 0 931 54 824 1369 32 68 0
4 0 0 267500 87136 12404 2576992 0 0 112 64 847 1456 37 63 0

1. top
2:39pm up 5 days, 16:30, 5 users, load average: 5.30, 5.04, 4.77
234 processes: 228 sleeping, 6 running, 0 zombie, 0 stopped
CPU states: 31.1% user, 68.8% system, 0.0% nice, 0.0% idle
Mem: 3031704K av, 2946764K used, 84940K free, 0K shrd, 16456K buff
Swap: 1052216K av, 319376K used, 732840K free 2475920K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
29356 oracle 16 0 339M 333M 332M S 28.0 11.2 1037m oracle
29307 oracle 25 0 995M 993M 992M R 15.4 33.5 1523m oracle
10366 oracle 25 0 432M 431M 417M R 14.4 14.5 6:26 oracle
10576 oracle 15 0 177M 176M 175M S 3.7 5.9 0:04 oracle
29303 oracle 25 0 743M 741M 740M R 2.9 25.0 1603m oracle
29372 oracle 16 0 857M 855M 853M S 2.9 28.8 75:08 oracle
10823 oracle 25 0 38012 36M 36392 R 1.6 1.2 0:00 oracle
29322 oracle 16 0 378M 376M 374M S 1.5 12.7 35:24 oracle
29350 oracle 17 0 447M 446M 445M S 1.3 15.0 9:29 oracle
29374 oracle 15 0 859M 857M 855M S 1.3 28.9 45:35 oracle
10828 oracle 25 0 31280 30M 29684 R 0.9 1.0 0:00 oracle
5952 oracle 15 0 338M 336M 334M S 0.7 11.3 0:18 oracle
30322 oracle 16 0 28384 26M 26880 S 0.6 0.9 9:07 oracle
6621 oracle 16 0 58092 54M 55472 S 0.6 1.8 0:02 oracle
29370 oracle 15 0 863M 861M 860M S 0.5 29.1 38:32 oracle
29284 oracle 15 0 4068 2144 1936 S 0.3 0.0 14:07 oracle

2. I didn't say that it is auditing. All I had done was that I altered init.ora parameters above and restarted. I didn't turn on auditing (typing audit table or so in sqlplus). The load was 20% before altering the parameters.

BTW: should the process ID (PID) I see using top match the processes in v$session (or how can I see what database session match my OS top process?)

ops$tkyte@ORA9IR2> l
  1  select a.spid dedicated_server,
  2        b.process clientpid
  3    from v$process a, v$session b
  4   where a.addr = b.paddr
  5*    and b.sid = (select sid from v$mystat where rownum=1)
ops$tkyte@ORA9IR2> /
 
DEDICATED_SE CLIENTPID
------------ ------------
16609        16608
 
ops$tkyte@ORA9IR2> !ps -aef | grep '1660[89]'
tkyte    16608 16607  0 08:14 pts/2    00:00:00 sqlplus
ora9ir2  16609 16608  0 08:14 ?        00:00:00 oracleora9ir2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
tkyte    16791 16608  0 09:16 pts/2    00:00:00 /bin/bash -c ps -aef | grep '1660[89]'

see what those top three processes are (you might use something that shows the extented name like I did -- not just the top name)
 

Tom,

Thanks for all help you always provide.

Oracle 9ir2, Linux RHES2.1, 4G memory
dedicated server, just Oracle and OS
SGA 1.7G, PGa 540M

After rebooting the machine and starting Oracle, everithing is fine, Oracle using about 2.3G, more than 1G free, and no swapping.

After some days of use, the top reports almost all the 4G memory used, and high swap usage (500M+). See the example below:

15:50:59 up 54 days, 10:34, 1 user, load average: 0.29, 0.30, 0.22
131 processes: 129 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 14.0% 0.0% 3.2% 0.0% 0.4% 9.6% 371.6%
cpu00 0.0% 0.0% 0.9% 0.0% 0.1% 3.9% 94.8%
cpu01 1.7% 0.0% 1.3% 0.0% 0.0% 0.5% 96.2%
cpu02 1.3% 0.0% 0.7% 0.0% 0.0% 4.5% 93.2%
cpu03 10.9% 0.0% 0.3% 0.0% 0.3% 0.5% 87.6%
Mem: 4100868k av, 4075500k used, 25368k free, 0k shrd, 150256k buff
2967144k actv, 568748k in_d, 81780k in_c
Swap: 4192888k av, 502648k used, 3690240k free 3389896k cached

All top processes are from oracle, but they don't match this amount of memory usage.

I read on Metalink (</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=320180.999 <code> that it could be an OS bug, but the answers was not complete.

Could you clarify something about it for me, please? Have you ever heard something similar to this issue?

I've searched a lot, but didn't find much information.

Thanks in advance.

EDUARDO

Tom,

Once more, thank you a lot. It's clear for me.

EDUARDO

Tom,
By default, it seems that Oracle processes can only be swapped out on Solaris. Due to this, I assume we don't have to concern ourselves with paging on Solaris. Have you found this to be the case? Guess the only real reason to actually allow paging on Solaris is if there is a shortage of memory.

That's understandable. However, I was under the impression that the USE_ISM parameter available on Solaris (hidden in 9i and defaults to TRUE) would prevent a page-in operation from occurring?

I recently came across a Linux system(OEL 5.5) hosting 2 Oralce 11GR2 databases(version 11.2.0.3). One with 60GB SGA and another with 20GB SGA. And surprisingly enough, the system had just 2GB of swap space.

As per the documentation and other installation manuals the SWAP space recommended swap size for such a system is 16GB.
When I question this setup, the justification I get is
1. The system has abundant RAM to make up for the SWAP.
2. The LOCK_SGA parameter is set to TRUE, so the DB wont be using as much SWAP.
3. Somehow the client here is purchasing the RAM at a much lower cost than the diskspace, which is why having more RAM makes better business sense to them.

I'm not sure how to negate these justifications. Your comments/views on this are much appreciated.

Thanks!