SQL Session Problem
We have recently upgrade our servers and Informix versions from
Solaris 6, IDS 7.3 to Solaris 8, IDS 9.4, ESQL 9.52 from SDK 2.80.
We run an archive program every week which runs various selects on the
database and then archives data. This process used to process five
records every second but now it only processes one. When i check
session info the session always appears to be on wait(1 second). I
assume this is why it is only processing one record a second.
The output from onstat -g ses is as follows;
IBM Informix Dynamic Server Version 9.40.UC3 -- On-Line (Prim) --
Up 22:44:4
0 -- 74752 Kbytes
session #RSAM total used
dyna
mic
id user tty pid hostname threads memory
memory expl
ain
318 ef 12 4319 che-fil- 1 110592 78064
off
tid name rstcb flags curstk status
373 sqlexec c1e6e88 --CPX-- 3072 sleeping(secs: 1)
Memory pools count 1
name class addr totalsize freesize #allocfrag #freefrag
318 V ca97020 110592 32528 263 36
name free used name free
used
overhead 0 1648 scb 0 96
opentable 0 25240 filetable 0
3584
ru 0 224 log 0
2160
temprec 0 6240 blob 0
4344
keys 0 192 ralloc 0
6144
gentcb 0 1256 ostcb 0
2520
sqscb 0 17072 sql 0 40
rdahead 0 184 hashfiletab 0 280
osenv 0 1800 buft_buffer 0 256
sqtcb 0 3240 fragman 0 688
GenPg 0 592 sapi 0 136
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind directives
ca7a060 ccb6018 0 0 0 2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
Explain
318 ef_db CR Not Wait 0 0 9.03
Off
13976 byte(s) of memory is allocated from the sscpool
-----------------------------------------------------------------------------
I've checked our performance stats and cannot see anything wrong. The
selects all use efficient index searches, no sequecntial scans, etc..
the data in all the tables is in one extent per table, ive got plenty
of buffers, the output from onstat -p is all in the recommended ranges
and i've run update stats high. I think there must be some kind of
setting which is only allowing one per second that i am not aware of.
Can anyone help with this please, it would be much appreciated.