[Info-Ingres] Query stopped running since Ingres upgrade.
McCulloch, Stephen
Hi All
Since upgrading our Ingres Installation from 2.6 to II 9.2.0 (su9.us5/143) with patch 13590 a query that was working has now stopped working,
Returning the following error:
Error : E_US1265 Database reorganization activity has rendered the query plan invalid. Try again after the db activity is complete. In case of dynamic SQL, the statement must be prepared again.
Has anyone seen this before and can offer an explanation/solution?
Thanks
Stephen McCulloch
*********************************************************************************************
The information contained in this e-mail is confidential and is intended only for the named recipient(s). If you are not the intended recipient, you must not copy, distribute or take any action or reliance on it. If you have received this e-mail in error, please notify the sender. Any unauthorised disclosure of the information contained in this e-mail is strictly prohibited.
The views and opinions expressed in this e-mail are the senders own and do not necessarily represent the views and opinions of Falkirk Council.
*********************************************************************************************
Martin Bowes
Hi Stevo,
Could you post the query?
Marty
Martin Bowes
Hi Steve,
Typically this message is generated by a database procedure or repeated query where one of the tables involved has been restructured since the query plan was created.
Karl Schendel
On Dec 14, 2009, at 7:29 AM, Martin Bowes wrote:
> Hi Steve,
>
> Typically this message is generated by a database procedure or repeated query where one of the tables involved has been restructured since the query plan was created.
It can also happen if the QSF memory setting is too low, and there are many
repeated queries, or the cache_dynamic parameter is ON. (I don't
recall whether cache_dynamic is 9.2 or 9.3.) The US1265 message can be
sort-of-kind-of a bug, because there's insufficient prioritization of
pending QSF usage. A very undersized cache can bounce a query
object out of QSF between the time it's created and the time it's
actually used, and if that happens often enough, you get US1265.
Try increasing qsf_memory and see if that fixes it.
Karl
> From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of McCulloch, Stephen
> Sent: 14 December 2009 11:20
> To: 'Ingres and related product discussion forum'
> Subject: [Info-Ingres] Query stopped running since Ingres upgrade.
>
> Hi All
>
> Since upgrading our Ingres Installation from 2.6 to II 9.2.0 (su9.us5/143) with patch 13590 a query that was working has now stopped working,
> Returning the following error:
>
> Error : E_US1265 Database reorganization activity has rendered the query plan invalid. Try again after the db activity is complete. In case of dynamic SQL, the statement must be prepared again.
>
> Has anyone seen this before and can offer an explanation/solution?
>
> Thanks
> Stephen McCulloch
>
>
>
>
>
> *********************************************************************************************
> The information contained in this e-mail is confidential and is intended only for the named recipient(s). If you are not the intended recipient, you must not copy, distribute or take any action or reliance on it. If you have received this e-mail in error, please notify the sender. Any unauthorised disclosure of the information contained in this e-mail is strictly prohibited.
>
> The views and opinions expressed in this e-mail are the senders own and do not necessarily represent the views and opinions of Falkirk Council.
> *********************************************************************************************
> _______________________________________________
> Info-Ingres mailing list
> Info-...@kettleriverconsulting.com
> http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
McCulloch, Stephen
The qsf_memory on my installation is set at 26214400. Is this figure derived from other parameters that would be affected by increasing the value?
Marty, is it just a case of rebuilding the query plan and if so how would I do that?
Stephen
Betty & Karl Schendel
On Dec 14, 2009, at 8:55 AM, McCulloch, Stephen wrote:
> Hi Karl/Marty
>
> The qsf_memory on my installation is set at 26214400. Is this figure derived from other parameters that would be affected by increasing the value?
It's only derived from qsf_guideline, which is probably set to medium. You can set qsf_guideline
to large, or you can simply set qsf_memory yourself. (The guideline is there to help people
who might otherwise be unsure as to what value to set qsf_memory to.) qsf_memory does not
affect any other parameters directly, that I can recall.
Karl
Roy Hann
26Mb for the QSF pool doesn't seem obviously cramped, but there is no
harm enlarging it. You might like to see what is reported when you set
trace point QS501. It will report the fraction that is unused.
Provided you have ~20% free you should normally be OK.
Another thing to consider is that the SQF pool is local to a server
process, so running additional server processes may be helpful too.
--
Roy
UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.
David Stephens
-----Original Message-----
From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com]On Behalf Of McCulloch, Stephen
Sent: 14 December 2009 11:20
To: 'Ingres and related product discussion forum'
Subject: [Info-Ingres] Query stopped running since Ingres upgrade.Hi All
Since upgrading our Ingres Installation from 2.6 to II 9.2.0 (su9.us5/143) with patch 13590 a query that was working has now stopped working,
Returning the following error:
Error : E_US1265 Database reorganization activity has rendered the query plan invalid. Try again after the db activity is complete. In case of dynamic SQL, the statement must be prepared again.
Has anyone seen this before and can offer an explanation/solution?
Thanks
Stephen McCulloch
*********************************************************************************************
The information contained in this e-mail is confidential and is intended only for the named recipient(s). If you are not the intended recipient, you must not copy, distribute or take any action or reliance on it. If you have received this e-mail in error, please notify the sender. Any unauthorised disclosure of the information contained in this e-mail is strictly prohibited.
The views and opinions expressed in this e-mail are the senders own and do not necessarily represent the views and opinions of Falkirk Council.
*********************************************************************************************
______________________________________________________________
This message has been scanned for all viruses by BTnet VirusScreen.
The service is delivered in partnership with MessageLabs.
This service does not scan any password protected or encrypted
attachments.
______________________________________________________________
This message has been checked for all viruses by BTnet VirusScreen.
The service is delivered in partnership with MessageLabs and does not scan any password protected or encrypted attachments.
Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of the company. The contents of this email are confidential to the addressee and may also be privileged. If you are not the addressee of this email, you must not copy, forward, disclose or otherwise use it, or any part of it, for any purpose, nor disclose its contents to any other person. If you have received this email in error please notify the sender. Please be aware that any email sent to, or received from, this address may be monitored for quality control, staff training or security purposes. Although the company scans all outgoing email and attachments for viruses, neither the sender nor the company accepts any responsibility for viruses and it remains the responsibility of the recipient to scan email and attachments (if any) for viruses.