IDS Forum

Re: Optimizer Question Â

Posted By: vze2qjg5@verizon.net
Date: Wednesday, 6 June 2007, at 10:31 a.m.

In Response To: Optimizer Question (Mary Gregory)

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.

Messages In This Thread

• Optimizer Question
  Mary Gregory -- Wednesday, 6 June 2007, at 10:07 a.m.
  • Re: Optimizer Question
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Wednesday, 6 June 2007, at 10:17 a.m.
    • RE: Optimizer Question
      Mary Gregory -- Wednesday, 6 June 2007, at 10:28 a.m.
      • RE: Optimizer Question
        Thirugnanam, Kannan -- Wednesday, 6 June 2007, at 11:08 a.m.
  • Re: Optimizer Question Â
    vze2qjg5@verizon.net -- Wednesday, 6 June 2007, at 10:31 a.m.
  • Re: Optimizer Question
    Esteban Casuscelli -- Wednesday, 6 June 2007, at 12:02 p.m.
  • Re: Optimizer Question
    NILESH BHAVSAR -- Thursday, 7 June 2007, at 8:37 a.m.
    • Re: Optimizer Question
      ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 7 June 2007, at 8:48 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.