QPlan sanity failure cause
A user on IDS 9.30.UC1X9 (Solaris 2.6) asked what causes the periodic error 'QPlan sanity failure'. Replies explained it is an internal consistency check in the optimizer that failed, i.e. essentially a bug in the check or in the structures it inspects. Suggested causes/workarounds: stale or corrupt distributions in sysdistrib (drop distributions then UPDATE STATISTICS MEDIUM), missing UPDATE STATISTICS FOR PROCEDURE after tables were dropped/altered/renamed, calling procedures with the wrong number of arguments, and a known bug (152052) where DISTINCT in a stored procedure triggers error 640, worked around by using UNIQUE. Moving off the patched engine/old OS to a mainstream release was also advised. The poster never confirmed which, if any, fixed it.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I'm wondering what the source of the 'QPlan sanity failure' is. It seems to occur periodically on a particular 9.3 server. Any thoughts? Thanks in advance
--0__=88BBE4E7DFC7EB668f9e8a93df938690918c88BBE4E7DFC7EB66 Content-type: multipart/alternative; Boundary="1__=88BBE4E7DFC7EB668f9e8a93df938690918c88BBE4E7DFC7EB66" --1__=88BBE4E7DFC7EB668f9e8a93df938690918c88BBE4E7DFC7EB66 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Was this database migrated in-place to 9.30 and no update stats were ru= n after the migration was complete ?? Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 If we all did the things we are capable of doing, we would literally astound ourselves. -- T. Edison = "RON BETS" = <rbets@metasolv.c = om> = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect QPlan sanity failure cause [281= 1] 04/12/2004 06:40 = AM = = = = = I'm wondering what the source of the 'QPlan sanity failure' is. It see= ms to occur periodically on a particular 9.3 server. Any thoughts? Thanks in advance = --1__=88BBE4E7DFC7EB668f9e8a93df938690918c88BBE4E7DFC7EB66 Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <p>Was this database migrated in-place to 9.30 and no update stats were= run after the migration was complete ??<br> <br> Thanx much,<br> <br> Rajib Sarkar<br> Advisory Software Engineer (RAS)<br> IBM Data Management Group <br> Ph : (602)-217-2100<br> Fax: (602)-217-2100<br> T/L : 667-2100<br> <br> If we all did the things we are capable of doing, we would literally as= tound ourselves. -- T. Edison<br> <br> <img src=3D"cid:10__=3D88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "RON BETS"= ; <rbets@metasolv.com>">"RON BETS" <rbets@metasolv.c= om><br> <br> <br> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D88BBE4E= 7DFC7EB668f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">"RON BETS" <rbets@metasolv.com>= </font></b><font size=3D"2"> </font><br> <font size=3D"2">Sent by: forum.subscriber@iiug.org</font> <p><font size=3D"2">04/12/2004 06:40 AM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D88BBE4E7DFC7EB668f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">QPlan sanity failure cause [2811]</font></td></tr> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D88BBE4E7DFC7= EB668f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D88BBE4E7DFC7EB668f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>I'm wondering what the source of the 'QPlan sanity failure' is. &nb= sp;It seems to occur periodically on a particular 9.3 server. Any= thoughts?<br> <br> Thanks in advance<br> <br> </tt><br> </body></html>= --1__=88BBE4E7DFC7EB668f9e8a93df938690918c88BBE4E7DFC7EB66-- --0__=88BBE4E7DFC7EB668f9e8a93df938690918c88BBE4E7DFC7EB66 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7 --0__=88BBE4E7DFC7EB668f9e8a93df938690918c88BBE4E7DFC7EB66 Content-type: image/gif; name="pic00114.gif" Content-Disposition: inline; filename="pic00114.gif" Content-ID: <20__=88BBE4E7DFC7EB668f9e8a93df938@us.ibm.com> Content-transfer-encoding: base64 R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZlqWmyFy5/6yBBuji0AxFR M00oQAqNIstqI6qKHUsWRAEAvagsmfUEAImyxgbmUpJk3IklNUtJOUAVLoUr1+wqDGTE4zk+T6FG uQb3SizBCwatiiUgCBN8vrz+zFjVyQ8FWkOlg4NQiZMB5QS8QO3mpOaKnL0Z2EKvNMSILEThKhCg zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXpuFKhSYZALd0O5RKa2z9EYKBbpb qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NENQj2jXjmfNgOZDaXb5glRmXQ33 YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4rwxxINMvpFFAz1KOODHiu+4aEw NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoCWOOUwgltIwAKRxJgbIkJAQZEq0 2YliZnpZZ4BH3CnYOXldOU
The system was freshly installed at 9.30.UC1X9 on Solaris 2.6. When we were experiencing a rash of these failures, we did the suggested update statistics on affected tables. We had corrected some apparent SCSI errors, but we are still seeing periodic QPlan sanity failures. If it is possible to know, what is a 'QPlan sanity failure' and what is it's root cause? Cheers :-)
Roughly, a 'Qplan sanity check' is an internal check for an inconsistency
in the system. A 'Qplan sanity failure' indicates that one of those
checks failed. It is probably time to start asking why you're using UC1X9
rather than a more mainstream version (say UC8 - I think that's current).
It would probably be a good idea to run ON-Check rather thoroughly -
though I don't know it would necessarily spot the problem. It would
probably be an idea to upgrade from Solaris 2.6 to something a little more
avant garde - say Solaris 8 (only about 3 years old - maybe more than
that) or even Solaris 9. This will allow you to migrate to newer versions
of IDS, too.
RCA (root cause analysis) - a bug; either in the the Qplan sanity check or
in the data structures it is checking.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
forum.subscriber@iiug.org wrote on 04/13/2004 06:28:29 AM:
> The system was freshly installed at 9.30.UC1X9 on Solaris 2.6. When
> we were experiencing a rash of these failures, we did the suggested
> update statistics on affected tables. We had corrected some> apparent SCSI errors, but we are still seeing periodic QPlan sanity
> failures. If it is possible to know, what is a 'QPlan sanity
> failure' and what is it's root cause?
>
> Cheers :-)
>
It normally arises from invalid data in sysdistrib table - used in generating the optimiser Query Plan. The fix is update stats drop distributions, then update stats medium. You are on an old and patched engine (which could be the cause). You are now supposed to upgrade to a real version as soon as it becomes available. MW > -----Original Message----- > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On > Behalf Of RON BETS > Sent: Wednesday, 14 April 2004 1:28 a.m. > To: ids@iiug.org > Subject: Re: Re: QPlan sanity failure cause [2817] > > > The system was freshly installed at 9.30.UC1X9 on Solaris 2.6. > When we were experiencing a rash of these failures, we did the > suggested update statistics on affected tables. We had corrected > some apparent SCSI errors, but we are still seeing periodic QPlan > sanity failures. If it is possible to know, what is a 'QPlan > sanity failure' and what is it's root cause? > > Cheers :-) >
We've had this problem when we forgot to do update statistics for the stored procedures, and in particular those on tables that have been dropped and recreated, altered or renamed. To be safe, I'd make a list of the stored procedures and update stats on them one at a time or in a few parallel streams. Doing a single "update statistics for procedures" could cause you to hang. This may be true if you have a lot of sp's, if you have nested sp's. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of RON BETS Sent: Tuesday, April 13, 2004 8:28 AM To: ids@iiug.org Subject: Re: Re: QPlan sanity failure cause [2817] The system was freshly installed at 9.30.UC1X9 on Solaris 2.6. When we were experiencing a rash of these failures, we did the suggested update statistics on affected tables. We had corrected some apparent SCSI errors, but we are still seeing periodic QPlan sanity failures. If it is possible to know, what is a 'QPlan sanity failure' and what is it's root cause? Cheers :-)
We had these errors when we migrated from 7.31 FD3/5 to FD7. Error occurred when the older procedures were not sent same number arguments as it was expecting. It had worked fine till then. Solution: Changed the procedures to receive the same number of arguments. Let me explain you with an example: procedure ABC_PROC(name char(20), age integer); call procedure ABC_PROC('Vivek'); QPlan sanity failure ... Solution: call procedure ABC_PROC('Vivek', 25); If this doesn't work then you have to comment procedure lines one by one to find out the culprit. Vivek -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Ostrar, Mic.... Sent: Tuesday, April 13, 2004 2:48 PM To: ids@iiug.org Subject: RE: Re: QPlan sanity failure cause [2820] We've had this problem when we forgot to do update statistics for the stored procedures, and in particular those on tables that have been dropped and recreated, altered or renamed. To be safe, I'd make a list of the stored procedures and update stats on them one at a time or in a few parallel streams. Doing a single "update statistics for procedures" could cause you to hang. This may be true if you have a lot of sp's, if you have nested sp's. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of RON BETS Sent: Tuesday, April 13, 2004 8:28 AM To: ids@iiug.org Subject: Re: Re: QPlan sanity failure cause [2817] The system was freshly installed at 9.30.UC1X9 on Solaris 2.6. When we were experiencing a rash of these failures, we did the suggested update statistics on affected tables. We had corrected some apparent SCSI errors, but we are still seeing periodic QPlan sanity failures. If it is possible to know, what is a 'QPlan sanity failure' and what is it's root cause? Cheers :-)
My apologies for this one. Actually, I found this issue from my archive. STORED PROCEDURE WITH DISTINCT KEYWORK RETURNS 640: QPLAN SANITY FAILURE. It is bug number 152052. We had initial work around by changing procedures with UNIQUE instead of DISTINCT key word. Vivek -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Vivek CHAUD.... Sent: Tuesday, April 13, 2004 4:42 PM To: ids@iiug.org Subject: RE: Re: QPlan sanity failure cause [2821] We had these errors when we migrated from 7.31 FD3/5 to FD7. Error occurred when the older procedures were not sent same number arguments as it was expecting. It had worked fine till then. Solution: Changed the procedures to receive the same number of arguments. Let me explain you with an example: procedure ABC_PROC(name char(20), age integer); call procedure ABC_PROC('Vivek'); QPlan sanity failure ... Solution: call procedure ABC_PROC('Vivek', 25); If this doesn't work then you have to comment procedure lines one by one to find out the culprit. Vivek -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Ostrar, Mic.... Sent: Tuesday, April 13, 2004 2:48 PM To: ids@iiug.org Subject: RE: Re: QPlan sanity failure cause [2820] We've had this problem when we forgot to do update statistics for the stored procedures, and in particular those on tables that have been dropped and recreated, altered or renamed. To be safe, I'd make a list of the stored procedures and update stats on them one at a time or in a few parallel streams. Doing a single "update statistics for procedures" could cause you to hang. This may be true if you have a lot of sp's, if you have nested sp's. -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of RON BETS Sent: Tuesday, April 13, 2004 8:28 AM To: ids@iiug.org Subject: Re: Re: QPlan sanity failure cause [2817] The system was freshly installed at 9.30.UC1X9 on Solaris 2.6. When we were experiencing a rash of these failures, we did the suggested update statistics on affected tables. We had corrected some apparent SCSI errors, but we are still seeing periodic QPlan sanity failures. If it is possible to know, what is a 'QPlan sanity failure' and what is it's root cause? Cheers :-)