selects on syschunks running slow

Hello. Its my first post here and i'm also new in Informix so feel free to correct any mistake. I have just inherited some undocumented Informix databases. I'm checking everything through selects I found in the web. Problem: When I try to run any select on syschunks its takes about 1-5 minutes to return the result. I red that syschunks is a view over 2 tables and it this is the one getting hanged: syschktab. If I run one "onstat -d" the server returns information very fast, but If I do "onstat -d update" it also takes a lot stating "Waiting for server to update BLOB chunk statistics...". I dont know how much is too much BLOB for Informix database, is it normal this delay? Many health avisors are giving a time out because they take too much to reach the answer... I'm pasting here the "onstat -d update" result hoping someone can help me with the answer. Many thanks in advance to anybody willing to help. onstat -d update IBM Informix Dynamic Server Version 11.70.FC7W3 -- On-Line -- Up 3 days 17:08:09 -- 3321344 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 9083f028 1 0x40002 1 1 2048 M BA informix rootdbs 92c38890 2 0x42001 2 1 2048 N TBA informix tmpdbs1 92c38a38 3 0x42001 3 1 2048 N TBA informix tmpdbs2 92c38be0 4 0x42001 4 1 2048 N TBA informix tmpdbs3 92c38d88 5 0x42001 5 1 2048 N TBA informix tmpdbs4 92c3a028 6 0x4a001 6 1 2048 N UBA informix tmpsbs 92c3a1d0 7 0x48001 7 1 2048 N SBA informix sbspace 92c3a378 8 0x48002 8 1 2048 M SBA informix syssbspace 92c3a520 9 0x40001 34 2 2048 N BA informix web2dbs 92c3a6c8 10 0x40002 10 1 2048 M BA informix physdbs 92c3a870 11 0x40001 11 1 2048 N BA informix sitdtdbs 92c3aa18 12 0x40011 12 6 4096 N BBA informix sitdtbld 92c3abc0 13 0x40001 13 3 2048 N BA informix prepardbs 92c3ad68 14 0x40001 14 1 2048 N BA informix preparidx 92c3b028 15 0x40011 15 18 4096 N BBA informix sspbld 92c3b1d0 16 0x40001 19 3 2048 N BA informix sspdbs 92c3b378 17 0x40002 29 1 2048 M BA informix logdbs1 92c3b520 18 0x40001 25 1 2048 N BA informix efacturadbs 92c3b6c8 19 0x40001 26 1 2048 N BA informix efacturaidx 92c3b870 20 0x40011 27 1 2048 N BBA informix efacturabld 92c3ba18 21 0x40002 30 1 2048 M BA informix logdbs2 92c3bbc0 22 0x40002 31 1 2048 M BA informix logdbs3 92c3bd68 23 0x40001 35 1 2048 N BA informix web2idx 92c3d028 24 0x40011 36 1 2048 N BBA informix web2bld_2k 92c3d1d0 25 0x40001 38 1 2048 N BA informix vixsaudbs 92c3d378 26 0x40001 39 2 2048 N BA informix vixsauidx 92c3d520 27 0x44011 44 37 2048 N BBA informix sesbld 92c3d6c8 28 0x40001 55 2 2048 N BA informix sesdbs 92c3d870 29 0x40001 57 1 2048 N BA informix sesidx 92c3da18 30 0x40001 61 1 2048 N BA informix formuldbs 92c3dbc0 31 0x40011 62 2 2048 N BBA informix formulbld_2k 92c3dd68 32 0x40011 63 2 4096 N BBA informix formulbld_4k 92c3f028 33 0x40001 68 1 2048 N BA informix iptdbs 92c3f1d0 34 0x40011 69 3 2048 N BBA informix iptbld_2k 92c3f378 35 0x40011 71 1 32768 N BBA informix web2bld_32k 92c3f520 36 0x40001 80 1 2048 N BA informix seriedbs 92c3f6c8 37 0x40001 88 1 2048 N BA informix sehwebdbs 92c3f870 38 0x40011 82 8 8192 N BBA informix seriebld_2k 92c3fa18 39 0x40001 90 1 2048 N BA informix srmndocdbs 92c3fbc0 40 0x40001 91 1 2048 N BA informix srmndocidx 92c3fd68 41 0x40001 97 1 2048 N BA informix axutrdbs 92c41028 42 0x40011 98 1 2048 N BBA informix axutrbld_2k 92c411d0 43 0x40011 99 2 20480 N BBA informix axutrbld_20k 92c41378 44 0x40011 102 1 4096 N BBA informix web2bld_4k 92c41520 45 0x40001 103 1 2048 N BA informix rgeedbs 92c416c8 46 0x40001 104 1 2048 N BA informix rgeeidx 92c41870 47 0x40011 105 2 262144 N BBA informix rgeebld_256k 92c41a18 48 0x40011 106 1 2048 N BBA informix rgeebld_2k 92c41bc0 49 0x40001 113 1 2048 N BA informix web2idx_obs 92c41d68 50 0x40001 114 2 2048 N BA informix web2dbs_obs 92c42028 51 0x40011 118 2 2048 N BBA informix web2bld_obs_2k 92c421d0 52 0x40011 119 3 16384 N BBA informix web2bld_obs_16k 92c42378 53 0x40011 120 2 10240 N BBA informix web2bld_obs_10k 53 active, 2047 maximum Waiting for server to update BLOB chunk statistics... Chunks address chunk/dbs offset size free bpages flags pathname 9083f1d0 1 1 0 524288 488189 PO-B-- /ifx11web2/dbspaces/rootdbs 9083f3d0 1 1 0 524288 0 MO-B-- /ifx11web2/m_dbspaces/m_rootdbs 92c42520 2 2 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs1 92c42720 3 3 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs2 92c42920 4 4 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs3 92c42b20 5 5 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs4 92c42d20 6 6 0 262144 244424 244424 POSB-- /ifx11web2/dbspaces/tmpsbs Metadata 17667 13146 17667 92c44a28 7 7 0 262144 244424 244424 POSB-- /ifx11web2/dbspaces/sbspace Metadata 17667 13146 17667 92c44c28 8 8 0 32768 30487 30487 POSB-- /ifx11web2/dbspaces/syssbspace Metadata 2228 1657 2228 92c44028 8 8 0 32768 0 0 MOSB-- /ifx11web2/m_dbspaces/m_syssbspace 92c44e28 9 13 0 262144 93199 PO-B-- /ifx11web2/dbspaces/prepardbs2 92c45028 10 10 0 524288 63435 PO-B-- /ifx11web2/dbspaces/physdbs 92c44228 10 10 0 524288 0 MO-B-- /ifx11web2/m_dbspaces/m_physdbs 92c45228 11 11 0 524288 515728 PO-B-- /ifx11web2/dbspaces/sitdtdbs 92c45428 12 12 0 262144 1 131072 POBB-- /ifx11web2/dbspaces/sitdtbld 92c45628 13 13 0 262144 26399 PO-B-- /ifx11web2/dbspaces/prepardbs 92c45828 14 14 0 131072 123536 PO-B-- /ifx11web2/dbspaces/preparidx 92c45a28 15 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld1 92c45c28 16 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld2 92c45e28 17 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld3 92c46028 18 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld4 92c46228 19 16 0 524288 52 PO-B-- /ifx11web2/dbspaces/sspdbs 92c46428 20 12 0 262144 1 131072 POBB-- /ifx11web2/dbspaces/sitdtbld2 92c46628 21 12 0 524288 1 262144 POBB-- /ifx11web2/dbspaces/sitdtbld3 92c46828 22 12 0 1048576 1 524288 POBB-- /ifx11web2/dbspaces/sitdtbld4 92c46a28 23 12 0 1048576 263997 524288 POBB-- /ifx11web2/dbspaces/sitdtbld5 92c46c28 24 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld5 92c46e28 25 18 0 524288 400686 PO-B-- /ifx11web2/dbspaces/efacturadbs 92c47028 26 19 0 262144 258433 PO-B-- /ifx11web2/dbspaces/efacturaidx 92c47228 27 20 0 2097152 1369782 2097152 POBB-- /ifx11web2/dbspaces/efacturabld 92c47428 28 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld6 92c47628 29 17 0 131072 28619 PO-B-- /ifx11web2/dbspaces/logdbs1 92c44428 29 17 0 131072 0 MO-B-- /ifx11web2/m_dbspaces/logdbs1 92c47828 30 21 0 131072 28619 PO-B-- /ifx11web2/dbspaces/logdbs2 92c44628 30 21 0 131072 0 MO-B-- /ifx11web2/m_dbspaces/logdbs2 92c47a28 31 22 0 131072 28619 PO-B-- /ifx11web2/dbspaces/logdbs3 92c44828 31 22 0 131072 0 MO-B-- /ifx11web2/m_dbspaces/logdbs3 92c47c28 32 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld7 92c47e28 33 16 0 524288 347389 PO-B-- /ifx11web2/dbspaces/sspdbs1 92c4a028 34 9 0 524288 116245 PO-B-- /ifx11web2/dbspaces/web2dbs 92c4a228 35 23 0 262144 231643 PO-B-- /ifx11web2/dbspaces/web2idx 92c4a428 36 24 0 524288 387626 524288 POBB-- /ifx11web2/dbspaces/web2bld_2k 92c4a628 37 15 0 5242880 1 2621440 POBB-- /ifx11web2/dbspaces/sspbld8 92c4a828 38 25 0 1572864 700294 PO-B-- /ifx

If you want an exact usage of the blob space usage then= it will need to read all the blob free map pages to find out their exact s= tate. The number of free map pages is determined by the size of the blob st= orage. The exact information can be gathered via onstat -d upda= te or sysmaster:syschunks. If an highly accurate estimate will = do then user onstat -d or sysmaster:syschunks=5Ffast will always run veryfast and return very good numbers. Hope this helps, John F.= Miller III STSM, Lead Architect [1]miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic= Server (IDS) [2]-----ids-bounces@iiug.org wro= te: ----- >To: [3]ids@iiug.org >From: "JACOBO BALBUENA" >Sent by: <= a target=3D"=5Fblank" href=3D"mailto:ids-bounces@iiug.org">ids-bounces@iiug= .org >Date: 01/11/2016 08:45AM >Subject: selects on syschun= ks running slow [36363] > >Hello. Its my first post here and i= 'm also new in Informix so feel >free to >correct any mistake.= > >I have just inherited some undocumented Informix databases= . I'm >checking >everything through selects I found in the web= . > >Problem: When I try to run any select on syschunks its ta= kes about >1-5 minutes >to return the result. > >= I red that syschunks is a view over 2 tables and it this is the one >= getting >hanged: syschktab. > >If I run one "onstat -d"= the server returns information very fast, >but If I do >"onst= at -d update" it also takes a lot stating "Waiting for server to >upd= ate >BLOB chunk statistics...". > >I dont know how much= is too much BLOB for Informix database, is it >normal this >d= elay? Many health avisors are giving a time out because they take >to= o much to >reach the answer... > >I'm pasting here the = "onstat -d update" result hoping someone can >help me with >th= e answer. Many thanks in advance to anybody willing to help. > &g= t;onstat -d update > >IBM Informix Dynamic Server Version 11.7= 0.FC7W3 -- On-Line -- Up 3 >days >17:08:09 -- 3321344 Kbytes <= br>> >Dbspaces >address number flags fchunk nchunks pgsize = flags owner name >9083f028 1 0x40002 1 1 2048 M BA informix rootdbs = >92c38890 2 0x42001 2 1 2048 N TBA informix tmpdbs1 >92c38a38= 3 0x42001 3 1 2048 N TBA informix tmpdbs2 >92c38be0 4 0x42001 4 1 2= 048 N TBA informix tmpdbs3 >92c38d88 5 0x42001 5 1 2048 N TBA inform= ix tmpdbs4 >92c3a028 6 0x4a001 6 1 2048 N UBA informix tmpsbs &g= t;92c3a1d0 7 0x48001 7 1 2048 N SBA informix sbspace >92c3a378 8 0x4= 8002 8 1 2048 M SBA informix syssbspace >92c3a520 9 0x40001 34 2 204= 8 N BA informix web2dbs >92c3a6c8 10 0x40002 10 1 2048 M BA informix= physdbs >92c3a870 11 0x40001 11 1 2048 N BA informix sitdtdbs &= gt;92c3aa18 12 0x40011 12 6 4096 N BBA informix sitdtbld >92c3abc0 1= 3 0x40001 13 3 2048 N BA informix prepardbs >92c3ad68 14 0x40001 14 = 1 2048 N BA informix preparidx >92c3b028 15 0x40011 15 18 4096 N BBA= informix sspbld >92c3b1d0 16 0x40001 19 3 2048 N BA informix sspdbs= >92c3b378 17 0x40002 29 1 2048 M BA informix logdbs1 >92c3b5= 20 18 0x40001 25 1 2048 N BA informix efacturadbs >92c3b6c8 19 0x400= 01 26 1 2048 N BA informix efacturaidx >92c3b870 20 0x40011 27 1 204= 8 N BBA informix efacturabld >92c3ba18 21 0x40002 30 1 2048 M BA inf= ormix logdbs2 >92c3bbc0 22 0x40002 31 1 2048 M BA informix logdbs3 <= br>>92c3bd68 23 0x40001 35 1 2048 N BA informix web2idx >92c3d028= 24 0x40011 36 1 2048 N BBA informix web2bld=5F2k >92c3d1d0 25 0x400= 01 38 1 2048 N BA informix vixsaudbs >92c3d378 26 0x40001 39 2 2048 = N BA informix vixsauidx >92c3d520 27 0x44011 44 37 2048 N BBA inform= ix sesbld >92c3d6c8 28 0x40001 55 2 2048 N BA informix sesdbs &g= t;92c3d870 29 0x40001 57 1 2048 N BA informix sesidx >92c3da18 30 0x= 40001 61 1 2048 N BA informix formuldbs >92c3dbc0 31 0x40011 62 2 20= 48 N BBA informix formulbld=5F2k >92c3dd68 32 0x40011 63 2 4096 N BB= A informix formulbld=5F4k >92c3f028 33 0x40001 68 1 2048 N BA inform= ix iptdbs >92c3f1d0 34 0x40011 69 3 2048 N BBA informix iptbld=5F2k = >92c3f378 35 0x40011 71 1 32768 N BBA informix web2bld=5F32k >= ;92c3f520 36 0x40001 80 1 2048 N BA informix seriedbs >92c3f6c8 37 0= x40001 88 1 2048 N BA informix sehwebdbs >92c3f870 38 0x40011 82 8 8= 192 N BBA informix seriebld=5F2k >92c3fa18 39 0x40001 90 1 2048 N BA= informix srmndocdbs >92c3fbc0 40 0x40001 91 1 2048 N BA informix sr= mndocidx >92c3fd68 41 0x40001 97 1 2048 N BA informix axutrdbs &= gt;92c41028 42 0x40011 98 1 2048 N BBA informix axutrbld=5F2k >92c41= 1d0 43 0x40011 99 2 20480 N BBA informix axutrbld=5F20k >92c41378 44= 0x40011 102 1 4096 N BBA informix web2bld=5F4k >92c41520 45 0x40001= 103 1 2048 N BA informix rgeedbs >92c416c8 46 0x40001 104 1 2048 N = BA informix rgeeidx >92c41870 47 0x40011 105 2 262144 N BBA informix= rgeebld=5F256k >92c41a18 48 0x40011 106 1 2048 N BBA informix rgeeb= ld=5F2k >92c41bc0 49 0x40001 113 1 2048 N BA informix web2idx=5Fobs = >92c41d68 50 0x40001 114 2 2048 N BA informix web2dbs=5Fobs >= 92c42028 51 0x40011 118 2 2048 N BBA informix web2bld=5Fobs=5F2k >92= c421d0 52 0x40011 119 3 16384 N BBA informix web2bld=5Fobs=5F16k >92= c42378 53 0x40011 120 2 10240 N BBA informix web2bld=5Fobs=5F10k >53= active, 2047 maximum > >Waiting for server to update BLOB chu= nk statistics... > >Chunks >address chunk/dbs offset si= ze free bpages flags pathname >9083f1d0 1 1 0 524288 488189 PO-B-- /= ifx11web2/dbspaces/rootdbs >9083f3d0 1 1 0 524288 0 MO-B-- /ifx11web= 2/m=5Fdbspaces/m=5Frootdbs >92c42520 2 2 0 65536 65483 PO-B-- /ifx11= web2/dbspaces/tmpdbs1 >92c42720 3 3 0 65536 65483 PO-B-- /ifx11web2/= dbspaces/tmpdbs2 >92c42920 4 4 0 65536 65483 PO-B-- /ifx11web2/dbspa= ces/tmpdbs3 >92c42b20 5 5 0 65536 65483 PO-B-- /ifx11web2/dbspaces/t= mpdbs4 >92c42d20 6 6 0 262144 244424 244424 POSB-- /ifx11web2/dbspac= es/tmpsbs > > >Metadata 17667 13146 17667 >92c44a2= 8 7 7 0 262144 244424 244424 POSB-- >/ifx11web2/dbspaces/sbspace = > >Metadata 17667 13146 17667 >92c44c28 8 8 0 32768 30487 3= 0487 POSB-- >/ifx11web2/dbspaces/syssbspace > >Metadata = 2228 1657 2228 >92c44028 8 8 0 32768 0 0 MOSB-- /ifx11web2/m=5Fdbspa= ces/m=5Fsyssbspace >92c44e28 9 13 0 262144 93199 PO-B-- /ifx11web2/d= bspaces/prepardbs2 >92c45028 10 10 0 524288 63435 PO-B-- /ifx11web2/= dbspaces/physdbs >92c44228 10 10 0 524288 0 MO-B-- /ifx11web2/m=5Fdb= spaces

Jocobo: OK, so you have 53 dbspaces and 136 chunks. First it takes time to report on all of that storage. Next, several dbspaces and chunks are dumb-blob spaces which, unlike regular dbspaces and smart-blob spaces do not keep meta-data about their free space up-to-date. The onstat -d with the "update" modifier updates that meta-data by systematically going through each dumb-blob space and counting used and free pages so that the onstat report will be accurate. This process takes time. Without the "update" modifier onstat -d reports the results of the last update which will probably be out-of-date. If you query the syschktab pseudo-table directly (or by extension the syschunks view) it will always trigger a meta-data update of the dumb-blob spaces, there is no way around that. Onstat -d does not query the pseudo-table, rather it directly reads the chunk list on disk, so it does not automatically trigger the update unless you ask for it. That is why the onstat runs quickly without the "update" modifier and the query of syscktab or of syschunks is always slow if you have dumb-blob spaces. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jan 11, 2016 at 11:44 AM, JACOBO BALBUENA <jacobo.bc@gmail.com> wrote: > Hello. Its my first post here and i'm also new in Informix so feel free to > correct any mistake. > > I have just inherited some undocumented Informix databases. I'm checking > everything through selects I found in the web. > > Problem: When I try to run any select on syschunks its takes about 1-5 > minutes > to return the result. > > I red that syschunks is a view over 2 tables and it this is the one getting > hanged: syschktab. > > If I run one "onstat -d" the server returns information very fast, but If > I do > "onstat -d update" it also takes a lot stating "Waiting for server to > update > BLOB chunk statistics...". > > I dont know how much is too much BLOB for Informix database, is it normal > this > delay? Many health avisors are giving a time out because they take too > much to > reach the answer... > > I'm pasting here the "onstat -d update" result hoping someone can help me > with > the answer. Many thanks in advance to anybody willing to help. > > onstat -d update> > IBM Informix Dynamic Server Version 11.70.FC7W3 -- On-Line -- Up 3 days > 17:08:09 -- 3321344 Kbytes> > Dbspaces > address number flags fchunk nchunks pgsize flags owner name > 9083f028 1 0x40002 1 1 2048 M BA informix rootdbs > 92c38890 2 0x42001 2 1 2048 N TBA informix tmpdbs1 > 92c38a38 3 0x42001 3 1 2048 N TBA informix tmpdbs2 > 92c38be0 4 0x42001 4 1 2048 N TBA informix tmpdbs3 > 92c38d88 5 0x42001 5 1 2048 N TBA informix tmpdbs4 > 92c3a028 6 0x4a001 6 1 2048 N UBA informix tmpsbs > 92c3a1d0 7 0x48001 7 1 2048 N SBA informix sbspace > 92c3a378 8 0x48002 8 1 2048 M SBA informix syssbspace > 92c3a520 9 0x40001 34 2 2048 N BA informix web2dbs > 92c3a6c8 10 0x40002 10 1 2048 M BA informix physdbs > 92c3a870 11 0x40001 11 1 2048 N BA informix sitdtdbs > 92c3aa18 12 0x40011 12 6 4096 N BBA informix sitdtbld > 92c3abc0 13 0x40001 13 3 2048 N BA informix prepardbs > 92c3ad68 14 0x40001 14 1 2048 N BA informix preparidx > 92c3b028 15 0x40011 15 18 4096 N BBA informix sspbld > 92c3b1d0 16 0x40001 19 3 2048 N BA informix sspdbs > 92c3b378 17 0x40002 29 1 2048 M BA informix logdbs1 > 92c3b520 18 0x40001 25 1 2048 N BA informix efacturadbs > 92c3b6c8 19 0x40001 26 1 2048 N BA informix efacturaidx > 92c3b870 20 0x40011 27 1 2048 N BBA informix efacturabld > 92c3ba18 21 0x40002 30 1 2048 M BA informix logdbs2 > 92c3bbc0 22 0x40002 31 1 2048 M BA informix logdbs3 > 92c3bd68 23 0x40001 35 1 2048 N BA informix web2idx > 92c3d028 24 0x40011 36 1 2048 N BBA informix web2bld_2k > 92c3d1d0 25 0x40001 38 1 2048 N BA informix vixsaudbs > 92c3d378 26 0x40001 39 2 2048 N BA informix vixsauidx > 92c3d520 27 0x44011 44 37 2048 N BBA informix sesbld > 92c3d6c8 28 0x40001 55 2 2048 N BA informix sesdbs > 92c3d870 29 0x40001 57 1 2048 N BA informix sesidx > 92c3da18 30 0x40001 61 1 2048 N BA informix formuldbs > 92c3dbc0 31 0x40011 62 2 2048 N BBA informix formulbld_2k > 92c3dd68 32 0x40011 63 2 4096 N BBA informix formulbld_4k > 92c3f028 33 0x40001 68 1 2048 N BA informix iptdbs > 92c3f1d0 34 0x40011 69 3 2048 N BBA informix iptbld_2k > 92c3f378 35 0x40011 71 1 32768 N BBA informix web2bld_32k > 92c3f520 36 0x40001 80 1 2048 N BA informix seriedbs > 92c3f6c8 37 0x40001 88 1 2048 N BA informix sehwebdbs > 92c3f870 38 0x40011 82 8 8192 N BBA informix seriebld_2k > 92c3fa18 39 0x40001 90 1 2048 N BA informix srmndocdbs > 92c3fbc0 40 0x40001 91 1 2048 N BA informix srmndocidx > 92c3fd68 41 0x40001 97 1 2048 N BA informix axutrdbs > 92c41028 42 0x40011 98 1 2048 N BBA informix axutrbld_2k > 92c411d0 43 0x40011 99 2 20480 N BBA informix axutrbld_20k > 92c41378 44 0x40011 102 1 4096 N BBA informix web2bld_4k > 92c41520 45 0x40001 103 1 2048 N BA informix rgeedbs > 92c416c8 46 0x40001 104 1 2048 N BA informix rgeeidx > 92c41870 47 0x40011 105 2 262144 N BBA informix rgeebld_256k > 92c41a18 48 0x40011 106 1 2048 N BBA informix rgeebld_2k > 92c41bc0 49 0x40001 113 1 2048 N BA informix web2idx_obs > 92c41d68 50 0x40001 114 2 2048 N BA informix web2dbs_obs > 92c42028 51 0x40011 118 2 2048 N BBA informix web2bld_obs_2k > 92c421d0 52 0x40011 119 3 16384 N BBA informix web2bld_obs_16k > 92c42378 53 0x40011 120 2 10240 N BBA informix web2bld_obs_10k > 53 active, 2047 maximum > > Waiting for server to update BLOB chunk statistics... > > Chunks > address chunk/dbs offset size free bpages flags pathname > 9083f1d0 1 1 0 524288 488189 PO-B-- /ifx11web2/dbspaces/rootdbs > 9083f3d0 1 1 0 524288 0 MO-B-- /ifx11web2/m_dbspaces/m_rootdbs > 92c42520 2 2 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs1 > 92c42720 3 3 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs2 > 92c42920 4 4 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs3 > 92c42b20 5 5 0 65536 65483 PO-B-- /ifx11web2/dbspaces/tmpdbs4 > 92c42d20 6 6 0 262144 244424 244424 POSB-- /ifx11web2/dbspaces/tmpsbs > > Metadata 17667 13146 17667 > 92c44a28 7 7 0 262144 244424 244424 POSB-- /ifx11web2/dbspaces/sbspace > > Metadata 17667 13146 17667 > 92c44c28 8 8 0 32768 30487 30487 POSB-- /ifx11web2/dbspaces/syssbspace > > Metadata 2228 1657 2228 > 92c44028 8 8 0 32768 0 0 MOSB-- /ifx11web2/m_dbspaces/m_syssbspace > 92c44e28 9 13 0 262144 93199 PO-B-- /ifx11web2/dbspaces/prepardbs2 > 92c45028 10 10 0 524288 63435 PO-B-- /ifx11web2/dbspaces/physdbs > 92c44228 10 10 0 524288 0 MO-B-- /ifx11web2/m_dbspaces/m_physdbs > 92c45228 11 11 0 524288 515728 PO-B-- /ifx11web2/dbspaces/sitdtdbs > 92c45428 12 12 0 262144 1 1

The thing is that syschunks is used by many health checks selects. Is migration from blob to sblob very difficult or has many problems?

If you change those selects to use the syschunks_fast view instead you won't see the slowdown. Sort of. Handling smart blobs is very different from handling dumb blobs. If you are running v12.10, you could try using the undocumented new type LONGLVARCHAR which automatically handles storage. It works just like an LVARCHAR but its maximum size is 2GB rather than 32K. Strings shorter than 4K are stored in-row and longer strings are moved to smartblob space but you don't have to get involved in handling the smartblobs directly, the type handles it for you. I have not done any performance testing on LONGLVARCHAR types yet. Tables with LONGLVARCHAR columns should probably reside on pages wider than 8K unless the column is empty in most rows. Example: { TABLE "art".very_large row size = 4104 number of columns = 2 index size = 0 } CREATE TABLE "art".very_large ( one SERIAL(1) NOT NULL, two "informix".longlvarchar ) IN rootdbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW; Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jan 13, 2016 at 11:40 AM, JACOBO BALBUENA <jacobo.bc@gmail.com> wrote: > The thing is that syschunks is used by many health checks selects. > > Is migration from blob to sblob very difficult or has many problems? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013a2268eb0fe905293a3096

Thanks a lot Art S. Kagel!