Help! Poor performance

I am getting very bad performance on an unloaded system and need some more ideas on what might be wrong. Platform: Silicon Graphics Challenge XL 8 250-Mhz CPUs 2048 megabytes memory Lots of disks Informix Online DSA v7.12.uc3x2 If I use dbaccess to do the following: unix> dbaccess db1 <<EOF output to /dev/null select * from consumer;EOF I can sequentially scan 7.8 million rows in 45 minutes - not good! I have look for waiters, locks, I/O, everything. The only thing that I see when this query is executing is a single aio vp reading big buffers (onstat -g iob). Since I have lots of memory, I have made all memory-related parameters large in the hopes of gaining good throughput for such sequential scans. This is a data warehouse application, so sequential scans are not out of the ordinary. I believe that the disk I/O is running smoothly, but that Online is getting hung up in reading big buffers into the data buffer pool, then reading those buffers and returning to sqlexec thread. I thought that memory reads were quick, so I must be missing something. Here is my onconfig file: # # INFORMIX SOFTWARE, INC. # # Title: onconfig.pmds01 # Description: INFORMIX-OnLine Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name# Use OS symbolic links to ease data movement in case of the need # to do emergency reconfiguration # ...but, if the /apps filesystem goes down, we lose the system, so... # maybe put links on the root disk in a directory called /I/pmds01/rdsk ROOTPATH /apps/informix/admin/pmds01/link/s7s91l2p11 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)# Yeah, it's huge, but it's dedicated to a single, 2GB raw LV #ROOTSIZE 2097150 # Size of root dbspace (Kbytes) ROOTSIZE 500000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) # Probably need to do xlv mirroring on rootdbs, logical log, # physical log MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS phylog_01 # Location (dbspace) of physical log # Sized larger than Perf book, since dbi 200GB used 10240 PHYSFILE 204800 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 6 # Number of logical log files# Assuming two PDL jobs running 8-10 threads each and # committing every 100 rows # Check this size vis a vis checkpoint strategy LOGSIZE 4096 # Logical log size (Kbytes) # Diagnostics MSGPATH /apps/informix/admin/pmds01/log/online.log # System message log file path CONSOLE /apps/informix/admin/pmds01/log/console.log # System console message path ALARMPROGRAM # Alarm program path # System Archive Tape Device TAPEDEV /dev/null # Tape device path TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 10 # Unique id corresponding to a OnLine instance DBSERVERNAME pmds01 # Name of default database server DBSERVERALIASES pmds01_tcp # List of alternate dbservernames DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 7 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one NOAGE 1 # Process aging AFF_SPROC 1 # Affinity start processor AFF_NPROCS 7 # Affinity number of processors # Shared Memory Parameters LOCKS 20480 # Maximum number of locks BUFFERS 61440 # Maximum number of shared buffers# No kernel async I/O, so set to 2X number of disks...but we're running # on logical (LUN) RAID disks, so set to 4X number of LUNs and monitor. # Probably an overallocation, but can be down-sized #NUMAIOVPS 48 # Number of IO vps NUMAIOVPS 8 # Number of IO vps PHYSBUFF 32 # Physical log buffer size (Kbytes) LOGBUFF 32 # Logical log buffer size (Kbytes)LOGSMAX 12 # Maximum number of logical log files # Since using RAID LUN-based LVs, try 2X number of LUNs CLEANERS 24 # Number of buffer cleaner processes SHMBASE 0x0 # Shared memory base address# Make this very large (larger than BUFFERS), since we will be # doing a lot of DSS reads/writes, requiring virtual pool Big # buffers SHMVIRTSIZE 307200 # initial virtual shared memory segment size SHMADD 32768 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) # LRU=max(4,NUMCPUVPs)#Old LRUS 28 # Number of LRU queues LRUS 7 # Number of LRU queues# Used dbI values here LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit LTXHWM 40 # Long transaction high water mark percentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 32 # Stack size (Kbytes) # System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine on your platform # see the last line of output from the command, 'onstat -b'. # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS 10 # Default number of offline worker threads ON_RECVRY_THREADS 1 # Default number of online worker threads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switcho