Slow commits

From: Kristen Cameron <cameronkgd_at_inac.gc.ca>
Date: Fri, 05 May 2000 17:31:28 -0400
Message-Id: <10495.105577@fatcity.com>

--=_B2EA9A71.56374951

Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I am a brand new DBA, and apologize in advance for the far too general nature of my question. However, I have reached the=20 end of my meagre knowledge. I'm looking for a little guidance=20 from the more experienced. I've attached BSTAT/ESTAT reports=20 for the truly generous, along with a couple of other text files of statistics. Contact me if you want them but didn't receive them.

Using an Oracle Forms application, users are finding that commits take 3 to 5 minutes when done on certain tables in the database. It would seem that the problem should be=20 obvious, but I cannot find it. There are about 15 tables involved in the part of the application that is experiencing this slowdown. Most of them are look up tables, but a few are relatively large (10,000+ records) tables of data that are being updated. The tables seem to be indexed reasonably. Most items are referenced using an id number, and the id column is indexed either alone or jointly with another column.

We are using Oracle 7.3.4 on Sun Solaris 2.6. Our users are using Windows 95. Our network is Novell.

To date, I have run all kinds of statistics on the database, but=20 have found no problems. CPU, memory, storage, waits, latches, I/O, locks, hit ratios, etc. all seem good. There may be some disk contention, but it appears minimal and may be unavoidable, since we only have two processors, anyway. Actually, on the whole, the=20 numbers look fantastic, as near as I can tell from my reading on the=20 subject - Oracle documentation, online articles, Niemiec's book on Performance Tuning, and Loney's DBA Handbook.

At this point, I am assuming that the problem is within the application code itself, especially since it began when the=20 application was developed. However, it seems to be getting worse.

Is there anything further I can do to test it, or better yet, fix it?=20

Thanks for any and all help.

Kristen Cameron Indian and Northern Affairs Canada =20

--=_B2EA9A71.56374951

Content-Type: text/plain; name="reportmay02pm.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="reportmay02pm.txt"

--=_B2EA9A71.56374951

Content-Type: text/plain
Content-Disposition: attachment; filename="vmstat2.may02"

May 02

VMSTAT 2 procs memory page disk faults cpu r b w swap free re mf pi po fr de sr m0 s0 s6 s7 in sy cs us sy id 0 0 0 17464 184 1 6 241 76 233 0 39 2 4 0 0 282 310 430 3 3 95 0 0 0 161248 6920 0 0 0 2 2 0 0 0 0 0 0 553 3519 1663 3 5 92 0 0 0 161248 6928 0 0 0 0 0 0 0 0 1 0 0 552 3509 1697 3 5 92 0 0 0 161248 6936 0 0 0 2 2 0 0 1 1 0 0 569 3517 1674 3 5 92 0 0 0 161248 6944 0 0 0 2 2 0 0 0 0 0 0 566 3541 1747 5 5 90 0 0 0 161240 6840 0 11 33 2 2 0 0 1 1 0 0 560 3603 1680 4 4 92 0 0 0 161224 6608 0 11 0 4 4 0 0 1 1 0 0 498 3231 1464 4 4 93 0 0 0 161184 6344 0 22 99 4 29 0 42 2 4 0 0 290 1122 507 2 2 96 0 0 0 161168 5864 8 0 38 140 151 0 6 16 16 0 0 847 4551 1903 8 6 86 0 0 0 161152 5984 3 0 14 43 43 0 0 4 2 0 0 626 3963 1720 6 4 90 0 0 0 161144 5976 0 11 1 8 8 0 0 2 1 0 0 585 3438 1640 3 5 92 0 0 0 161144 5992 0 0 1 3 3 0 0 0 0 0 0 550 3674 1736 4 5 91 0 0 0 161144 5984 0 0 1 3 3 0 0 0 0 0 0 540 3435 1680 4 4 92 0 0 0 161144 5976 0 0 3 4 4 0 0 0 0 0 0 609 4372 1812 5 5 90 0 0 0 161144 5992 0 0 0 4 4 0 0 1 2 0 0 960 6369 2867 7 8 85 0 0 0 161144 5992 0 0 0 1 1 0 0 0 1 0 0 926 6612 2821 8 8 84 0 0 0 161144 5992 0 0 0 1 1 0 0 0 0 0 0 931 6613 2758 7 7 86 0 0 0 161144 6000 0 0 0 4 4 0 0 1 2 0 0 948 6370 2841 7 7 86 0 0 0 161120 5992 0 0 0 0 0 0 0 0 0 0 0 920 6576 2865 9 7 84 0 0 0 161104 5960 0 0 0 4 4 0 0 0 1 0 0 924 6486 2797 8 7 85 0 0 0 161136 6000 0 0 0 2 2 0 0 0 0 0 0 923 6621 2843 7 7 86 0 0 0 161136 6008 0 0 1 3 3 0 0 1 1 0 0 961 6729 2841 8 7 85 0 0 0 161168 5856 0 0 25 4 7 0 7 1 1 0 0 957 6778 2830 9 7 84 0 0 0 161176 5840 0 0 0 2 2 0 0 0 0 0 0 921 6404 2881 7 8 85 0 0 0 161168 5848 0 0 0 0 0 0 0 1 3 0 0 971 6570 2834 7 7 85 0 0 0 161208 5872 0 0 1 2 3 0 0 0 0 0 0 937 6430 2823 6 7 87 0 0 0 161208 5872 0 0 0 5 5 0 0 0 1 0 0 942 6555 2835 7 7 86 0 0 0 161184 5888 0 0 0 1 1 0 0 0 1 0 0 942 6560 2867 8 7 86 0 0 0 161184 5880 0 0 0 0 0 0 0 0 1 0 0 731 5054 2232 6 7 87 0 0 0 161184 5880 0 11 0 1 1 0 0 0 0 0 0 556 3735 1715 4 4 92 0 0 0 161192 5848 3 0 9 32 33 0 0 1 2 0 0 1104 7875 3254 11 8 81 0 0 0 161184 5840 0 0 7 6 13 0 6 2 3 0 0 1148 7838 3332 8 8 85 0 0 0 161128 5848 0 0 3 8 8 0 0 1 1 0 0 1484 10413 4226 12 11 77 0 0 0 161128 5864 0 0 0 1 1 0 0 0 0 0 0 1459 10325 4230 11 11 78 0 0 0 161128 5864 0 7 0 0 0 0 0 0 0 0 0 1431 10535 4070 13 11 76 0 0 0 161096 5872 10 0 15 127 129 0 2 12 14 0 0 1375 8612 3501 13 10 77 0 0 0 160920 6880 15 0 111 288 400 0 77 23 31 0 0 1648 7842 3590 8 11 81 0 0 0 160848 7128 0 15 0 5 5 0 0 0 0 0 0 1079 7734 3340 8 7 84 0 0 0 161032 7232 0 22 0 4 4 0 0 2 3 0 0 1118 7752 3239 9 8 83 0 0 0 160984 7176 0 0 0 0 0 0 0 0 0 0 0 1402 9873 4085 12 11 78 0 0 0 160984 7176 0 0 0 6 6 0 0 1 0 0 0 1474 10406 4217 13 11 75 0 0 0 160968 7144 0 0 5 1 1 0 0 0 0 0 0 1454 10624 4210 13 11 76 0 0 0 160824 7056 0 15 0 4 4 0 0 1 1 0 0 1492 10465 4240 12 10 78 0 0 0 160448 6848 0 15 0 4 4 0 0 0 0 0 0 1472 10525 4232 12 12 77 0 0 0 160328 6776 0 0 0 2 2 0 0 0 0 0 0 1480 10403 4294 12 11 77 0 0 0 160512 6872 0 0 0 3 3 0 0 1 0 0 0 1478 10507 4204 13 12 75 0 0 0 160952 7088 0 0 1 2 2 0 0 1 1 0 0 946 6596 2819 7 8 85 0 0 0 160944 7096 0 11 0 4 4 0 0 0 0 0 0 934 6700 2908 7 7 86 0 0 0 160928 7096 0 0 3 6 6 0 0 0 0 0 0 938 6545 2790 7 6 86 0 0 0 160904 7056 0 11 0 1 1 0 0 1 1 0 0 955 6507 2826 8 8 84 0 0 0 160936 7080 0 0 0 3 3 0 0 0 0 0 0 925 6447 2768 7 7 86 0 0 0 160920 7072 0 0 0 4 4 0 0 0 0 0 0 939 6600 2815 7 7 86 0 0 0 160920 7080 0 0 0 0 0 0 0 0 1 0 0 937 6372 2802 7 8 86 0 0 0 160912 7072 0 0 0 3 3 0 0 0 0 0 0 667 4648 1991 5 5 90 0 0 0 160800 6928 0 6 43 4 4 0 0 1 0 0 0 620 5559 1877 8 6 86 0 0 0 160424 6408 1 0 20 10 10 0 0 1 1 0 0 568 3526 1744 3 5 92 0 0 0 160360 6160 4 20 20 108 162 0 18 1 2 0 0 589 3711 1635 5 5 91

procs memory page disk faults cpu r b w swap free re mf pi po fr de sr m0 s0 s6 s7 in sy cs us sy id 0 0 0 159792 5944 0 0 0 1 1 0 0 0 0 0 0 560 3624 1670 5 5 90 0 0 0 159968 6064 1 2 23 8 8 0 0 3 1 0 0 606 3846 1833 4 4 91 0 0 0 160296 6320 0 11 2 5 5 0 0 2 2 0 0 593 3867 1662 5 4 91 0 0 0 160304 6304 0 0 27 2 2 0 0 2 1 0 0 173 470 264 1 2 98 0 0 0 159768 6368 1 18 49 132 228 0 32 1 0 0 0 147 383 99 1 1 97 0 0 0 159968 6688 0 0 0 7 7 0 0 0 0 0 0 153 496 182 2 1 96 0 0 0 160320 7008 0 0 2 3 3 0 0 1 2 0 0 157 148 96 1 1 98 0 0 0 160320 7016 0 0 0 4 4 0 0 1 1 0 0 131 278 72 1 2 97 0 0 0 160312 7016 0 0 0 3 3 0 0 0 0 0 0 127 538 73 2 1 97 0 0 0 160312 7024 0 0 0 0 0 0 0 0 1 0 0 132 106 61 0 2 98 0 0 0 160312 7016 0 0 0 2 2 0 0 0 0 0 0 209 975 369 2 2 97 0 0 0 160312 7016 0 0 0 1 1 0 0 0 0 0 0 548 3478 1676 3 3 93 0 0 0 160304 7008 0 0 0 0 0 0 0 0 0 0 0 538 3631 1650 4 4 92 0 0 0 160304 7008 0 0 0 1 1 0 0 0 1 0 0 563 3613 1678 4 5 91 0 0 0 160296 7000 0 0 1 4 4 0 0 1 1 0 0 561 3741 1681 4 4 91 0 0 0 160296 6992 0 0 0 1 1 0 0 0 0 0 0 542 3617 1679 4 4 92 0 0 0 160296 6712 3 33 53 12 10 0 0 0 1 0 0 582 4032 1607 5 5 90 0 0 0 160296 6320 0 0 1 3 3 0 0 1 1 0 0 559 3699 1681 4 5 91 0 0 0 160272 6304 0 0 0 0 0 0 0 0 0 0 0 548 3500 1670 3 4 93 0 0 0 160272 6272 0 0 6 4 4 0 0 1 0 0 0 557 3463 1678 4 5 92 0 0 0 160264 6272 0 0 0 4 4 0 0 1 1 0 0 569 3588 1651 4 5 92 0 0 0 160240 6272 0 0 0 0 0 0 0 0 0 0 0 545 3611 1636 4 5 92 0 0 0 160224 6240 0 0 0 3 3 0 0 0 0 0 0 560 3629 1715 4 4 92 0 0 0 160256 6280 0 0 0 3 3 0 0 0 0 0 0 559 3713 1624 3 4 93 0 0 0 160256 6304 0 0 0 0 0 0 0 0 0 0 0 545 3487 1674 3 4 93 0 0 0 160256 6312 0 0 0 1 1 0 0 0 0 0 0 536 3488 1690 4 6 90 0 0 0 160256 6312 0 0 1 4 4 0 0 0 0 0 0 542 3515 1616 4 3 93 0 0 0 160256 6312 0 0 0 0 0 0 0 0 1 0 0 551 3533 1630 4 5 92 0 0 0 160728 6776 0 0 0 1 1 0 0 0 0 0 0 546 3579 1629 4 4 92 0 0 0 160728 6776 0 0 0 2 2 0 0 0 0 0 0 546 3485 1683 4 4 92 0 0 0 161536 7544 0 6 0 2 2 0 0 1 1 0 0 367 1985 949 2 3 95 0 0 0 162008 7984 0 0 0 0 0 0 0 0 0 0 0 138 143 78 0 2 98 0 0 0 162008 7976 0 0 0 0 0 0 0 0 0 0 0 123 144 61 0 1 99 0 0 0 162008 7976 0 0 0 0 0 0 0 0 0 0 0 117 113 56 0 1 99 0 0 0 162008 7976 0 0 0 0 0 0 0 0 0 0 0 127 267 61 1 2 98 0 0 0 162008 7968 0 0 0 0 0 0 0 0 0 0 0 119 108 55 0 1 99 0 0 0 162008 7968 0 0 0 0 0 0 0 0 0 0 0 117 238 60 0 1 99 0 0 0 162008 7952 0 0 0 0 0 0 0 0 1 0 0 131 141 62 0 2 98 0 0 0 162008 7944 0 0 0 0 0 0 0 0 0 0 0 117 112 54 0 1 99 0 0 0 162008 7936 0 0 0 0 0 0 0 0 0 0 0 121 175 63 0 1 99 0 0 0 162008 7920 0 0 0 0 0 0 0 0 0 0 0 208 872 349 1 2 97 0 0 0 162008 7904 0 0 0 0 0 0 0 0 1 0 0 561 3545 1673 3 4 93 Received on Fri May 05 2000 - 16:31:28 CDT