一执行数据库就慢下来的sql，请问如何解决？

chiqieqie

SELECT   apa_file.*,apb_file.*,rvv03,apo02, apr02,  pmm22, pmn31, pmn04,
pmn041,       azi03, azi04, azi05  FROM apa_file,  OUTER (apb_file,OUTER
(pmn_file,pmm_file),OUTER rvv_file),  OUTER azi_file,OUTER apo_file,
OUTER apr_file WHERE apa01 = apb01   AND azi01 = apa13   AND apo01 = apa19
AND apr01 = apa36   AND pmn01 = apb06   AND pmn02 = apb07   AND rvv01 =
apb21   AND rvv02 = apb22   AND pmm01 = pmn01   AND apo01 = apa19 AND
apa00 = '22' AND apa41 = 'N'   AND apa42='N' AND apa01='RYT-YA0062'
ORDER By apa01；

这个sql一执行，其它用户就进不了数据库了，请高手帮忙看看如何优化，谢谢

liaosnet

进不了数据库有什么报错信息没?

chiqieqie

查看/u/informix/tmp/af.2e352469文件，里相关信息如下：

15:41:24  邏輯登錄 517547 - 備份完成
15:44:41  斷言失敗: Exception Caught. Type: MT_EX_OS, Context: mem
15:41:24  邏輯登錄 517547 - 備份完成
15:44:41  斷言失敗: Exception Caught. Type: MT_EX_OS, Context: mem
15:44:41  IBM Informix Dynamic Server Version 9.40.FC7
15:44:41
  使用者:Session(705317, [email=zh16879@zhdb01]zh16879@zhdb01[/email], 31689, c00000018a5c61a
  Thread(731725, sqlexec, c000000185aaa838, 1)
  File: mtex.c Line: 377
15:44:41   動作: 請通知 Informix 技術支援部門.
15:44:41  stack trace for pid 4936 written to /u/inf7.3/tmp/af.2e352469
15:44:42   請參閱: /u/informix/tmp/af.2e352469, shmem.2e352469.0

查看onstat -g ses 705317：
/u/inf7.3/bin/onstat -g ses 705317:

IBM Informix Dynamic Server Version 9.40.FC7     -- On-Line -- Up 33 days 03:10:15 -- 8305372 Kbytes

session                                      #RSAM    total      used       dynamic
id       user     tty      pid      hostname threads  memory     memory     explain
705317   zh16879 142      31689    zhdb01 1        344064     317800     off

tid      name     rstcb            flags    curstk   status
731725   sqlexec  c000000185aaa838 ---P---  61456    running

Memory pools    count 7
name         class addr              totalsize  freesize   #allocfrag #freefrag
705317       V     c00000018bc05040 229376     10984      224        23
705317*O0    V     c000000194fb2040 32768      3912       26         2
705317*O1    V     c00000019b8b3040 12288      840        9          1
705317*O2    V     c00000019ba1f040 20480      1864       16         2
705317*O3    V     c00000019bb6d040 20480      840        17         1
705317*O4    V     c00000019c25f040 16384      3912       10         2
705317*O5    V     c00000019c3ea040 12288      3912       6          2

name           free       used           name           free       used
overhead       0          22792          scb            0          208
opentable      0          5752           filetable      0          1016
log            0          2184           temprec        0          10104
keys           0          416            ralloc         0          240200
gentcb         0          1840           ostcb          0          3416
sqscb          0          22344          sql            0          72
rdahead        0          224            hashfiletab    0          552
osenv          0          2520           sqtcb          0          3808
fragman        0          352

sqscb info
scb              sqscb            optofc   pdqpriority sqlstats optcompind  directives
c00000018997e818 c0000001bccbd028 0        0           0        2           1

Sess  SQL            Current            Iso Lock       SQL  ISAM F.E.
Id    Stmt type      Database           Lvl Mode       ERR  ERR  Vers Explain
705317 SELECT         xyz          DR  Wait 1     0    0    7.20 Off

Current SQL statement :
  SELECT   apa_file.*,apb_file.*,rvv03,apo02, apr02,  pmm22, pmn31, pmn04,
    pmn041,       azi03, azi04, azi05  FROM apa_file,  OUTER (apb_file,OUTER
    (pmn_file,pmm_file),OUTER rvv_file),  OUTER azi_file,OUTER apo_file,
    OUTER apr_file WHERE apa01 = apb01   AND azi01 = apa13   AND apo01 = apa19
      AND apr01 = apa36   AND pmn01 = apb06   AND pmn02 = apb07   AND rvv01 =
    apb21   AND rvv02 = apb22   AND pmm01 = pmn01   AND apo01 = apa19 AND
    apa00 = '22' AND apa41 = 'N'   AND apa42='N' AND apa01='RYT-YA0062'
     ORDER By apa01

这个语句执行后，基本系统就hang住了，dbaccess进数据库都不行，整个机器相当慢，后来用onstat -g seg看发现共享内存虚拟段多了很多。

IBM Informix Dynamic Server Version 9.40.FC7     -- On-Line -- Up 71 days 01:42:40 -- 14654172 Kbytes

Segment Summary:
id       key        addr             size             ovhd     class blkused  blkfree
87047    1388660737 c00000000041d000 4294705152       554920   R*    1048512  0      
11       1388660741 c0000001003dd000 3125768192       96064    V     761686   1441   
15       1388660745 c0000001ba8d4000 4456448          808      M     1062     26      
16       1388660746 c0000001bad14000 4456448          808      M     1054     34      
17       1388660747 c0000001bb154000 4456448          808      M     1054     34      
18       1388660748 c0000001bb594000 4456448          808      M     1054     34      
19       1388660749 c0000001bb9d4000 4456448          808      M     1054     34      
20       1388660750 c0000001bbe14000 4456448          808      M     1054     34      
21       1388660751 c0000001bc254000 4456448          808      M     1054     34      
22       1388660752 c0000001bc694000 4456448          808      M     1054     34      
1513495  1388660753 c0000001bcad4000 209715200        7072     V     48471    2729   
6168     1388660754 c0000001c92d4000 209715200        7072     V     48858    2342   
6169     1388660755 c0000001d5ad4000 209715200        7072     V     48134    3066   
359962   1388660756 c0000001e22d4000 209715200        7072     V     48125    3075   
7707     1388660757 c0000001eead4000 209715200        7072     V     49424    1776   
7196     1388660758 c0000001fb2d4000 209715200        7072     V     48608    2592   
360477   1388660759 c000000207ad4000 209715200        7072     V     48230    2970   
8734     1388660760 c0000002142d4000 209715200        7072     V     48437    2763   
8735     1388660761 c000000220ad4000 209715200        7072     V     48545    2655   
8224     1388660762 c00000022d2d4000 209715200        7072     V     49736    1464   
7201     1388660763 c000000239ad4000 209715200        7072     V     48572    2628   
359970   1388660764 c0000002462d4000 209715200        7072     V     48263    2937   
7715     1388660765 c000000252ad4000 209715200        7072     V     48323    2877   
7204     1388660766 c00000025f2d4000 209715200        7072     V     48899    2301   
6181     1388660767 c00000026bad4000 209715200        7072     V     49487    1713   
4646     1388660768 c0000002782d4000 209715200        7072     V     48371    2829   
357927   1388660769 c000000284ad4000 209715200        7072     V     48623    2577   
5160     1388660770 c0000002912d4000 209715200        7072     V     48389    2811   
4649     1388660771 c00000029dad4000 209715200        7072     V     48389    2811   
4650     1388660772 c0000002aa2d4000 209715200        7072     V     48832    2368   
4139     1388660773 c0000002b6ad4000 209715200        7072     V     49391    1809   
3628     1388660774 c0000002c32d4000 209715200        7072     V     48127    3073   
365101   1388660775 c0000002cfad4000 209715200        7072     V     48194    3006   
4142     1388660776 c0000002dc2d4000 209715200        7072     V     48126    3074   
4143     1388660777 c0000002e8ad4000 209715200        7072     V     48172    3028   
3632     1388660778 c0000002f52d4000 209715200        7072     V     48545    2655   
3633     1388660779 c000000301ad4000 209715200        7072     V     49252    1948   
3122     1388660780 c00000030e2d4000 209715200        7072     V     48426    2774   
364083   1388660781 c00000031aad4000 209715200        7072     V     48253    2947   
4148     1388660782 c0000003272d4000 209715200        7072     V     48006    3194   
4149     1388660783 c000000333ad4000 209715200        7072     V     48378    2822   
3638     1388660784 c0000003402d4000 209715200        7072     V     48304    2896   
3639     1388660785 c00000034cad4000 209715200        7072     V     48065    3135   
2104     1388660786 c0000003592d4000 209715200        7072     V     48186    3014   
2105     1388660787 c000000365ad4000 209715200        7072     V     48954    2246   
2106     1388660788 c0000003722d4000 209715200        7072     V     36490    14710   
Total:   -          -                15005872128      -        -     3554223  109320

[ 本帖最后由 chiqieqie 于 2009-11-23 14:22 编辑 ]

liaosnet

帖下/u/informix/tmp/af.2e352469 文件前面的stack部分~

做下explain计划~
dbaccess dbname -
set explain avoid_execute;
SELECT   apa_file.*,apb_file.*,rvv03,apo02, apr02,  pmm22, pmn31, pmn04,
pmn041,       azi03, azi04, azi05  FROM apa_file,  OUTER (apb_file,OUTER
(pmn_file,pmm_file),OUTER rvv_file),  OUTER azi_file,OUTER apo_file,
OUTER apr_file WHERE apa01 = apb01   AND azi01 = apa13   AND apo01 = apa19
AND apr01 = apa36   AND pmn01 = apb06   AND pmn02 = apb07   AND rvv01 =
apb21   AND rvv02 = apb22   AND pmm01 = pmn01   AND apo01 = apa19 AND
apa00 = '22' AND apa41 = 'N'   AND apa42='N' AND apa01='RYT-YA0062'
ORDER By apa01;

chiqieqie

15:44:41  Stack for thread: 731725 sqlexec

base: 0xc0000001ec542000
  len:   69632
   pc: 0x0000000000000000
  tos: 0xc0000001ec544640
state: running
   vp: 1

( 0)  0x400000000089fc08   legacy_hp_afstack + 0x258  [/u/inf7.3/bin/oninit]
( 1)  0x400000000089f25c   afstack + 0x5c  [/u/inf7.3/bin/oninit]
( 2)  0x400000000089e4ec   afhandler + 0x984  [/u/inf7.3/bin/oninit]
( 3)  0x400000000089da98   affail_interface + 0x50  [/u/inf7.3/bin/oninit]
( 4)  0x40000000008a448c   mt_ex_throw_sig + 0x19c  [/u/inf7.3/bin/oninit]
( 5)  0x4000000000867a48   afsig_handler + 0xf8  [/u/inf7.3/bin/oninit]
( 6)  0xc0000000003f99d0   _sigreturn  [/usr/lib/pa20_64/libc.2]
( 7)  0x40000000003f774c   op_2phase_prune + 0x3dc  [/u/inf7.3/bin/oninit]
(   0x40000000003f5014   opprune + 0x19c  [/u/inf7.3/bin/oninit]
( 9)  0x40000000003f6368   op_join + 0x358  [/u/inf7.3/bin/oninit]
(10)  0x40000000003f7be0   op_phasejoin + 0x360  [/u/inf7.3/bin/oninit]
(11)  0x4000000000357144   sqoptim + 0x14d4  [/u/inf7.3/bin/oninit]
(12)  0x400000000048f2b8   bldstructs + 0x148  [/u/inf7.3/bin/oninit]
(13)  0x400000000048f070   sqcmd + 0x250  [/u/inf7.3/bin/oninit]
(14)  0x400000000048eb4c   sq_cmnd + 0x94  [/u/inf7.3/bin/oninit]
(15)  0x400000000048ec70   sq_prepare + 0x40  [/u/inf7.3/bin/oninit]
(16)  0x4000000000521080   sqmain + 0x128  [/u/inf7.3/bin/oninit]
(17)  0x4000000000876a74   startup + 0xd4  [/u/inf7.3/bin/oninit]
(1  0x400000000087685c   resume + 0x10c  [/u/inf7.3/bin/oninit]

base: 0xc0000001ec542000
  len:   69632
   pc: 0x0000000000000000
  tos: 0xc0000001ec544640
state: running
   vp: 1

15:44:42   请参阅: /u/inf7.3/tmp/af.2e352469, shmem.2e352469.0

---------------------------------
Begin System Alarm Program Output
---------------------------------

Assertion Failure Type: FAILURE
Host Name:              chnyts01
Database Server Name:   on_tcp111
Time of failure:        Fri Oct 16 15:44:42 EAT 2009
AF file:                /u/inf7.3/tmp/af.2e352469
Shared memory file:     None
System Blocking:        OFF

3sane

哇，好多外连接，条件里apa00 = '22' AND apa41 = 'N'   AND apa42='N' AND apa01='RYT-YA0062'相关的记录多吗？考虑先过滤成临时表再进行外连接？另外建议先看看连接项是否都有索引支持，包括其他条件，红色的V段是执行后产生的吗？如果限制共享内存的大小，则不至于把系统拖垮，不过这个任务就更慢。

MicroHard

外联接太多了，真不知哪个破强人设计出这样的数据库来了，SQL建议改为如下试试：
select pmn01, pmn02, pmm22, pmn31, pmn04, pmn041
from pmn_file, pmm_file
where pmm01 = pmn01
into temp tmp1 with no log;

select apb_file.*, rvv_file.rvv03, tmp1.pmm22, tmp1.pmn31, tmp1.pmn04, tmp1.pmn041
from apb_file, outer tmp1, outer rvv_file
where apb_file.apb06 = tmp1.pmn01 and apb_file.apb07 = tmp1.pmn02
  and apb_file.apb21 = rvv_file.rvv01 and apb_file.apb22 = rvv_file.rvv02
into temp tmp2 with no log;

select apa_file.*, tmp2.*, apo02, apr02, azi03, azi04, azi05
from apa_file, outer tmp2, outer azi_file, outer apo_file, outer apr_file
where apa01 = tmp2.apb01 AND azi01 = apa13 AND apo01 = apa19
  AND apr01 = apa36 AND apo01 = apa19
  AND apa00 = '22' AND apa41 = 'N' AND apa42='N' AND apa01='RYT-YA0062'
ORDER By apa01;

另外，如果apa_file记录数很多，可以考虑使用并行查询。