sysmaster select hungs

Hello, What could be the reason for this select to hung: It seems as if sysmaster database was corrupt or "out of date". I have run UPDATE STATISTICS HIGH, but nothing changed. database sysmaster; set explain on; select dbsname, sum( pe_size ) total_pages from systabnames, sysptnext where partnum = pe_partnum group by 1 order by 2 desc Here is the explain: QUERY: (OPTIMIZATION TIMESTAMP: 01-23-2018 16:35:36) ------ select dbsname, sum( pe_size ) total_pages from systabnames, sysptnext where partnum = pe_partnum group by 1 order by 2 desc Estimated Cost: 1078934656 Estimated # of Rows Returned: 17586 Temporary Files Required For: Order By Group By 1) informix.systabnames: SEQUENTIAL SCAN 2) informix.sysptnext: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: informix.systabnames.partnum = informix.sysptnext.pe_partnum Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 systabnames t2 sysptnext Table map : ---------------------------- Internal name Table name ---------------------------- t1 systabnames t2 sysptnext type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 175866 0 00:00.00 181143 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 48439 185687 48484 01:44.81 181438 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ------------------------------------------------------------------------------ hjoin 0 323327040 48439 0 0 00:00.00 523846 type rows_prod est_rows rows_cons time est_cost ------------------------------------------------------------ group 0 17587 0 00:00.00 1078403328 type rows_sort est_rows rows_cons time est_cost ------------------------------------------------------------ sort 0 17587 0 00:00.00 7519

How many tables do you have? How many rows in systabname and sysptnext? This query can take a very long time if there are a huge number of tables, or partitions. sysptnext is not a "real" table, but regardless has no index on the part number, so all rows have to be read from both tables, and as you can see from the plan, are then joined with a dynamic hash join. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JUAN ROCA Sent: Tuesday, January 23, 2018 8:55 AM To: ids@iiug.org Subject: sysmaster select hungs [40537] Hello, What could be the reason for this select to hung: It seems as if sysmaster database was corrupt or "out of date". I have run UPDATE STATISTICS HIGH, but nothing changed. database sysmaster; set explain on; select dbsname, sum( pe_size ) total_pages from systabnames, sysptnext where partnum = pe_partnum group by 1 order by 2 desc Here is the explain: QUERY: (OPTIMIZATION TIMESTAMP: 01-23-2018 16:35:36) ------ select dbsname, sum( pe_size ) total_pages from systabnames, sysptnext where partnum = pe_partnum group by 1 order by 2 desc Estimated Cost: 1078934656 Estimated # of Rows Returned: 17586 Temporary Files Required For: Order By Group By 1) informix.systabnames: SEQUENTIAL SCAN 2) informix.sysptnext: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: informix.systabnames.partnum = informix.sysptnext.pe_partnum Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 systabnames t2 sysptnext Table map : ---------------------------- Internal name Table name ---------------------------- t1 systabnames t2 sysptnext type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 175866 0 00:00.00 181143 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 48439 185687 48484 01:44.81 181438 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ---------------------------------------------------------------------------- -- hjoin 0 323327040 48439 0 0 00:00.00 523846 type rows_prod est_rows rows_cons time est_cost ------------------------------------------------------------ group 0 17587 0 00:00.00 1078403328 type rows_sort est_rows rows_cons time est_cost ------------------------------------------------------------ sort 0 17587 0 00:00.00 7519 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Can you show the result of running: SELECT * FROM sysmaster:systables WHERE tabname IN ('sysptnext','systabnames'); ? Thanks! On Tue, Jan 23, 2018 at 3:55 PM, JUAN ROCA <jluis.roca@hotmail.com> wrote: > Hello, > > What could be the reason for this select to hung: > > It seems as if sysmaster database was corrupt or "out of date". > I have run UPDATE STATISTICS HIGH, but nothing changed. > > database sysmaster; > set explain on; > select dbsname,> > sum( pe_size ) total_pages > from systabnames, sysptnext > where partnum = pe_partnum > group by 1 > order by 2 desc > > Here is the explain: > QUERY: (OPTIMIZATION TIMESTAMP: 01-23-2018 16:35:36) > ------ > select dbsname,> > sum( pe_size ) total_pages > from systabnames, sysptnext > where partnum = pe_partnum > group by 1 > order by 2 desc > > Estimated Cost: 1078934656 > Estimated # of Rows Returned: 17586 > Temporary Files Required For: Order By Group By > > 1) informix.systabnames: SEQUENTIAL SCAN > > 2) informix.sysptnext: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: informix.systabnames.partnum = > informix.sysptnext.pe_partnum > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 systabnames > t2 sysptnext > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 systabnames > t2 sysptnext > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 175866 0 00:00.00 181143 > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t2 48439 185687 48484 01:44.81 181438 > > type rows_prod est_rows rows_bld rows_prb novrflo time est_cost > ------------------------------------------------------------ > ------------------ > hjoin 0 323327040 48439 0 0 00:00.00 523846 > > type rows_prod est_rows rows_cons time est_cost > ------------------------------------------------------------ > group 0 17587 0 00:00.00 1078403328 > > type rows_sort est_rows rows_cons time est_cost > ------------------------------------------------------------ > sort 0 17587 0 00:00.00 7519 > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

tabname sysptnext owner informix partnum 12 tabid 111 rowsize 22 ncols 6 nindexes 1 nrows 185832,0000000 created 26/06/2014 version 7340038 tabtype T locklevel R npused 175902,0000000 fextsize 32 nextsize 32 flags 0 site dbname type_xid 0 am_id 0 pagesize 4096 ustlowts 2018-01-23 16:40:06.00000 secpolicyid 0 protgranularity statchange statlevel A tabname systabnames owner informix partnum 15 tabid 101 rowsize 328 ncols 6 nindexes 2 nrows 175898,0000000 created 26/06/2014 version 6750214 tabtype T locklevel R npused 175898,0000000 fextsize 32 nextsize 32 flags 0 site dbname type_xid 0 am_id 0 pagesize 4096 ustlowts 2018-01-23 16:40:05.00000 secpolicyid 0 protgranularity statchange statlevel A

Ok. Thanks. I was just trying to check for something I went through, but it's not your case. Meanwhile it seems you have barely more extents than partitions. I am assuming this based on the fact that you run statistics recently. Regards. On Tue, Jan 23, 2018 at 4:26 PM, JUAN ROCA <jluis.roca@hotmail.com> wrote: > tabname sysptnext > owner informix > partnum 12 > tabid 111 > rowsize 22 > ncols 6 > nindexes 1 > nrows 185832,0000000 > created 26/06/2014 > version 7340038 > tabtype T > locklevel R > npused 175902,0000000 > fextsize 32 > nextsize 32 > flags 0 > site > dbname > type_xid 0 > am_id 0 > pagesize 4096 > ustlowts 2018-01-23 16:40:06.00000 > secpolicyid 0 > protgranularity > statchange > statlevel A > > tabname systabnames > owner informix > partnum 15 > tabid 101 > rowsize 328 > ncols 6 > nindexes 2 > nrows 175898,0000000 > created 26/06/2014 > version 6750214 > tabtype T > locklevel R > npused 175898,0000000 > fextsize 32 > nextsize 32 > flags 0 > site > dbname > type_xid 0 > am_id 0 > pagesize 4096 > ustlowts 2018-01-23 16:40:05.00000 > secpolicyid 0 > protgranularity > statchange > statlevel A > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

There is a lot of pages and a lot of extents. This=0Avalue sum(pe_size) is= already pre-computed and stored in sysptnhdr=0Aas nptotal. If you know t= he tables you are interested in are already=0Aopened you can use sysactptnh= dr which will NOT go to disk, but only=0Ashow you the tables in memory.=0A= =0A=0AHere is my quick thought. You can optimize =0Athis select by avoidin= g the tempdbspaces as=0Athey generally have lots of partitions/extents =0Aw= ith no real data. =0A=0AAlso you can apply an optimizer directive to=0Aget = a nested loop join as there is exactly a 1-to-1=0Alook up.=0A=0A=0A=0Aselec= t dbsname,=0Asum( np_total) total_pages =0Afrom systabnames, sysptnhdr =0Aw= here systabnames.partnum =3D sysptnhdr.partnum=0Agroup by 1 =0Aorder by 2 d= esc =0A=0A-------- Original Message --------=0ASubject: sysmaster select hu= ngs [40537]=0AFrom: "JUAN ROCA" <jluis.roca@hotmail.com>=0ADate: Tue, Janua= ry 23, 2018 7:55 am=0ATo: ids@iiug.org=0A=0AHello, =0A=0AWhat could be the = reason for this select to hung: =0A=0AIt seems as if sysmaster database was= corrupt or "out of date". =0AI have run UPDATE STATISTICS HIGH, but nothin= g changed. =0A=0Adatabase sysmaster; =0Aset explain on; =0Aselect dbsname, = =0A=0Asum( pe_size ) total_pages =0Afrom systabnames, sysptnext =0Awhere pa= rtnum =3D pe_partnum =0Agroup by 1 =0Aorder by 2 desc =0A=0AHere is the exp= lain: =0AQUERY: (OPTIMIZATION TIMESTAMP: 01-23-2018 16:35:36) =0A------ =0A= select dbsname, =0A=0Asum( pe_size ) total_pages =0Afrom systabnames, syspt=next =0Awhere partnum =3D pe_partnum =0Agroup by 1 =0Aorder by 2 desc =0A= =0AEstimated Cost: 1078934656 =0AEstimated # of Rows Returned: 17586 =0ATem= porary Files Required For: Order By Group By =0A=0A1) informix.systabnames:= SEQUENTIAL SCAN =0A=0A2) informix.sysptnext: SEQUENTIAL SCAN =0A=0ADYNAMIC= HASH JOIN =0A=0ADynamic Hash Filters: informix.systabnames.partnum =3D =0A= informix.sysptnext.pe_partnum =0A=0AQuery statistics: =0A----------------- = =0A=0ATable map : =0A---------------------------- =0AInternal name Table na= me =0A---------------------------- =0At1 systabnames =0At2 sysptnext =0A=0A= Table map : =0A---------------------------- =0AInternal name Table name =0A= ---------------------------- =0At1 systabnames =0At2 sysptnext =0A=0Atype t= able rows_prod est_rows rows_scan time est_cost =0A------------------------= ------------------------------------------- =0Ascan t1 0 175866 0 00:00.00 = 181143 =0A=0Atype table rows_prod est_rows rows_scan time est_cost =0A-----= -------------------------------------------------------------- =0Ascan t2 4= 8439 185687 48484 01:44.81 181438 =0A=0Atype rows_prod est_rows rows_bld ro= ws_prb novrflo time est_cost =0A-------------------------------------------= -----------------------------------=0A=0Ahjoin 0 323327040 48439 0 0 00:00.= 00 523846 =0A=0Atype rows_prod est_rows rows_cons time est_cost =0A--------= ---------------------------------------------------- =0Agroup 0 17587 0 00:= 00.00 1078403328 =0A=0Atype rows_sort est_rows rows_cons time est_cost =0A-= ----------------------------------------------------------- =0Asort 0 17587= 0 00:00.00 7519 =0A=0A=0A*************************************************= ******************************=0A=0A Forum Note: Use "Reply" to post a resp= onse in the discussion forum.