Stored proc problem on 9.30.UC1

Stored proc problem on 9.30.UC1

Post by Weaver, Bil » Thu, 10 Jul 2003 22:49:01



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

 
 
 

Stored proc problem on 9.30.UC1

Post by Paul Watso » Thu, 10 Jul 2003 23:35:16


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.


> 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          #

 
 
 

Stored proc problem on 9.30.UC1

Post by Madison Prue » Fri, 11 Jul 2003 19:59:11


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.


Quote:

> 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

 
 
 

1. Problems droping a procedure in IDS - 9.30.UC1

Hi everybody,

I had an IDS - 9.30.UC1 and for my surprise i have to procedures
installed in  a
database with the same name. The diference is that this two procedures
with  the
same name are different in one thing : The number of args they recieve
is not equal.

Working with IDS - 7.31.UC6 this is not possible cause the index in
sysprocedures
dont supports this.

All of this is very interesting and almost fun, but when i try to drop
"the procedure"
with a  DROP PROCEDURE procedure-name Informix say's me :

-9700   Routine (routine_name) ambiguous - more than one routine
resolves to given signature.
 ....

At last, i decided to access sysprocedures and delete by procid.

Seems that all is allright but i cant beleive what i saw. :-0

Is this the solution my friends or sameone has another ????

Regards

Jordi

2. Named Pipe Backup Device

3. Date Conversion Problem( Due to ESQL 9.30.UC1 upgrade)

4. ORDER BY DESC returning ASC not DESC !?

5. Linux RDS 4GL 7.30.UC1 problem

6. SQL Server job history

7. Stored Proc String parameter limited to 30 Characters ?????

8. IDS 9.30.UC1 on RedHat v8.0 [91]

9. 4GL 7.30.UC1 to UC6

10. Undefined symbols with 4GL 7.30.UC1

11. 4gl and NULL integers with 7.30.UC1

12. preparing sql with datetime using 7.30.UC1