Informix - Problem description
Problem IT41439 | Status: Closed |
REPEATED CALLS ON SECONDARY SERVERS TO RETRIEVE NON-EXISTENT QUERY DEPENDENCY PLAN MAY LEAD TO REDUCED PERFORMANCE | |
product: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problem description: | |
Stored procedures created without use of SQL statements or reference to other stored procedures generally have no dependencies and so do not have a dependency plan saved in sysprocplan. One exception to this concerns the use of the CURRENT keyword, for example: create procedure pcheck (p_start_time datetime year to second) returning char(1); if (CURRENT < p_start_time) then return 'Y'; else return 'N'; end if end procedure; When such a procedure is executed on a secondary server an optimization is skipped that will flag the procedure as not having a dependency plan meaning that every execution of the procedure within a session will attempt to load a plan. On large instances with many hundreds of concurrent sessions executing these type of stored procedures this may create contention on the partition mutex for the sysprocplan table resulting in reduced performance. The contention may be viewed with the "onstat -g wmx" command when a list may be observed of many sessions waiting for the partition with the name "pt_NNNNNN" where NNNNNN represents the hexadecimal partition number of the sysprocplan table. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC16 and 14.10.xC9. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC16 or 14.10.xC9. * **************************************************************** | |
Local Fix: | |
Solution | |
Workaround | |
**************************************************************** * USERS AFFECTED: * * Users of Informix Server prior to 12.10.xC16 and 14.10.xC9. * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Informix Server 12.10.xC16 or 14.10.xC9. * **************************************************************** | |
Comment | |
Fixed in Informix Server 12.10.xC16 and 14.10.xC9. | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 06.07.2022 08.05.2023 08.05.2023 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |