Stored proc problem on 9.30.UC1

I have a problem with at stored proc "freezing" (actually just taking a long time to complete - going from less than a second to several minutes) periodically under 9.30.UC1. We recently upgraded from 7.31 where this proc had no problems whatsoever. However, since upgrading this has been happening about once a week. The fix is to update statistics for stored procs and it works again. HOWEVER, I have a nightly process that updates statistics which includes stored procs so I don't understand why it has to be run yet again to fix it! In addition, the underlying table the stored proc uses does not change all that much so I don't understand why the query plan would be effected so significantly. We NEVER had this problem under 7.x. Has anyone else had a similar problem with 9.x? Any solutions? I've tried using the AVOID FULL optimizer hint but that hasn't worked. I was trying to avoid forcing the optimizer to the appropriate index but that may be what I have to do. sending to informix-list

The query plan will be regenerated when the underlying tables change, I'd expected other procedures that are running at the time to start having locking problems on sysprocplan. Have you tried dropping the lock wait time down to see if will error out with a locking problem? Running nightly stats on the procedure will not always get round it the problem. "Weaver, Bill" wrote: > > I have a problem with at stored proc "freezing" (actually just taking a long > time to complete - going from less than a second to several minutes) > periodically under 9.30.UC1. We recently upgraded from 7.31 where this proc > had no problems whatsoever. However, since upgrading this has been > happening about once a week. The fix is to update statistics for stored > procs and it works again. HOWEVER, I have a nightly process that updates > statistics which includes stored procs so I don't understand why it has to > be run yet again to fix it! In addition, the underlying table the stored > proc uses does not change all that much so I don't understand why the query > plan would be effected so significantly. We NEVER had this problem under > 7.x. > > Has anyone else had a similar problem with 9.x? Any solutions? I've tried > using the AVOID FULL optimizer hint but that hasn't worked. I was trying to > avoid forcing the optimizer to the appropriate index but that may be what I > have to do. > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Just a wild guess.. Is there nightly administrative batch work that 'cycles' the tables? It might be that the stored procedure is being optimized while one of the tables used within the SP is purged or empty, so the stored procedure is generating a sequential scan. "Weaver, Bill" <bweaver@fscorp.com> wrote in message news:beh7se$43u$1@terabinaries.xmission.com... > > I have a problem with at stored proc "freezing" (actually just taking a long > time to complete - going from less than a second to several minutes) > periodically under 9.30.UC1. We recently upgraded from 7.31 where this proc > had no problems whatsoever. However, since upgrading this has been > happening about once a week. The fix is to update statistics for stored > procs and it works again. HOWEVER, I have a nightly process that updates > statistics which includes stored procs so I don't understand why it has to > be run yet again to fix it! In addition, the underlying table the stored > proc uses does not change all that much so I don't understand why the query > plan would be effected so significantly. We NEVER had this problem under > 7.x. > > Has anyone else had a similar problem with 9.x? Any solutions? I've tried > using the AVOID FULL optimizer hint but that hasn't worked. I was trying to > avoid forcing the optimizer to the appropriate index but that may be what I > have to do. > sending to informix-list