informix数据库查询数据很慢
本帖最后由 y331044508 于 2012-04-09 13:56 编辑请高手帮忙看看，谢谢。其中CPU time有的时间很长，但不知道怎么回事，还请多指教。
onstat -g cpu
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 5 days 02:13:00 -- 19804032 Kbytes
Thread CPU Info:
tid name vp Last Run CPU Time #scheds status
2 lio vp 0 14lio* 04/04 09:20:42 0.0000 1 IO Idle
3 pio vp 0 15pio* 04/04 09:20:43 0.1098 2 IO Idle
4 aio vp 0 16aio* 04/09 11:33:33 204430.0616 52148062 running
5 msc vp 0 17msc* 04/09 11:33:32 974.6616 4204236 IO Idle
6 fifo vp 0 18fifo*04/04 09:20:46 0.1099 2 IO Idle
7 aio vp 1 19aio* 04/09 11:33:33 159125.3601 22498096 IO Idle
tnmsdb#onstat -g rea
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 5 days 02:13:23 -- 19804032 Kbytes
Ready threads:
tid tcb rstcb prty status vp-class name
2815110 c0000001f4933b28 c0000001ec26abc8 1 ready 10cpu sqlexec
2926524 c00000020902ead0 c0000001e6c84388 1 ready 1cpu sqlexec
4531729 c00000021973c450 c0000001fd1fee08 1 ready 3cpu sqlexec
6641714 c0000002246f4028 c00000020b791da8 1 ready 8cpu sqlexec
7279783 c00000022783f658 c0000001e6c808e8 1 ready 5cpu sqlexec
7294491 c00000022644c868 c0000001e6c91d48 1 ready 6cpu sqlexec
7299960 c000000223c01970 c0000001fd1f8988 1 ready 10cpu sqlexec
7389153 c000000229f5e398 c0000001fd1f0388 1 ready 10cpu sqlexec
8943289 c00000022f517188 c0000001e6c7b528 1 ready 10cpu sqlexec
8948463 c000000228a050d8 c0000001fd1fdd48 1 ready 4cpu sqlexec
8978353 c00000022e057238 c00000020b7936c8 1 ready 10cpu sqlexec
tnmsdb#onstat -g wai
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 5 days 02:13:34 -- 19804032 Kbytes
Waiting threads:
tid tcb rstcb prty status vp-class name
2 c0000001e803abe8 0 1 IO Idle 14lio* lio vp 0
3 c0000001e80716c0 0 1 IO Idle 15pio* pio vp 0
2815102 c000000207b0cb80 c00000020b7811a8 1 cond waitnetnorm 8cpu sqlexec
2815103 c0000001ea674188 c0000001ef681308 1 cond waitnetnorm 5cpu sqlexec
2815104 c0000002033300d8 c0000001ef67e928 1 cond waitnetnorm 1cpu sqlexec
2815109 c0000001f27d26b8 c0000001fd1f2508 1 cond waitnetnorm 12cpu sqlexec
2815110 c0000001f4933b28 c0000001ec26abc8 1 IO Wait 10cpu sqlexec
2815173 c00000020a134978 c0000001fd1fe5a8 1 cond waitnetnorm 6cpu sqlexec
2815182 c0000001e9c00888 c0000001fd1fbbc8 1 cond waitnetnorm 5cpu sqlexec
tnmsdb#onstat -g ioq
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 5 days 02:13:49 -- 19804032 Kbytes
AIO I/O queues:
q name/id len maxlen totalopsdskread dskwritedskcopy
fifo 0 0 0 0 0 0 0
drda_dbg 0 0 0 0 0 0 0
sqli_dbg 0 0 0 0 0 0 0
kio 0 0 66 29549456 263413193208137 0
kio 1 0 65 30971650 271517163819934 0
kio 2 0 65 60294168 566255033668665 0
kio 3 0 65 35834100 315774854256615 0
kio 4 0 65 29532375 271687452363630 0
kio 5 0 47 31878715 286372183241497 0
kio 6 0 50 42210137 386097093600428 0
tnmsdb#onstat -g seg
IBM Informix Dynamic Server Version 11.50.FC6 -- On-Line -- Up 5 days 02:13:59 -- 19804032 Kbytes
Segment Summary:
id key addr size ovhd class blkusedblkfree
7208964 52564801 c000000001208000 7990767616 94079440 R 19508692
327687 52564802 c0000001dd69f000 12288000000 144001944 V 311138 2688862
262152 52564803 c0000004b9d72000 561152 7848 M 136 1
Total: - - 20279328768 - - 22621432688865 这些信息对查询很慢这个问题的判断没有什么实际意义～～
回复 2# liaosnet
那需要看那些东西。。。系统IO吗？
tnmsdb#sar -d 1 100
HP-UX tnmsdb B.11.23 U 9000/800 04/09/12
11:49:58 device %busy avque r+w/sblks/savwaitavserv
11:49:59 c0t6d0 0.97 0.50 1 2 0.00 7.21
c6t0d1 98.06 0.52 1889 28439 0.17 3.18
c6t0d6 92.23 1.78 370 2183 1.18 4.79
11:50:00 c6t0d1100.00 0.53 1545 23576 0.06 2.99
c6t0d6 96.97 2.73 391 2145 3.12 6.57
11:50:01 c0t6d0 25.74 0.50 41 370 0.00 6.58
c3t6d0 16.83 0.50 26 311 0.00 6.62
c6t0d1 97.03 0.65 700 11008 1.13 8.82
c12t0d4 0.99 0.50 3 129 0.00 0.69
c6t0d6 96.04 2.25 137 796 18.04 28.15
11:50:02 c0t6d0 5.05 0.50 10 103 0.00 9.55
c3t6d0 4.04 0.50 6 87 0.00 11.81
c6t0d1100.00 0.86 119 2216 3.90 62.59
c12t0d5 1.01 0.50 1 2 0.00 5.74
c6t0d6100.00 5.47 60 622 97.66 90.32
c6t0d5 1.01 0.50 1 2 0.00 9.67
c8t0d5 1.01 0.50 1 2 0.00 8.15
c10t0d5 1.01 0.50 1 2 0.00 11.76
11:50:03 c0t6d0 1.01 0.50 2 20 0.00 5.55
c6t0d1100.00 0.60 676 9766 0.55 9.29
c6t0d6100.00 1.62 220 1119 1.39 11.46
11:50:04 c0t6d0 1.98 0.50 4 51 0.00 12.11
IO繁忙，CPU忙。。
你的数据库查询没建索引的吗？？ 回复 4# liaosnet
索引是有的，没索引系统早就完蛋了。 informix 使用什么命令可以查询到那个SQL正在进行全表扫描查询 在忙的时候，直接输出onstat -g ses 0 和onstat -g athonstat -g act onstat -g glo 和top信息。。。
对着查忙的CPU上跑的是什么SQL 可以抓一下select sqx_sessionid,sqx_estcost,sqx_sqlstatement from sysmaster:syssqexplain order by sqx_estcost desc 多谢各位的热心帮助，最后我使用以下方式总于找到出问题的SQL；
select sqx_sessionid,sqx_estcost,sqx_seqscan,sqx_sqlstatement
from sysmaster:syssqexplain order by sqx_estcost desc;
多执行几次，找到了那条执行成本最高的SQL，然后查看了这个SQL的执行计划，发现里面有个字段没有创建索引；
索引加上后效果明显改变了。
页:
[1]