APAR status
Closed as program error.
Error description
Performance degradation after upgrading from IDS 9.40.FC5XT to IDS 11.50.FC5W4 on HP Itanium. I've provided a test case and also verified the slower performance in 11.50.FC6W1 on Itanium. The query plans are the same from IDS 9 to IDS 11 and the query is: select --+ ORDERED unique jobcard.job_no from channel_cust, jobcard, allcusts where jobcard.job_no = channel_cust.job_no and channel_cust.custcode = allcusts.custcode and 1=1 into temp t1 with no log; I added the ORDERED directive in testing to ensure a simple means for generating the same query plan across versions. The view definition is as follows: create view channel_cust (job_no,custcode,cname,sstreet1,sstreet2,scity,state,szip,scount ry) as select x1.job_no ,x0.custcode ,x0.cname ,x0.sstreet1 ,x0.sstreet2 ,x0.scity ,x0.sstate ,x0.szip ,x0.scountry from allcusts x0 ,jobcard x1 where ((x1.cust_no = x0.cust_no ) AND (x1.cacct_no IS NULL ) ) union all select x3.job_no ,x2.custcode ,x2.cname ,x2.sstreet1 ,x2.sstreet2 ,x2.scity ,x2.sstate ,x2.szip ,x2.scountry from allcusts x2 ,jobcard x3 where ((x3.cacct_no = x2.cust_no ) AND (x3.cacct_no IS NOT NULL ) ) ; In the provided repro, I run the query 100x as the timing results are more obvious with multiple iterations of the query. The problem can be observed in the timex output for these runs. For instance, on average, these are the times: IDS 9.40.FC5XT: 46.61 seconds IDS 11.50.FC5W4: 62.45 seconds IDS 11.50.FC6W1: 58.58 seconds The query plans for all versions are alike with the estimated cost in the IDS 9 explain output being very small compared to that in IDS 11.5: QUERY: ------ create view "informix".channel_cust (job_no,custcode,cname,sstreet1,sstreet2,scity,state,szip,scount ry) as select x1.job_no ,x0.custcode ,x0.cname ,x0.sstreet1 ,x0.sstreet2 ,x0.scity ,x0.sstate ,x0.szip ,x0.scountry from "informix".allcusts x0 ,"informix".jobcard x1 where ((x1.cust_no = x0.cust_no ) AND (x1.cacct_no IS NULL ) ) union all select x3.job_no ,x2.custcode ,x2.cname ,x2.sstreet1 ,x2.sstreet2 ,x2.scity ,x2.sstate ,x2.szip ,x2.scountry from "informix".allcusts x2 ,"informix".jobcard x3 where ((x3.cacct_no = x2.cust_no ) AND (x3.cacct_no IS NOT NULL ) ) ; Estimated Cost: 20770 Estimated # of Rows Returned: 6169 1) informix.jobcard: INDEX PATH (1) Index Keys: cacct_no (Serial, fragments: ALL) Lower Index Filter: informix.jobcard.cacct_no IS NULL 2) informix.allcusts: INDEX PATH (1) Index Keys: cust_no (Serial, fragments: ALL) Lower Index Filter: informix.jobcard.cust_no = informix.allcusts.cust_no NESTED LOOP JOIN Union Query: ------------ 1) informix.jobcard: SEQUENTIAL SCAN Filters: informix.jobcard.cacct_no IS NOT NULL 2) informix.allcusts: INDEX PATH (1) Index Keys: cust_no (Serial, fragments: ALL) Lower Index Filter: informix.jobcard.cacct_no = informix.allcusts.cust_no NESTED LOOP JOIN QUERY: ------ select --+ ORDERED unique jobcard.job_no from channel_cust, jobcard, allcusts where jobcard.job_no = channel_cust.job_no and channel_cust.custcode = allcusts.custcode and 1=1 into temp t1 with no log DIRECTIVES FOLLOWED: ORDERED DIRECTIVES NOT FOLLOWED: Estimated Cost: 4 Estimated # of Rows Returned: 1 1) (Temp Table For View): SEQUENTIAL SCAN 2) informix.jobcard: INDEX PATH (1) Index Keys: job_no (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.jobcard.job_no = (Temp Table For View).job_no NESTED LOOP JOIN 3) informix.allcusts: INDEX PATH (1) Index Keys: custcode type_code (Key-Only) (Serial, fragments: ALL) Lower Index Filter: (Temp Table For View).custcode = informix.allcusts.custcode NESTED LOOP JOIN
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: * * All Users * **************************************************************** * PROBLEM DESCRIPTION: * * Performance degradation seen in nested loop join query when * * customer upgraded from IDS 9.40 to IDS 11.50 on HPIA64 * **************************************************************** * RECOMMENDATION: * * Upgrade to IDS 11.50.xC8 * ****************************************************************
Problem conclusion
Protect a structure from being modified while in use. Fix is in11.50.FC7W1.
Temporary fix
Comments
For download information, go to: http://www-933.ibm.com/support/fixcentral/ For documentation information, go to http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp
APAR Information
APAR number
IC66048
Reported component name
IBM IDS ENTRP E
Reported component ID
5724L2304
Reported release
B15
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2010-02-03
Closed date
2011-01-18
Last modified date
2011-01-21
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Fixed component name
IBM IDS ENTRP E
Fixed component ID
5724L2304
Applicable component levels
RB15 PSY
UP
[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSGU8G","label":"Informix Servers"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"B15","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
21 January 2011