Please Help - Performance problem when opening a CURSOR!

I am running INFORMIX 5.10 on a HP-K220 server with UNIX 10.20. My application(ESQLC) prepares,declares and opens a cursor into a table. The select statement looks like this: SELECT field_1,field_2,....,field_x FROM table WHERE field_2 = ? AND field_n = 'xyz' AND field_o = '123' ORDER BY field_p The OPEN statement looks like this: OPEN cursor_name using $FIELD_2; This works fine most of the time, but sometimes the application is held up waiting on the OPEN CURSOR for as long as 5 minutes. Monitoring it through glance, the sqlturbo spawned by the process is waiting for IO, even though this is the only user process running on the system. I can also see that the disk utilization of the chunk that this table resides on stays at about 95% to 100%(which does not happen normally). I have used 'tbcheck -pe' to see if the table had too many extents and found that it always has only 2 extents. The number of rows in the table is not very different when this happens compared to when the system functions normally. I have also found out that dropping the table after unloading the data and doing a dbload to load back the data corrects the problem. Can anybody help me in figuring out what could be causing this hangup. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

How big are the extents? If they are large (10s - 100s Mb) and the table grows and shrinks during the day, you have run into a problem that seems to occur at every bloody site I work on. If the query optimiser has been induced to do a sequential scan, it seems to check those empty slots anyway, hence the delay. In fact, this is a situation where running Update Statistics can do you more harm than good - if it knows there are few records in the table, the optimiser won't bother going to the index and will trawl the database. The only solutions I can think of are to make sure your query is doing an index read, or to drop and recreate the table (as you are doing). Hope this helps, and I look forward to someone posting a more cogent solution ;) Joe. sdayalan@my-dejanews.com wrote: > I am running INFORMIX 5.10 on a HP-K220 server with UNIX 10.20. My > application(ESQLC) prepares,declares and opens a cursor into a table. > > The select statement looks like this: > SELECT field_1,field_2,....,field_x FROM table > WHERE field_2 = ? AND field_n = 'xyz' AND field_o = '123' > ORDER BY field_p> > The OPEN statement looks like this: > OPEN cursor_name using $FIELD_2; > > This works fine most of the time, but sometimes the application is held up > waiting on the OPEN CURSOR for as long as 5 minutes. Monitoring it through > glance, the sqlturbo spawned by the process is waiting for IO, even though > this is the only user process running on the system. I can also see that the > disk utilization of the chunk that this table resides on stays at about 95% > to 100%(which does not happen normally). I have used 'tbcheck -pe' to see if > the table had too many extents and found that it always has only 2 extents. > The number of rows in the table is not very different when this happens > compared to when the system functions normally. I have also found out that > dropping the table after unloading the data and doing a dbload to load back > the data corrects the problem. Can anybody help me in figuring out what could > be causing this hangup. > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

> If the query optimiser has been induced to do a > sequential scan, it seems to check those empty slots anyway, hence the delay. You mean, that Infx will scan all pages in extent ? As far as I know extent size hasn't big impact on performance - finding free or home(remainder,index,blob) pages within a dbspace is easy because of using bitmap pages. So, I think, extent size not a problem in this case. In my opinion, the problem is the path the optimizer chooses. Try to create indexes and perform UPDATE STATISTICS. P.S. Have all recommended patches installed on your system ? Hope this help . Regads, Juri Dovgart juri@softline.kiev.ua > > Hope this helps, and I look forward to someone posting a more cogent solution ;) > Joe. >