Re: anyway to optimize this query?
query only.
Obnoxio The Clown wrote:
> >let's see...
> >
> >there are 61772773 rows in the table.
> >
> >The table is indexed on three fields, let's call it f1,f2,f3, it is a
> >composite index, the reason I do that is because
> >there is also a where clause in the query which uses all three fields
> as
> >criterias.
> >
> >update satistics was performed.
> >
> >we are running 7.22 on solaris 2.6 with 2 p300 processors and 256 meg
> of
> >rams. here are my onconfig file:
> >
> >
> >ROOTNAME rootdbs # Root dbspace name> >ROOTPATH /usr/informix/dbspace/rootdbs
> >ROOTOFFSET 0 # Offset of root dbspace into device
> >(Kbytes)
> >ROOTSIZE 125000 # Size of root dbspace (Kbytes)> >MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
> >MIRRORPATH # Path for device containing mirrored> root
> >MIRROROFFSET 0 # Offset into mirrored device (Kbytes)> >PHYSDBS rootdbs # Location (dbspace) of physical log
> >PHYSFILE 10000 # Physical log file size (Kbytes)
> >LOGFILES 8 # Number of logical log files
> >LOGSIZE 10000 # Logical log size (Kbytes)> >MSGPATH /usr/informix/online.log # System message log file path
> >CONSOLE /dev/null # System null message path> >ALARMPROGRAM /usr/informix/log_full.sh # Alarm program path
> >TAPEDEV /dev/null # Tape device path
> >TAPEBLK 16 # Tape block size (Kbytes)
> >TAPESIZE 10240 # Maximum amount of data to put on tape
> >(Kbytes)
> >LTAPEDEV value # Log tape device path
> >LTAPEBLK 16 # Log tape block size (Kbytes)
> >LTAPESIZE 10240 # Max amount of data to put on log tape
> >(Kbytes)> >STAGEBLOB # INFORMIX-OnLine/Optical staging area
> >SERVERNUM 1 # Unique id corresponding to a OnLine> >instance
> >DBSERVERNAME Hamlet # Name of default database server
> >DBSERVERALIASES Macbeth # List of alternate dbservernames
> >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 2 # Number of user (cpu) vps
> >SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps> to
> >one
>
> Sometimes it helps to treat a 2 CPU box as a single CPU box. Have you
> tried this?
>
> >NOAGE 1 # Process aging
> >AFF_SPROC 0 # Affinity start processor
> >AFF_NPROCS 0 # Affinity number of processors
> >LOCKS 2000 # Maximum number of locks
> >BUFFERS 37768 # Maximum number of shared buffers
> >NUMAIOVPS 15 # Number of IO vps
> >PHYSBUFF 32 # Physical log buffer size (Kbytes)
> >LOGBUFF 32 # Logical log buffer size (Kbytes)>
> Might try bigger PHYSBUFF and LOGBUFF, unless you're using unbuffered
> logging.
>
> >LOGSMAX 10 # Maximum number of logical log files
> >CLEANERS 4 # Number of buffer cleaner processes>
> Do you only have 4 disks? How come you've got more AIO VPs than
> cleaners?
>
> >SHMBASE 0xa000000 # Shared memory base address
> >SHMVIRTSIZE 8000 # initial virtual shared memory segment> size
> >
> >SHMADD 8192 # Size of new shared memory segments
> >(Kbytes)
> >SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> >
> >CKPTINTVL 120 # Check point interval (in sec)
> >LRUS 4 # Number of LRU queues>
> Why do you have so few LRU queues?
>
> >LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning> limit
> >LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
> >LTXHWM 50 # 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)
> >OFF_RECVRY_THREADS 10 # Default number of offline worker> >threads
> >ON_RECVRY_THREADS 1
> >DRAUTO 0 # DR automatic switchover
> >DRINTERVAL 30 # DR max time between DR buffer flushes
> (in
> >sec)
> >DRTIMEOUT 30 # DR network timeout (in sec)
> >DRLOSTFOUND /tmp # DR lost+found file path> >CDR_LOGBUFFERS 2048 # size of log reading buffer pool
> (Kbytes)
> >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)
> >BAR_ACT_LOG /tmp/bar_act.log
> >BAR_MAX_BACKUP 0
> >BAR_RETRY 1
> >BAR_NB_XPORT_COUNT 10
> >BAR_XFER_BUF_SIZE 31
> >RA_PAGES 80 # Number of pages to attempt to read> ahead
> >RA_THRESHOLD 20 # Number of pages left before next> group
> >DBSPACETEMP tempdb1 # Default temp dbspaces>
> Add some more temp dbspaces.
>
> >DUMPDIR /tmp # Preserve diagnostics in this> directory
> >DUMPSHMEM 1 # Dump a copy of shared memory
> >DUMPGCORE 0 # Dump a core image using 'gcore'
> >DUMPCORE 0 # Dump a core image (Warning:this> aborts
> >OnLine)
> >DUMPCNT 1 # Number of shared memory or gcore> dumps for
> >
> >FILLFACTOR 90 # Fill factor for building indexes
> >USEOSTIME 0 # 0: use internal time(fast), 1: get> time
> >from OS(slow)
> >MAX_PDQPRIORITY 12 # Maximum allowed pdqpriority
> >DS_MAX_QUERIES 8 # Maximum number of decision support> queries
> >
> >DS_TOTAL_MEMORY 36000 # Decision support memory (Kbytes)
> >DS_MAX_SCANS 150 # Maximum number of decision support> scans
>
> Are you running this as a PDQ query? If you are, turn that off and try
> again.
>
> >DATASKIP off # List of dbspaces to skip
> >OPTCOMPIND 2 # To hint the optimizer
> >ONDBSPACEDOWN 2 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 => WAIT
> >LBU_PRESERVE 0 # Preserve last log for log backup
> >OPCACHEMAX 0 # Maximum optical cache size (Kbytes)
> >HETERO_COMMIT 0
> >NETTYPE ipcshm,2,100,CPU # Configure poll thread(s) for nettype
> >NETTYPE tlitcp,2,100,NET # Configure poll thread(s) for nettype> >
> >ok, hmm, what else? that's about all I can think of. btw, I can't index
> all
> >the grouped field. I am constructing the sql by a web p