hi all,
i ran vmstat during the test :
[yb@centos08 ~]$ vmstat 1 15
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 0 6131400 160556 1115792 0 0 1 12 22 17 0 0 100 0 0
0 0 0 6131124 160556 1115800 0 0 0 532 540 360 1 0 99 0 0
5 1 0 6127852 160556 1116048 0 0 0 3352 1613 1162 18 1 80 1 0
7 0 0 6122984 160556 1117312 0 0 0 14608 5408 3703 86 7 6 1 0
8 0 0 6121372 160556 1117968 0 0 0 13424 5434 3741 86 7 5 2 0
7 1 0 6120504 160556 1118952 0 0 0 13616 5296 3546 86 7 5 2 0
7 0 0 6119528 160572 1119728 0 0 0 13836 5494 3597 86 7 4 2 0
6 1 0 6118744 160572 1120408 0 0 0 15296 5552 3869 89 8 3 1 0
2 0 0 6118620 160572 1120288 0 0 0 13792 4548 3054 63 6 25 6 0
0 0 0 6118620 160572 1120392 0 0 0 3552 1090 716 8 1 88 3 0
0 0 0 6118736 160572 1120392 0 0 0 1136 787 498 1 0 98 1 0
0 0 0 6118868 160580 1120400 0 0 0 28 348 324 1 0 99 0 0
0 0 0 6118992 160580 1120440 0 0 0 380 405 347 1 0 99 1 0
0 0 0 6118868 160580 1120440 0 0 0 1544 468 320 1 0 100 0 0
0 0 0 6118720 160580 1120440 0 0 0 0 382 335 0 0 99 0 0
the temp-tables normally don't populate more then 10 rows. they are being created in advanced. we don't drop them, we use ON COMMIT DELETE ROWS. i believe temp-tables are in the RAM, so no disk-i/o, right? and also: no writing to the system catalogs, right?
about returning multiple refcursors, we checked this issue in the past, and we concluded that returning many small refcursors (all have the same structure), is faster than returning 1 big refcursor. dose it sound wired (maybe it worth more tests)? that's why we took that path.
about having multiple procedures each returning one resultset: it's too much code rewrite at the web-server's code.
the disk system is a built-in intel fake-raid, configured as raid0. i do a dual-boot, so both windows and centos are on the same hardware.
Thanks again for any more help.
