Oracle Database Discussions

Hello,

I have a database 9.2.0.6.0 which is exported by SAP R3load ... the SAP system itself is stopped.
Nevertheless there is high dbwr activity on the database.
The read/write ratio is about 4:1 or 3:1

10 minute snaps of statspack

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 155,976.90 49,912,608.00
Logical reads: 13,059.75 4,179,121.00
Block changes: 1,666.10 533,153.00
Physical reads: 5,767.22 1,845,510.00
Physical writes: 1,628.10 520,991.00
User calls: 1,639.92 524,775.00

cleanout - number of ktugct calls 523,746 1,636.7 523,746.0
cleanouts and rollbacks - consist 0 0.0 0.0
cleanouts only - consistent read 523,252 1,635.2 523,252.0
cluster key scan block gets 213 0.7 213.0
cluster key scans 135 0.4 135.0
commit cleanout failures: callbac 57 0.2 57.0
commit cleanouts 1,066 3.3 1,066.0
commit cleanouts successfully com 1,009 3.2 1,009.0
commit txn count during cleanout 806,309 2,519.7 806,309.0
consistent changes 3 0.0 3.0
consistent gets 4,169,996 13,031.2 4,169,996.0
consistent gets - examination 92,041 287.6 92,041.0
current blocks converted for CR 0 0.0 0.0
cursor authentications 3 0.0 3.0
data blocks consistent reads - un 3 0.0 3.0
db block changes 533,153 1,666.1 533,153.0
db block gets 9,125 28.5 9,125.0
deferred (CURRENT) block cleanout 39 0.1 39.0
dirty buffers inspected 505,724 1,580.4 505,724.0

sorts (memory) 301 0.9 301.0
sorts (rows) 107,320 335.4 107,320.0
summed dirty queue length 722,045 2,256.4 722,045.0

Tablespace
------------------------------
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------
PSAPSTABD
123,808 387 12.9 8.0 280,746 877 0 0.0
PSAPBTABD
104,259 326 11.9 8.0 235,163 735 0 0.0
PSAPICP
3,105 10 9.5 8.0 7,028 22 0 0.0

Approx. all 90 minutes there is a log switch (1 GB online redo log files).

Within the SGA I see that there are really only SELECT-statements on the tables (BTW ... some tables have
triggers which is not usually for SAP R3 ... but the triggers will fire only on updates, which are not performed).

Sorts are performed in memory (seen by statspack ... no sorts (disk) are listed). I also don't find temporary
segments in the tablespaces (dba_segments queried).

Nevertheless there is write activity on the SAP tablespaces and dirty blocks are written to the database files.

There are no jobs running in the database. No other programs are connected to the database.

What can cause this HIGH dbwr activity?

oraicq 19211 1 1 Sep 27 ? 801:37 ora_dbw1_ICQ
oraicq 19209 1 1 Sep 27 ? 816:33 ora_dbw0_ICQ
oraicq 19213 1 1 Sep 27 ? 814:05 ora_dbw2_ICQ

/107: pwrite(780, "0602\0\0 n88F9FD % n8C j".., 8192, 0x11F3FA000) = 8192
/108: pwrite(1216, "0602\0\0 c A 'AD % n8C j".., 8192, 0x24F5A000) = 8192
/106: pwrite(931, "0602\0\080CABCBD % n8C k".., 8192, 0x15797A000) = 8192
/103: pwrite(1191, "0602\0\0 ZC1 AA6 % n8C i".., 8192, 0x2834C000) = 8192
/1: lwp_cond_signal(0xFFFFFFFF7BF6BF70) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BF59F70) = 0
/109: lwp_cond_wait(0xFFFFFFFF7BF59F70, 0xFFFFFFFF7BF59F80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BF47F70) = 0
/110: lwp_cond_wait(0xFFFFFFFF7BF47F70, 0xFFFFFFFF7BF47F80, 0x00000000) = 0
/111: lwp_cond_wait(0xFFFFFFFF7BF35F70, 0xFFFFFFFF7BF35F80, 0x00000000) = 0
/109: pwrite(1191, "0602\0\0 ZC1 AB2 % n8C l".., 8192, 0x28364000) = 8192
/110: pwrite(1243, "0602\0\08A AFAC6 % n8C l".., 8192, 0x3F58C000) = 8192
/111: pwrite(1269, "0602\0\0E0C3A4DA % n8C m".., 8192, 0x749B4000) = 8192
/1: lwp_cond_signal(0xFFFFFFFF7BF35F70) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BF23F70) = 0
/112: lwp_cond_wait(0xFFFFFFFF7BF23F70, 0xFFFFFFFF7BF23F80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BF11F70) = 0
/113: lwp_cond_wait(0xFFFFFFFF7BF11F70, 0xFFFFFFFF7BF11F80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BE1FF70) = 0
/114: lwp_cond_wait(0xFFFFFFFF7BE1FF70, 0xFFFFFFFF7BE1FF80, 0x00000000) = 0
/115: lwp_cond_wait(0xFFFFFFFF7BE0DF70, 0xFFFFFFFF7BE0DF80, 0x00000000) = 0
/112: pwrite(1183, "0602\0\0AE86 u05 % n8C m".., 8192, 0xCEA0A000) = 8192
/113: pwrite(1179, "0602\0\0 x\f050F % n8C n".., 8192, 0x180A1E000) = 8192
/115: pwrite(1100, "0602\0\097C1 tE2 % n8C n".., 8192, 0x2E9C4000) = 8192
/1: lwp_cond_signal(0xFFFFFFFF7BE0DF70) = 0
/114: pwrite(892, "0602\0\0 E82A519 % n8C n".., 8192, 0x54A32000) = 8192
/116: lwp_cond_wait(0xFFFFFFFF7BDFBF70, 0xFFFFFFFF7BDFBF80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BDFBF70) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BDE9F70) = 0
/117: lwp_cond_wait(0xFFFFFFFF7BDE9F70, 0xFFFFFFFF7BDE9F80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BDD7F70) = 0
/118: lwp_cond_wait(0xFFFFFFFF7BDD7F70, 0xFFFFFFFF7BDD7F80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BDC5F70) = 0
/119: lwp_cond_wait(0xFFFFFFFF7BDC5F70, 0xFFFFFFFF7BDC5F80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BDB3F70) = 0
/120: lwp_cond_wait(0xFFFFFFFF7BDB3F70, 0xFFFFFFFF7BDB3F80, 0x00000000) = 0
/1: lwp_cond_signal(0xFFFFFFFF7BDA1F70) = 0
/121: lwp_cond_wait(0xFFFFFFFF7BDA1F70, 0xFFFFFFFF7BDA1F80, 0x00000000) = 0
/117: pwrite(1216, "0602\0\0 c A 'CB % n8C n".., 8192, 0x24F96000) = 8192
/118: pwrite(780, "0602\0\0 n88FA16 % n8C n".., 8192, 0x11F42C000) = 8192
^C/121: pwrite(1100, "0602\0\097C1 tF9 % n8C q".., 8192, 0x2E9F2000) = 8192
/1: lwp_cond_signal(0xFFFFFFFF7BD8FF70) = 0
/119: pwrite(892, "0602\0\0 E82A5 * % n8C n".., 8192, 0x54A54000) = 8192
/122: lwp_cond_wait(0xFFFFFFFF7BD8FF70, 0xFFFFFFFF7BD8FF80, 0x00000000) = 0
/116: pwrite(1183, "0602\0\0AE86 u # % n8C n".., 8192, 0xCEA46000) = 8192
/120: pwrite(1179, "0602\0\0 x\f051B % n8C p".., 8192, 0x180A36000) = 8192


Thanks for any ideas,
Peter