Re[2]: Performance degraded after detaching indexes / fragme

Hi Steven, Thanks for the suggestion, but I really think the CPUs can't be the bottleneck in my case. I already tried the same on a much bigger machine too (SUN Enterprise Server E6000 with 14 CPUs - NUMCPUVPS set to 13), with the same performance degradation for both possible solutions to my problem (32 GB limit for a non-fragmented table). [When using iostat/vmstat/mpstat, I really never see CPU bottlenecks. If we encounter performance problems, they are mostly caused by disk I/O and the fact that the ABAP/4 programs are not always so efficient. With the size of our OLTP production DB (currently 300GB with a weekly growth rate of 3GB and already a lot of tables over 1 GB), inefficient ABAP programs have a global impact on performance. Some of the inefficiences are caused by coding many levels of NESTED SELECTs instead of using JOINs or by ABAP programs which do not use the proper index or do not use enough of the right index as lower index filter.] My performance test consists of two parts : a single SELECT against the big table which reads 1% of the data rows via the index (ixda-ra) and four simultaneous SELECTs which each read about 1% of the data rows (different rows though for each of the SELECTs) via the index. I used the same ONCONFIG and ENVIRONMENT VARIABLE values during most of my tests. I once ran the tests in the RR-fragmentation setup with PDQPRIORITY=25 (as I have 4 parallel SELECTs), but this did not made it faster. Do you have any idea about the reason for the significant differences in the onstat -p and onstat -g glo output for the original setup compared with the "detached indexes" setup and the "RR-fragmentation with detached indexes" setup ? I have no explanation at all for them. Kind regards, Mario Opsomer ______________________________ Reply Separator _________________________________ Subject: Re: Performance degraded after detaching indexes / fragmenta Author: hause011@garnet.tc.umn.edu (Steven Hauser) at internet Date: 13/1/99 10:58 Possibly you have hit the limits of your little SMP (4 CPU?). Or if you got them, jack NUMCPUVPS up and check the parallel processing parameters in the ONCONFIG. Time for more CPUs. Fragmentation splits up stuff so more parallelization can happen. but it won't happen if you don't have any CPUs. >> I used the same ONCONFIG file during all tests (NUMCPUVPS=3; LRU=32; >> BUFFERS=100000; SHMVIRTSIZE=128000; RA_PAGES=32; RA_THRESHOLD=26). -- --------------------------------------------------------- Steven Hauser, hause011@tc.umn.edu Phone: (612)626-7135 Fax: (612)625-6853 ---------------------------------------------------------