RE: Informix On-Line 7.24 UC5 Shared Memory Error.

Is there something bogus with Informix 7.2.4? We're having horrible performance problems running 7.2.4UC8 on a Pyramid ES with 512M memory (62M allocated for shared memory.) Another instance is running Informix 7.1.4 on the same OS same memory and only 15m allocated to shared memory and their performance is great! If anyone has any info/advice, please send! It almost makes us want to take 7.2.4 off and install 7.1.4 and see what happens... what would you all do? Val -----Original Message----- From: David Murray [mailto:dmurray2@nycap.rr.com] Sent: Monday, September 27, 1999 11:49 PM To: informix-list@iiug.org Subject: Informix On-Line 7.24 UC5 Shared Memory Error. Gurus: Whenever I run a rather simplist SQL query I get an immediate error indicating an "208-Memory Allocation Error". This seems to indicate that the DB doesn't have enough memory to support the query. However, the query is a simple two table sorted SQL UNLOAD that will produce about 30000 rows. The DB is setup to use an unlimited portion of the HP 400 box's 1 GIG of memory. It's almost as if the optimizer is OVER estimating the resources needed successfully execute the query. Most of the applications, other more complex queries run OK. Any suggestions would be appreciated. Thanks. Notes: - We have another production DB instance setup under 7.20 UC2 w/ half the memory of the box we're getting the error on. The query runs OK under that Informix release and environment. - I've already tried to update the DB statistics prior to running the query. Didn't improve results. - When I remove the order by clauses from the query it works ok. - When I run the sql explain on the query, I get the following: QUERY: ------ SELECT * FROM product_vendor, product Note: 30000 rows in product_vendor, 30000 rows in product WHERE prod_upc_num = pdvn_upc_num AND prod_pc_code_num = pdvn_pc_code_num AND prod_status = 'A' ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num Estimated Cost: 39577 ***NOTE: This seems way too high. Estimated # of Rows Returned: 31142 Maximum Threads: 4 Temporary Files Required For: Order By 1) informix.product: SEQUENTIAL SCAN ***NOTE: There is a dup idx on prod_status. Not sure why it's not being used. Updated table stats HIGH Filters: informix.product.prod_status = 'A' 2) informix.product_vendor: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: (informix.product.prod_pc_code_num = informix.product_ vendor.pdvn_pc_code_num AND informix.product.prod_upc_num = informix.product_ven dor.pdvn_upc_num )

IDS 7.24UC8 should be noticably faster than IDS 7.14 which was a particularly horrid release. I suggest that you have a tuning problem. Read my article on Tuning in Informix TechNotes Vol 8, No 3 (online) and/or get one of the excellent tuning/admin books available (Joe Lumbley's 2nd edition of DBAs Survival Guide is a good one). If you are still having trouble or have specific tuning question post your ONCONFIG file and some onstat output showing suspicious stats and someone will lend a hand. Art S. Kagel Webber Valerie H wrote: > > Is there something bogus with Informix 7.2.4? We're having horrible > performance problems running 7.2.4UC8 on a Pyramid ES with 512M memory (62M > allocated for shared memory.) Another instance is running Informix 7.1.4 on > the same OS same memory and only 15m allocated to shared memory and their > performance is great! > > If anyone has any info/advice, please send! It almost makes us want to take > 7.2.4 off and install 7.1.4 and see what happens... what would you all do? > > Val > > -----Original Message----- > From: David Murray [mailto:dmurray2@nycap.rr.com] > Sent: Monday, September 27, 1999 11:49 PM > To: informix-list@iiug.org > Subject: Informix On-Line 7.24 UC5 Shared Memory Error. > > Gurus: > > Whenever I run a rather simplist SQL query I get an immediate error > indicating an "208-Memory Allocation Error". This seems to indicate that the > DB > doesn't have enough memory to support the query. However, the query is a > simple two table sorted SQL UNLOAD that will produce about 30000 rows. The > DB is setup > to use an unlimited portion of the HP 400 box's 1 GIG of memory. It's > almost as if the optimizer is OVER estimating the resources needed > successfully execute the query. Most of the applications, other more complex > queries run OK. Any suggestions would be appreciated. Thanks. > > Notes: - We have another production DB instance setup under 7.20 UC2 w/ half > the memory of the box we're getting the error on. The query > runs OK under that Informix > release and environment. > - I've already tried to update the DB statistics prior to > running the query. Didn't improve results. > - When I remove the order by clauses from the query it works ok. > - When I run the sql explain on the query, I get the following: > > QUERY: > ------ > SELECT * > FROM product_vendor, product Note: 30000 rows in > product_vendor, 30000 rows in product > WHERE prod_upc_num = pdvn_upc_num > AND prod_pc_code_num = pdvn_pc_code_num > AND prod_status = 'A' > ORDER BY pdvn_vendor_num, pdvn_upc_num, pdvn_pc_code_num> > Estimated Cost: 39577 ***NOTE: This seems way too high. > Estimated # of Rows Returned: 31142 > Maximum Threads: 4 > Temporary Files Required For: Order By > > 1) informix.product: SEQUENTIAL SCAN ***NOTE: There is a dup idx on > prod_status. Not sure why it's not > > being used. Updated table stats HIGH > Filters: informix.product.prod_status = 'A' > > 2) informix.product_vendor: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > Dynamic Hash Filters: (informix.product.prod_pc_code_num = > informix.product_ > vendor.pdvn_pc_code_num AND informix.product.prod_upc_num = > informix.product_ven > dor.pdvn_upc_num )