Optimizer Question

Could the addition of memory to IDS (BUFFERS, SHMVIRTSIZE) cause the optimizer to act differently? Over the past month or two we've added memory to our server and increased these two parameters (as well as CLEANERS and LRUS) and we keep coming across queries that have run for years and now take forever to complete. We are still running update statistics like we always have, and nothing has changed with any table structures, indexes or programs. Here is a little info about our system: HPUX 11.11 6 CPU's 12 GB Memory IDS 9.40.FC4 Some of the config File: # Root Dbspace Configuration ROOTNAME a_rootdbs # Root dbspace nameROOTPATH /dev/inf/chunk001 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 500000 # Size of root dbspace (Kbytes)# Physical Log Configuration PHYSDBS a_plogdbs # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes)# Logical Log Configuration LOGFILES 41 # Number of logical log files#LOGSIZE 2000 # Logical log size (Kbytes) LOGSIZE 50000 # Logical log size (Kbytes) ALARMPROGRAM /home/informix/9.40/etc/log_full.sh # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME online_shm # Name of default database server DBSERVERALIASES online_tcp # List of alternate dbservernames NETTYPE ipcshm,3,75,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 240 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 500000 # Maximum number of locks BUFFERS 200000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 127 # 5/10/07 M. Gregory SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 1792000 # initial virtual shared memory segmentsize SHMADD 256000 # Size of new shared memory segments (Kbytes) SHMTOTAL 6292484 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 127 # 5/10/07 - M. Gregory LRU_MAX_DIRTY 4.000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) DYNAMIC_LOGS 2 LTXHWM 40 LTXEHWM 55 OFF_RECVRY_THREADS 30 # Default number of offline workerthreads ON_RECVRY_THREADS 20 # Default number of online workerthreads # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled bydefault # Read Ahead Variables RA_PAGES 20 # Number of pages to attempt to readahead RA_THRESHOLD 8 # Number of pages left before next group # DBSPACETEMP: DBSPACETEMPtempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7 FILLFACTOR 90 # Fill factor for building indexes # method for OnLine to use when determining current time USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow) # Parallel Database Queries (pdq) MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority#DS_MAX_QUERIES # Maximum number of decision support queries #DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN THIS OFF AFTER THE IMPORT DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURNTHIS OFF AFTER THE IMPORT DS_MAX_SCANS 1048576 # Maximum number of decision supportscans DATASKIP off# OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. OPTCOMPIND 2 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAITOPCACHEMAX 0 # Maximum optical cache size (Kbytes) BLOCKTIMEOUT 3600 # Default timeout for system block SYSALARMPROGRAM /home/informix/9.40/etc/evidence.sh # System Alarmprogram path # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS OPT_GOAL -1 ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 oranything but 1) BAR_BSALIB_PATH /opt/omni/lib/libob2informix_64bit.sl DS_MAX_QUERIES 6 # Maximum number of decision supportqueries And our onstat -p output: Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 853365810 1694740843 21147087047 95.96 32177895 72548903 233327825 86.21 isamtot open start read write rewrite delete commit rollbk 16032208597 74657947 503175174 12697405047 52576047 12624733 7721511 10778784 4 599 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 193051.85 123974.95 1063 3084 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 77516285 3282 20350227577 7 0 2145 4501445 3945343 ixda-RA idx-RA da-RA RA-pgsused lchwaits 343504294 25831160 120490006 486820227 19178796 Mary Gregory Senior Systems Programmer Rheem Water Heating Office (334) 260-1460 Cell (334) 462-1852 mgregory@rheem.com

Could be lots of reasons, unfortunately nothing showing in your config or stats. If you are using HP disk arrays, make sure the arrays haven't decided to reconfigure themselves into RAID5 because they were getting close to filling up. Make sure that the HP KAIO modules are still loaded and running and see if you can tune them. Growing tables can cause the optimizer to change the query plans it generates. Mass deletes can leave indexes in a non-optimal state and 9.40's btree cleaners were inefficient. If you have a lot of btree cleaner activity you may want to rebuild some of the affected indexes manually. Art S. Kagel ----- Original Message ----- From: Mary Gregory <ids@iiug.org> At: 6/06 10:08:32 Could the addition of memory to IDS (BUFFERS, SHMVIRTSIZE) cause the optimizer to act differently? Over the past month or two we've added memory to our server and increased these two parameters (as well as CLEANERS and LRUS) and we keep coming across queries that have run for years and now take forever to complete. We are still running update statistics like we always have, and nothing has changed with any table structures, indexes or programs. Here is a little info about our system: HPUX 11.11 6 CPU's 12 GB Memory IDS 9.40.FC4 Some of the config File: # Root Dbspace Configuration ROOTNAME a_rootdbs # Root dbspace nameROOTPATH /dev/inf/chunk001 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 500000 # Size of root dbspace (Kbytes)# Physical Log Configuration PHYSDBS a_plogdbs # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes)# Logical Log Configuration LOGFILES 41 # Number of logical log files#LOGSIZE 2000 # Logical log size (Kbytes) LOGSIZE 50000 # Logical log size (Kbytes) ALARMPROGRAM /home/informix/9.40/etc/log_full.sh # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME online_shm # Name of default database server DBSERVERALIASES online_tcp # List of alternate dbservernames NETTYPE ipcshm,3,75,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 240 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 500000 # Maximum number of locks BUFFERS 200000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 127 # 5/10/07 M. Gregory SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 1792000 # initial virtual shared memory segmentsize SHMADD 256000 # Size of new shared memory segments (Kbytes) SHMTOTAL 6292484 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 127 # 5/10/07 - M. Gregory LRU_MAX_DIRTY 4.000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) DYNAMIC_LOGS 2 LTXHWM 40 LTXEHWM 55 OFF_RECVRY_THREADS 30 # Default number of offline workerthreads ON_RECVRY_THREADS 20 # Default number of online workerthreads # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled bydefault # Read Ahead Variables RA_PAGES 20 # Number of pages to attempt to readahead RA_THRESHOLD 8 # Number of pages left before next group # DBSPACETEMP: DBSPACETEMPtempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7 FILLFACTOR 90 # Fill factor for building indexes # method for OnLine to use when determining current time USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow) # Parallel Database Queries (pdq) MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority#DS_MAX_QUERIES # Maximum number of decision support queries #DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN THIS OFF AFTER THE IMPORT DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURNTHIS OFF AFTER THE IMPORT DS_MAX_SCANS 1048576 # Maximum number of decision supportscans DATASKIP off# OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. OPTCOMPIND 2 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAITOPCACHEMAX 0 # Maximum optical cache size (Kbytes) BLOCKTIMEOUT 3600 # Default timeout for system block SYSALARMPROGRAM /home/informix/9.40/etc/evidence.sh # System Alarmprogram path # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS OPT_GOAL -1 ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 oranything but 1) BAR_BSALIB_PATH /opt/omni/lib/libob2informix_64bit.sl DS_MAX_QUERIES 6 # Maximum number of decision supportqueries And our onstat -p output: Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 853365810 1694740843 21147087047 95.96 32177895 72548903 233327825 86.21 isamtot open start read write rewrite delete commit rollbk 16032208597 74657947 503175174 12697405047 52576047 12624733 7721511 10778784 4 599 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 193051.85 123974.95 1063 3084 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 77516285 3282 20350227577 7 0 2145 4501445 3945343 ixda-RA idx-RA da-RA RA-pgsused lchwaits 343504294 25831160 120490006 486820227 19178796 Mary Gregory Senior Systems Programmer Rheem Water Heating Office (334) 260-1460 Cell (334) 462-1852@@

We are using EMC disks, so the RAID 5 thing isn't an issue. KAIO stuff looks ok (although I'm not sure how to tune it ... I'll have to look into that.) I'm thinking that rebuilding our indexes might be the best bet. Is there any way for me to tell that I have a lot of btree cleaner activity for a particular index, or do I just have to guess based on the number of inserts and deletes I think we have done? I know of two tables that are candidates for rebuilds, but I'm wondering if there is a way I can identify others (trying to be proactive here.) Thanks!! -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, BLOOMBERG/ 731 LEXIN Sent: Wednesday, June 06, 2007 9:17 AM To: ids@iiug.org Subject: Re: Optimizer Question [9298] Could be lots of reasons, unfortunately nothing showing in your config or stats. If you are using HP disk arrays, make sure the arrays haven't decided to reconfigure themselves into RAID5 because they were getting close to filling up. Make sure that the HP KAIO modules are still loaded and running and see if you can tune them. Growing tables can cause the optimizer to change the query plans it generates. Mass deletes can leave indexes in a non-optimal state and 9.40's btree cleaners were inefficient. If you have a lot of btree cleaner activity you may want to rebuild some of the affected indexes manually. Art S. Kagel ----- Original Message ----- From: Mary Gregory <ids@iiug.org> At: 6/06 10:08:32 Could the addition of memory to IDS (BUFFERS, SHMVIRTSIZE) cause the optimizer to act differently? Over the past month or two we've added memory to our server and increased these two parameters (as well as CLEANERS and LRUS) and we keep coming across queries that have run for years and now take forever to complete. We are still running update statistics like we always have, and nothing has changed with any table structures, indexes or programs. Here is a little info about our system: HPUX 11.11 6 CPU's 12 GB Memory IDS 9.40.FC4 Some of the config File: # Root Dbspace Configuration ROOTNAME a_rootdbs # Root dbspace nameROOTPATH /dev/inf/chunk001 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 500000 # Size of root dbspace (Kbytes)# Physical Log Configuration PHYSDBS a_plogdbs # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes)# Logical Log Configuration LOGFILES 41 # Number of logical log files#LOGSIZE 2000 # Logical log size (Kbytes) LOGSIZE 50000 # Logical log size (Kbytes) ALARMPROGRAM /home/informix/9.40/etc/log_full.sh # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME online_shm # Name of default database server DBSERVERALIASES online_tcp # List of alternate dbservernames NETTYPE ipcshm,3,75,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 240 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 500000 # Maximum number of locks BUFFERS 200000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 127 # 5/10/07 M. Gregory SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 1792000 # initial virtual shared memory segmentsize SHMADD 256000 # Size of new shared memory segments (Kbytes) SHMTOTAL 6292484 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 127 # 5/10/07 - M. Gregory LRU_MAX_DIRTY 4.000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) DYNAMIC_LOGS 2 LTXHWM 40 LTXEHWM 55 OFF_RECVRY_THREADS 30 # Default number of offline workerthreads ON_RECVRY_THREADS 20 # Default number of online workerthreads # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled bydefault # Read Ahead Variables RA_PAGES 20 # Number of pages to attempt to readahead RA_THRESHOLD 8 # Number of pages left before next group # DBSPACETEMP: DBSPACETEMPtempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7 FILLFACTOR 90 # Fill factor for building indexes # method for OnLine to use when determining current time USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow) # Parallel Database Queries (pdq) MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority#DS_MAX_QUERIES # Maximum number of decision support queries #DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN THIS OFF AFTER THE IMPORT DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURNTHIS OFF AFTER THE IMPORT DS_MAX_SCANS 1048576 # Maximum number of decision supportscans DATASKIP off# OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. OPTCOMPIND 2 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAITOPCACHEMAX 0 # Maximum optical cache size (Kbytes) BLOCKTIMEOUT 3600 # Default timeout for system block SYSALARMPROGRAM /home/informix/9.40/etc/evidence.sh # System Alarmprogram path # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS OPT_GOAL -1 ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 oranything but 1) BAR_BSALIB_PATH /opt/omni/lib/libob2informix_64bit.sl DS_MAX_QUERIES 6 # Maximum number of decision supportqueries And our onstat -p output: Profile dskreads pagreads bufre

Yoo could lose light scans by making buffers too big. j. >From: Mary Gregory <mary.gregory@rheem.com> >Date: 2007/06/06 Wed AM 09:07:04 CDT >To: ids@iiug.org >Subject: Optimizer Question [9297] >Could the addition of memory to IDS (BUFFERS, SHMVIRTSIZE) cause the >optimizer to act differently? Over the past month or two we've added >memory to our server and increased these two parameters (as well as >CLEANERS and LRUS) and we keep coming across queries that have run for >years and now take forever to complete. We are still running update >statistics like we always have, and nothing has changed with any table >structures, indexes or programs. Here is a little info about our >system: > >HPUX 11.11 > >6 CPU's > >12 GB Memory > >IDS 9.40.FC4 > >Some of the config File: > ># Root Dbspace Configuration >ROOTNAME a_rootdbs # Root dbspace name>ROOTPATH /dev/inf/chunk001 # Path for device containing root >dbspace >ROOTOFFSET 0 # Offset of root dbspace into device >(Kbytes) >ROOTSIZE 500000 # Size of root dbspace (Kbytes)># Physical Log Configuration >PHYSDBS a_plogdbs # Location (dbspace) of physical log >PHYSFILE 100000 # Physical log file size (Kbytes)># Logical Log Configuration >LOGFILES 41 # Number of logical log files>#LOGSIZE 2000 # Logical log size (Kbytes) >LOGSIZE 50000 # Logical log size (Kbytes)> >ALARMPROGRAM /home/informix/9.40/etc/log_full.sh # Alarm program path >TBLSPACE_STATS 1 # Maintain tblspace statistics> ># System Configuration > >SERVERNUM 1 # Unique id corresponding to a OnLine>instance >DBSERVERNAME online_shm # Name of default database server >DBSERVERALIASES online_tcp # List of alternate dbservernames >NETTYPE ipcshm,3,75,CPU # Configure poll thread(s) for nettype >NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype >DEADLOCK_TIMEOUT 240 # 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 5 # 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 0 # Affinity start processor >AFF_NPROCS 0 # Affinity number of processors> ># Shared Memory Parameters > >LOCKS 500000 # Maximum number of locks >BUFFERS 200000 # Maximum number of shared buffers >NUMAIOVPS 2 # Number of IO vps >PHYSBUFF 64 # Physical log buffer size (Kbytes) >LOGBUFF 64 # Logical log buffer size (Kbytes) >CLEANERS 127 # 5/10/07 M. Gregory >SHMBASE 0x0 # Shared memory base address >SHMVIRTSIZE 1792000 # initial virtual shared memory segment>size >SHMADD 256000 # Size of new shared memory segments >(Kbytes) >SHMTOTAL 6292484 # Total shared memory (Kbytes). >0=>unlimited >CKPTINTVL 600 # Check point interval (in sec) >LRUS 127 # 5/10/07 - M. Gregory >LRU_MAX_DIRTY 4.000 # LRU percent dirty begin cleaning limit >LRU_MIN_DIRTY 2.000 # LRU percent dirty end cleaning limit >TXTIMEOUT 0x12c # Transaction timeout (in sec) >STACKSIZE 64 # Stack size (Kbytes) > >DYNAMIC_LOGS 2 >LTXHWM 40 >LTXEHWM 55 > >OFF_RECVRY_THREADS 30 # Default number of offline worker>threads >ON_RECVRY_THREADS 20 # Default number of online worker>threads > ># CDR Variables >CDR_EVALTHREADS 1,2 # evaluator threads >(per-cpu-vp,additional) >CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) >CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR >queue (Kbytes) >CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 >none, 9 max) >CDR_SERIAL 0,0 # Serial Column Sequence >CDR_DBSPACE # dbspace for syscdr database >CDR_QHDR_DBSPACE # CDR queue dbspace (default same as >catalog) >CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces >CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled by>default ># Read Ahead Variables >RA_PAGES 20 # Number of pages to attempt to read>ahead >RA_THRESHOLD 8 # Number of pages left before next group> ># DBSPACETEMP: >DBSPACETEMP>tempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7 > >FILLFACTOR 90 # Fill factor for building indexes> ># method for OnLine to use when determining current time >USEOSTIME 0 # 0: use internal time(fast), 1: get >time from OS(slow)> ># Parallel Database Queries (pdq) >MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority>#DS_MAX_QUERIES # Maximum number of decision support >queries >#DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN THIS >OFF AFTER THE IMPORT >DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN>THIS OFF AFTER THE IMPORT >DS_MAX_SCANS 1048576 # Maximum number of decision support>scans >DATASKIP off># OPTCOMPIND ># 0 => Nested loop joins will be preferred (where ># possible) over sortmerge joins and hash joins. ># 1 => If the transaction isolation mode is not ># "repeatable read", optimizer behaves as in (2) ># below. Otherwise it behaves as in (0) above. ># 2 => Use costs regardless of the transaction isolation ># mode. Nested loop joins are not necessarily ># preferred. Optimizer bases its decision purely ># on costs. >OPTCOMPIND 2 # To hint the optimizer > >DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or >OFF (0) > >ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = >ABORT, 2 = WAIT>OPCACHEMAX 0 # Maximum optical cache size (Kbytes) > >BLOCKTIMEOUT 3600 # Default timeout for system block >SYSALARMPROGRAM /home/informix/9.40/etc/evidence.sh # System Alarm>program path > ># Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS >OPT_GOAL -1 > >ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 or>anything but 1) > >BAR_BSALIB_PATH /opt/omni/lib/libob2informix_64bit.sl >DS_MAX_QUERIES 6 # Maximum number of decision support>queries > >And our onstat -p output: > >Profile > >dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached > >853365810 1694740843 21147087047 95.96 32177895 72548903 233327825 >86.21 > >isamtot open start read write rewrite delete commit >rollbk > >16032208597 74657947 503175174 12697405047 52576047 12624733 7721511 >10778784 4 > >599 > >gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs > >0 0 0 0 0 0 0 > >ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes > >0 0 0 193051.85 123974.95 1063 3084 > >bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans > >77516285 3282 20350227577 7 0 2145 4501445 >3945343 > >ixda-RA idx-RA da-RA RA-pgsused lchwaits > >343504294 25831160 120490006 486820227 19178796 > >Mary Gregory >Senior Systems Programmer >Rheem Water Heating >Office (334) 260-1460 >Cell (334) 462-1852 >mgregory@rheem.com > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

A few years back when I upgrade a server to 9.4, I had to change OPTCOMPIND for a few programs. You could do it with in a shell script. export OPTCOMPIND=0 Take a shot at it. Thank you, Kannan Thirugnanam -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mary Gregory Sent: Wednesday, June 06, 2007 10:29 AM To: ids@iiug.org Subject: RE: Optimizer Question [9300] We are using EMC disks, so the RAID 5 thing isn't an issue. KAIO stuff looks ok (although I'm not sure how to tune it ... I'll have to look into that.) I'm thinking that rebuilding our indexes might be the best bet. Is there any way for me to tell that I have a lot of btree cleaner activity for a particular index, or do I just have to guess based on the number of inserts and deletes I think we have done? I know of two tables that are candidates for rebuilds, but I'm wondering if there is a way I can identify others (trying to be proactive here.) Thanks!! -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of ART KAGEL, BLOOMBERG/ 731 LEXIN Sent: Wednesday, June 06, 2007 9:17 AM To: ids@iiug.org Subject: Re: Optimizer Question [9298] Could be lots of reasons, unfortunately nothing showing in your config or stats. If you are using HP disk arrays, make sure the arrays haven't decided to reconfigure themselves into RAID5 because they were getting close to filling up. Make sure that the HP KAIO modules are still loaded and running and see if you can tune them. Growing tables can cause the optimizer to change the query plans it generates. Mass deletes can leave indexes in a non-optimal state and 9.40's btree cleaners were inefficient. If you have a lot of btree cleaner activity you may want to rebuild some of the affected indexes manually. Art S. Kagel ----- Original Message ----- From: Mary Gregory <ids@iiug.org> At: 6/06 10:08:32 Could the addition of memory to IDS (BUFFERS, SHMVIRTSIZE) cause the optimizer to act differently? Over the past month or two we've added memory to our server and increased these two parameters (as well as CLEANERS and LRUS) and we keep coming across queries that have run for years and now take forever to complete. We are still running update statistics like we always have, and nothing has changed with any table structures, indexes or programs. Here is a little info about our system: HPUX 11.11 6 CPU's 12 GB Memory IDS 9.40.FC4 Some of the config File: # Root Dbspace Configuration ROOTNAME a_rootdbs # Root dbspace nameROOTPATH /dev/inf/chunk001 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 500000 # Size of root dbspace (Kbytes)# Physical Log Configuration PHYSDBS a_plogdbs # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes)# Logical Log Configuration LOGFILES 41 # Number of logical log files#LOGSIZE 2000 # Logical log size (Kbytes) LOGSIZE 50000 # Logical log size (Kbytes) ALARMPROGRAM /home/informix/9.40/etc/log_full.sh # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME online_shm # Name of default database server DBSERVERALIASES online_tcp # List of alternate dbservernames NETTYPE ipcshm,3,75,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 240 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 500000 # Maximum number of locks BUFFERS 200000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 127 # 5/10/07 M. Gregory SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 1792000 # initial virtual shared memory segmentsize SHMADD 256000 # Size of new shared memory segments (Kbytes) SHMTOTAL 6292484 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 127 # 5/10/07 - M. Gregory LRU_MAX_DIRTY 4.000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) DYNAMIC_LOGS 2 LTXHWM 40 LTXEHWM 55 OFF_RECVRY_THREADS 30 # Default number of offline workerthreads ON_RECVRY_THREADS 20 # Default number of online workerthreads # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled bydefault # Read Ahead Variables RA_PAGES 20 # Number of pages to attempt to readahead RA_THRESHOLD 8 # Number of pages left before next group # DBSPACETEMP: DBSPACETEMPtempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7 FILLFACTOR 90 # Fill factor for building indexes # method for OnLine to use when determining current time USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow) # Parallel Database Queries (pdq) MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority#DS_MAX_QUERIES # Maximum number of decision support queries #DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN THIS OFF AFTER THE IMPORT DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURNTHIS OFF AFTER THE IMPORT DS_MAX_SCANS 1048576 # Maximum number of decision supportscans DATASKIP off# OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. OPTCOMPIND 2 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAITOPCACHEMAX 0 # Maximum optical cache size (Kbytes) BLOCKTIMEOUT 3600 # Default timeout fo

Mary, It does not sound like an optimizer issue to me. Do you have the old query path and the new one ? is it the same or is it different?. If it is different you may say it is an optimizer issue. But if the query path is the same then the optimizer is doing exactly the same as it did before your changes. Do you have longer checkpoint times?. I remember there was a problem in 9.3 when you increased the buffer pool to higher values ( in my case it was from 3 to 6 GB ) and then it took longer to complete a checkpoint. However, if you have specific queries taking longer than it used to take I don't think what I mentioned about the checkpoint times is your issue. hth esteban.- ----- Mensaje original ---- De: Mary Gregory <mary.gregory@rheem.com> Para: ids@iiug.org Enviado: miércoles 6 de junio de 2007, 15:07:04 Asunto: Optimizer Question [9297] Could the addition of memory to IDS (BUFFERS, SHMVIRTSIZE) cause the optimizer to act differently? Over the past month or two we've added memory to our server and increased these two parameters (as well as CLEANERS and LRUS) and we keep coming across queries that have run for years and now take forever to complete. We are still running update statistics like we always have, and nothing has changed with any table structures, indexes or programs. Here is a little info about our system: HPUX 11.11 6 CPU's 12 GB Memory IDS 9.40.FC4 Some of the config File: # Root Dbspace Configuration ROOTNAME a_rootdbs # Root dbspace nameROOTPATH /dev/inf/chunk001 # Path for device containing root dbspace ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes) ROOTSIZE 500000 # Size of root dbspace (Kbytes)# Physical Log Configuration PHYSDBS a_plogdbs # Location (dbspace) of physical log PHYSFILE 100000 # Physical log file size (Kbytes)# Logical Log Configuration LOGFILES 41 # Number of logical log files#LOGSIZE 2000 # Logical log size (Kbytes) LOGSIZE 50000 # Logical log size (Kbytes) ALARMPROGRAM /home/informix/9.40/etc/log_full.sh # Alarm program path TBLSPACE_STATS 1 # Maintain tblspace statistics # System Configuration SERVERNUM 1 # Unique id corresponding to a OnLineinstance DBSERVERNAME online_shm # Name of default database server DBSERVERALIASES online_tcp # List of alternate dbservernames NETTYPE ipcshm,3,75,CPU # Configure poll thread(s) for nettype NETTYPE soctcp,6,200,NET # Configure poll thread(s) for nettype DEADLOCK_TIMEOUT 240 # Max time to wait of lock indistributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor NUMCPUVPS 5 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vpsto one NOAGE 1 # Process aging AFF_SPROC 0 # Affinity start processor AFF_NPROCS 0 # Affinity number of processors # Shared Memory Parameters LOCKS 500000 # Maximum number of locks BUFFERS 200000 # Maximum number of shared buffers NUMAIOVPS 2 # Number of IO vps PHYSBUFF 64 # Physical log buffer size (Kbytes) LOGBUFF 64 # Logical log buffer size (Kbytes) CLEANERS 127 # 5/10/07 M. Gregory SHMBASE 0x0 # Shared memory base address SHMVIRTSIZE 1792000 # initial virtual shared memory segmentsize SHMADD 256000 # Size of new shared memory segments (Kbytes) SHMTOTAL 6292484 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 600 # Check point interval (in sec) LRUS 127 # 5/10/07 - M. Gregory LRU_MAX_DIRTY 4.000 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 2.000 # LRU percent dirty end cleaning limit TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 64 # Stack size (Kbytes) DYNAMIC_LOGS 2 LTXHWM 40 LTXEHWM 55 OFF_RECVRY_THREADS 30 # Default number of offline workerthreads ON_RECVRY_THREADS 20 # Default number of online workerthreads # CDR Variables CDR_EVALTHREADS 1,2 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) CDR_NIFCOMPRESS 0 # Link level compression (-1 never, 0 none, 9 max) CDR_SERIAL 0,0 # Serial Column Sequence CDR_DBSPACE # dbspace for syscdr database CDR_QHDR_DBSPACE # CDR queue dbspace (default same as catalog) CDR_QDATA_SBSPACE # List of CDR queue smart blob spaces CDR_MAX_DYNAMIC_LOGS 0 # Dynamic log addition disabled bydefault # Read Ahead Variables RA_PAGES 20 # Number of pages to attempt to readahead RA_THRESHOLD 8 # Number of pages left before next group # DBSPACETEMP: DBSPACETEMPtempdbs1,tempdbs2,tempdbs3,tempdbs4,tempdbs5,tempdbs6,tempdbs7 FILLFACTOR 90 # Fill factor for building indexes # method for OnLine to use when determining current time USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow) # Parallel Database Queries (pdq) MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority#DS_MAX_QUERIES # Maximum number of decision support queries #DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURN THIS OFF AFTER THE IMPORT DS_TOTAL_MEMORY # Decision support memory (Kbytes) TURNTHIS OFF AFTER THE IMPORT DS_MAX_SCANS 1048576 # Maximum number of decision supportscans DATASKIP off# OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. OPTCOMPIND 2 # To hint the optimizer DIRECTIVES 1 # Optimizer DIRECTIVES ON (1/Default) or OFF (0) ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAITOPCACHEMAX 0 # Maximum optical cache size (Kbytes) BLOCKTIMEOUT 3600 # Default timeout for system block SYSALARMPROGRAM /home/informix/9.40/etc/evidence.sh # System Alarmprogram path # Optimization goal: -1 = ALL_ROWS(Default), 0 = FIRST_ROWS OPT_GOAL -1 ALLOW_NEWLINE 0 # embedded newlines(Yes = 1, No = 0 oranything but 1) BAR_BSALIB_PATH /opt/omni/lib/libob2informix_64bit.sl DS_MAX_QUERIES 6 # Maximum number of decision supportqueries And our onstat -p output: Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 853365810 1694740843 21147087047 95.96 32177895 72548903 233327825 86.21 isamtot open start read write rewrite delete commit rollbk 16032208597 74657947 503175174 12697405047 52576047 12624733 7721511 10778784 4 599 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 193051.85 123974.95 1063 3084 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 77516285 3282 20350227577 7 0 2145 4501445 3945343 ixda-RA idx-RA da-RA RA-pgsused lchwaits 34

Hi For better performance some key aspects are 1. Fast checkpoint ( in your case its 600 -> try to reduce it to 480) 2. RA_PAGES and RA_THRESHOLD increase values to RA_PAGES to 32 or above and RA_THRESHOLD to 30 As you have IDS 9.4 you also should check for LRU_MAX_DIRTY , LRU_MIN_DIRTY settings as in IDS 10 only BUFFERPOOL setting is needed instead of LRU... Regards, Nilesh P Bhavsar

I'm going to diagree with Nilesh. I'm a bit supporter of dropping RA_ support altogether and of configuring it as low as practical. When you have a modern disk farm with lots of cache on the array and controllers and more on the drives themselves all performing read ahead for you into their own caches, there's no reason to waste your IDS buffer cache pages on read ahead. Performance monitoring backs me up on this. Actually I was going to recommend that you reduce your RA_ settings a bit, but I don't think that it is a significant factor for you either way. I calculated your RAU metric the other day from your post and it was 99.3 which is fine, a smidge low (I like to see 99.8 myself) but not hurting performance. Art S. Kagel ----- Original Message ----- From: Nilesh Bhavsar <ids@iiug.org> At: 6/07 8:38:19 Hi For better performance some key aspects are 1. Fast checkpoint ( in your case its 600 -> try to reduce it to 480) 2. RA_PAGES and RA_THRESHOLD increase values to RA_PAGES to 32 or above and RA_THRESHOLD to 30 As you have IDS 9.4 you also should check for LRU_MAX_DIRTY , LRU_MIN_DIRTY settings as in IDS 10 only BUFFERPOOL setting is needed instead of LRU... Regards, Nilesh P Bhavsar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.