Re: Arghh! Please Help.

As mentioned, UPDATE STATISTICS is a mandatory maintenance task. But, use EXTREME caution with the HIGH option, i tried using HIGH with JUST lead columns of indexes and it caused the engine to grab TONS of virtual memory! an unexpected result. but if the optimizer does not have accurate statistics performance will suffer. "Thomas J. Girsch" wrote: > I found that when I moved from 5.x to 7.x, my memory requirements DOUBLED. > Just something to be aware of, 7.x chews a ton of memory. > > <rico@wsx.wsex.com> wrote in message news:7f0aei$41g$1@news.xmission.com... > > > > Hello All, > > > > I have upgraded Online 5.0 to IDS 7.3 while also upgrading from a ALR > 6xPentiumPro 200 to a Sun E3500 w/4x336Mhz UltraSparc. The new setup is > overall a tiny bit faster for some things, or about twice as slow for a lot > of things. > > > > I have spent a lot of time trying to tune IDS 7.3, but still cannot get > the results I would expect. Simple things on the SCO/Online5.0 are about 1.5 > to 2 times slower on the SUN/IDS7.3 box. (Before you yell at me > OPTCOMPIND=0) > > > > Here are a few questions I was hoping some of you might be able to help me > with: > > > > 1) On the SCO/Online5.0 box, when I turn on explain processing, a query > takes around 2 to 3 times longer, on the SUN/IDS box, a query with explain > processing turned on takes around 40 - 50 times as long. Is this normal? > > > > 2) In the release notes for IDS 7.3 on Solaris 2.6, the kernel parameter > "enable_sm_wa = 1" should be set, however my Solaris 2.6 box complains that > the parameter "enable_sm_wa" is not defined within the kernel. Could this > affect anything? > > > > 3) I am using Disksuite on the E3500 to software mirror my root disk and > my /usr partition which is where the informix installation is located, could > this adversely affect performance (I am sure it would a little, but how > much?) ? > > > > 4) Would having the physical and logical logs residing in the root dbspace > effect query performance? > > > > 5) Last question is I have noticed on some queries, IDS7.3 does a dynamic > hash join where Online 5.0 does a sort merge. Is there any way for me to > force IDS 7.3 to do a sort merge? > > > > Some final notes, both systems are configured with 512MB main memory, the > Sparc processors have a 4MB L2 cache as opposed to the P-Pro's which have a > 256k L2. The Intel box uses Fast-UW-SCSI and the E3500 uses internal FC-AL. > Both DB's use buffered logging. > > > > Included below is my IDS 7.3 onstat -c and -p. Thanks in advance. > > > > Olaf > > > > PS - I really hope someone can help me because I am going to puke if I > cannot get a E3500 w/IDS 7.3 to run faster than 6 Pentium Pros w/Online5.0. > > > > -------------------------------------------------------------------------- > ------ > > -------------------------------------------------------------------------- > ------ > > > > Informix Dynamic Server Version 7.30.UC6 -- On-Line -- Up 00:05:30 -- > 311296 Kbytes > > > > Configuration File: /usr/local/informix/etc/onconfig.use > > > #************************************************************************** > > # > > # INFORMIX SOFTWARE, INC. > > # > > # Title: onconfig.std > > # Description: Informix Dynamic Server Configuration Parameters > > # > > > #************************************************************************** > > > > # Root Dbspace Configuration > > > > ROOTNAME dbroot # Root dbspace name > > ROOTPATH /dev/lrdbroot # Path for device containing root dbspace > > ROOTOFFSET 0 # Offset of root dbspace into device > (Kbytes) > > ROOTSIZE 2090000 # Size of root dbspace (Kbytes)> > > > # Disk Mirroring Configuration Parameters > > > > MIRROR 1 # Mirroring flag (Yes = 1, No = 0) > > MIRRORPATH /dev/lrdbroot2 # Path for device containing mirrored root > > MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> > > > # Physical Log Configuration > > > > PHYSDBS dbroot # Location (dbspace) of physical log > > PHYSFILE 8000 # Physical log file size (Kbytes)> > > > # Logical Log Configuration > > > > LOGFILES 55 # Number of logical log files > > LOGSIZE 12500 # Logical log size (Kbytes)> > > > # Diagnostics > > > > MSGPATH /usr/local/informix/online.log # System message log file > path > > CONSOLE /usr/local/informix/console.log # System console message > path > > ALARMPROGRAM /usr/local/informix/etc/log_full.sh # Alarm program path > > SYSALARMPROGRAM /usr/local/informix/etc/evidence.sh # System Alarm program > path > > TBLSPACE_STATS 1> > > > # System Archive Tape Device > > > > TAPEDEV /dev/rmt/0 # Tape device path > > TAPEBLK 32 # Tape block size (Kbytes) > > TAPESIZE 12000000 # Maximum amount of data to put on tape > (Kbytes)> > > > # Log Archive Tape Device > > > > LTAPEDEV /dev/null # Log tape device path > > LTAPEBLK 32 # Log tape block size (Kbytes) > > LTAPESIZE 12000000 # Max amount of data to put on log tape > (Kbytes)> > > > # Optical > > > > STAGEBLOB # Informix Dynamic Server/Optical staging > area > > > > # System Configuration > > > > SERVERNUM 0 # Unique id corresponding to a Dynamic> Server instance > > DBSERVERNAME world # Name of default database server > > DBSERVERALIASES # 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 3 # 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 3 # Affinity number of processors> > > > # Shared Memory Parameters > > > > LOCKS 20000 # Maximum number of locks > > BUFFERS 128000 # Maximum number of shared buffers > > NUMAIOVPS 1 # Number of IO vps > > PHYSBUFF 32 # Physical log buffer size (Kbytes) > > LOGBUFF 32 # Logical log buffer size (Kbytes)> > LOGSMAX 100 # Maximum number of logical log files > > CLEANERS 8 # Number of buffer cleaner processes > > SHMBASE 0xa000000 # Shared memory base address > > SHMVIRTSIZE 32768 # initial virtual shared memory segment> size > > SHMADD 16384 # Size of new shared memory segments > (Kbytes) > > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited@@NL@