The reason I posted the 'during the day' results was that they seemed
to indicate to me that there were problems due to memory constraints
even during periods of low activity. I'll post an excerpt from the
overnight stats as well. I've done quite a lot of analysis of the
processing over the last few weeks. One of the problems is that a
commit is issued after every row inserted and I'm currently trying to
persuade the application team that batching the commits would give some
benefit. Aside from this, despite the fact that I've managed to reduce
the consistent gets peformed via various pieces of the code by over
90%, with no significant increase in disk reads, the run time for the
job remains fairly static. I've used the statspack analysis tool at
Oraperf.com and it recommends that 'tuning the internal write batch'
would dramatically improve performance. Unfortunately all the
documentation I've seen regarding this issue seems to indicate that
this is not possible from 8.1.6 and above. I tried upping the number
of database writers to 1 per physical disk but got no benefit from that
either.
Regards,
Rob
21:30
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr
cpu
r w u act free wire fault cow zero react pin pout in sy cs us
sy id
4 216 42 427K 68 87K 2265M 196M 771M 26M 310M 1M 240 5K 2K
17 4 79
3 224 39 426K 126 87K 2285 480 776 14 456 0 1K 27K 6K
34 6 60
3 222 38 426K 400 87K 2257 521 671 0 397 0 1K 30K 7K
37 13 50
8 217 37 427K 128 87K 1259 174 396 11 143 23 2K 26K 13K
31 10 59
3 218 36 426K 570 87K 3975 430 1785 0 693 3 1K 12K 5K
13 6 81
3 215 36 426K 844 87K 828 73 287 0 83 0 606 4K 4K
1 8 92
21:35
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr
cpu
r w u act free wire fault cow zero react pin pout in sy cs us
sy id
5 215 37 427K 440 86K 2265M 196M 771M 26M 310M 1M 240 5K 2K
17 4 79
7 213 37 426K 225 87K 2434 494 895 0 516 0 972 19K 5K
24 5 72
3 216 37 426K 128 87K 695 45 227 46 29 27 1K 17K 7K
16 6 78
5 214 37 426K 128 87K 700 45 260 1 61 0 958 19K 5K
22 9 69
5 215 36 426K 128 87K 577 1 222 0 38 0 334 6K 2K
8 2 90
6 210 40 426K 209 87K 716 46 253 650 47 26 1K 17K 7K
16 6 78
21:40
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr
cpu
r w u act free wire fault cow zero react pin pout in sy cs us
sy id
6 215 36 426K 299 87K 2265M 196M 771M 26M 310M 1M 240 5K 2K
17 4 79
3 221 36 426K 128 87K 3161 618 1099 0 870 0 144 789 1K
2 6 92
4 220 36 426K 111 87K 519 1 230 0 6 0 99 610 1K
1 1 99
6 215 37 426K 439 87K 721 1 346 0 44 0 320 6K 3K
8 2 90
5 210 43 426K 87 87K 963 118 319 0 102 0 1K 21K 8K
19 10 71
5 213 38 426K 138 87K 787 45 264 0 92 0 833 15K 5K
19 4 78
21:45
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr
cpu
r w u act free wire fault cow zero react pin pout in sy cs us
sy id
3 221 36 426K 101 87K 2265M 196M 771M 26M 310M 1M 240 5K 2K
17 4 79
3 221 36 426K 607 87K 2157 432 718 8 460 6 778 4K 5K
1 7 92
3 221 36 426K 369 87K 635 1 230 0 54 0 741 4K 5K
1 2 97
3 219 38 426K 127 87K 625 1 263 0 32 13 738 6K 4K
6 5 89
4 217 37 426K 128 87K 1084 113 389 0 147 0 1K 19K 6K
23 11 66
3 217 37 426K 150 87K 691 45 262 0 60 0 911 17K 4K
26 6 68
21:50
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr
cpu
r w u act free wire fault cow zero react pin pout in sy cs us
sy id
6 215 35 426K 61 87K 2265M 196M 771M 26M 310M 1M 240 5K 2K
17 4 79
3 221 36 426K 128 87K 3120 617 1102 1 848 0 154 2K 2K
5 7 87
3 221 36 426K 128 87K 599 1 230 0 45 0 112 575 1K
1 1 99
3 221 36 426K 128 87K 577 1 230 0 35 0 91 564 1K
0 1 99
3 221 36 426K 128 87K 584 1 231 0 31 0 95 2K 1K
2 6 92
3 219 36 426K 325 87K 589 1 230 0 37 0 94 569 1K
0 1 99
21:55
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr
cpu
r w u act free wire fault cow zero react pin pout in sy cs us
sy id
3 218 36 426K 600 87K 2265M 196M 771M 26M 310M 1M 240 5K 2K
17 4 79
3 220 36 426K 132 87K 2067 430 689 0 439 0 107 2K 1K
2 7 91
3 220 36 426K 128 87K 559 1 228 0 32 0 89 557 1K
1 1 99
3 220 36 426K 120 87K 571 1 228 0 30 0 92 582 1K
1 1 99
3 220 36 426K 128 87K 585 1 228 0 36 0 99 2K 1K
2 7 92
3 215 39 426K 200 87K 842 1 291 0 186 0 116 2K 1K
5 1 93
22:00
Virtual Memory Statistics: (pagesize = 8192)
procs memory pages intr
cpu
r w u act free wire fault cow zero react pin pout in sy cs us
sy id
8 216 36 426K 61 87K 2265M 196M 771M 26M 310M 1M 240 5K 2K
17 4 79
3 223 36 426K 128 87K 6095 1105 2561 1232 1371 35 330 6K 3K
8 11 81
3 223 36 426K 128 87K 576 1 236 200 31 0 469 3K 3K
1 2 97
3 222 36 426K 128 87K 728 51 234 119 34 0 443 2K 3K
1 2 97
3 222 36 426K 128 87K 820 1 232 68 199 0 340 3K 3K
2 8 90
4 221 35 426K 152 87K 745 45 270 85 61 0 139 1K 1K
1 1 98
Received on Mon Mar 20 2006 - 07:50:48 CST