sql insert increases the value of nreads in onstat -u

Hi, I have a esql/c program that run this sql (output of onstat -g ses): Insert into cs_nyd_disg_ofi(cod_periodo,grupo_datos,numero_cliente,monto_a_compensar,di ......pond_t_pro_e,di_pond_d_pro_e,nhs) values(?,?,?,?,?,?,?,?,?,?....,?,?,?,?,?,?,?,?,?,?,?,?,?) When I run, the checkpoint rises. Usually this time is 0 seconds, but when this insert runs, the checkpoints rise to 5 seconds aprox. We consider this a normal behaviour. But, when I run onstat -u each second, I notice that the nwrites don't increase (this increase only after many seconds), but the nreads increases quickly. c0000000a032f4f8 --BPX-- 3642148 luz - 0 0 5678 5953233 466518 .. c0000000a032f4f8 --BPX-- 3642148 luz - 0 0 5686 5975871 466518 .. c0000000a032f4f8 --BPX-- 3642148 luz - 0 0 5690 5985582 466518 Is this behaviour normal ? Why the nreads increases if the sql that shows onstat -g ses is an insert ? Thanks in advance Peter

What else is the application doing? Is it selecting data from other tables before inserting? Is the insert being used as part of a PUT CURSOR? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Thu, Nov 4, 2010 at 7:03 PM, Peternt <roger_vilca@yahoo.es> wrote: > Hi, > I have a esql/c program that run this sql (output of onstat -g ses): > > Insert into > > cs_nyd_disg_ofi(cod_periodo,grupo_datos,numero_cliente,monto_a_compensar,di > ......pond_t_pro_e,di_pond_d_pro_e,nhs) > values(?,?,?,?,?,?,?,?,?,?....,?,?,?,?,?,?,?,?,?,?,?,?,?) > > > When I run, the checkpoint rises. Usually this time is 0 seconds, but > when this insert runs, the checkpoints rise to 5 seconds aprox. We > consider this a normal behaviour. > > But, when I run onstat -u each second, I notice that the nwrites don't > increase (this increase only after many seconds), but the nreads > increases quickly. > > c0000000a032f4f8 --BPX-- 3642148 luz - 0 > 0 5678 5953233 466518 > .. > c0000000a032f4f8 --BPX-- 3642148 luz - 0 > 0 5686 5975871 466518 > .. > c0000000a032f4f8 --BPX-- 3642148 luz - 0 > 0 5690 5985582 466518 > > > Is this behaviour normal ? Why the nreads increases if the sql that > shows onstat -g ses is an insert ? > > Thanks in advance > > Peter > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

As Art says what else is it doing; try to get an sqexplain; (should be possible using onmode nowadays) or get a sqlidebug... eq export SQLIDEBUG=2:/somedirwithloootttsofspace/somefile ./<run your program.> how does the schema look like maybe a trigger gets fired, (which may do a select without a where clause or?? dono) or it has zillion of indexes to maintain?? Superboer On 5 nov, 03:41, Art Kagel <art.ka...@gmail.com> wrote: > What else is the application doing?  Is it selecting data from other tables > before inserting?  Is the insert being used as part of a PUT CURSOR? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (a...@iiug.org) > Blog:http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Advanced DataTools, 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 Thu, Nov 4, 2010 at 7:03 PM, Peternt <roger_vi...@yahoo.es> wrote: > > Hi, > > I have a esql/c program that run this sql (output of onstat -g ses): > > >  Insert into > > > cs_nyd_disg_ofi(cod_periodo,grupo_datos,numero_cliente,monto_a_compensar,di > >    ......pond_t_pro_e,di_pond_d_pro_e,nhs) > >    values(?,?,?,?,?,?,?,?,?,?....,?,?,?,?,?,?,?,?,?,?,?,?,?) > > > When I run, the  checkpoint rises. Usually this time is 0 seconds, but > > when this insert runs, the checkpoints rise to 5 seconds aprox. We > > consider this a normal behaviour. > > > But, when I run onstat -u each second, I notice that the nwrites don't > > increase (this increase only after many seconds), but the nreads > > increases quickly. > > > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > > 0    5678  5953233  466518 > > .. > > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > > 0    5686  5975871  466518 > > .. > > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > > 0    5690  5985582  466518 > > > Is this behaviour normal ?  Why the nreads increases if the sql that > > shows onstat -g ses is an insert ? > > > Thanks in advance > > > Peter > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list

One more thing was a bit too trigger happy: Make sure TBLSPACE_STATS 1 in your onconfig if you can run this stand alone eq noone else accessing it during a test then maybe zero the counters (onstat -z) and run your stuff. then when done select * from sysmaster:sysptprof -->> check for nreads etc so now you can see what the engine is reading during your program. Superboer On 5 nov, 09:10, Superboer <superbo...@t-online.de> wrote: > As Art says what else is it doing; try to get an sqexplain; > > (should be possible using onmode nowadays) or get a sqlidebug... > > eq > > export SQLIDEBUG=2:/somedirwithloootttsofspace/somefile > > ./<run your program.> > > how does the schema look like > maybe a trigger gets fired, > (which may do a select without a where clause or?? dono) > or it has zillion of indexes to maintain?? > > Superboer > > On 5 nov, 03:41, Art Kagel <art.ka...@gmail.com> wrote: > > > What else is the application doing?  Is it selecting data from other tables > > before inserting?  Is the insert being used as part of a PUT CURSOR? > > > Art > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (a...@iiug.org) > > Blog:http://informix-myview.blogspot.com/ > > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > > do not reflect on my employer, Advanced DataTools, 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 Thu, Nov 4, 2010 at 7:03 PM, Peternt <roger_vi...@yahoo.es> wrote: > > > Hi, > > > I have a esql/c program that run this sql (output of onstat -g ses): > > > >  Insert into > > > > cs_nyd_disg_ofi(cod_periodo,grupo_datos,numero_cliente,monto_a_compensar,di > > >    ......pond_t_pro_e,di_pond_d_pro_e,nhs) > > >    values(?,?,?,?,?,?,?,?,?,?....,?,?,?,?,?,?,?,?,?,?,?,?,?) > > > > When I run, the  checkpoint rises. Usually this time is 0 seconds, but > > > when this insert runs, the checkpoints rise to 5 seconds aprox. We > > > consider this a normal behaviour. > > > > But, when I run onstat -u each second, I notice that the nwrites don't > > > increase (this increase only after many seconds), but the nreads > > > increases quickly. > > > > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > > > 0    5678  5953233  466518 > > > .. > > > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > > > 0    5686  5975871  466518 > > > .. > > > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > > > 0    5690  5985582  466518 > > > > Is this behaviour normal ?  Why the nreads increases if the sql that > > > shows onstat -g ses is an insert ? > > > > Thanks in advance > > > > Peter > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list

On Nov 4, 6:03 pm, Peternt <roger_vi...@yahoo.es> wrote: > Hi, > I have a esql/c program that run this sql (output of onstat -g ses): > >   Insert into > > cs_nyd_disg_ofi(cod_periodo,grupo_datos,numero_cliente,monto_a_compensar,di >     ......pond_t_pro_e,di_pond_d_pro_e,nhs) >     values(?,?,?,?,?,?,?,?,?,?....,?,?,?,?,?,?,?,?,?,?,?,?,?) > > When I run, the  checkpoint rises. Usually this time is 0 seconds, but > when this insert runs, the checkpoints rise to 5 seconds aprox. We > consider this a normal behaviour. > > But, when I run onstat -u each second, I notice that the nwrites don't > increase (this increase only after many seconds), but the nreads > increases quickly. > > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > 0    5678  5953233  466518 > .. > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > 0    5686  5975871  466518 > .. > c0000000a032f4f8 --BPX-- 3642148  luz      -        0 > 0    5690  5985582  466518 > > Is this behaviour normal ?  Why the nreads increases if the sql that > shows onstat -g ses is an insert ? > > Thanks in advance > > Peter Well - it is if there are indexes on the table ;-) We have to read through the index structure to figure out where to put in the new index item.