AW: selects on sysmaster are very slow
Dear list,
after rebooting the server the query performs between 18 and 58 seconds
which is not too fast but much faster than 30 minutes. Interesting: Before
the reboot the query retrieved 14455 rows, after the reboot only 10747 rows.
> Can you please send us the output of both onstat -g sql SID /
> onstat -g ses SID> during the select?
IBM Informix Dynamic Server Version 9.40.FC4W2 -- On-Line -- Up 01:27:22
-- 6493184 Kbytes
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
Explain
40919 SELECT sysmaster CR Not Wait 0 0 9.03 Off
Current statement name : slctcur
Current SQL statement :
select * from systabnames
Last parsed SQL statement :
select * from systabnames
IBM Informix Dynamic Server Version 9.40.FC4W2 -- On-Line -- Up 01:27:22
-- 6493184 Kbytes
session #RSAM total used
dynamic
id user tty pid hostname threads memory memory
explain
40919 informix 1 21027 db1 1 69632 65936
off
tid name rstcb flags curstk status
40981 sqlexec 2427ba700 ---PR-- 11103 yield aiowait
Memory pools count 1
name class addr totalsize freesize #allocfrag
#freefrag
40919 V 2596e6040 69632 3696 116 7
name free used name free used
overhead 0 3248 scb 0 144
opentable 0 4680 filetable 0 1336
log 0 2184 temprec 0 10104
keys 0 384 ralloc 0 10184
gentcb 0 1632 ostcb 0 2864
sqscb 0 17576 sql 0 72
rdahead 0 1120 hashfiletab 0 552
osenv 0 3880 buft_buffer 0 2160
sqtcb 0 3184 fragman 0 488
sapi 0 72
sqscb info
scb sqscb optofc pdqpriority sqlstats optcompind
directives
261754028 26bb6f028 0 0 0 1
1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers
Explain
40919 SELECT sysmaster CR Not Wait 0 0 9.03 Off
Current statement name : slctcur
Current SQL statement :
select * from systabnames
Last parsed SQL statement :
select * from systabnames
sid cumtime reason
40919 311891216,0000 condition
40919 60270460,00000 aio
40919 6584457,000000 mt ready
40919 2464614,000000 running
40919 14884,00000000 mutex
40919 10538,00000000 buffer
40919 10070,00000000 mt yield
Overall waitstates:
2844222607635 condition
28990175732,00 running
15850457074,00 buffer
12452310205,00 aio
11497394902,00 mt ready
4583042671,000 checkpoint
1376000545,000 mutex
771279106,0000 mt yield 0
22529029,00000 mt yield
2141490,000000 mt yield n
55340,00000000 lock
Regards,
Reinhard
> -----Ursprüngliche Nachricht-----
> Von: Bogdan Botez [mailto:bmbogdan@gmail.com]
> Gesendet am: Freitag, 30. März 2007 12:03
> An: Habichtsberg, Reinhard
> Betreff: Re: selects on sysmaster are very slow
>
> On 3/30/07, Habichtsberg, Reinhard
> <RHabichtsberg@arz-emmendingen.de> wrote:
> > Dear list,
> >
> > since 3 or 4 weeks selects on sysmaster esp. systabnames
> behave very, very
> > slow.
> >
> > A simple select:
> > select * from systabnames
> > into temp t1 with no log;> >
>
> Hello Reinhard,
>
> Can you please send us the output of both onstat -g sql SID /
> onstat -g ses SID> during the select? Also, from our experience, please make
> sure that the
> pdqpriority of that session is low.
>
> Regards,
> Bogdan BOTEZ.
>