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