Bloody ERP schema

Hi! We changed Informix version from 7.30 to 7.31.FD2X1 on AIX 4.3.3 and after that we have regonised some performance problems. Our database is about 1Tb. Is there some general things that we haven't regoniced or has anybody had the same kind of problems? Thanks in advance. I will really apreciate your help. Thanks Rem: I will do the following onstat commands as soons as the machine will be brought up. onstat -m onstat -c onstat -g iov onstat -D onstat -R onstat -F onstat -g glo onstat -P | tail If you have any other suggestions, don't hesitate to contact me.... Thanks. This is our onconfig file : #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.SAP # Description: INFORMIX-OnLine Configuration Parameters for SAP R/3 # #************************************************************************** # Modified on the 19th of February 2003 #************************************************************************** # Root Dbspace Configuration ROOTSIZE 500000 ROOTNAME rootdbs # Root dbspace nameROOTPATH /informix/PR1/sapdata/physdev1/data01 # Path for device containing root dbspace ROOTOFFSET 16 # Offset of root dbspace into device (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 1 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH /informix/PR1/sapdata/physdev2/data05 # Path for device containing mirrored root MIRROROFFSET 16 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS physdbs # Location (dbspace) of physical log PHYSFILE 200000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 375 # Number of logical log files LOGSIZE 50000 # initial (!) Logical log size (Kbytes) # Diagnostics MSGPATH /informix/online.log # System message log file path CONSOLE /informix/console.log # System console message path ALARMPROGRAM /home/DBA/Procedures/logevent.sh # Alarm program path # System Archive Tape Device TAPEDEV /tmpdba/Legato # Tape device path for backup withLEGATO #TAPEDEV /tmpdba/Legato # Tape device path #TAPEDEV /dev/null # Tape device path TAPEBLK 16384 # Tape block size (Kbytes) TAPESIZE 65000000 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /tmpdba/Legato # Log tape device path for backup withLEGATO #LTAPEDEV /dev/null # Log tape device path LTAPEBLK 256 # Log tape block size (Kbytes) LTAPESIZE 5000000 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 0 # Unique id corresponding to a OnLineinstance DBSERVERNAME sapshm # Name of default database server DBSERVERALIASES saptcp # List of alternate dbservernames NETTYPE ipcshm,1,60,NET # Override sqlhosts nettype parameters NETTYPE soctcp,2,200,CPU # Override sqlhosts nettypeparameters DEADLOCK_TIMEOUT 60 # Max time to wait of lock indistributed env. RESIDENT 0 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, # 1 for multi-processor#NUMCPUVPS 1 # Number of user (cpu) vps NUMCPUVPS 8 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one MUTEX_WAIT_LISTS 0 # If non-zero, force use of mutex wait lists NOAGE 0 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 6000000 # Maximum number of locks BUFFERS 392000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 1280 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes)LOGSMAX 500 # Maximum number of logical log files CLEANERS 24 # Number of buffer cleaner processes SHMBASE 0x700000000000000 # Shared memory base address SHMVIRTSIZE 4194304 # initial virtual shared memorysegment size #SHMVIRTSIZE 1500000 # initial virtual shared memory segment size SHMADD 400000 # Size of new shared memory segments (Kbytes)#SHMADD 200000 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 1800 # Check point interval (in sec) LRUS 12 # Number of LRU queues LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaninglimit LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit WSTATS 1 # Caution: # The settings of LTXHWM to 70 and LTXEHWM to 80 will produce a warning in the # online log file, but the values are correct in an SAP R/3 environment LTXHWM 70 # Long transaction high water markpercentage LTXEHWM 80 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 256 # 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 workerthreads ON_RECVRY_THREADS 1 # Default number of online workerthreads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRAUTO 0 # DR automatic switchover DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec)DRLOSTFOUND /informix/etc/dr.lostfound # DR lost+found file path # Backup/Restore variables BAR_ACT_LOG /informix/bar_act.log BAR_BSALIB_PATH /usr/lib/libxnmi.o.1 # 64bits lib #BAR_MAX_BACKUP 8 BAR_MAX_BACKUP 10 BAR_RETRY 1 #BAR_NB_XPORT_COUNT 10 BAR_NB_XPORT_COUNT 10

Update stats? Also, the optimiser is prone to change its workings between releases, and we always receommend to clients that it's very important to test out critical processes on the new release *before* upgrading... "Oxow" <oxow@yahoo.com> wrote in message news:d169ee4.0305181920.35b1dff4@posting.google.com... > Hi! > > We changed Informix version from 7.30 to 7.31.FD2X1 on AIX 4.3.3 and > after > that we have regonised some performance problems. > > Our database is about 1Tb. > > Is there some general things that we haven't regoniced or has anybody > had the same kind of problems? Thanks in advance. > > I will really apreciate your help. Thanks > > Rem: > I will do the following onstat commands as soons as the machine will > be brought up. > onstat -m > onstat -c > onstat -g iov > onstat -D > onstat -R > onstat -F > onstat -g glo > onstat -P | tail> > If you have any other suggestions, don't hesitate to contact me.... > Thanks. > > This is our onconfig file : > #************************************************************************** > # > # INFORMIX SOFTWARE, INC. > # > # Title: onconfig.SAP > # Description: INFORMIX-OnLine Configuration Parameters for SAP R/3 > # > #************************************************************************** > # Modified on the 19th of February 2003 > #************************************************************************** > > # Root Dbspace Configuration > ROOTSIZE 500000 > ROOTNAME rootdbs # Root dbspace name> ROOTPATH /informix/PR1/sapdata/physdev1/data01 > # Path for device containing root > dbspace > ROOTOFFSET 16 # Offset of root dbspace into device > (Kbytes)> > # Disk Mirroring Configuration Parameters > > MIRROR 1 # Mirroring flag (Yes = 1, No = 0) > MIRRORPATH /informix/PR1/sapdata/physdev2/data05 > # Path for device containing mirrored > root > MIRROROFFSET 16 # Offset into mirrored device (Kbytes)> > # Physical Log Configuration > > PHYSDBS physdbs # Location (dbspace) of physical log > PHYSFILE 200000 # Physical log file size (Kbytes)> > # Logical Log Configuration > > LOGFILES 375 # Number of logical log files > LOGSIZE 50000 # initial (!) Logical log size > (Kbytes)> > # Diagnostics > > MSGPATH /informix/online.log > # System message log file path > CONSOLE /informix/console.log > # System console message path > ALARMPROGRAM /home/DBA/Procedures/logevent.sh # Alarm program path > > # System Archive Tape Device > > TAPEDEV /tmpdba/Legato # Tape device path for backup with> LEGATO > #TAPEDEV /tmpdba/Legato # Tape device path > #TAPEDEV /dev/null # Tape device path > TAPEBLK 16384 # Tape block size (Kbytes) > TAPESIZE 65000000 # Maximum amount of data to put on > tape (Kbytes)> > # Log Archive Tape Device > > LTAPEDEV /tmpdba/Legato # Log tape device path for backup with> LEGATO > #LTAPEDEV /dev/null # Log tape device path > LTAPEBLK 256 # Log tape block size (Kbytes) > LTAPESIZE 5000000 # Max amount of data to put on log > tape (Kbytes)> > # Optical > > STAGEBLOB # INFORMIX-OnLine/Optical staging area > > # System Configuration > > SERVERNUM 0 # Unique id corresponding to a OnLine> instance > DBSERVERNAME sapshm # Name of default database server > DBSERVERALIASES saptcp # List of alternate dbservernames > NETTYPE ipcshm,1,60,NET # Override sqlhosts nettype parameters > NETTYPE soctcp,2,200,CPU # Override sqlhosts nettype> parameters > DEADLOCK_TIMEOUT 60 # Max time to wait of lock in> distributed env. > RESIDENT 0 # Forced residency flag (Yes = 1, No = > 0) > > MULTIPROCESSOR 1 # 0 for single-processor, > # 1 for multi-processor> #NUMCPUVPS 1 # Number of user (cpu) vps > NUMCPUVPS 8 # Number of user (cpu) vps > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to one > MUTEX_WAIT_LISTS 0 # If non-zero, force use of mutex > wait lists > > NOAGE 0 # Process aging > AFF_SPROC 0 # Affinity start processor > AFF_NPROCS 0 # Affinity number of processors> > # Shared Memory Parameters > > LOCKS 6000000 # Maximum number of locks > BUFFERS 392000 # Maximum number of shared buffers > NUMAIOVPS 2 # Number of IO vps > PHYSBUFF 1280 # Physical log buffer size (Kbytes) > LOGBUFF 64 # Logical log buffer size (Kbytes)> LOGSMAX 500 # Maximum number of logical log files > CLEANERS 24 # Number of buffer cleaner processes > SHMBASE 0x700000000000000 # Shared memory base address > SHMVIRTSIZE 4194304 # initial virtual shared memory> segment size > #SHMVIRTSIZE 1500000 # initial virtual shared memory > segment size > SHMADD 400000 # Size of new shared memory segments > (Kbytes)> #SHMADD 200000 # Size of new shared memory segments > (Kbytes) > SHMTOTAL 0 # Total shared memory (Kbytes). > 0=>unlimited > CKPTINTVL 1800 # Check point interval (in sec) > LRUS 12 # Number of LRU queues > LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning> limit > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit > WSTATS 1> > # Caution: > # The settings of LTXHWM to 70 and LTXEHWM to 80 will produce a > warning in the > # online log file, but the values are correct in an SAP R/3 > environment > LTXHWM 70 # Long transaction high water mark> percentage > LTXEHWM 80 # Long transaction high water mark > (exclusive) > > TXTIMEOUT 0x12c # Transaction timeout (in sec) > STACKSIZE 256 # 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