Professional Web Applications Themes

Stored proc problem on 9.30.UC1 - Informix

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

  1. #1

    Default 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
    Weaver, Bill Guest

  2. #2

    Default Re: Stored proc problem on 9.30.UC1

    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 #
    [url]www.oninit.com[/url] #
    Paul Watson Guest

Similar Threads

  1. MySQL 5 + MX7 stored proc problem
    By jperkins99 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 29th, 09:12 PM
  2. CF5 to MX7 Problem: Sybase Stored Proc's dont work right
    By DaveF67 in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 28th, 06:37 PM
  3. cfc and stored proc
    By mcoop in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: April 13th, 05:09 PM
  4. Stored Proc problem
    By mitaka in forum ASP Database
    Replies: 1
    Last Post: May 3rd, 05:11 PM
  5. ASP vs Stored Proc vs UDF
    By Brad in forum ASP Database
    Replies: 11
    Last Post: October 28th, 01:46 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139